Using Stored Procedure in AutoComplete Extender TextBox in ASP.Net C#

To use a Stored Procedure in an AutoComplete extender TextBox in ASP.Net C# use the following procedure.

  1. Open SQL Server Management Studio then

    select the database where you want to create a table and Stored Procedure (in my case it is the library database and the table name is available book).
     
  2. CREATE TABLE [dbo].[AvailableBook](

    [Title] [varchar](500) NULL,

    [Authorname] [varchar](100) NULL,

    [Edition] [varchar](max) NULL,

    [Year] [varchar](max) NULL,

    [Volume] [varchar](max) NULL,

    [BookNo] [varchar](max) NULL,

    [ShelfNO] [varchar](50) NULL,

    [enrollment] [varchar](50) NULL,

    [Issuedate] [varchar](max) NULL,

    [Returndate] [varchar](50) NULL,

    [Status] [varchar](50) NULL,

    [Bookid] [varchar](max) NULL,

    [Type] [varchar](max) NULL

    )

    Insert values into the availablebook table like this:

    insert into availablebook values('c#','apress','2011','2010','3','1','2','NULL','NULL','NULL','Available','1','NULL')

  3. Create a Stored Procedure like this:
     

    Create proc [dbo].[searchbook]

    @book varchar(90)='%'

    as

    begin

    select Title,Authorname from availablebook where title like @book or authorname like @book

    end
     
    After that open Visual Studio then open your project or create a new project.
     
    Drag and drop a TextBox to your webform, in design view you are getting an arrow like addextender, see the following figure:

    AutoComplete-extender-textbox.jpg

    Select AutoCompleteExtender (don't forget to add a toolkit scriptmanager and reference it in your page header or in web.config).
     
    See example :(aspx page source view)

    <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">

    <head>

        <meta name="description" content="" />

        <meta name="keywords" content="" />

        <title>Big Business 2.0 by FCT</title>

        <meta http-equiv="content-type" content="text/html; charset=utf-8" />

        <link rel="stylesheet" type="text/css" href="Styles/style.css" />

        <script type="text/javascript" src="Scripts/jquery-1.7.1.min.js"></script>

        <script type="text/javascript" src="Scripts/jquery.dropotron-1.0.js"></script>

        <script type="text/javascript" src="Scripts/jquery.slidertron-1.1.js"></script>

        <style type="text/css">

            .watermark

            {

                background-color: Gray;

            }

        </style>

    </head>

    <body>

        <form id="Form1" runat="server">

        <h1>

            Library</h1>

        <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

        </asp:ToolkitScriptManager>

        Search Book:&nbsp;&nbsp;<asp:TextBox ID="TextBox2" AutoCompleteType="Search" AutoPostBack="true"

            runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox>

        <asp:AutoCompleteExtender ID="TextBox2_AutoCompleteExtender" runat="server" MinimumPrefixLength="1"

            CompletionInterval="5" Enabled="True" ServicePath="~/WebService.asmx" EnableCaching="true"

            CompletionSetCount="12" ServiceMethod="GetCompletionList" TargetControlID="TextBox2">

        </asp:AutoCompleteExtender>

        <asp:TextBoxWatermarkExtender ID="TextBox2_TextBoxWatermarkExtender" WatermarkCssClass="watermark"

            WatermarkText="search by title" runat="server" Enabled="True" TargetControlID="TextBox2">

        </asp:TextBoxWatermarkExtender>

        <asp:RoundedCornersExtender ID="TextBox2_RoundedCornersExtender" runat="server" Enabled="True"

            TargetControlID="TextBox2">

        </asp:RoundedCornersExtender>

        </form>

    </body>

    </html>
     

  4. Add a webservice from a new item and reference it in your project and write the following code (be careful while giving the webservice name; in my case it is WebService.asmx).
      

    [WebMethod]

        Public List<string> GetCompletionList(string prefixText)

        {

            List<string> result = new List<string>();

            con.Open();

            SqlCommand cmd = new SqlCommand("searchbook", con);

            cmd.Parameters.AddWithValue("@book", prefixText);

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())

            {

                result.Add(dr["Title"].ToString());

                result.Add(dr["Authorname"].ToString());

            }

            return result;

        } 

Rebuild your project and run it; you'll get some suggetions when you type characters.