Introduction
In this article, I am going to discuss about my EnhanceGrid which has the following features:
- Custom paging to fetch only the required number of records
- Icon in the header of the column to display sorting direction
- Dropdown in the pager to adjust the number of records in a page
- Properties of the extended grid
- 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)
,@SortingCol nvarchar(200)
,@SelectList nvarchar(200) = '*'
,@WhereClause nvarchar(200) = ''
,@PageNum int = 1
,@PageSize int = 5
,@TotalNoOfRecord int output
AS
Begin
SET NOCOUNT ON
DECLARE @Query nvarchar(max)
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
EXEC sp_executeSQL
@Query,
@params = N'@TotalNoOfRecord INT OUTPUT',
= @TotalNoOfRecord OUTPUT
DECLARE @lbound int, @ubound int
SET @lbound = ((@PageNum - 1) * @PageSize)
SET @ubound = @lbound + @PageSize + 1
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.
protected override void OnRowCreated(GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
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.
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.
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.
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();
ddlNoPages.Items.Add("10");
ddlNoPages.Items.Add("50");
ddlNoPages.Items.Add("100");
ddlNoPages.AutoPostBack = true;
ListItem li = ddlNoPages.Items.FindByText(this.PageSize.ToString());
if (li != null)
ddlNoPages.SelectedIndex = ddlNoPages.Items.IndexOf(li);
ddlNoPages.SelectedIndexChanged +=
new EventHandler(ddlNoPages _SelectedIndexChanged);
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);
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;
PageSize = int.Parse(((DropDownList)sender).SelectedValue);
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
pagedDataSource.AllowCustomPaging = true;
pagedDataSource.VirtualCount = Convert.ToInt32(lbltotal);
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.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.
<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.
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();
}
