• Download source code - 7.7 KB
• Download updated grid with search functionality - 6.91 KB

Introduction

In this article, I am going to discuss about my EnhanceGrid which has the following features:

1. Custom paging to fetch only the required number of records
2. Icon in the header of the column to display sorting direction
3. Dropdown in the pager to adjust the number of records in a page
4. Properties of the extended grid
5. How to use the custom grid control

Custom paging to fetch only the required number of records

Following is the Stored Procedure I use to provide custom paging feature in my custom grid. The comments explain the fields and give detailed information about the Stored Procedure:

CREATE PROCEDURE [dbo].[GetRequestedRecordByPage] 
 @FromList nvarchar(200)              -- Table Name  
,@SortingCol nvarchar(200)         -- Sorting column Name
,@SelectList nvarchar(200) = '*'         -- Select columns list
,@WhereClause nvarchar(200) = ''        -- Where clause i.e condition
,@PageNum int = 1                           -- Requested page number
,@PageSize int = 5                 -- No of record in page
,@TotalNoOfRecord int output         -- Total no of selected records
AS 
Begin
  SET NOCOUNT ON 
  DECLARE @Query nvarchar(max)         -- query going to be execute
 
  IF rtrim(ltrim(@WhereClause)) <> '' 
  BEGIN
      SET @Query ='SELECT   @TotalNoOfRecord = COUNT(*) 
                      FROM     ' + @FromList + ' 
        WHERE    ' + @WhereClause 
  END
  ELSE 
  BEGIN 
      SET @Query ='SELECT   @TotalNoOfRecord = COUNT(*) 
                      FROM     ' + @FromList 
  END

    /* Count no. of record */
       EXEC sp_executeSQL 
        @Query, 
        @params = N'@TotalNoOfRecord INT OUTPUT', 
         = @TotalNoOfRecord OUTPUT 

DECLARE @lbound int, @ubound int 




/* Calculating upper and lower bound */
        SET @lbound = ((@PageNum - 1) * @PageSize) 
        SET @ubound = @lbound + @PageSize + 1 


/* Get list of record(s) */
        SELECT @Query =  ''
        SELECT @Query =  'SELECT  * 
                          FROM    ( 
SELECT  ROW_NUMBER() OVER(ORDER BY ' + @SortingCol  + ') AS rownumber,' +@SelectList  +   
                                        ' FROM    ' + @FromList 
        
        IF rtrim(ltrim(@WhereClause)) <> '' 
        BEGIN
            SELECT @Query = @Query + ' WHERE   ' + @WhereClause 
        END

            SELECT @Query = @Query + '     ) AS tbl 
WHERE rownumber > ' + CONVERT(varchar(9), @lbound) + 
      ' AND rownumber < ' + CONVERT(varchar(9), @ubound) 
 
       EXEC (@Query)                 
End

As you can see, the above procedure is generic so that it can be used anywhere. I have therefore included it as part of my EnhanceGrid, as it is applicable in any project developed with ASP.NET.

Icon in the header of the column to display sorting direction

To shows the sorting icon in the header text, I have overridden the following events of the standard Grid control which is part of the .NET Framework.

• OnRowCreated: This event gets fired when rows are created. Here, if the RowType is Header, then I add a sorting icon by getting the index of the item that gets clicked, and then I call the SortingIcon method to add the icon in the header row.
C#

protected override void OnRowCreated(GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.Header)
    {
        /* Get sorting column index */
        int index = GetSortColumnIndex();
        if (index != -1)
        {
            sortingIcon(index, e.Row);
        }
    }
}

• OnSorting: This event gets called when the user clicks on the header column to sort records of the grid control. As you can seen from the code below, it stores the column sort expression in lblSortExp and stores the sort direction in lblSortDirection and reverses the currently stored value.
C#

protected override void OnSorting(GridViewSortEventArgs e)
{
    try
    {
        lblSortExp = e.SortExpression;

        switch (lblSortDirection)
        {
            case "asc":
            {
                lblSortDirection = "desc";
                break;
            }
            case "desc":
            case "":
            case null:
            {
                lblSortDirection = "asc";
                break;
            }
        }
        BindGrid();
    }
    catch (Exception ex) { }
}

