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

Last updated: 26th April 2024

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. There will be situations when you want to search or filter records in a GridView control, especially when paging is enabled to the grid. I'll show you a simple method on how to search and filter records in a paging enabled GridView control using the FilterParameters property of SqlDataSource control.

See this demo

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.

Create a table in SQL Server

We need data. Therefore, before starting with our page design, we need to create a 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.

The Markup with a GridView

Open visual studio and add a new web site. In the default page add a GridView control and attach it to SqlDataSource control. See the markup below.

<!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>

I also have a textbox control, along with a "button" and "label" control.

See this demo

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.

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. I 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.

C# Code

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 Code

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

See this demo

Conclusion

SqlDataSource <FilterParameters> has made searching records in a GridView relatively simple and it is fast. Try with more records or rows to know its performance.

Please share this article if you like it. Happy coding. 🙂

← PreviousNext →