Simple ASP.NET Autocomplete

Autocomplete can easily be added to an ASP.NET TextBox control using the jQuery UI. Here I will explain how this can be implemented using an ASHX generic handler to access a data source containing the suggestions. A SQL Server database is used as this data source.

Firstly, jQuery is used on the page containing the text box. Enabling the autoFocus option places focus on the first item in the menu. The delay specifies how many milliseconds should be waited between a keystroke and a search being made. This should not be set too low if a database is used as it could place a high load upon it. The minLength sets how many characters should be entered before autocomplete is used. The JavaScript for this example is shown below:

$(function () {
    $("#txtSearch").autocomplete({
        source: '/Autocomplete.ashx',
        autoFocus: true,
        delay: 500,
        minLength: 1
    });
});

The handler accepts the term entered into the text box as a single GET parameter, which is then used in the SQL query. The name of the database table is specified as well as the specific column name. Finally, the number of results to return can be defined. This is the C# used for this:

public void ProcessRequest(HttpContext context)
{
    // Custom variables
    const string columnName = "FirstName";
    const string tableName = "Names";
    const int resultsToDisplay = 10;
    string sqlStatement = "SELECT DISTINCT TOP " + resultsToDisplay + " " + columnName + " FROM " + tableName + " WHERE " + columnName + " LIKE @term + '%' ORDER BY " + columnName + " ASC";

    // Create DataTable of results
    context.Response.ContentType = "application/javascript";
    DataTable dataTable = ExecuteStatement(sqlStatement, context.Request.QueryString["term"]);

    // Add DataTable items to ArrayList
    ArrayList items = new ArrayList();
    foreach (DataRow row in dataTable.Rows)
    {
        items.Add(row[columnName]);
    }

    // Convert the ArrayList to a string array, serialise to Javascript and write back
    context.Response.Write(new JavaScriptSerializer().Serialize(items.ToArray(typeof(string))));
}

An ArrayList of suggestions is created from the DataTable of results, and is serialised before being returned to the JavaScript and displayed. This method can be customised to suit the project, for example by substituting the database with an alternative data source.

Source code for Autocomplete example.

«