LINQ TO SQL GridView (Enhanced Gridview)

• Download source code - 136.57 KB

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

Collapse | Copy Code

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 theDataAccess layer to get entity of type T.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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 andorderby to order entity list.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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

Collapse | Copy Code

   }  }

LinqGridView.cs

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

           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.

Collapse | Copy Code

            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.

Collapse | Copy Code

            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.

Collapse | Copy Code

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.

Collapse | Copy Code

      pagedDataSource.AllowCustomPaging = true;

Set total no of record retrieved for the custom datasource.

Collapse | Copy Code

        pagedDataSource.VirtualCount = Convert.ToInt32(lbltotal);

Set the current page index for the custom datasource.

Collapse | Copy Code

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

Set custom datasource for the grid view control.

Collapse | Copy Code

            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 ingridview control. This method is also overridden to show sorting icon in header row of the grid.

Collapse | Copy Code

protected override void OnRowCreated(GridViewRowEventArgs e)  {       try       { 

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

Collapse | Copy Code

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

Collapse | Copy Code

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

Create dropdown control and add varieties of pager size.

Collapse | Copy Code

                    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.

Collapse | Copy Code

                      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.

Collapse | Copy Code

                        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.

Collapse | Copy Code

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.

Collapse | Copy Code

    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.

Collapse | Copy Code

protected override void OnSorting(GridViewSortEventArgs e)  {       try       { 

Line of code stores last expression in the lblSortExp variable.

Collapse | Copy Code

             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.

Collapse | Copy Code

               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.

Collapse | Copy Code

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.

Collapse | Copy Code

private void sortingIcon(int index, GridViewRow row)  { 

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

Collapse | Copy Code

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.

Collapse | Copy Code

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

Collapse | Copy Code

} 

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.

Collapse | Copy Code

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

Collapse | Copy Code

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

Collapse | Copy Code

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