LINQ TO SQL GridView (Enhanced Gridview)

Rate this article on code project

Introduction

Now days most of the developer creates new web or windows application using the LINQ 2 SQL. In this article I am going to discuss about the building Custom GridView control which supports any LINQ 2 SQL application structure.

Grid view control has following feature:

• 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

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.(http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx)
EntityList - Which contains implementation of Generic class to fetch data to display in grid view control.
LinqGridView - Which contains implmentation 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<T> where T : class 
{ 
   public EntityList() 
   { 
      // 
     // TODO: Add constructor logic here 
     // 
   } 

Property

getAllEntity - List out all entity of the Type T.

Below line of the code declare a static generic property which return System.Data.Linq.Table of type T. MyDataContext is datacontext class generated by the 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<T> getAllEntity 
{ 
  get 
  { 
      MyDataContext db = new MyDataContext(); 
      return db.GetTable<T>(); 
   } 
} 

DataContext : Represents the main entry point for the LINQ to SQL framework. (http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.aspx)

Method

GetCount - Method used to get count of total number of entity.

public static int GetCount(string whereClause) 
{ 
  if (!string.IsNullOrEmpty(whereClause)) 
     return getAllEntity.Where(whereClause).Count(); 
  else 
     return getAllEntity.Count(); 
}

whereClause - is where condition if its not empty or null than method return count of recrods which filter by where clause. And if null or empty than it return 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 - numberber of row in one page.
whereClause - condition to filter reocrds.
orderBy - column name with asending or decending order.

public static IQueryable<T> GetEntityByPage
(int startRowIndex, int maximumRows, string whereClause, string orderBy) 
{ 

If the whereClause and orderBy both is not null and not empty than its applies whereconditon to filter and orderby to order entiry 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 than applies orderby to order entiry 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 than applies wherecondition to filter enity list.

else if (!string.IsNullOrEmpty(whereClause) && string.IsNullOrEmpty(orderBy)) 
{ 
       return getAllEntity.Where(whereClause).Skip(startRowIndex 
                * maximumRows).Take(maximumRows); 
} 

If the orderBy and whereClause both null or empty than return list of enity.

else 
{ 
       return getAllEntity.Skip(startRowIndex * maximumRows).Take(maximumRows); 
} 

Following tow method plays 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.(http://msdn.microsoft.com/en-us/library/bb358985.aspx)

Take : Returns a specified number of contiguous elements from the start of a sequence.(http://msdn.microsoft.com/en-us/library/bb503062(v=VS.100).aspx)

} 
}

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<T>() where T : class 
{ 
    try 
    { 

Following store the type of the class in the typeHolder variable which later used for binding grid with type when soring and searching take place.

if (null == typeHolder) 
             typeHolder = typeof(T); 

below line of code store the soring expression default expression assinged to grid and store the pageIndex of grid.

if (string.IsNullOrEmpty(lblSortExp)) 
               lblSortExp = DefaultSortExp; 
            lblpageIndex = this.PageIndex; 

create and store orderby exprssion which in turn used by the linq query info class collection.

string orderby = 
            "" + lblSortExp + " " + (lblSortDirection == string.Empty ? " asc" : lblSortDirection);   lbltotal = EntityList<T>.GetCount(WhereClause); 
            this.DataSource = EntityList<T>.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 

sets the custome paging property to true which allow to set custom datasorce for the paging.

pagedDataSource.AllowCustomPaging = true;

sets total no of record retived for the custom datasource.

pagedDataSource.VirtualCount = Convert.ToInt32(lbltotal);

sets the current page index for the custom datasource.

pagedDataSource.CurrentPageIndex = lblpageIndex; 
        #endregion code for standard paging 

Sets custom datasource for the grid view control.

base.InitializePager(row, columnSpan, pagedDataSource); 
      } 
     catch (Exception ex) { } 
} 

OnRowCreated - overridden to provide Dropdown which allow use to change paging to set number of record per page in gridview control. This method also over ridden to show sorting icon in header row of the grid.

protected override void OnRowCreated(GridViewRowEventArgs e) 
{ 
     try 
     { 

get the soring column index and set 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 varites 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); 

Follwing line of code add table cell to the page row the grid view which contains drop down to set number of record 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 get fired when the page size changed.

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 use the reflection to call the generic Bind method of the grid to bind the records again with the gridview when the page size chage in dropdown box.

MethodInfo method = this.GetType().GetMethod("BindGrid"); 
    MethodInfo generic = method.MakeGenericMethod(typeHolder); 
    generic.Invoke(this, null); 
} 

OnSorting - overriden to set sroting icon on grid column when sorting event cause 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 take place.

MethodInfo method = this.GetType().GetMethod("BindGrid"); 
               MethodInfo generic = method.MakeGenericMethod(typeHolder); 
               generic.Invoke(this, null); 
         } 
        catch (Exception ex) { } 
} 

OnPageIndexChanging - overriden to bind grid again with the recod 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 create label control and add that label to colmn text to show sort direction.

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 - 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

line of the code register the custome 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 frist template column used to display no with the recods like the rownumber and other template column bind the property of the class to display it.

Below line of the code utilize custom gridview by binding it with the employee class of the datacontext layer. Here frist template column used to display no with the recods like the rownumber and other template column bind 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 is two line of code to bind gridview control. When page get load it call BindGrid method which than call 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(); 
   } 
} 






/// <summary> 
/// Method to bind datagrid of user. 
/// </summary> 
private void BindGrid() 
{ 
  try 
  { 
     grduser.BindGrid<Employee>(); 
   } 
   catch (Exception ex) 
   { } 
}

Conclusion

This grid control implementation provides a basic implementation of a custom control and also give infomation about linq to sql.