• sortingIcon: This function is used to add sorting icons in the header of the column. In this function, I have created a Label which is added dynamically with the header text in the clicked header column.
C#

private void sortingIcon(int index, GridViewRow row)
{
    System.Web.UI.WebControls.Label lblSorting = 
                  new System.Web.UI.WebControls.Label();
    if (lblSortDirection == "desc")
    {
        lblSorting.Text = "<span style=\"font-family:" + 
                          "Marlett; font-weight:bold\">6</span>";
    }
    else
    {
        lblSorting.Text = "<span style=\"font-family:Marlett;" + 
                          " font-weight:bold\">5</span>";
    }
    row.Cells[index].Controls.Add(lblSorting);
}

• GetSortColumnIndex: This function is used to get the index of the column which is clicked by the user for sorting. In this function, I compare the sorting expression of the clicked column with each column and get the index of the clicked column. This is needed because I don’t know the index of the clicked column.
C#

private int GetSortColumnIndex()
{
    foreach (DataControlField field in this.Columns)
    {
        if (field.SortExpression.ToString() == lblSortExp)
        {
            return this.Columns.IndexOf(field);
        }
    }
    return -1;
}

Dropdown in the pager to adjust the number of records per page

For the number of records per page, I have overridden the same method that I overrode to show the sorting icon, but here the condition is changed. I check for the pager type row, as you can see below. In the code below, I have created a dropdown control which contains the per page record number and the attached selected change.

protected override void OnRowCreated(GridViewRowEventArgs e)
{
    try
    {
        if (e.Row.RowType == DataControlRowType.Pager)
        {
            DropDownList ddl ddlNoPages = new DropDownList();
            //adds variants of pager size
            ddlNoPages.Items.Add("10");
            ddlNoPages.Items.Add("50");
            ddlNoPages.Items.Add("100");
            ddlNoPages.AutoPostBack = true;
            //selects item due to the GridView current page size
            ListItem li = ddlNoPages.Items.FindByText(this.PageSize.ToString());
                    
            if (li != null)
                ddlNoPages.SelectedIndex = ddlNoPages.Items.IndexOf(li);
           
            ddlNoPages.SelectedIndexChanged += 
              new EventHandler(ddlNoPages _SelectedIndexChanged);
          
            //adds dropdownlist in the additional cell to the pager table
            Table pagerTable = e.Row.Cells[0].Controls[0] as Table;
            TableCell cell = new TableCell();
            cell.Style["padding-left"] = "50px";
            cell.Style["text-align"] = "right";
            cell.Controls.Add(new LiteralControl("Page Size:"));

            cell.Controls.Add(ddlNoPages);
            //e.Row.Cells[0].Controls.Add(cell);
            pagerTable.Rows[0].Cells.Add(cell);
       }
   }
   catch (Exception ex)
   {
   }
}

The following event gets fired when the combobox index gets changed. The code is very simple to understand, so I think there is no need to explain it in detail.

void ddlNoPages_SelectedIndexChanged(object sender, EventArgs e)
{
    if (PageSize > int.Parse(((DropDownList)sender).SelectedValue))
        IsPagesizeChanged = true;
    else
        IsPagesizeChanged = false;
    PageIndex = 0;
    //changes page size
    PageSize = int.Parse(((DropDownList)sender).SelectedValue);
    //binds data source
    BindGrid();
}

Note: There are still some errors in this method in which I am working, and I will provide a full solution for this.

After the above gets done, you require to override the following method of the grid for the custom paging Store Procedure to work. In this method, you have to set the values for the paging datasource properties.

protected override void InitializePager(GridViewRow row, 
          int columnSpan, PagedDataSource pagedDataSource)
{
   try
   {
     #region code for standard paging
     //To set custome paging
     pagedDataSource.AllowCustomPaging = true;
     //To set total no of records retived 
     pagedDataSource.VirtualCount = Convert.ToInt32(lbltotal);
     //To set current page index
     pagedDataSource.CurrentPageIndex = lblpageIndex;
     #endregion code for standard paging
               
     base.InitializePager(row, columnSpan, pagedDataSource);
    }
    catch (Exception ex) { }
}

