Various Types of Search on Database and Display Results on GridView

Introduction

This article explains how to apply various types of search on a database and display the results in a GridView.

Some of you might be trying to create a Search Engine that can be used either for searching the Jobs, Resume, Candidate and so on. In this article I am creating a demo application that will help you to search the database depending on the requirements and then display the results.

Step 1

First of all I created a database in which a few entries of some IT students are made, I named this database "Student", in this database I created a table named IT_Students. It's data is as follows:

Step 2

Then I created a new application in Visual Studio.

On this application I took a TextBox, a button and a Grid View:

  <div>

    Search User by Name:-

     <asp:TextBox runat="server" ID="txt1"></asp:TextBox>

 

    <br />

      <asp:Button ID="btn1" runat="server" Text="Submit" OnClick="btn1_Click" />

  </div>

    <br />

  <div>

     <asp:Label ID="lbl1" runat="server" Enabled="false"></asp:Label>

         <asp:GridView ID="grd" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">

           <AlternatingRowStyle BackColor="White" />

           <EditRowStyle BackColor="#2461BF" />

           <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

           <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

           <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

           <RowStyle BackColor="#EFF3FB" />

           <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />

           <SortedAscendingCellStyle BackColor="#F5F7FB" />

           <SortedAscendingHeaderStyle BackColor="#6D95E1" />

           <SortedDescendingCellStyle BackColor="#E9EBEF" />

           <SortedDescendingHeaderStyle BackColor="#4870BE" />

      </asp:GridView>

  </div>

Step 3

Then I worked on the button click event for the code behind. Provide this code in the .aspx page.

Now I will show various ways to search the database.

First Method

In the first method I used the "%" after the letter that to be searched for, what this will do is it will search the starting letters of all the records and then provide the reasults.

        protected void btn1_Click(object sender, EventArgs e)

        {

            SqlConnection x = new SqlConnection(@"Your Connection");

            x.Open();

            SqlDataAdapter da = new SqlDataAdapter("select * from IT_Student where 
            Student_Name like '" + txt1.Text + "%'", x);

            DataSet ds = new DataSet();

            da.Fill(ds);

            grd.DataSource = ds;

            grd.DataBind();

        }

Here I first provided the connection to my database using "SqlConnection".

Then I used "SqlDataAdapter" to provide the query for searching the database, this query will search the student names for names that start with the specified letter.

Then I bound the Grid. We can now run our application to see the results.

You can see that I provided "a" in the search TextBox and as I clicked on the button all the data with "a" in the beginning of the name is displayed in the Grid.

Second Method

Now I used "%" in the beginning of the letter specified for the search, this searched the student names for that specified letter at the end of them.

So the query was changed to this:

SqlDataAdapter da = new SqlDataAdapter("select * from IT_Student where Student_Name like '%" + txt1.Text + "'", x);

Then I bound the Grid, and the result is as in the following:

You can see that those students are fetched whose name end with "a".

Third Method

Now I used "%" in the beginning and the end for the letter that is to be specified for the search, this searches the student names from both sides, in other words from the beginning and from the end as well.

So the query was changed to this:

SqlDataAdapter da = new SqlDataAdapter("select * from IT_Student where Student_Name like '%" + txt1.Text + "%'", x);

Then I bound to the Grid and the result is as in the following:

You can see that on searching for the "an" all the students whose name has an "an" anywhere is displayed.