Click here to Skip to main content
11,478,174 members (77,034 online)
Click here to Skip to main content

GridView-Search Control

, 15 Dec 2006 155.6K 8.1K 67
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 Pin
valleyriver24-May-13 14:31
membervalleyriver24-May-13 14:31 
Questiondb error Pin
ankit_parmar55513-Dec-11 2:57
memberankit_parmar55513-Dec-11 2:57 
GeneralMy vote of 1 Pin
ankit_parmar55513-Dec-11 2:47
memberankit_parmar55513-Dec-11 2:47 
GeneralMy vote of 2 Pin
arunk5256-Dec-11 7:36
memberarunk5256-Dec-11 7:36 
GeneralThanks for the samples and demo Pin
Robert Currie28-Jul-10 7:41
memberRobert Currie28-Jul-10 7:41 
GeneralRe: Thanks for the samples and demo Pin
Chris Hambleton28-Jul-10 7:58
memberChris Hambleton28-Jul-10 7:58 
GeneralMy vote of 4 Pin
Robert Currie28-Jul-10 7:37
memberRobert Currie28-Jul-10 7:37 
Questionwhat is MSCD? Pin
debjyoti.biswas3-Dec-09 11:33
memberdebjyoti.biswas3-Dec-09 11:33 
QuestionTrying to use the search control with my database Pin
theeskyline15-Jul-09 19:31
membertheeskyline15-Jul-09 19:31 
GeneralCan't use it Pin
ambola22-May-09 21:34
memberambola22-May-09 21:34 
GeneralRe: Can't use it Pin
Chris Hambleton26-May-09 4:40
memberChris Hambleton26-May-09 4:40 
General[Message Removed] Pin
Mojtaba Vali9-Apr-08 2:20
memberMojtaba Vali9-Apr-08 2:20 
GeneralRe: Better Solution Pin
Member 175156214-Apr-08 23:29
memberMember 175156214-Apr-08 23:29 
General[Message Removed] Pin
Mojtaba Vali15-Apr-08 2:45
memberMojtaba Vali15-Apr-08 2:45 
GeneralRe: Better Solution Pin
Aldorado9-Sep-08 14:57
memberAldorado9-Sep-08 14:57 
GeneralGreat work!! but make it simple for us Pin
rama charan4-Dec-07 23:59
memberrama charan4-Dec-07 23:59 
QuestionWhat is MSCD? Pin
Brian Hart15-Oct-07 14:40
memberBrian Hart15-Oct-07 14:40 
AnswerRe: What is MSCD? Pin
Chris Hambleton15-Oct-07 18:42
memberChris Hambleton15-Oct-07 18:42 
GeneralRe: What is MSCD? Pin
SvenVdb6-Jan-08 12:11
memberSvenVdb6-Jan-08 12:11 
Questiondo you have a vb.net example of this Pin
regwood7925-Jul-07 7:04
memberregwood7925-Jul-07 7:04 
AnswerRe: do you have a vb.net example of this Pin
Chris Hambleton27-Jul-07 4:31
memberChris Hambleton27-Jul-07 4:31 
GeneralToo Specific Pin
duwke25-Jun-07 6:15
memberduwke25-Jun-07 6:15 
GeneralRe: Too Specific Pin
Chris Hambleton26-Jun-07 5:53
memberChris Hambleton26-Jun-07 5:53 
GeneralRe: Too Specific Pin
duwke26-Jun-07 5:57
memberduwke26-Jun-07 5: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 | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 15 Dec 2006
Article Copyright 2006 by Chris Hambleton
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid