Click here to Skip to main content
Click here to Skip to main content

GridView-Search Control

, 15 Dec 2006
Rate this:
Please Sign up or sign in to vote.
Search control with basic/advanced modes for DataTable filtering capabilities

Sample Image - screen0.jpg

Introduction

The GridViewSearch control is an ASP.NET user control that provides search/filtering capabilities which can then be used to filter a DataTable/DataSource bound to a GridView control. There are two search modes available in the control: Basic and Advanced. Both modes construct a SQL-based WHERE-clause filter, which can be later set to a DataView's RowFilter property.

The Basic search mode primarily works for varchar/string fields, while the Advanced mode can construct a filter-string that can be used in boolean, integer, string, and date fields. The Advanced mode also can combine fields with either AND-type or an OR-type clause in the resulting filter string.

Using the GridViewSearch UserControl

To use the GridViewSearch UserControl in a web project, drop a copy of the control (both the HTML and code file) into your web project and change the name/class of the control to meet your needs. The columns and handling of the GridViewSearch's controls in the sample source are intentionally left in for the coder to easily copy/remove/modify the fields as quickly as possible, without having to start from scratch. The columns and controls demonstrate the handling of different field types and controls, most of which are common when working with database tables and stored-procs.

In the code file, substitute your own business/data layer to bind the GridViewSearch fields. Many of the supporting functions in the class can be used with any business/data object, and just a specific handful of lines of code need to be modified/removed.

To catch the Search event that is raised by the user control, add a handler to the same class/control on which the GridView control is contained, and then you would most likely apply the search filter to the GridView's datasource RowFilter and re-bind.

To reference the control from another UserControl or a Page, don't forget to call the class's Initialize() function to set the properties for the control.

Note: This control is not meant to be a stand-alone, snap-in UserControl - the intention of it is to be used as almost a "starter kit" when working in conjunction with another GridView control. Enhance the sections you need to use, and throw away the parts you don't need.

Before running the sample demonstration, run the Orders_SPs.sql file against the Northwind database.

Below is a demonstration of several of the different views in the GridViewSearch sample:

Basic View of the GridViewSearch Control

Basic Search View

Advanced View of the GridViewSearch Control

Advanced Search View

Results of a Basic Search

Basic Search View Results

Results of an Advanced Search

Advanced Search View Results

How the GridViewSearch Control Works

The GridViewSearch control consists of two panels (Basic and Advanced) each containing the controls used for generating the filter string. The Basic search panel has a TextBox and a Search LinkButton. The Advanced Search is similar, except that instead of a TextBox in which the search string is entered, a DetailsView control is used to display all the columns and fields to be used in generating the Filter string. Both views have a Cancel LinkButton, which is only displayed after a Filter string is generated.

After either a Basic search or Advanced search filter string is generated, the Search event is raised from the control and passes the newly generated filter string. This event needs to be caught by a parent control, and then the filter string can be applied to the RowFilter of the GridView's datasource. When the "Cancel" LinkButton is clicked, the search filter string is cleared out, and then the Search event is raised again to the parent control (effectively clearing out the RowFilter of the data source).

The Advanced Search panel is hidden by default, and when the user clicks the Advanced Search LinkButton, the Basic Search panel is hidden and the Advanced Search panel is displayed. The user can return to the Basic Search view by clicking the "Exit Advanced Search" link button.

