Stone Coast Web Design's Weblog

Navigation

Search

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

| |

E-mail

Total Posts: 112
This Year: 50
This Month: 4
This Week: 0
Comments: 0

Friday, April 18, 2008

Paging with LINQ to SQL

Friday, April 18, 2008 10:22:44 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET | LINQ )

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>

Comments [0] | Trackback |

Name
E-mail
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):

Mostly Web Development

Navigation

Search

Categories

On this page

Archive

Blogroll