Search and Filter records in GridView using Asp.Net FilterParameters in C# and Vb.Net

← PrevNext →

Displaying a huge cache of data in a GridView control has many benefits, as it can organize and present data in a well-formatted manner. Therefore, at times situations come when you wish to search or filter records in a GridView control, especially when paging is enabled. This function has been around for many years, it still baffles a fresher (a beginner) and they frantically look for solutions.

Search records in GridView using FilterParameters

There are many ways to do it, but if you have hooked the GridView control with a database table using SqlDataSource, then you can use FilterParameters attribute, which is a property of SqlDataSource.

Related Article: Filter Records in a GridView Based on a Date Range with FilterExpression Property

You can find more help if you go through this MSDN link that explains everything in detail about SqlDataSource.FilterParameters property.

A general syntax of FilterParameters attribute will look like this.

<FilterParameters>
    ...
</FilterParameters>

If you are still looking for a solution on populating data (extracted from a database) in a GridView control, then I'll suggest you first go through this article on how to bind data to a GridView control using SqlDataSource. This will help you understand (step-by-step) about how to attach SqlDataSource to a GridView control. It has many benefits.

Create a small table in SQL Server

Before starting with our page design, we need to create a small table in our SQL Server database. The name of the table is dbo.Books. I already have a sample table with a list of books, designed exclusively for these situations. It will spare us from creating it repeatedly.

A small table with a list of books will be sufficient for this demo, since I am going to show five rows per page in the GridView control.

The Markup with a GridView

Open visual studio and add a new web site. On your default page add a GridView control and attach it with a SQL Server database table.

In addition, I'll add a textbox control, which will allow users to enter the search value (the value can be either a character or a word), a button and label control.

Related: Add Values from Textboxes to GridView using jQuery

The label will show us the returned number of rows, when the FilterParameters property finds keywords matching with the entered values in the search textbox.

<!DOCTYPE html>
<html>
<head>
    <title>Search Data in GridView Control using Asp.Net</title>
</head>
<body>
    <form>
        <div>
            <h3>Search Data in GridView</h3>

            <asp:GridView ID="GridView" 
                DataSourceID="SqlDataSource1" 
                CellPadding="5" CellSpacing="0" 
                AllowPaging="true" PageSize="5" 
                OnRowCreated="GridView_RowCreated"
                runat="server">

                <HeaderStyle BackColor="#989898" ForeColor="white" /></asp:GridView>

                <asp:SqlDataSource
                    ID="SqlDataSource1" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:YOURDBConnectionString %>"
                
                    SelectCommand="SELECT [BookID], [BookName], 
                        [Category], [Price] FROM dbo.[Books]"
                            FilterExpression="[BookName] LIKE '%{0}%'">

                    <FilterParameters
                        <asp:ControlParameter Name="BookName" 
                            ControlID="txtFind" PropertyName="Text" />
                    </FilterParameters>
                </asp:SqlDataSource>
            <br />

            Enter Search Value: 
            <asp:TextBox ID="txtFind" runat="server"></asp:TextBox>
            <input type="submit" id="btSubmit" runat="server" />

            <%--LABEL TO SHOW ROW COUNT.--%>
            <div style="clear:both;padding:10px 0;">
                <label id="msg" runat="server"></label>
            </div>
        </div>
    </form>
</body>
</html>

Also Read:Filter Records in a GridView Based on a Date Range with FilterExpression Property

There are two important properties in the above markup that we want you to pay attention.

01) The FilterExpression property in the SqlDataSource attributes.
02) The <FilterParameters> attribute.

The FilterExpression property contains a placeholder for the filter parameter BookName, which is contained in one of the collections (ControlParameters) of <FilterParameters> attribute.

The second column of the GridView control will display the BookName field with values. We have applied the filter on the second column and the search result will show records that will match the value entered in the search textbox.

In addition, I have set paging as true (AllowPaging="true"), and the page size to five. This will allow us to check if it searches for keywords on every page and returns the desired result.

Code Behind (C#)

We are not doing much at the code behind level, and it is optional. All we have is a GridView RowCreated event, which will get the number of rows found (row count) that match the search keyword.

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;

partial class _Default : System.Web.UI.Page
{

    protected void GridView_RowCreated(object sender, 
        System.Web.UI.WebControls.GridViewRowEventArgs e)
    {
        if (!string.IsNullOrEmpty(txtFind.Text))
        {
            msg.InnerText = "Found " + GridView.Rows.Count + 
                " rows matching keyword '" + txtFind.Text + "'.";
        }
    }
}
Vb.Net
Option Explicit On

Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub GridView_RowCreated(sender As Object, 
            e As System.Web.UI.WebControls.GridViewRowEventArgs)

        If Trim(txtFind.Text) <> "" Then
            msg.InnerText = "Found " & GridView.Rows.Count & _
                " rows matching keyword '" & txtFind.Text & "'."
        End If
    End Sub
End Class
Conclusion

SqlDataSource <FilterParameters> has made searching records in a GridView relatively simple and it is quick. Try experimenting with many records or rows to know its performance. If you have, any queries related to this article, then do not forget to write to us or just leave a message or comment below. We will love to answer your queries.

Hey, don't forget to share this article with your friends. Thank's for reading :-).

← PreviousNext →


Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.
Delivered by FeedBurner