Tuesday, November 17, 2015

jQuery UI AutoComplete TextBox with asp.net webforms



Method 1 (using Handlers)


Connection String
<connectionStrings>
      <addname="constr"connectionString="Data Source = .\SQLExpress;       
       Initial Catalog = Northwind; Integrated Security = true"/>
</connectionStrings>

  
AutoComplete Handler
<%@ WebHandler Language="C#" Class="Search_CS" %>
using System;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
public class Search_CS : IHttpHandler {
   
    public void ProcessRequest (HttpContext context) {
        string prefixText = context.Request.QueryString["q"];
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager
                    .ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select ContactName from Customers where " +
                "ContactName like @SearchText + '%'";
                cmd.Parameters.AddWithValue("@SearchText", prefixText);
                cmd.Connection = conn;
                StringBuilder sb = new StringBuilder();
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        sb.Append(sdr["ContactName"])
                            .Append(Environment.NewLine);
                    }
                }
                conn.Close();
                context.Response.Write(sb.ToString());
            }
        }
    }
    public bool IsReusable {
        get {
            return false;
        }
    }
}

Client Side Implementation
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
<link href="css/jquery.autocomplete.css" rel="stylesheet" type="text/css" />
<script src="scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<script src="scripts/jquery.autocomplete.js" type="text/javascript"></script>
<script type="text/javascript">
    $(document).ready(function() {
        $("#<%=txtSearch.ClientID%>").autocomplete('Search_CS.ashx');
    });      
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    </div>
    </form>
</body>
</html>

Javascript
<script type="text/javascript">
    $(document).ready(function() {
        $("#<TextBoxControlId>").autocomplete('<Url of handler>');
    });      
</script>
Example AJAX Autocomplete Extender using jQuery in ASP.Net



Method 2 (using web Methods)


Aspx page
protected void Page_Load(object sender, EventArgs e)
{

}

[WebMethod]
public static List<string> GetAutoCompleteData(string username)
{
List<string> result = new List<string>();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
using (SqlCommand cmd = new SqlCommand("select DISTINCT UserName from UserInformation where UserName LIKE '%'+@SearchText+'%'", con))
{
con.Open();
cmd.Parameters.AddWithValue("@SearchText", username);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
result.Add(dr["UserName"].ToString());
}
return result;
}
}
}


Javascript in Head section of the page 
 
$(".autosuggest").autocomplete({
source: function(request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetAutoCompleteData",
data: "{'username':'" + document.getElementById('txtSearch').value + "'}",
dataType: "json",
success: function(data) {
response(data.d);
},
error: function(result) {
alert("Error");
}
});
}
});