ASP.NET Search Records In GridView Footer And Highlight Results

Search Records In GridView Footer And Highlight Results using ajax in asp.net, In this example i am populating Gridview by creating Sqlconnection and SqlCommand.

I've put a textbox in FooterTemplate of gridview for text to search, and the search results are highlighted using regular expression, i m using AJAX for partial postback and update progress template to show search progress

   1:  <style type="text/css">
   2:  .highlight {text-decoration:none; font-weight:bold;
   3:  color:black; background:yellow;}
   4:  </style>
   5:  </head>
   6:  <body>
   7:  <form id="form1" runat="server">
   8:  <asp:ScriptManager ID="ScriptManager1" runat="server"/>
   9:  <asp:UpdatePanel ID="UpdatePanel1" runat="server">
  10:  <ContentTemplate>
  11:  <div>
  12:  <asp:GridView ID="grdSearch" runat="server" 
  13:                ShowFooter="True"
  14:                OnRowCommand="grdSearch_RowCommand" 
  15:                AutoGenerateColumns="False">
  16:   
  17:  <Columns>
  18:  <asp:TemplateField HeaderText="FirstName">
  19:  <ItemTemplate>
  20:  <asp:Label ID="lblFIrstName" runat="server"
  21:             Text='<%# Highlight(Eval("FirstName").ToString()) %>'/>
  22:  </ItemTemplate>
  23:   
  24:  <FooterTemplate>
  25:  <asp:TextBox ID="txtSearch" runat="server"/>
  26:  <asp:Button ID="btnSearch" CommandName="Search" 
  27:              runat="server" Text="Search"/>
  28:  </FooterTemplate>
  29:  </asp:TemplateField>
  30:   
  31:  <asp:BoundField DataField="LastName" HeaderText="LastName"/>
  32:  </Columns>
  33:  </asp:GridView>
  34:  </div>
  35:  </ContentTemplate>
  36:  </asp:UpdatePanel>
  37:   
  38:  <asp:UpdateProgress ID="UpdateProgress1" runat="server">
  39:  <ProgressTemplate>
  40:  <br />
  41:  <img src="Images/ajax.gif" alt="Searchig"/>
  42:  </ProgressTemplate>
  43:  </asp:UpdateProgress>
  44:  </form>
  45:  </body>

Populate GridView by making SqlConnection and SqlCommand to fetch data from database, then bind the data to Grid in Page_Load event.
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
        }
    }

    private DataTable GetRecords()
    {
        SqlConnection conn = new SqlConnection(strConnection);
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Select * from Employees";
        SqlDataAdapter dAdapter = new SqlDataAdapter();
        dAdapter.SelectCommand = cmd;
        DataSet objDs = new DataSet();
        dAdapter.Fill(objDs);
        return objDs.Tables[0];

    }

    private void BindGrid()
    {
        DataTable dt = GetRecords();
        if (dt.Rows.Count > 0)
        {
            grdSearch.DataSource = dt;
            grdSearch.DataBind();
        }
    }

Now i've written a method to search GridView rows or records for the text entered in footer textbox by user
private void SearchText(string strSearchText)
    {
        DataTable dt = GetRecords();
        DataView dv = new DataView(dt);
        string SearchExpression = null;
        if (!String.IsNullOrEmpty(strSearchText))
        {
            SearchExpression =
            string.Format("{0} '%{1}%'",
            grdSearch.SortExpression, strSearchText);

        }
        dv.RowFilter = "FirstName like" + SearchExpression;
        grdSearch.DataSource = dv;
        grdSearch.DataBind();
    }

Next step is to check the command in RowCommand event of gridview,if it is what u've defined in while creating the button in footer of grid by assigning the commandname property, if yes than get the text entered by user in textbox placed in footer of gridview by using findcontrol method and pass this text to the search method written earlier by making a call to that method
protected void grdSearch_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        System.Threading.Thread.Sleep(2000);
        if (e.CommandName == "Search")
        {
            TextBox txtGrid =
            (TextBox)grdSearch.FooterRow.FindControl("txtSearch");
            SearchText(txtGrid.Text);
        }
    }

To highlight search results use regular expression and replace the the words found with highlighted in yellow color
public string Highlight(string InputTxt)
    {
        GridViewRow gvr = grdSearch.FooterRow;
        if (gvr != null)
        {
            TextBox txtExample =
            (TextBox)grdSearch.FooterRow.FindControl("txtSearch");

            if (txtExample.Text != null)
            {
                string strSearch = txtExample.Text;
                Regex RegExp =
                new Regex(strSearch.Replace(" ", "|").Trim(),
                RegexOptions.IgnoreCase);
                return
                RegExp.Replace(InputTxt, new MatchEvaluator(ReplaceKeyWords));
                RegExp = null;
            }
            else
                return InputTxt;
        }
        else
        {
            return InputTxt;
        }
    }

    public string ReplaceKeyWords(Match m)
    {
        return "" + m.Value + "";
    }

13 comments:

  1. Content GeneratorJanuary 7, 2009 at 9:45 PM

    That method is going to be an absolute killer for performance. Not only are you posting the whole page back every time a key is pressed, you are hitting the database to get the records every time.

    ReplyDelete
  2. UnknownJanuary 8, 2009 at 5:39 PM


    @Mark Smith :I've altered the code ,download the new one and check it out

    ReplyDelete
  3. UnknownJanuary 9, 2009 at 4:21 AM

    I think the returned data can be saved in a Cache or session and use the dataview to filter the rows and that will boost the performance

    ReplyDelete
  4. Shakti Singh DulawatJanuary 9, 2009 at 1:40 PM

    This is very nice example mention my you, this is helpful for programming logic.
    Thanks
    Shakti
    www.nextmvp.blogspot.com

    ReplyDelete
  5. Abhay GoyalJune 30, 2009 at 10:31 AM

    Thanks Amit, That was really informative and it really works, Thankyou very much , I love it.
    Keep writing and helping us out.

    ReplyDelete
  6. UnknownJune 30, 2009 at 8:42 PM

    @Abhay Goyal:

    Thanks for the appreciation :) , keep visiting

    ReplyDelete
  7. AnonymousSeptember 12, 2009 at 4:37 AM

    Muchas gracias!!!

    ReplyDelete
  8. UnknownOctober 26, 2009 at 2:34 PM

    great post !!!!!
    it's working thanks
    But, what if we want to search in all columns of dataview like 'name','ID','product' etc. (in single input search text)

    ReplyDelete
  9. AnonymousDecember 14, 2009 at 11:56 AM

    The download link is not working. Could you please help me to get your code

    ReplyDelete
  10. AnonymousMarch 18, 2010 at 10:38 AM

    Need download link for this article or please mail me to dora.meka@gmail.com. The given link is not working..

    ReplyDelete
  11. AnonymousJuly 30, 2010 at 8:35 PM

    Download link is dead... please mail me.. robin_housem8@hotmail.com..thanks

    ReplyDelete
  12. kvministriesJuly 4, 2011 at 11:50 AM

    I was looking for something like this, and finally got it here in your blog. This is really fascinating, and I am going to be a returning visitor to your blog. Just keep it updated with such fresh and innovative concepts.

    ReplyDelete
  13. AnonymousNovember 26, 2011 at 1:56 PM

    Thanks a lot for your effort .I have a problem The download link is not working. Could you please download it again .Thanks

    ReplyDelete