jQuery Mobile AutoComplete in ASP.NET with SQL Server Database Connection

• Download source - 748.4 KB
• Download SQL_DB - 198.3 KB

Introduction

This article shows how to use jQuery mobile autocomplete in ASP.NET using generic handler (.ashx) file with database connection.

In this example, autocomplete uses a database comprised of strings and callback data with mouseClick() event.

Background

Autocomplete can be customized to work with various data sources, by just specifying the source option. A data source can be:

An Array with local data

The local data can be a simple Array of Strings, or it can contain Objects for each item in the array, with either a label or value property or both. The label property would be displayed in the suggestion menu. The value will be inserted into the input element after the user selected something from the menu. If just one property is specified, it will be used for both, e.g., if you provide only value-properties, the value will also be used as the label.

A String, specifying a URL

When a String is used, the Autocomplete plugin expects that string to point to a URL resource that will return JSON data. It can be on the same host or on a different one (if different, must provide JSONP). The request parameter "term" gets added to that URL. The data itself can be in the same format as the local data as above.

A Callback Function

The third variation, the callback, provides the most flexibility, and can be used to connect any data source to Autocomplete. The callback gets two arguments:

A request object, with a single property called "term", which refers to the value currently in the text input. For example, when the user entered "tu" in a country field, the Autocomplete term will equal "tu".

A response callback, which expects a single argument to contain the data to suggest to the user. This data should be filtered based on the provided term, and can be in any of the formats described above for simple local data (String-Array or Object-Array with label/value/both properties).

It's important when providing a custom source callback to handle errors during the request. You must always call the response callback even if you encounter an error. This ensures that the widget always has the correct state.

Using the Code

First of all, extract "SQL DB.zip" file and attach database files to your SQL Server (jQueryMobileAutoComplete.mdf, jQueryMobileAutoComplete_log.ldf files) or create another database.

Here is an SQL table script that you need:

USE [jQueryMobileAutoComplete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Countries](
	[CountryID] [int] IDENTITY(1,1) NOT NULL,
	[CountryName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED 
(
	[CountryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

The front end code with these methods is very light and simple.

Add new "Generic Handler" to your project as a name of "Search.ashx" file. Actually, .ashx is an HTTP handler; you can create a handler using IHttpHandler, register this in the section, and use this to process requests.

When the handler is requested by the autocomplete plugin, it puts the typed in term on the querystring as a "term" item. Our code simply needs to grab that and use it in a SQL LIKE statement to get the matching resultset. We then convert the values to a string array, pass it into the JavaScriptSerializer to convert to JSON, and send it on out as ContentType "application/javascript". The plugin on the page will then display the choices and the user can select one. When this is done, the selected choice is populated into the <ul> element.

///
/// Search.ashx 
///
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;

namespace jQueryMobileAutoComplete
{
    /// <summary>
    /// Summary description for Search
    /// </summary>
    public class Search : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            if (!String.IsNullOrEmpty(context.Request.QueryString["term"].ToString()))
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBCONNSTRING"].ToString();
                SqlConnection sqlconn = new SqlConnection(connStr);
                SqlCommand sqlcmd = new SqlCommand();

                try
                {
                    if (sqlconn.State == ConnectionState.Closed)
                    {
                        sqlconn.Open();
                    }

                    sqlcmd.Connection = sqlconn;
                    sqlcmd.CommandType = CommandType.Text;
                    sqlcmd.CommandText = "SELECT top 10 x.CountryName as cn " + 
                      "FROM Countries as x WHERE x.CountryName LIKE '%' + @cn + '%'";
                    sqlcmd.Parameters.AddWithValue("@cn", 
                      context.Request.QueryString["term"].ToString());
                    
                    sqlcmd.ExecuteNonQuery();

                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        string[] items = new string[dt.Rows.Count];
                        int ctr = 0;
                        foreach (DataRow row in dt.Rows)
                        {
                            items[ctr] = (string)row["cn"];
                            ctr++;
                        }

                        //convert the string array to Javascript and send it out
                        context.Response.Write(new JavaScriptSerializer().Serialize(items));
                    }
                    if (sqlconn.State == ConnectionState.Open)
                    {
                        sqlcmd.Dispose();
                        sqlconn.Close();
                        sqlconn.Dispose();
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    if (sqlconn.State == ConnectionState.Open)
                    {
                        sqlcmd.Dispose();
                        sqlconn.Close();
                        sqlconn.Close();
                    }
                }
            }
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

And we are done! Run your project:

Points of Interest

jQuery Mobile does not support multiple tag selection yet.

Conclusion

So in this way, you can search and select data with jQuery mobile autocomplete in ASP.NET using generic handler (.ashx) file.

History

• 6^th December, 2013: Initial post
• 12^th December, 2013: Updated post

References

• http://www.jquerymobile.com/
• http://www.andymatthews.net/read/2012/03/27/jQuery-Mobile-Autocomplete-now-available