LINQ to SQL Paging usandoGridView in C# e ASP.NET 3.5

Using LINQ to SQL, we can make use of the built-in methods to page the database data a lot easier than with using SQL alone. LINQ to SQL can make it extremely easy for us to create pages from our data source using these two methods:
- Skip() -- Allows us to skip a certain number of records;
- Take() -- Allows us to select a certain number of records.

In this tutorial, we will use a SQL database and extend the LINQ to SQL Class that Visual Studio creates to represent our database by allowing the user to page the data displayed in a GridView control.
We will start by creating our database. For this example, we will use one table of employees with three columns - id, name, position.
We should also add some sample data once the database is set up - we will need at least 5 records to make use of the paging feature.

Once we have our database set up and data added to it, we then need to create a representation of our database using LINQ to SQL Classes. Right-click your project in the Solution Explorer, and goto Add ASP.NET Folder > App_Code. Now right-click the App_Code folder and choose Add New Item.. LINQ to SQL Classes. This will bring up the Object Relationship Designer. All we need to do here is drag into the designer the tables we will be working with from the Server Explorer, and then Save. This will allow Visual Studio to create a representation of our database. For this example, we will name it Employees.dbml

Next, we will create an extension of this class by again right-clicking the App_Code folder and choose Add New Item.. Class. We will also name this Employees and change the public class to public partial class. We may need to also add extra assembly references; we will be using the System.Collections.Generic, System.Data.Linq and System.Linq in particular.
We are going to extend this class by providing methods to select the data in pages. Our first method will select all the data:

public static IEnumerable<tblEmployee> Select()
{

EmployeesDataContext db = new EmployeesDataContext();
return db.tblEmployees;
}

Notice the EmployeesDataContext refers to our LINQ to SQL class.
Next, we add a method to move between the pages of the data:

public static int SelectCount()
{

return Select().Count();
}

The entire class extension will look something like this:

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Data.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;

/// <summary>
/// Extension class for Employees.dbml
/// </summary>
public partial class Employees
{

public static IEnumerable<tblEmployee> Select()
{
EmployeesDataContext db = new EmployeesDataContext();
return db.tblEmployees;
}

public static IEnumerable<tblEmployee> SelectPage(int startRowIndex, int maximumRows)
{
return Select().Skip(startRowIndex).Take(maximumRows);
}

public static int SelectCount()
{
return Select().Count();
}
}

We are now done with the class, and can implement the functionality into our ASPX page. To make this work, we will need to use a GridView control and an ObjectDataSource:

<form id="form1" runat="server">

<asp:ScriptManager ID="ScriptManager1" runat="server" />

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="DataGrid1" runat="server" Width="400px" />

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" />
</ContentTemplate>
</asp:UpdatePanel>
</form>

Because we are using VS.NET 2008, we can simply add AJAX Functionality to our web application using a ScriptManager and UpdatePanel.
In order to implement paging, we need to set the EnablePaging attributes on both of our controls. We also set the Method attributes of the ObjectDataSource to reflect those we created in our partial class - note that the TypeName refers to our class name.

<form id="form1" runat="server">

<asp:ScriptManager ID="ScriptManager1" runat="server" />

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="DataGrid1" runat="server" 
AllowPaging="true" PageSize="5" DataSourceID="ObjectDataSource1"
Width="400px" />

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="Employees" SelectMethod="SelectPage"
SelectCountMethod="SelectCount" EnablePaging="true" />
</ContentTemplate>
</asp:UpdatePanel>
</form>


Add Feedback