ASP.NET GridView with search option (SearchableGridView)

• Download demo - 10.8 KB
• Download source - 22.1 KB

Introduction

I was searching for a way to include a search textbox option in the ASP.NET GridView control. I couldn’t find an elegant solution and decided to implement it myself. So here’s my solution for the problem.

Why use this solution?

You can use this solution and implement row filtering in a grid very easily. Search and filter operations can be performed by just handling the search event that is raised. In addition, this GridView also allows you to set options to show row serial numbers, total number of rows, and show the header and footer even when no rows are available in the GridView. (By default, the header and footer are hidden when the GridView has no rows to display.)

The solution

What I have done

1. I’ve extended the GridView and created a SearchableGridView class.
2. Added a TemplateColumn to display the row number.
3. Added controls in the footer to handle the search operation.
4. Raise an event with the search string as argument when a search is fired.

The code

I’ve extended the GridView control as a SearchableGridView to include the search option in the footer of the GridView.

public class SearchGridView : GridView

In order to show the row serial number, I’ve created the following template column:

public class NumberColumn : ITemplate
{
    public void InstantiateIn(Control container)
    {

    }
}

In SearchableGridView, I’ve overridden the OnInit function to add the template column as the first column to show the row serial number, if the ShowRowNumber flag is turned on.

protected override void OnInit(EventArgs e)
{
    base.OnInit(e);
    //If showrownumber option is turned on then add 
    //the template column as the first column.
    if (!IsDesign() && ShowRowNumber) 
    {
        TemplateField tmpCol = new TemplateField();
        NumberColumn numCol = new NumberColumn();
        tmpCol.ItemTemplate = numCol;
        // Insert this as the first column
        this.Columns.Insert(0, tmpCol);
    }
}

The OnRowCreated method is called every time a row is created. During runtime, depending on the RowType, I add the search controls and the number of the row's label (in the footer), the row number (in every row), and the column header for the row number column (in the header).

protected override void OnRowCreated(GridViewRowEventArgs e)
{
    base.OnRowCreated(e);
    if (!IsDesign()) //During Runtime
    {
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            //If ShowFooter is set to true
            if (ShowFooter && e.Row.Cells.Count > 0)
            {
                //If TotalRows has to be shown
                if (ShowTotalRows)
                {
                    e.Row.Cells[0].Text = ViewState[NO_OF_ROWS] + " Rows.";
                }
                if (e.Row.Cells[e.Row.Cells.Count - 1].Controls.Count == 0)
                {
                    //Create the search control
                    Table table = new Table();
                    table.Style.Add("width", "100%");
                    table.Style.Add("align", "right");
                    TableRow tr = new TableRow();
                    TableCell tc = new TableCell();
                    tc.Style.Add("align", "right");
                    tc.Style.Add("width", "100%");

                    //Populate the dropdownlist with the Ids
                    //of the columns to be filtered
                    if (_ddlFinder.Items.Count == 0)
                        SetFilter();

                    _btnSearch.Width = 20;
                    _btnSearch.Height = 20;
                    _btnSearch.ImageAlign = ImageAlign.AbsMiddle;
                    _btnSearch.AlternateText = "Search";
                    //Assign the function that is called when search button is clicked
                    _btnSearch.Click += new ImageClickEventHandler(_btnSearch_Click);
                    
                    tc.Controls.Add(_ddlFinder);
                    tc.Controls.Add(_tbSearch);
                    tc.Controls.Add(_btnSearch);
                    tr.Cells.Add(tc);
                    table.Rows.Add(tr);

                    _pnlSearchFooter.Controls.Add(table);
                    e.Row.Cells[e.Row.Cells.Count - 1].Controls.Add(_pnlSearchFooter);
                
                }
            }
        }
        if (e.Row.RowType == DataControlRowType.Header)
        {
            // If ShowHeader is set to true and 
            // If Row number has to be shown

            if (ShowRowNumber && ShowHeader) 
            {
                e.Row.Cells[0].Text = "Sno";
            }
        }
        else if (e.Row.RowType == DataControlRowType.DataRow)
        {
            if (ShowRowNumber)
            {
                //Set the row number in every row
                e.Row.Cells[0].Text = (e.Row.RowIndex + 
                  (this.PageSize * this.PageIndex) + 1).ToString();
            }
        }
    }
}

The SearchFilters property of the SearchableGridView sets the dropdownlist values for the search option. The Text property of the list item corresponds to the display name in the dropdownlist and the Value property of the list item is the column name of the data source.

public void SetFilter()
{
    _ddlFinder.Items.Clear();
    //Copy the items to the dropdownlist
    foreach (ListItem li in SearchFilters)
        _ddlFinder.Items.Add(li);
}

Now, let's move on to the handling of the search event. For this, I created a delegate and fire the event SearchGrid, when the search button is hit. The search string is formed using the syntax _ddlFinder.SelectedValue + " like '" + _tbSearch.Text.Trim() + "%'".

public delegate void SearchGridEventHandler(string _strSearch);
public event SearchGridEventHandler SearchGrid;

void _btnSearch_Click(object sender, ImageClickEventArgs e)
{
    string sSearchText = ConstructSearchString();
    OnSearchGrid(sSearchText);
}

protected string ConstructSearchString()
{
    string _strText = _tbSearch.Text.Trim();

    if (_strText == string.Empty)
        return string.Empty;

    return _ddlFinder.SelectedValue + " like '" + _strText + "%'";
}