You can also override the above method to provide custom paging of a different kind as per you client requirements.

Properties of the extended grid

Note that, most of the properties get their value form the ViewState and are set in the ViewState. It is by using the ViewState mechanism that the grid retains property values during postback events.

Another thing to note here is that the control ID is attached with each ViewState property because if you drop the same grid control two or more times on one page, it works property without interfering with other grid operations.

• lbltotal: This property of the grid is used to store the total number of records retrieved by the Stored Procedure; it is used to adjust the paging accordingly.
C#

public int lbltotal
{
    get
    {
        if (null != ViewState["lbltotal" + ControlID])
                 return (int)ViewState["lbltotal" + ControlID];
        else
                 return 0;
    }
    set
    {
          ViewState["lbltotal" + ControlID] = value;
    }
}

• lblpageIndex: Stores the current page index.
C#

public int lblpageIndex

{
   get
   {
        if (null != ViewState["lblpageIndex" + ControlID])
                return (int)ViewState["lblpageIndex" + ControlID];
        else
                 return 0;
   }
   set
   {
          ViewState["lblpageIndex" + ControlID] = value;
   }
}

• lblSortDirection: Stores the sorting direction of the column.
C#

public string lblSortDirection
{
  get
  {
     if (null != ViewState["lblSortDirection" + ControlID])
        return (string)ViewState["lblSortDirection" + ControlID];
     else
        return string.Empty;
  }
  set
  {
     ViewState["lblSortDirection" + ControlID] = value;
  }
}

• lblSortExp: Stores the sorting expression, i.e., column sorting expression.
C#

public string lblSortExp
{
    get
    {
        if (null != ViewState["lblSortExp" + ControlID])
            return (string)ViewState["lblSortExp" + ControlID];
        else
            return string.Empty;
    }
    set
    {
        ViewState["lblSortExp" + ControlID] = value;
    }
}

• FromClause: Stores the From clause of the query which is passed to the Stored Procedure to retrieve records.
C#

public string FromClause
{
    get
    {
       if (null != ViewState["fromClause" + ControlID])
         return (string)ViewState["fromClause" + ControlID];
       else
          return string.Empty; 
    }
    set
    {
         ViewState["fromClause" + ControlID] = value;
    }
}

• WhereClause: Stores the Where clause of the query which is passed as the where condition of the query to the Stored Procedure.
C#

public string WhereClause
{
    get
    {
        if (null != ViewState["whereClause" + ControlID])
            return (string)ViewState["whereClause" + ControlID];
         else
             return string.Empty;
     }
     set
     {
         ViewState["whereClause" + ControlID] = value;
     }
}

• SelectList: Stores the select list column name which is going to be passed to the Stored Procedure.
C#

public string SelectList
{
   get
   {
       if (null != ViewState["selectList" + ControlID])
           return (string)ViewState["selectList" + ControlID];
       else
           return string.Empty;
   }
   set
   {
       ViewState["selectList" + ControlID] = value;
   }
}

• ControlID: Stores the ID of the control.
C#

private string _controlId;

public string ControlID
{
   get { return _controlId; }
   set { _controlId = value; }
}

• DefaultSortExp: Stores the default sort expression which is used by the grid for sorting purposes till the first sorting event occurs.
C#

private string _DefaultSortExp;
public string DefaultSortExp
{
     set{ _DefaultSortExp = value;}
     get{ return _DefaultSortExp;}
}

Other important things

Following are the properties which allow to attach your own events when using the grid on a page.

public event GridViewRowEventHandler onRowCreate
{
    add
    {
        base.RowCreated += value;
    }
    remove
    {
        base.RowCreated -= value;
    }
}

public event GridViewSortEventHandler onSort
{

    add
    {
         base.Sorting += value;
    }
    remove
    {
         base.Sorting -= value;
    }
}

How to use the custom grid control

