Implementing AJAX keyword search from database table

In this exercise, I will demonstrate how to implement a simple keyword search in MS SQL Server database using textbox and gridview control in Asp.NET AJAX technology. Most of the websites now use AJAX technology to avoid the full postback and better user experience. It is wonderful, personally I love it. To begin with this exercise I assume you have a SQL Express or SQL Server database installed. I will use global connection string from web.config file. The connection string in my web config file pointing the SQL Express database located in App_Data folder. Here is the connection string –

<connectionStrings>     
  <!--Your connection string for database-->     
  <add name="myConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=¦DataDirectory¦\practice.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>   

</connectionStrings>

Now, in the default.aspx page. I have created a textbox control and a submit button to trigger the query when something entered in the input textbox. I also have created a simple validation control and validation summary control to force user to type anything in the textbox before submitting the page on button click. I also have created a gridview control to display the query data. In this exercise I have not selected any specific column in the gridview control so that  it will display all columns from the source table.Here is what I have done –

<asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager>     

<div>         

<asp:UpdatePanel ID="UpdatePanel1" runat="server">         
<ContentTemplate>        
<p>Enter employee last name and click on submit button to search. You can enter a few letters of the employee last name to search.</p>                   
<asp:ValidationSummary ID="ValidationSummary1" runat="server" />                  
Last Name: <asp:TextBox ID="txtName" runat="server" />                  
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="You must enter a name."             ControlToValidate="txtName">*</asp:RequiredFieldValidator>         
<asp:Button ID="btnSubmit" runat="server" Text="Submit" />             

<p>Display result here.</p>
<asp:GridView ID="GridView1" runat="server">             
</asp:GridView>         
</ContentTemplate>         

</asp:UpdatePanel>         

<asp:UpdateProgress ID="UpdateProgress1" runat="server">         
        <ProgressTemplate>             
                <img src="loading.gif" />         
        </ProgressTemplate>         
</asp:UpdateProgress>         

</div>          

<asp:SqlDataSource ID="dsEmployee" runat="server" ConnectionString="<%$ ConnectionStrings:myConnection %>"         
SelectCommand="SELECT * FROM emp WHERE (lastName like @LastName+'%')">         
    <SelectParameters>             
        <asp:ControlParameter Name="LastName" ControlID="txtName" type="String" />         
    </SelectParameters>     
</asp:SqlDataSource>

 [important]Note, inside update progress template I am using an image to display while fetching the gridview result.[/important]

Now I have to create an event for submit button. I have created an event handler for submit button so that it will be triggered when button is clicked by user. It is very simple event here, I have indicated the datasource for Gridview control and then bind data. When button is clicked, dataSourceID gets select parameter value from the textbox control and refine the search accordingly.

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click         

	'selecting the datasource for gridview1 on button click event
	GridView1.DataSource = dsEmployee
	'Bind data to gridview
	GridView1.DataBind()
End Sub

I have saved the page, and right click on the default.aspx page, click on view in browser. Here you got a simple ajax keyword search engine. Type a latter of a name  and click on submit button it will display all the records starting the last name with that letter.

You can also bind the the specific column/s to the gridview if you do not want to display all records from the table. Make sure you have set the AutoGenerateColumns to false  in Gridview like below –

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="empId">             

    <Columns>             
        <asp:BoundField DataField="FirstName" HeaderText="First Name" />             
        <asp:BoundField DataField="LastName" HeaderText="Last Name" />             
    </Columns>             
</asp:GridView>