GridView-Search Control

• Download source files - 3.93 Kb
• Download demo project - 107 Kb

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

Advanced View of the GridViewSearch Control

Results of a Basic Search

Results of an Advanced Search

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!