Following is the code to register the grid control on your page:

<%@ Register TagPrefix="cc"  Namespace="AppEngine.ComponentControls" Assembly="__code" %>

Here is the code to use the grid control on your ASPX page:

<cc:MyGridView  runat="server" ID="grdEmployee" 
      AutoGenerateColumns="False" AllowPaging="true"
      AllowSorting="true" DefaultSortExp="FIRSTNAME"
      EnableSortingAndPagingCallbacks = "false">
  <Columns>
    <asp:BoundField DataField="FIRSTNAME" 
        HeaderText="FIRSTNAME" SortExpression="FIRSTNAME" />
    <asp:BoundField DataField="LASTNAME" 
        HeaderText="LASTNAME" SortExpression="LASTNAME" />
    <asp:BoundField DataField="LOGINNAME" 
        HeaderText="LOGINNAME" SortExpression="LOGINNAME" />
    <asp:BoundField DataField="EMAIL" 
        HeaderText="EMAIL" SortExpression="EMAIL" />
  </Columns>
  <PagerSettings Mode="NumericFirstLast" Position ="TopAndBottom"
             PageButtonCount="5"  />
  <PagerStyle BackColor="Pink" />
</cc:MyGridView>

The following code is part of your aspx.cs file. As you can see in the code below, I specified SelectList, which is a list of columns; FromClause contains the table name, which is Employee here; WhereClause has the filter condition.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        grdEmployee.SelectList = "[FIRSTNAME],[LASTNAME],[LOGINNAME],[EMAIL]";
        grdEmployee.FromClause = "[EMPLOYEE]";
        grdEmployee.WhereClause = string.Empty;
        //grdEmployee.WhereClause = "[FIRSTNAME] like '%a'";
        grdEmployee.BindGrid();
    }
}

Search with Grid

Following is an example of searching data in the EnhanceGrid control:

For searching purposes, I have added the following controls on the page where I drop the grid control which helps to search a record:

• ddlColumn: Which contains the name of the column which is going to be displayed by the EnhanceGrid control. The point to note here is the value field of the the list item which has the names of the columns of the database table.
• txtValue: The control which allows the user to enter a value to search for a particular column.
ASP.NET

<div style="width: 100%;">
<div style="float: left; width : 10%;">
<asp:Label runat="server" ID="lblSearch" Text="Select Criteria"></asp:Label>
        </div>
        <div style="float: left; width :10%;">
            <asp:DropDownList runat="server" ID="ddlColumn">
<asp:ListItem Text="First Name" Value="FIRSTNAME"></asp:ListItem>
<asp:ListItem Text="Last Name" Value="LASTNAME"></asp:ListItem>
<asp:ListItem Text="Login ID" Value="LOGINNAME"></asp:ListItem>
<asp:ListItem Text="E-mail ID" Value="EMAIL"></asp:ListItem>
            </asp:DropDownList>
        </div>
  <div style="float: left; width :10%;">
<asp:Label runat="server" ID="lblValue" Text="Value"></asp:Label>
        </div>
        <div style="float: left; width :15%;">
            <asp:TextBox runat="server" ID="txtValue" ></asp:TextBox>
        </div>
        <div>
            <asp:Button runat="server" ID="btnSearch" Text="Search" 
                onclick="btnSearch_Click" />
        </div>
</div>

• btnSearch: Which contains the code for searching a particular data from a database. As you can see in the code below, if there are values present in the txtValue text field, it forms a Where clause, and if not present, then it passes an empty Where clause which searches all the records of the grid control.
C#

protected void btnSearch_Click(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(txtValue.Text))
    {
        grdEmployee.WhereClause = ddlColumn.SelectedValue + 
                                  " like '%" + txtValue.Text + "%'";
    }
    else
    {
        grdEmployee.WhereClause = string.Empty;
    }
    grdEmployee.SelectList = 
      "[FIRSTNAME],[LASTNAME],[LOGINNAME],[EMAIL]";
    grdEmployee.FromClause = "[EMPLOYEE]";
    grdEmployee.BindGrid();    
}