GridView Custom Paging Using LINQ and Object Data Source

Retrieving large set of data from database is common need for any data driven website and if you have one you know how much effort it take to maintain the performance of that website. We all have used GridView to present data on web pages. For small chunk of data Gridview's inbuilt paging technique can work just fine but for large data using inbuilt technique is going to have huge impact on performance. Custom paging is an efficient technique to only send number of records that are visible on the page rather than loading whole data in server's memory.

Today we will see how to implement custom paging using LINQ to SQL and object data source. For demo purposes we will use northwind database.

Snapshot:

For this we will create following:

  1. LINQ to SQL Class
  2. Custom Data Access Class to query data using LINQ
  3. Object Data Source
  4. Gridview

LINQ to SQL Class:

In order to create this, add new item to your project of type LINQ to SQL Classes with name "NorthwindDB.dbml". Now connect to your database using server explorer and drag and drop Customers and Orders tables from database to design view of LINQ to SQL file.

Data Access Class:

Add a new class in your project with name "NWDAL.cs" with following code:

public class NWDAL

{

public List<Customer> GetAllCustomers(int startRow,int maxRow)

{

NorthwindDBDataContext context = new NorthwindDBDataContext();

IEnumerable<Customer> cust =

(from c in context.Customers

select c).Skip(startRow).Take(maxRow);

 

return cust.ToList();

}

 

public int GetCustomerCount()

{

NorthwindDBDataContext context = new NorthwindDBDataContext();

IEnumerable<Customer> cust =

(from c in context.Customers

orderby c.CustomerID descending

select c);

 

return cust.Count();

}

}

 

As you can see in above class, we have created two methods.

  1. GetAllCustomers will take two parameters start row and maximum rows and will return collection of Customer. We will pass this information from Object data source.
  2. GetCustomerCount method will be used to count total number of records, which Object data source will use to show how many pages are there in Gridview

 

Default.ASPX file:

Here we will drop two controls, gridview and object data source.

<asp:GridView ID="GridView1" runat="server" ViewStateMode="Disabled"

AllowPaging="True" DataSourceID="ObjectDataSource1" PageSize="5"

BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"

CellPadding="3">

<FooterStyle BackColor="White" ForeColor="#000066" />

<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />

<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />

<RowStyle ForeColor="#000066" />

<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />

<SortedAscendingCellStyle BackColor="#F1F1F1" />

<SortedAscendingHeaderStyle BackColor="#007DBB" />

<SortedDescendingCellStyle BackColor="#CAC9C9" />

<SortedDescendingHeaderStyle BackColor="#00547E" />

</asp:GridView>

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True"

MaximumRowsParameterName="maxRow" SelectCountMethod="GetCustomerCount"

SelectMethod="GetAllCustomers" StartRowIndexParameterName="startRow"

TypeName="ASPNETPrac.NWDAL" SortParameterName="sortOrder"></asp:ObjectDataSource>

 

As you can see in object data source we will pass reference of DAL class name in type name, select method and select count method. Once all these properties are set Object data source will take care of rest.

Tags: , , , ,

ASP.NET | LINQ