Below are some of the more useful code-snippets used in the control to read/write data between the business objects and the GridView control and the Search control.

    public void Initialize()
    {
        BindDetailView();
        ShowAdvancedSearch(false);  
    }

    protected void BindDetailView()
    { 
        CIF.Business.Northwind.Orders _Orders = 
               new CIF.Business.Northwind.Orders();
        DataTable dtOrders = _Orders.GetDataTable("OrderID", "0");

        dvOrders.DataSource = dtOrders;
        dvOrders.DataBind();
    }

    protected void ShowAdvancedSearch(bool bShow)
    {
        pnlAdvancedSearch.Visible = bShow;
        pnlBasicSearch.Visible = !bShow;
    }

    protected string GetBasicSearchString()
    {
        StringBuilder sb = new StringBuilder(string.Empty);
        string sBasicSearchText = txtBasicSearch.Text.Trim();

        if (sBasicSearchText == string.Empty)
            return string.Empty;            
   
        string sCustomerID = GetSearchFormattedFieldString("CustomerID", 
                                sBasicSearchText, string.Empty, true);
        sb.Append(sCustomerID);
        sb.Append(" OR ");           
        
        string sShipName = GetSearchFormattedFieldString("ShipName", 
                                sBasicSearchText, string.Empty, true);
        sb.Append(sShipName);
        sb.Append(" OR ");
        
        string sShipAddress = GetSearchFormattedFieldString("ShipAddress", 
                                sBasicSearchText, string.Empty, true);
        sb.Append(sShipAddress);
        sb.Append(" OR ");
        
        string sShipCity = GetSearchFormattedFieldString("ShipCity", 
                            sBasicSearchText, string.Empty, true);  
        sb.Append(sShipCity);
        sb.Append(" OR ");
        
        string sShipRegion = GetSearchFormattedFieldString("ShipRegion", 
                                sBasicSearchText, string.Empty, true);
        sb.Append(sShipRegion);
        sb.Append(" OR ");
        
        string sShipPostalCode = 
               GetSearchFormattedFieldString("ShipPostalCode", 
               sBasicSearchText, string.Empty, true);
        sb.Append(sShipPostalCode);
        sb.Append(" OR ");
        
        string sShipCountry = GetSearchFormattedFieldString("ShipCountry", 
                              sBasicSearchText, string.Empty, true);
        sb.Append(sShipCountry);
        
        string sSearch = sb.ToString();
        return sSearch;
    }

    protected string GetAdvancedSearchString()
    {            
        StringBuilder sb = new StringBuilder(string.Empty);
        string sLastRowOperator = string.Empty;

        
        string sOrderID = GetSearchFormattedFieldString("OrderID", 
               ((TextBox)dvOrders.FindControl("txtOrderID")).Text, 
               ((DropDownList)dvOrders.FindControl(
                "ddlOrderIDOperator")).SelectedValue);
        sOrderID = sOrderID.Trim();
        if (sOrderID != string.Empty)
        {
            if (sb.ToString().Trim() != string.Empty)
                sb.Append(" " + sLastRowOperator + " ");

            sb.Append(sOrderID);
            sLastRowOperator = ((DropDownList)dvOrders.FindControl(
                                "ddlOrderIDRowOperator")).SelectedValue;
        }
        
        ...
        ...
        ...
        
        string sShipCountry = GetSearchFormattedFieldString("ShipCountry", 
               ((TextBox)dvOrders.FindControl("txtShipCountry")).Text, 
               ((DropDownList)dvOrders.FindControl(
                "ddlShipCountryOperator")).SelectedValue);
        sShipCountry = sShipCountry.Trim();
        if (sShipCountry != string.Empty)
        {
            if (sb.ToString().Trim() != string.Empty)
                sb.Append(" " + sLastRowOperator + " ");

            sb.Append(sShipCountry);                
        }
        
        string sSearch = sb.ToString();
        return sSearch;
    }

    protected string GetSearchFormattedFieldString(string sfield, 
                     string value, string soperator)
    {
        return GetSearchFormattedFieldString(sfield, value, soperator, false);
    }

    protected string GetSearchFormattedFieldString(string sfield, 
                     string value, string soperator, bool bForceStringType)
    {
        if (value.Trim() == string.Empty)
            return string.Empty;

        string _newValue = value.Trim();
        string _invalidChars = "`~!@#$%^&()-_=+[{]}\\|;:<,>./?\"";

        string _operator = "=";
        if(soperator != string.Empty)
            _operator = soperator;

        if (MSCD.Utilities.Validation.IsDateTime(_newValue) == false)
        {
            for (int i = 0; i < _invalidChars.Length; i++)
            {
                _newValue = _newValue.Replace(_invalidChars.Substring(i, 1), 
                                              string.Empty);
            }

            _newValue = _newValue.Replace('*', '%');
            _newValue = _newValue.Replace("'", "''");
        }

        if (bForceStringType == false)
        {
            if (MSCD.Utilities.Validation.IsDateTime(_newValue) == true)
            {
                DateTime dtValue = DateTime.Parse(_newValue);
                _newValue = sfield + " " + _operator + " #" +
                            dtValue.ToShortDateString() + "#";
            }
            else if (MSCD.Utilities.Validation.IsNumeric(_newValue) == true)
            {
                _newValue = sfield + " " + _operator + " " + _newValue;
            }
            else
            {
                if(_newValue.IndexOf("%") > -1)
                    _newValue = sfield + " LIKE '" + _newValue + "'";
                else
                    _newValue = sfield + " " + _operator + " '" + 
                                _newValue + "'";
            }
        }
        else
        {
            if(_newValue.IndexOf("%") > -1)
                _newValue = sfield + " LIKE '" + _newValue + "'";
            else
                _newValue = sfield + " " + _operator + " '" + _newValue + "'";
        }

        return _newValue;
    }          
          

