In my seminars I always show how to fetch data from an SQL Server database to some UI controls (usually a GridView control). I use the connected data access model,I use the disconnected data access model (datasets) and obviously I provide samples that leverage LINQ to SQL and Entity Framework. Sometimes I get asked how to search through the results that are bound to a GridView control.

In this post I would like to show you how to get data from an SQL Server database and bind the data to a Gridview web server control (which is the easy part) and then demonstrate how to search through the results according the serach terms the user enters. Let's move on with our hands-on example.I will use datasets to bind data to the GridView control.

<connectionStrings>
<add name="NorthwindConnectionString" 

connectionString="Data Source=.;Initial Catalog=Northwind;

Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

6) Add a class file inside the DataAccess folder. Name the file Connection.cs.So we have a Connection class. The code for the whole class is 

public class Connection
{
 
public static SqlConnection GetDBConnection()
{
       
string connectionString = ConfigurationManager.

ConnectionStrings["NorthwindConnectionString"].ConnectionString;
 
SqlConnection connection = new SqlConnection(connectionString);
 
       
connection.Open();
return connection;
    }
 
}

This is very simple static method. It returns an SQL connection.Inside this method I do the following.

• I get the connection string from the configuration file

• I create a new connection object

• I open the connection and return the connection object. 
  

Do not forget to add these lines of code in the top of the file. 

using System.Data.SqlClient;

using System.Configuration;

7) Add a GridView web server control to the Default.aspx page. Add a button,label and a textbox to the Default.aspx. Leave the default names. The user will enter the Category ID and then they will get the Product Names that belong to this category.

8) Now let's retrieve some data from our database. We will add another file to the DataAccess folder.So add a class file inside that folder and call it DataAccess.cs.

9) Now let's implement the method that will return rows from the Product table that belong to a particular category, the user enters in the textbox.Inside the DataAccess.cs file add this method

public static DataSet GetProductsPerCategory(int categoryID)
    {
        DataSet dataSet = new DataSet();
 
        using (SqlConnection connection = Connection.GetDBConnection())
        {
   string sql = "SELECT * FROM Products WHERE CategoryID=@CategoryID";
   SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
   command.CommandType = CommandType.Text;
 
   SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
   dataAdapter.Fill(dataSet, "Products");
        }
        return dataSet;
    } 

10) The code above is very simple.I have a static method that I pass a value as input parameter(CategoryID). It also returns a dataset.Inside the method

• I create a Dataset object

• I get the connection object back from the GetDBConnection function that "lives" inside the Connection class.I define the SQL statement

• I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object

• I set the input parameter value of the method as the input parameter to the stored procedure

• I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.

• I create a DataAdapter object and fill the dataset.Finally I return the dataset.

11) Now we need to write some code in the code behind in the Default.aspx page to call these methods we created in our data access layer.In the Button1_Click event handling routine I just call the GetProducts() method I create just below it. The code follows 

    protected void Button1_Click(object sender, EventArgs e)
    {
        GetProducts();
 
    }
 
    private void GetProducts()
    {
        try
        {
            int categoryID = int.Parse(TextBox1.Text);
 
 
      DataSet ds = DataAccess.GetProductsPerCategory(categoryID);
 
 
      GridView1.DataSource = ds;
      GridView1.DataBind();
 
        }
        catch (SqlException ex)
        {
 
            Label1.Text = "Cannot get product data." + ex.Message;
        }
    }

12) Now run your application and enter something in the textbox (e.g 1). You will see the Product names appearing in the Gridview.

13) Now we need to add some more web server controls to allow the user to enter search terms when he will be looking to highlight rows in the GridView that contain those search terms. Add a button, and a textbox to the Default.aspx.

   Search terms: <asp:TextBox ID="Textbox2" runat="server" />
 <asp:Button ID="Button2" runat="server" BackColor="#FF3300" 
BorderColor="#0000CC" BorderStyle="Dashed" BorderWidth="2px" 

Font-Bold="True" ForeColor="Yellow" 

onclick="Button2_Click" Text="Search" /> 

14) Now we need to add in the code behind - Default.aspx page - the code that will implement the searching in the GridView results.I will create the FoundText method that will highlight the search terms in the GridView control. We need to use regular expressions. Do not forget to include the namespace

using System.Text.RegularExpressions;
 

   protected string search = String.Empty;
 

    protected string FoundText(string searchitem, string input)
    {
 
 Regex myexpr = new Regex(searchitem.Replace(" ", "|"),

 RegexOptions.IgnoreCase);
 
  return myexpr.Replace(input, new MatchEvaluator(Replacewords));
 
    }
 
 
 
    public string Replacewords(Match word)
    {
 
        return "<span class='found'>" + word.Value + "</span>";
 
    } 

The Replacewords method adds a style to the found/matched text and returns it to the FoundText method.  In the head section of the Default.aspx  we need to add the style for the css class found.

<style type="text/css">
   .found {text-decoration: none;color:#FF0000;background:#E07516;}
</style>

15) Now we need to add the code in the Button2_Click event handling routine

    protected void Button2_Click(object sender, EventArgs e)
    {
        search = Textbox2.Text;
 
        GetProducts();
    }
} 

The code above is very easy to follow.I just get the search term entered by the user and then call again the GetProducts method.

16) Finally we need to make changes to the GridView web server control. We will change it to use the FoundText method on the ProductName column.The markup follows.

 <asp:GridView ID="GridView1" runat="server" BackColor="LightGoldenrodYellow" 
 BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" 
   GridLines="None" AutoGenerateColumns="false">
  <AlternatingRowStyle BackColor="PaleGoldenrod" />
  <FooterStyle BackColor="Tan" />
  <HeaderStyle BackColor="Tan" Font-Bold="True" />
   <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" 
     HorizontalAlign="Center" />
   <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
   <SortedAscendingCellStyle BackColor="#FAFAE7" />
   <SortedAscendingHeaderStyle BackColor="#DAC09E" />
   <SortedDescendingCellStyle BackColor="#E1DB9C" />
   <SortedDescendingHeaderStyle BackColor="#C2A47B" />
 
 
   <Columns>
        
  <asp:TemplateField HeaderText="Product Results">
   <ItemTemplate>
  <%# FoundText(search, (string)Eval("ProductName"))%>
   </ItemTemplate>
        
   </asp:TemplateField>
        
        </Columns>
        </asp:GridView>

17) Now run your application again. Type something in the first textbox (CategoryID) and click the first button that displays the results in the GridView. In the textbox for the search terms, enter your own terms.

Have a look at the picture below to see what are the results in my case where I enter 1 as CategoryID and "ac" as the search terms.

That is all folks!!!

Email me if you need the source code.

Hope it helps!!!