Paging With a ObjectDataSource - Demo


<%@ 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 Data Source 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="MoviesDSPaging"
        SelectMethod="GetMovies"
        SelectCountMethod="GetMovieCount"
        EnablePaging="True"
        Runat="server" />    
    
    </div>
    </form>
</body>
</html>

MoviesDSPaging.vb

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Public Class MoviesDSPaging

    Private Shared ReadOnly _conString As String

    Public Shared Function GetMovies(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As SqlDataReader
        ' Initialize connection     
        Dim con As SqlConnection = New SqlConnection(_conString)

        ' Initialize command
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = con
        cmd.CommandText = "GetPagedMovies"
        cmd.CommandType = CommandType.StoredProcedure

        ' Add ADO.NET parameters
        cmd.Parameters.AddWithValue("@StartRowIndex", startRowIndex)
        cmd.Parameters.AddWithValue("@MaximumRows", maximumRows)

        ' Execute command
        con.Open()
        Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

    Public Shared Function GetMovieCount() As Integer
        Dim context As HttpContext = HttpContext.Current
        If context.Cache("MovieCount") Is Nothing Then
            context.Cache("MovieCount") = GetMovieCountFromDB()
        End If
        Return CType(context.Cache("MovieCount"), Integer)
    End Function

    Private Shared Function GetMovieCountFromDB() As Integer
        Dim result As Integer = 0

        ' Initialize connection     
        Dim con As SqlConnection = New SqlConnection(_conString)

        ' Initialize command
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = con
        cmd.CommandText = "SELECT Count(*) FROM Movies"

        ' Execute command
        Using con
            con.Open()
            result = CType(cmd.ExecuteScalar(), Integer)
        End Using
        Return result
    End Function



    Shared Sub New()
        _conString = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
    End Sub

End Class

** GetPagedMovies.sql ** 

-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexId INT IDENTITY (1, 1) NOT NULL,
	RecordId INT
)

-- INSERT into the temp table
INSERT INTO #PageIndex (RecordId)
SELECT Id FROM Movies 

-- Get a page of movies
SELECT 
	Id,
	Title,
	Director,
	DateReleased
FROM
	Movies
	INNER JOIN #PageIndex WITH (nolock)
		ON Movies.Id = #PageIndex.RecordId 
WHERE
	#PageIndex.IndexID > @startRowIndex 
	AND	#PageIndex.IndexID < (@startRowIndex + @maximumRows + 1)
ORDER BY 
	#PageIndex.IndexID