Searching in paging enabled Asp.Net GridView control using C# VB.Net

In many forums Asp.net C# Vb.Net developers ask a common question How one can Search in a GridView and highlight GridView rows or record data. The answer is yes, we found a lot of example on this issue and here in this example i am going to explain how you can highlight GridView rows data based on search result. As we know that GridView control is a very nice and helpful control but still can’t provide us such facility to search within GridView and highlight data. To do that first add a page in your project then add a textbox and a CommandButton to search within the GridView. After that add a GridView control & bind with data. Also i would like to show you how you can search within GridView even the GridView has paging functionality. I would also like to show you how you can search within all Gridview rows & all columns.

Objective/Outcome:
Searching in paging enabled Asp.Net GridView control using Csharp VB.Net

To do the Example First Create a Database Table:

CREATE TABLE [dbo].[tblStock](
	[BrandName] [varchar](max) NULL,
	[CategoryName] [varchar](max) NULL,
	[ProductName] [varchar](max) NULL,
	[LogicalQuantity] [bigint] NULL,
	[PhysicalQuantity] [bigint] NULL,
	[QuarentineQuantity] [bigint] NULL
) ON [PRIMARY]

Now Insert Some Data:

INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Handwash',55781,55780,1)
INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Soap',38786,38780,6)
INSERT INTO tblStock VALUES('Kohinoor','ToothPaste','Tibbet Clear',34503,34500,3)
INSERT INTO tblStock VALUES('Proctor & Gamble','Bubble','',43809,43800,9)
INSERT INTO tblStock VALUES('Telenor','Mobile','Data SIM',43876,43870,6)
INSERT INTO tblStock VALUES('Telenor','Mobile','GP Modem',23907,23900,7)
INSERT INTO tblStock VALUES('Uniliver','Soap','Lux',19806,19800,6)
INSERT INTO tblStock VALUES('Uniliver','TeleProducts','Brittle',20703,20700,3)
INSERT INTO tblStock VALUES('Uniliver','ToothPaste','Close Up',16755,16750,5)

Now modify the Web.Config File to Connect to Database:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now Design GridView Control With Search:

<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <asp:Button ID="cmdSearch" runat="server" Text="Search" OnClick="cmdSearch_Click" />

<asp:GridView ID="GridView1" runat="server" Width="400px" AutoGenerateColumns="False" 
        AllowPaging="true" PageSize="5" OnPageIndexChanging="GridView1_PageIndexChanging" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
         <Columns>
            <asp:TemplateField HeaderText="Brand Name">
            <ItemTemplate>
            <%# HighlightText(search_Word, (string)Eval("BrandName"))%>
            </ItemTemplate>
            </asp:TemplateField>         
            <asp:TemplateField HeaderText="Category Name">
            <ItemTemplate>
            <%# HighlightText(search_Word, (string)Eval("CategoryName"))%>
            </ItemTemplate>
            </asp:TemplateField>         
            <asp:TemplateField HeaderText="Product Name">
            <ItemTemplate>
            <%# HighlightText(search_Word, (string)Eval("ProductName"))%>
            </ItemTemplate>
            </asp:TemplateField>         
        </Columns>
        </asp:GridView>

Now Add Highlight Word Style:

<style type="text/css">
    .highlight
    {
        background-Color:Yellow;
    }
    </style>

Now Bind the GridView Control with Database Table Data:
C# Code:

    protected string search_Word = String.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            RefreshData();
    }

    private void RefreshData()
    {
        DataTable dt;
        String SQL = "SELECT * FROM tblStock";


        string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(sConstr))
        {
            using (SqlCommand comm = new SqlCommand(SQL, conn))
            {
                conn.Open();
                using (SqlDataAdapter da = new SqlDataAdapter(comm))
                {
                    dt = new DataTable("tbl");
                    da.Fill(dt);
                }
            }
        }

        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

Note: Define a global variable “search_Word” in Page scope. Also please don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration”, “System.Text.RegularExpressions” namespaces.

VB.Net Code:

    Protected search_Word As String = [String].Empty
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            RefreshData()
        End If
    End Sub

    Private Sub RefreshData()
        Dim dt As DataTable
        Dim SQL As [String] = "SELECT * FROM tblStock"


        Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Using conn As New SqlConnection(sConstr)
            Using comm As New SqlCommand(SQL, conn)
                conn.Open()
                Using da As New SqlDataAdapter(comm)
                    dt = New DataTable("tbl")
                    da.Fill(dt)
                End Using
            End Using
        End Using

        GridView1.DataSource = dt
        GridView1.DataBind()
    End Sub

Note: Define a global variable “search_Word” in Page scope. Also please don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration”, “System.Text.RegularExpressions” namespaces.

Now enable GridView Paging:
C# Code:

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        search_Word = txtSearch.Text;
        RefreshData();
    }

VB.Net Code:

    Protected Sub GridView1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
        GridView1.PageIndex = e.NewPageIndex
        search_Word = txtSearch.Text
        RefreshData()
    End Sub

Now add 2 Helper Function to Search By Ragular Expression & use those functions from GridView Markup:
C# Code:

    protected string HighlightText(string searchWord, string inputText)
    {
        // Replace spaces by | for Regular Expressions
        Regex expression = new Regex(search_Word.Replace(" ", "|"), RegexOptions.IgnoreCase);
        return expression.Replace(inputText, new MatchEvaluator(ReplaceKeywords));
    }

    public string ReplaceKeywords(Match m)
    {
        return "<span class='highlight'>" + m.Value + "</span>";
    }

VB.Net Code:

    Protected Function HighlightText(searchWord As String, inputText As String) As String
        ' Replace spaces by | for Regular Expressions
        Dim expression As New Regex(search_Word.Replace(" ", "|"), RegexOptions.IgnoreCase)
        Return expression.Replace(inputText, New MatchEvaluator(AddressOf ReplaceKeywords))
    End Function

    Public Function ReplaceKeywords(m As Match) As String
        Return "<span class='highlight'>" & Convert.ToString(m.Value) & "</span>"
    End Function

Now Implement Serach Button within GridView:
C# Code:

    protected void cmdSearch_Click(object sender, EventArgs e)
    {
        // Assign search_Word
        search_Word = txtSearch.Text;
        RefreshData();
    }

VB.Net Code:

    Protected Sub cmdSearch_Click(sender As Object, e As EventArgs)
        ' Assign search_Word
        search_Word = txtSearch.Text
        RefreshData()
    End Sub

Hope now you can search within GridView control as well as can highlight search word based on search result even the GridView has paging functionality.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, Gridview, VB.Net
One comment on “Searching in paging enabled Asp.Net GridView control using C# VB.Net
  1. pradhan says:

    Nice thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*