I've been experimenting with LINQ to SQL using code examples from ASP.NET Unleashed 3.5. It's amazing how little code is needed to do some pretty amazing things without ever coding an SQL or ADO.NET.
In this example, we're using paging on a GridView using a LINQ to SQL class. Paging in a Gridviews typically works by loading all of the records into memory on the server and then displaying only x records at a time. This, of course, can place a tremendous load on the server if there are a lot of records. In this example, using LINQ's Skip and Take operators we pass in the the page number and the size creating a much more efficient way to page through a recordset.
The first step is to create an entity using the Object Relational Designer in Visual Studio 2008. Simply, create a new LINQ to SQL class and then drag whatever table you want from the database on to the designer surface. In this example, I created a data entity named Movie along with a class named MoviesLINQPaging. Inside of my class I created two methods named GetMovies and GetMovieCount.
Here is a working demo
App_Code/MoviesLINQPaging.vb
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Data.Linq
Imports System.Web
Public Class MoviesLINQPaging
Public Shared Function GetMovies(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As IEnumerable(Of Movie)
Dim db As New MoviesDataContext()
Return db.Movies.Skip(startRowIndex).Take(maximumRows)
End Function
Public Shared Function GetMovieCount() As Integer
Dim context As HttpContext = HttpContext.Current
If IsNothing(context.Cache("MovieCount")) Then
context.Cache("MovieCount") = GetMovieCountFromDB()
End If
Return CType(context.Cache("MovieCount"), Integer)
End Function
Private Shared Function GetMovieCountFromDB() As Integer
Dim db As New MoviesDataContext()
Return db.Movies.Count()
End Function
End Class
The next step is to create new aspx page with a gridview which uses a ObjectDataSource to display the data. Notice the properties of the ObjectDataSource: TypeName="MoviesLINQPaging" - this is the class name found in MoviesLINQPaging.vb. SelectMethod="GetMovies" and SelectCountMethod="GetMovieCount" are the two methods inside the MoviesLINQPaging class.
MoviesLINQPaging.aspx
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<style type="text/css">
.movies td,.movies th
{
padding:5px;
}
</style>
<title>Show LINQ Paging</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView
id="grdMovies"
DataSourceID="srcMovies"
AllowPaging="true"
PageSize="3"
CssClass="movies"
Runat="server" />
<asp:ObjectDataSource
id="srcMovies"
TypeName="MoviesLINQPaging"
SelectMethod="GetMovies"
SelectCountMethod="GetMovieCount"
EnablePaging="True"
Runat="server" />
</div>
</form>
</body>
</html>