protected void OnSearchGrid(string _strSearch)
{
    if (SearchGrid != null)
    {
        SearchGrid(_strSearch);
    }
}

Showing the footer when no rows are returned

The default property of the GridView is to hide the header and footer when no rows are bound to it. Setting an empty item template would only show the template but not the header or the footer. In our case, the footer has to be shown always, irrespective of the number of rows bound to the SearchableGridView, because the search option should be visible. To achieve this, I had to override the CreateChildControls method as below:

protected override int CreateChildControls(System.Collections.IEnumerable dataSource, 
                                           bool dataBinding)
{
    int count = base.CreateChildControls(dataSource, dataBinding);

    //  no rows in grid. create header and footer in this case
    if (count == 0 && (ShowEmptyFooter || ShowEmptyHeader))
    {
        //  create the table
        Table table = this.CreateChildTable();

        DataControlField[] fields;
        if (this.AutoGenerateColumns)
        {
            PagedDataSource source = new PagedDataSource();
            source.DataSource = dataSource;

            System.Collections.ICollection autoGeneratedColumns = 
                                           this.CreateColumns(source, true);
            fields = new DataControlField[autoGeneratedColumns.Count];
            autoGeneratedColumns.CopyTo(fields, 0);
        }
        else
        {
            fields = new DataControlField[this.Columns.Count];
            this.Columns.CopyTo(fields, 0);
        }

        if (ShowEmptyHeader)
        {
            //  create a new header row
            GridViewRow headerRow = base.CreateRow(-1, -1, DataControlRowType.Header, 
                                                   DataControlRowState.Normal);
            this.InitializeRow(headerRow, fields);
            // Fire the OnRowCreated event to handle showing row numbers
            OnRowCreated(new GridViewRowEventArgs(headerRow));
            //  add the header row to the table
            table.Rows.Add(headerRow);
        }

        //  create the empty row
        GridViewRow emptyRow = new GridViewRow(-1, -1, DataControlRowType.EmptyDataRow, 
                                               DataControlRowState.Normal);
        TableCell cell = new TableCell();
        cell.ColumnSpan = fields.Length;
        cell.Width = Unit.Percentage(100);

        //  respect the precedence order if both EmptyDataTemplate
        //  and EmptyDataText are both supplied ...
        if (this.EmptyDataTemplate != null)
        {
            this.EmptyDataTemplate.InstantiateIn(cell);
        }
        else if (!string.IsNullOrEmpty(this.EmptyDataText))
        {
            cell.Controls.Add(new LiteralControl(EmptyDataText));
        }

        emptyRow.Cells.Add(cell);
        table.Rows.Add(emptyRow);

        if (ShowEmptyFooter)
        {
            //  create footer row
            GridViewRow footerRow = base.CreateRow(-1, -1, DataControlRowType.Footer, 
                                                   DataControlRowState.Normal);
            this.InitializeRow(footerRow, fields);
            // Fire the OnRowCreated event to handle showing
            // search tool and total number of rows
            OnRowCreated(new GridViewRowEventArgs(footerRow));

            //  add the footer to the table
            table.Rows.Add(footerRow);
        }

        this.Controls.Clear();
        this.Controls.Add(table);
    }

    return count;
}

Working sample

Let me illustrate the above control with the help of an example. For this purpose, I have used the Customers table from the NorthWind database.

Step 1: Create a data source dsCustomers with the Select query: "SELECT CustomerID, CompanyName, Address, City, Country FROM Customers".

Step 2 : Create an instance of SearchableGridView and customize the SearchFilters property to have the list of columns on which the search can be performed.

Step 3: Add two hidden fields hfSearchText and hfSort to store the search text and the sort text, respectively.

Step 4: Implement the SearchGrid event to set the search string on the data source and filter the rows in the SearchableGridView. hfSearchText and hfSort are hidden fields that hold the search string and the sort string of the SearchableGridView. The BindData method binds the data after filtering and sorting.

protected void SearchGridView1_SearchGrid(string _strSearch)
{
    hfSearchText.Value = _strSearch;
    BindData();
}

protected void SearchGridView1_Sorting(object sender, GridViewSortEventArgs e)
{
    //If hfSort has the same value as before, 
    //the sorting should be done in descending order
    if (hfSort.Value == e.SortExpression)
        hfSort.Value = e.SortExpression + " Desc";
    else
        hfSort.Value = e.SortExpression;
    BindData();
}

void BindData()
{
    //hfSearchText has the search string returned from the grid.
    if (hfSearchText.Value != "")
        dsCustomers.SelectCommand += " where " + hfSearchText.Value;
    DataView dv = (DataView)dsCustomers.Select(new DataSourceSelectArguments());
    //hfSort has the sort string returned from the grid.
    if (hfSort.Value != "")
        dv.Sort = hfSort.Value;

    SearchGridView1.DataSource = dv;
    try
    {
        SearchGridView1.DataBind();
    }
    catch (Exception exp)
    {
        //If databinding threw exception b’coz current 
        //page index is > than available page index
        SearchGridView1.PageIndex = 0;
        SearchGridView1.DataBind();
    }
    finally
    {
        //Select the first row returned
        if (SearchGridView1.Rows.Count > 0)
            SearchGridView1.SelectedIndex = 0;
    }
}

Conclusion

SearchableGridView will be very useful when there are a large number of rows in the grid. Searching through the rows can be implemented without much hassle.