Conclusion

I hope you find this article and control useful - it saved me a lot of time when working with several different types of tables and datasets, and quickly getting a Search/GridView-filtering control up and running. Enjoy!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Chris Hambleton
Software Developer
United States United States
SOFTWARE: Chris Hambleton is a Software Developer with proven experience in developing both web and Windows client-server applications with WPF, ASP.NET, C#, SQL Server, VB.NET, Visual C++, and VB6.
 
Chris's website is at ChrisHambleton.com and he has a small web-hosting/consulting business called CustomersInFocus.com. He has several other websites such as EzekielWatch.com, iWriterPro.com, and BookBlitzer.com.
 
WRITING: He has also written several fiction books ("The Time of Jacob's Trouble" and "Endeavor in Time"), available at CWHambleton.com and of course, at Amazon.com (Full Amazon Profile).

Comments and Discussions

 
QuestionGood Pinmembervalleyriver24-May-13 13:31 
Questiondb error Pinmemberankit_parmar55513-Dec-11 1:57 
GeneralMy vote of 1 Pinmemberankit_parmar55513-Dec-11 1:47 
GeneralMy vote of 2 Pinmemberarunk5256-Dec-11 6:36 
GeneralThanks for the samples and demo PinmemberRobert Currie28-Jul-10 6:41 
GeneralRe: Thanks for the samples and demo PinmemberChris Hambleton28-Jul-10 6:58 
GeneralMy vote of 4 PinmemberRobert Currie28-Jul-10 6:37 
Questionwhat is MSCD? Pinmemberdebjyoti.biswas3-Dec-09 10:33 
QuestionTrying to use the search control with my database Pinmembertheeskyline15-Jul-09 18:31 
GeneralCan't use it Pinmemberambola22-May-09 20:34 
GeneralRe: Can't use it PinmemberChris Hambleton26-May-09 3:40 
General[Message Removed] PinmemberMojtaba Vali9-Apr-08 1:20 
GeneralRe: Better Solution PinmemberMember 175156214-Apr-08 22:29 
General[Message Removed] PinmemberMojtaba Vali15-Apr-08 1:45 
GeneralRe: Better Solution PinmemberAldorado9-Sep-08 13:57 
GeneralGreat work!! but make it simple for us Pinmemberrama charan4-Dec-07 22:59 
QuestionWhat is MSCD? PinmemberBrian Hart15-Oct-07 13:40 
AnswerRe: What is MSCD? PinmemberChris Hambleton15-Oct-07 17:42 
GeneralRe: What is MSCD? PinmemberSvenVdb6-Jan-08 11:11 
Questiondo you have a vb.net example of this Pinmemberregwood7925-Jul-07 6:04 
AnswerRe: do you have a vb.net example of this PinmemberChris Hambleton27-Jul-07 3:31 
GeneralToo Specific Pinmemberduwke25-Jun-07 5:15 
GeneralRe: Too Specific PinmemberChris Hambleton26-Jun-07 4:53 
GeneralRe: Too Specific Pinmemberduwke26-Jun-07 4:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140821.1 | Last Updated 15 Dec 2006
Article Copyright 2006 by Chris Hambleton
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid