LINQ TO SQL GridView (Enhanced Gridview)Introduction
Now a days, most developers create new web or Windows applications using LINQ 2 SQL. In this article, I am going to discuss about building Custom GridView control which supports any LINQ 2 SQL application structure.
Grid view control has the following features:
•Linq 2 SQL support
•Custom paging using Linq 2 SQL
•Display sorting direction
•Dropdown in the pager to adjust the number of records in a page
Implementation
The application is divided in three layer.
Layer 1: LINQ to SQL Data Access Layer
This layer contains DBML file generated by Visual Studio form the database selected used to demonstrate grid control.
Layer 2: Business Layer
It consists of three files:
Dynamic - which contains implementation of class to support dynamic linq execution.(See this link.)
EntityList - which contains implementation of Generic class to fetch data to display in grid view control.
LinqGridView - which contains implementation of custom grid view control.
EntityList.CS
Declares the Generic Entity EntityList class with generic type T. (where T : class means the type argument must be a reference type; this applies also to any class, interface, delegate, or array type.)
public class EntityList where T : class
{
public EntityList()
{
//
// TODO: Add constructor logic here
//
}
Property
getAllEntity - List out all entities of the Type T.
The below line of code declares a static generic property which returns System.Data.Linq.Table of type T. MyDataContext is datacontext class generated by Visual Studio, which is used to call GetTable method of the DataAccess layer to get entity of type T.
public static System.Data.Linq.Table getAllEntity
{
get
{
MyDataContext db = new MyDataContext();
return db.GetTable();
}
}
DataContext: Represents the main entry point for the LINQ to SQL framework. (See this link.)
Method
GetCount - Method used to get count of total number of entities.
public static int GetCount(string whereClause)
{
if (!string.IsNullOrEmpty(whereClause))
return getAllEntity.Where(whereClause).Count();
else
return getAllEntity.Count();
}
whereClause - is where condition if it's not empty or null, then method returns count of records which filter by where clause. And if null or empty, then it returns count of record without any filtered condition.
GetEntityByPage - Method used to get the list of enity by page, i.e., to support on demand page in grid view control.
•startRowIndex - is staring row index i.e. number
•maximumRows - number of rows in one page
•whereClause - condition to filter records
•orderBy - column name with ascending or descending order.
public static IQueryable GetEntityByPage
(int startRowIndex, int maximumRows, string whereClause, string orderBy)
{
If the whereClause and orderBy both are not null and not empty, then its applies wherecondition to filter and orderby to order entity list.
if (!string.IsNullOrEmpty(whereClause) && !string.IsNullOrEmpty(orderBy))
{
return getAllEntity.Where(whereClause).OrderBy(orderBy).Skip(startRowIndex
* maximumRows).Take(maximumRows);
}
If the whereClause is null or empty and orderBy is not null and not empty, then applies orderby to order entity list.
else if (string.IsNullOrEmpty(whereClause) && !string.IsNullOrEmpty(orderBy))
{
return getAllEntity.OrderBy(orderBy).Skip(startRowIndex
* maximumRows).Take(maximumRows);
}
If the orderBy is null or empty and whereClause is not null and not empty, then applies wherecondition to filter entity list.
else if (!string.IsNullOrEmpty(whereClause) && string.IsNullOrEmpty(orderBy))
{
return getAllEntity.Where(whereClause).Skip(startRowIndex
* maximumRows).Take(maximumRows);
}
If the orderBy and whereClause are both null or empty, then return list of enity.
else
{
return getAllEntity.Skip(startRowIndex * maximumRows).Take(maximumRows);
}
The following two methods play an important role to support on demand paging for the grid view control.
- Skip: Bypasses a specified number of elements in a sequence and then returns the remaining elements. (See this link.)
- Take: Returns a specified number of contiguous elements from the start of a sequence.
}
}
LinqGridView.cs
public class LinqGridView : GridView
{
Property
lbltotal - Property of the grid is used to store the total number of records retrieved. 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;
}
}
WhereClause - Stores the Where clause of the query which is passed as the where condition.
public string WhereClause
{
get
{
if (null != ViewState["whereClause" + ControlID])
return (string)ViewState["whereClause" + ControlID];
else
return string.Empty;
}
set
{
ViewState["whereClause" + 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;
}
}
typeHolder - Property holds the type of the entity which is binded to the grid view control.
private Type typeHolder
{
get
{
if (null != ViewState["typeHolder" + ControlID])
return (Type)ViewState["typeHolder" + ControlID];
else
return null;
}
set
{
ViewState["typeHolder" + ControlID] = value;
}
}
Method
Bindgrid - Method bind collection on the info class to the grid view.
public void BindGrid() where T : class
{
try
{
The following stores the type of the class in the typeHolder variable which is later used for binding grid with type when sorting and searching take place.
if (null == typeHolder)
typeHolder = typeof(T);
The below line of code stores the sorting expression default expression assigned to grid and stores the pageIndex of grid.
if (string.IsNullOrEmpty(lblSortExp))
lblSortExp = DefaultSortExp;
lblpageIndex = this.PageIndex;
Create and store orderby expression which in turn is used by the linq query info class collection.
string orderby =
"" + lblSortExp + " " + (lblSortDirection == string.Empty ?
" asc" : lblSortDirection);
lbltotal = EntityList.GetCount(WhereClause);
this.DataSource = EntityList.GetEntityByPage
(PageIndex, PageSize, WhereClause, orderby);
this.DataBind();
}
catch (Exception ex) { }
}
InitializePager - overridden to provide custom paging in the gridview control.
protected override void InitializePager
(GridViewRow row, int columnSpan, PagedDataSource pagedDataSource)
{
try
{
#region code for standard paging
Set the custom paging property to true which allows to set custom datasource for the paging.
pagedDataSource.AllowCustomPaging = true;
Set total no of record retrieved for the custom datasource.
pagedDataSource.VirtualCount = Convert.ToInt32(lbltotal);
Set the current page index for the custom datasource.
pagedDataSource.CurrentPageIndex = lblpageIndex;
#endregion code for standard paging
Set custom datasource for the grid view control.
base.InitializePager(row, columnSpan, pagedDataSource);
}
catch (Exception ex) { }
}
OnRowCreated - overridden to provide Dropdown which allows use to change paging to set number of records per page in gridview control. This method is also overridden to show sorting icon in header row of the grid.
protected override void OnRowCreated(GridViewRowEventArgs e)
{
try
{
Get the sorting column index and sets the sorting icon in header of the grid control.
#region set the icon in header row
if (e.Row.RowType == DataControlRowType.Header)
{
int index = GetSortColumnIndex();
if (index != -1)
sortingIcon(index, e.Row);
}
#endregion set the icon in header row
Add dropdown box control to page row of the grid to set number of record per page.
if (e.Row.RowType == DataControlRowType.Pager)
{
Create dropdown control and add varieties of pager size.
DropDownList ddl = new DropDownList();
ddl.Items.Add("5");
ddl.Items.Add("10");
ddl.AutoPostBack = true;
Set the pagesize in the dropdown box selected by end user.
ListItem li = ddl.Items.FindByText(this.PageSize.ToString());
if (li != null)
ddl.SelectedIndex = ddl.Items.IndexOf(li);
ddl.SelectedIndexChanged -=
new EventHandle(ddl_SelectedIndexChanged);
ddl.SelectedIndexChanged +=
new EventHandler(ddl_SelectedIndexChanged);
The following line of code adds table cell to the page row the grid view which contains drop down to set number of records per page.
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(ddl);
pagerTable.Rows[0].Cells.Add(cell);
}
}
catch (Exception ex)
{ }
base.OnRowCreated(e);
}
ddl_SelectedIndexChanged - attached with the paging dropdown box control which gets fired when the page size changes.
void ddl_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.PageSize > int.Parse(((DropDownList)sender).SelectedValue))
IsIndexChange = true;
else
IsIndexChange = false;
this.PageIndex = 0;
//changes page size
this.PageSize = int.Parse(((DropDownList)sender).SelectedValue);
Code uses the reflection to call the generic Bind method of the grid to bind the records again with the gridview when the page size changes in dropdown box.
MethodInfo method = this.GetType().GetMethod("BindGrid");
MethodInfo generic = method.MakeGenericMethod(typeHolder);
generic.Invoke(this, null);
}
OnSorting - overridden to set sorting icon on grid column when sorting event caused by the use to sort the records of the grid view control.
protected override void OnSorting(GridViewSortEventArgs e)
{
try
{
Line of code stores last expression in the lblSortExp variable.
lblSortExp = e.SortExpression;
switch (lblSortDirection)
{
case "asc":
{
lblSortDirection = "desc";
break;
}
case "desc":
case "":
case null:
{
lblSortDirection = "asc";
break;
}
}
Call the bind method of the grid to bind the records to the gridview after sorting event takes place.
MethodInfo method = this.GetType().GetMethod("BindGrid");
MethodInfo generic = method.MakeGenericMethod(typeHolder);
generic.Invoke(this, null);
}
catch (Exception ex) { }
}
OnPageIndexChanging - overridden to bind grid again with the record with the update page index.
protected override void OnPageIndexChanging(GridViewPageEventArgs e)
{
try
{
if (!IsIndexChange)
{
PageIndex = e.NewPageIndex;
MethodInfo method = this.GetType().GetMethod("BindGrid");
MethodInfo generic = method.MakeGenericMethod(typeHolder);
generic.Invoke(this, null);
base.OnPageIndexChanged(e);
}
else
IsIndexChange = false;
}
catch (Exception ex) { }
}
sortingIcon - utilize to set sorting icon to the header column.
private void sortingIcon(int index, GridViewRow row)
{
Line of code creates label control and adds that label to column text to show sort direction.
System.Web.UI.WebControls.Label lblSorting =
new System.Web.UI.WebControls.Label();
if (lblSortDirection == "desc")
{
lblSorting.Text = "6";
}
else
{
lblSorting.Text = "5";
}
row.Cells[index].Controls.Add(lblSorting);
}
GetSortColumnIndex - 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;
}
}
Layer 3 : Presentation Layer
This layer actually discusses about how to use custom gridview control in any application which use linq to SQL.
Default.aspx
This line of the code registers the custom control which is part of businesslayer.
<%@ Register TagPrefix="CC" Namespace="ComponentControls"
Assembly="BusinessLayer" %> Below line of the code utilize
custom gridview by binding it with the employee class of the
datacontext layer. Here first template column used to display
no with the records like the rownumber and other template column
bind the property of the class to display it.
The below line of the code utilizes custom gridview by binding it with the employee class of the datacontext layer. Here the first template column is used to display number with the records like the rownumber and other template column binds the property of the class to display it. ]
<CC:LinqGridView runat="server" DataKeyNames="pkey"
AutoUpdateAfterCallBack="true" Width="100%" ID="grduser"
AutoGenerateColumns="False" AllowPaging="true" AllowSorting="true"
DefaultSortExp="FirstName">
<Columns>
<asp:TemplateField HeaderText="No." ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<%#String.Format("{0}", (((GridViewRow)Container).RowIndex + 1) +
(grduser.lblpageIndex * 10))%>
</ItemTemplate>
<ItemStyle Width="2%" />
</asp:TemplateField>
<asp:BoundField HeaderText="FirstName" DataField="FirstName"
SortExpression="FirstName" ReadOnly="true" HeaderStyle-Width="120px"
ItemStyle-Width="120px" />
<asp:BoundField HeaderText="LastName" DataField="LastName"
SortExpression="LastName" ReadOnly="true" HeaderStyle-Width="120px"
ItemStyle-Width="120px" />
<asp:BoundField HeaderText="LOGINNAME" DataField="LOGINNAME"
SortExpression="LOGINNAME" ReadOnly="true" HeaderStyle-Width="120px"
ItemStyle-Width="120px" />
<asp:BoundField HeaderText="EMAIL" DataField="EMAIL" SortExpression="EMAIL"
ReadOnly="true" HeaderStyle-Width="120px" ItemStyle-Width="120px" />
</Columns>
<PagerSettings Mode="NumericFirstLast" Position="Top" PageButtonCount="5" />
<PagerStyle BackColor="Pink" />
</CC:LinqGridView>
Default.aspx.cs
In this file, there are two lines of code to bind gridview control. When page gets loaded, it calls BindGrid method which then calls custom grid BindGrid method to bind with Employee entity by passing Employee in generic type bracket.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
///
/// Method to bind datagrid of user.
///
private void BindGrid()
{
try
{
grduser.BindGrid();
}
catch (Exception ex)
{ }
}
Conclusion
This grid control implementation provides a basic implementation of a custom control and also gives information about Linq to SQL.