Article

Jquery Autocomplete Textbox in Asp.net with Database using C#

11 Jun 2015 Kamal Pratap
0 Comments 1714 Views



Here we explain How to populate jquery autocomplete textbox in asp.net with database using c#. This method is more effective than autocomplete extender because we use jquery instead of ajax it runs on client side to produce result fastly.

Jquery Autocomplete Textbox in Asp.net with Database using C#

Database :

Jquery Autocomplete Textbox in Asp.net with Database using C#

CREATE TABLE [dbo].[STUDENT](
	[StudentID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[RollNumber] [decimal](10, 2) NULL,
	[Class] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[STUDENT] ON
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (1, N'Andy', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (7, N'Anar', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (3, N'Bill', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (8, N'Amit', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (9, N'Anjali', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (6, N'Chris', CAST(300.00 AS Decimal(10, 2)), N'MBBS')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (10, N'Avinash', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (11, N'Rohan', CAST(200.00 AS Decimal(10, 2)), N'BCA')
SET IDENTITY_INSERT [dbo].[STUDENT] OFF

Connection string in Web.config :

<connectionStrings>
    <add name="Web" connectionString="Data Source=*;Initial Catalog=dbname;uid=sa;pwd=sql" providerName="System.Data.SqlClient"/>
</connectionStrings>

Asp.net :

<head runat="server">
    <title>Auto Complete Textbox Using JQuery</title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js" type="text/javascript"></script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"
        type="text/javascript"></script>
    <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css"
        rel="Stylesheet" type="text/css" />
    <script type="text/javascript">
        $(document).ready(function () {
            $("#<%=txtStudent.ClientID %>").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: '<%=ResolveUrl("~/WebService.asmx/GetStudents") %>',
                        data: "{ 'prefix': '" + request.term + "'}",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('-')[0],
                                    val: item.split('-')[1]
                                }
                            }))
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
                select: function (e, i) {
                    $("#<%=hfId.ClientID %>").val(i.item.val);
                },
                minLength: 1
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:TextBox ID="txtStudent" runat="server"></asp:TextBox>
    <asp:HiddenField ID="hfId" runat="server" />
    
    </form>
</body>
</html>

WebSercice.cs :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Script.Services;

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    public WebService()
    {
        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string[] GetStudents(string prefix)
    {
        List<string> students = new List<string>();
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Web"].ConnectionString;
           
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select Name, StudentID from STUDENT where Name like @prefix + '%'";
                cmd.Parameters.AddWithValue("@prefix", prefix);
                cmd.Connection = conn;
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        students.Add(string.Format("{0}-{1}", sdr["Name"], sdr["StudentId"]));
                    }
                }
                conn.Close();
            }
            return students.ToArray();
        }
    }
}

 

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment