LINQ to SQL Paging using GridView in C# and ASP.NET 3.5
See more tutorials in Display. This post has 2 Comments.
This tutorial will show you how we can extend the LINQ to SQL Class and create methods that will allow us to page data from a SQL database. C# version.
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 |
public static IEnumerable<tblEmployee> SelectPage(int startRowIndex, int maximumRows) { return Select().Skip(startRowIndex).Take(maximumRows); } |
This method will be called when a new page is requested. The GridView’s paging links will provide the variables required for this method.
Finally, we create a method that will get the number of records in the database:
|
1 2 3 4 |
public static int SelectCount() { return Select().Count(); } |
The entire class extension will look something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
<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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<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> |
2 Responses
12.30.2013
Hello
May be I am stupid in understanding this code. But I have a question.
When we use below code snippet, will records selection be done at server (DB server) end or done on client collection that resulted from Select()? I am having big doubt if Skip() and Take() will control server’s record set or client’s collection set. And how can we ensure if it is handling server’s set or client’s set?
return Select().Skip(startRowIndex).Take(maximumRows);
Can you please respond to me?
12.30.2013
Sudhakar,
Since we’re using return Select() and calling our methods on the Select() statement, our methods should only be affecting the data returned by Select(), not the actual database.