Creating a Database Driven Auto Complete TextBox Using jQuery for ASP.NET Websites

• Download source code

Introduction

This tip talks about creating an auto complete textbox using jQuery. The data will be fetched from the server side and pushed to the client side and will be displayed to the user as auto suggest. Previously, I had written a tip for AutoCompleteList using AJAX control toolkit. Now I tried AutoCompleteList using jQuery.

Background

There are many scenarios for using AutoCompleteList through which suggestions are displayed on keyup event in Textbox. This tip describes how AutoCompleteList works using jQuery provided in ASP.NET. This tip is written from a beginner's point of view.

Using the Code

Let us see how this small application works by looking at a small example. We will try to implement a small project and will incorporate the auto complete functionality in that.

Let us start with the database design for the sample application. We are creating a simple database with single table named tblCountry. It consists of the following two columns:

1. CountryID which is an identity field
2. CountryName which is nvarchar(50) datatype

Let us fill this database with some sample data for testing purposes.

Now we have a small database ready. Let us now see how the server side code will extract the data from this. We have a function named BindName() in which the details filled in a database table can be simply viewed. Then we will declare output object of StringBuilder class in which we will try to append the CountryName in a comma separated list as ("item1","item2") and then finally we will push it to client side so that it can be used via jQuery/JavaScript.

private string BindName()
{
    DataTable dt = null;
    using (conn = new SqlConnection
                  (ConfigurationManager.ConnectionStrings["tempdbConn"].ConnectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select CountryName from tblCountry";
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                dt = new DataTable();
                da.Fill(dt);
            }
        }
    }
   
    StringBuilder output = new StringBuilder();
    output.Append("[");
    for (int i = 0; i < dt.Rows.Count; ++i)
    {
        output.Append("\"" + dt.Rows[i]["CountryName"].ToString() + "\"");

        if (i != (dt.Rows.Count - 1))
        {
            output.Append(",");
        }
    }
    output.Append("];");

    return output.ToString();
}

Now in our PageLoad event, we will call the BindName function and assign the values to publicly defined string Datatype listFilter. This listFilter variable is defined publicly (as a class member variable) because we will use it in an .aspx page (i.e. markup) so that it can be accessible from client side scripts.

public string listFilter = null;

protected void Page_Load(object sender, EventArgs e)
{
    listFilter = BindName();
}

Now let us create a function named LoadList() on client side which will access this data and proceed further.

function LoadList()
{        
    var ds=null;
    ds = <%=listFilter %>

To call the loadlist function, the body tag is the perfect place so that we can be rest assured that this function will be called when the body of the HTML page is getting loaded.

onload="LoadList()

Now we have the basic skeleton in place. Now when we run the application, the serverside code will fetch the data and will put it on client side so that it can be used from the client side. The body of HTML when loaded will associate this data with the auto complete functionality.

The user will then be able to see the suggestions whenever he types in the associated textbox.

And we have an auto complete text box ready. The only catch in using this approach is that all the data from the table is being written on client side before it is being rendered. This is unlike the webservice approach where we can get the filtered data using the LIKE operator and process them at client side. But this approach could be useful when we have small amount to data in auto complete suggestions and writing a web service and having the extender controls seems like an overkill.

Points of Interest

This tip describes how AutoCompleteList works using Jquery provided in ASP.NET. This tip is written from a beginner's point of view. I tried to keep it simple so that auto suggest feature needed with small data can be put in place without having to create web services. This is one of the approaches. I will try to incorporate more features in this small application and to enhance further with other methods of doing the same thing.

History

• 24^th January 2013: First version