Anthem.NET [AJAX] Autosuggest Textbox Redux

by Peter A. Bromberg, Ph.D.

Peter Bromberg

"It is practically impossible to teach good programming to students that have had a prior exposure to BASIC: as potential programmers they are mentally mutilated beyond hope of regeneration. " -- Edsger Dijkstra

Back in April of 2006, I offered a revision of JC Murphy's Anthem AutoSuggest TextBox control here. There were some comments about my article on the Anthem.Net discussion forum (which comments I did read!), mostly revolving around the security implications of my putting the connection string in as a property of my revised control.



That's a valid concern; we can move the connection string to the appSettings section of the web.config and simply specify it's key name as a property and solve that issue "right quick". Recently the developers on the SourceForge Project for Anthem.Net (who, by the way, are doing a terrific job) decided to provide an AnthemExtensions namespace with a separate source code branch to allow developers to add proposed "extensions" to the base Anthem.Net library. That's a great idea, since it preserves the "pure" base Anthem implementation and still allows for enhancements. To his credit, Jason Diamond has steadfastly protected the base library from the myriad of well-intentioned although often wild requests and suggestions of various developers for their particular "Pet Enhancement". The result is that you have a base library that has remained immune to "feature creep" while still allowing extensions. The two extensions currently in the source tree are the EditLabel, and the AutosuggestTextBox.

First, if you aren't familiar with Anthem.Net, I suggest you visit the home page now. Anthem.Net is a Remote Scripting ("AJAX") ASP.NET Library. It's lightweight, and although ATLAS is much more sophisiticated, for many developers Anthem.Net will provide everything you could possibly want and more. It works with ASP.NET 1.1 and 2.0 -- all from the same source code tree because of conditional compilation directives.

The developers took some of my suggestions from the previous article, but they left out the main one, which I consider to be critical to a really usable control: Each typed character should result in a brand new query to the database, rather than filtering the entire datasource which would have to have been loaded at the start. I say this for obvious reasons - if you have a database table of 10,000 employees (Northwind had an IPO or something) and you wanted to have an AuotoSuggestTextBox that pulled from this table the matching names to your typed query term, you would need to load all 10,000 rows at the beginning with the current arrangement, which, to me, seems kind of ridiculous.

So here I offer a revised control: It uses OLEDB, which means you can use more databases (including SQL Server 2005 with the SQLNCLI provider, or even MS Access if you like). You specify the connection by naming the appSettings key name where you've stored the connection string. This not only hides the connection string from prying eyes, it also allows you to specify as many different connections as you like for different controls.

You can use either a stored procedure or a text command, which is specified as a property of the control, just like in my original implementation, and in addition we cache the data based on a unique key to avoid extra database round trips. I have arranged my modifications "around" the existing control so that its basic structure is not changed, and it works with both the Visual Studio.NET 2003 and the Visual Studio 2005 versions of Anthem.Net.

With that in mind, let's take a look at the key method in my revised Anthem.Net AutoSuggestTextBox:


private void GetData(string filter)

{

// create a unique cache key based on Request path and filter term

string cacheKey=this.Page.Request.FilePath+filter;

// is this data already in the Cache?

if (HttpContext.Current.Cache[cacheKey] != null)

{

this.DataSource = (DataSet)HttpContext.Current.Cache[cacheKey];

return;

}

// data is not cached, go get it and store in cache-

else

{

this._connectionString = System.Configuration.ConfigurationSettings.AppSettings[this._appSettingsKey];

OleDbConnection conn = new OleDbConnection(this._connectionString);

OleDbCommand cmd = new OleDbCommand(this._commandText, conn);

if (this._isStoredProcedure)

{

cmd.Parameters.Add(new OleDbParameter("@filter", filter));

cmd.CommandType = CommandType.StoredProcedure;

}

else

{

cmd.CommandText = cmd.CommandText.Replace("@filter", filter);

cmd.CommandType = CommandType.Text;

}

try

{

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

this._dataSource = ds;

if(ds.Tables[0].Rows.Count >0)

HttpContext.Current.Cache[cacheKey] = ds;

}

catch (Exception ex)

{

throw ex;

}

}

 

}

The above method is called from the pre-existing BindData method, which is called from the OnKeyUp method. This is what provides your dynamic "Typeahead" functionality when a letter is typed into the TextBox. Notice that we can have a text command or a stored procedure, and that the data is cached so that subsequent typeahead requests won't have to go to the database. Finally, you have new properties in the control- "AppSettingsKey", which names the particular appSettings section key that holds the connection string to be used with this control instance, "IsStoredProcedure", which tells the control whether to use a stored procedure, and "CommandText" - which provides the custom SQL statement or stored proc name. The SQL (And the sproc) need to return the results aliased as "Name" and use a filter parameter named "@filter". Otherwise you're free to do anything you want with the SQL - table joins, etc. Here's a sample SQL Statement and also a sample sproc for Northwind:

SQL STATEMENT:

SELECT LASTNAME +','+FIRSTNAME AS NAME FROM EMPLOYEES WHERE LASTNAME LIKE '@filter +'%'

SPROC:

CREATE PROC dbo.GetEmployeeNamesFiltered
@filter varchar(10)
AS
SELECT LASTNAME +','+FIRSTNAME AS [NAME] FROM EMPLOYEES
WHERE LASTNAME LIKE @filter+'%'
ORDER BY LASTNAME

Your appSettings connection string might look like this (using the SQLNCLI Provider for OleDb):

<add key="autoSuggestConnectionString" value="server=(local);Integrated Security=SSPI;database=northwind;provider=SQLNCLI.1"/>

The "autosuggestConnectionString" key name would be specified in the "AppSettingsKey" property of the control on the page, in the Property sheet for the control.

The downloadable solution contains the entire Anthem.Net Source tree, with solutions for both Visual Studio 2005 (WebSite project) and Visual Studio.NET 2003 (need to make a virtual directory for the "Anthem-Examples-2003" folder). Enjoy.

Download the combined Visual Studio.NET 2003 and 2005 Solution for this article



Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.
Article Discussion: