Click here to Skip to main content
15,868,016 members
Articles / Web Development / ASP.NET

LINQ TO SQL GridView (Enhanced Gridview)

Rate me:
Please Sign up or sign in to vote.
4.88/5 (51 votes)
4 Aug 2010CPOL6 min read 169.5K   2.4K   115   49
LINQ TO SQL 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.

DataBase

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

C#
public class EntityList<T> 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.

C#
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. (See this link.)

Method

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

C#
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.
C#
public static IQueryable<T> 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.

C#
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.

C#
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.

C#
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.

C#
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. (See this link.)
C#
   } 
}

LinqGridView.cs

C#
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.

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

WhereClause - Stores the Where clause of the query which is passed as the where condition.

C#
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.

C#
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.

C#
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.

C#
public void BindGrid<T>() 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.

C#
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.

C#
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.

C#
            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.

C#
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.

C#
pagedDataSource.AllowCustomPaging = true;

Set total no of record retrieved for the custom datasource.

C#
pagedDataSource.VirtualCount = Convert.ToInt32(lbltotal);

Set the current page index for the custom datasource.

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

Set custom datasource for the grid view control.

C#
            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.

C#
protected override void OnRowCreated(GridViewRowEventArgs e) 
{ 
     try 
     { 

Get the sorting column index and sets the sorting icon in header of the grid control.

C#
#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.

C#
 if (e.Row.RowType == DataControlRowType.Pager)
{

Create dropdown control and add varieties of pager size.

C#
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.

C#
          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.

C#
                        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.

C#
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.

C#
    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.

C#
protected override void OnSorting(GridViewSortEventArgs e) 
{ 
     try 
     { 

Line of code stores last expression in the lblSortExp variable.

C#
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.

C#
               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.

C#
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.

C#
private void sortingIcon(int index, GridViewRow row) 
{ 

Line of code creates label control and adds that label to column text to show sort direction.

C#
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.

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

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.

ASP.NET
<%@ 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. ]

ASP.NET
<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.

C#
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 gives information about Linq to SQL.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India

Microsoft C# MVP (12-13)



Hey, I am Pranay Rana, working as a Team Leadin MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.

For me def. of programming is : Programming is something that you do once and that get used by multiple for many years

You can visit my blog


StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr

Awards:



Comments and Discussions

 
QuestionIssue I found for OnRowCreated event Pin
kaushaltailor21-Feb-13 18:36
kaushaltailor21-Feb-13 18:36 
GeneralMy vote of 5 Pin
csharpbd19-Nov-12 1:39
professionalcsharpbd19-Nov-12 1:39 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey27-Mar-12 1:00
professionalManoj Kumar Choubey27-Mar-12 1:00 
Questionpaging in SQL Pin
z72-Dec-11 10:11
z72-Dec-11 10:11 
GeneralMy vote of 5 Pin
sachin10d11-Oct-11 22:47
sachin10d11-Oct-11 22:47 
GeneralPagerSettings-Mode="NextPreviousFirstLast" does'nt seem to work Pin
Bertuske25-May-11 2:33
Bertuske25-May-11 2:33 
GeneralMy vote of 5 Pin
Filip D'haene24-May-11 6:41
Filip D'haene24-May-11 6:41 
Generalsmall bug Pin
maximus.dec.meridius18-May-11 11:20
maximus.dec.meridius18-May-11 11:20 
GeneralWhere Clause Pin
SohelElite13-Jan-11 2:15
SohelElite13-Jan-11 2:15 
GeneralMy vote of 5 Pin
RaviRanjanKr24-Dec-10 1:46
professionalRaviRanjanKr24-Dec-10 1:46 
GeneralGood work Pin
tushar22914-Dec-10 20:10
tushar22914-Dec-10 20:10 
QuestionQuestion Regarding Display Data In GridView Pin
mshariq10-Dec-10 22:37
mshariq10-Dec-10 22:37 
AnswerRe: Question Regarding Display Data In GridView Pin
maximus.dec.meridius17-May-11 11:39
maximus.dec.meridius17-May-11 11:39 
GeneralGood Work Pin
Viral Upadhyay28-Aug-10 1:50
Viral Upadhyay28-Aug-10 1:50 
GeneralRe: Good Work Pin
Pranay Rana30-Aug-10 20:29
professionalPranay Rana30-Aug-10 20:29 
GeneralGood Job Pranay [modified] Pin
kkuni25-Aug-10 23:06
kkuni25-Aug-10 23:06 
GeneralRe: Good Job Pranay Pin
Pranay Rana25-Aug-10 23:15
professionalPranay Rana25-Aug-10 23:15 
GeneralMy vote of 5 Pin
Sandesh M Patil20-Aug-10 1:26
Sandesh M Patil20-Aug-10 1:26 
GeneralMy vote of 5 Pin
Sandesh M Patil20-Aug-10 1:26
Sandesh M Patil20-Aug-10 1:26 
GeneralRe: My vote of 5 Pin
Pranay Rana20-Aug-10 1:30
professionalPranay Rana20-Aug-10 1:30 
GeneralMy vote of 3 Pin
Member 179596120-Aug-10 0:00
Member 179596120-Aug-10 0:00 
GeneralRe: My vote of 3 Pin
Pranay Rana20-Aug-10 0:35
professionalPranay Rana20-Aug-10 0:35 
GeneralMy vote of 5 Pin
Bigdeak19-Aug-10 1:57
Bigdeak19-Aug-10 1:57 
GeneralRe: My vote of 5 Pin
Pranay Rana19-Aug-10 6:04
professionalPranay Rana19-Aug-10 6:04 
GeneralMy vote of 3 Pin
Koshy Panicker John18-Aug-10 5:59
Koshy Panicker John18-Aug-10 5:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.