Since I posted [Data Access Layer for Classic ASP], I've been trying to find time to post the ASP.Net version too..

This is a complete example of creating Data Access Layer(DAL) using the traditional yet powerful "Object to Relational mapping model" or "Custom Entity Classes" that support Paging too..

Many people talk about using the Datasets method for creating a DAL as in [Tutorial 1: Creating a Data Access Layer] and how it is fast to generate.
but for me the Traditional 'Object to Relational mapping' model seems more simpler to work with and to extend, and about the slow creation time issue: as a developer you know that creating these classes for the 2nd time things gets more faster, most of its code is cut+paste-friendly and there are software that would generate it for you too..
They would also say that datasets method is data-source independent (no DBMS), not just database independent, well my proposed DAL is surely database independent and what are the chances that your application will move away from a DBMS? well,ZERO. and still those classes can encapsulate anything you want, even if data is saved in csv files.

The 'Object to Relational mapping' model consists of :
Entity Class - represents a single record or row from a database resultset.
Entity Collection Class - contains a collection of entity objects or a tabular list of data from a resultset.

Add to that, DAL uses a very helpful generic Command class i posted before to [Write Less & Generic Data Access Code in ADO.NET 2.0].

As an example of DAL, we'll use a simple Table of 'Products', here is the SQL Create script:
CREATE TABLE [Products] (
   [ID] [int] PRIMARY KEY IDENTITY(1, 1),
   [Name] [varchar] (50) NOT NULL DEFAULT (''),
   [Description] [varchar] (500) NOT NULL DEFAULT (''),
   [Status] [tinyint] NOT NULL DEFAULT (0),
   [Price] [decimal](18, 0) NOT NULL DEFAULT (0)
)

The Entity Class
Imports System.Data
Imports System.Data.Common
Imports Microsoft.VisualBasic

Public Class Product
   '' Table Fields
   Public ID As Integer
   Public Name As String
   Public Description As String
   Public Price As Decimal
   Public Status As ProductStatus

   Public Enum ProductStatus
      None = 0
      Active = 1
      Inactive = 2
   End Enum

   Public Sub New(Optional ByVal ProductID As Integer = 0)
      SelectItem(ProductID)
   End Sub

   ''' <summary>Initialize Product Fields</summary>
   Public Sub Initialize()
      ID = 0
      Name = ""
      Description = ""
      Price = 0
      Status = ProductStatus.Active
   End Sub

   Friend Sub Populate(ByRef dr As DbDataReader)
      Populate(CType(dr, Object))
   End Sub
   Friend Sub Populate(ByRef dr As DataRow)
      Populate(CType(dr, Object))
   End Sub
   Private Sub Populate(ByRef dr As Object)
      ID = CInt(dr("ID"))
      Name = dr("Name")
      Description = dr("Description")
      Price = CDec(dr("Price"))
      Status = CByte(dr("Status"))
   End Sub

   ''' <summary>Select Product by ID</summary>
   Public Sub SelectItem(ByVal ProductID As Integer)
      Call Initialize()

      If ProductID = 0 Then Exit Sub

      Dim cmd As New GenericCommand("SQLConn")
      Dim rdr As DbDataReader
      cmd.CommandText = "Select Top 1 * from Products Where ID=@ID"
      cmd.AddParam("@ID", ProductID)
      rdr = cmd.ExecuteReader()
      If rdr.Read() Then Populate(rdr)
      rdr.Close()
      rdr = Nothing
      cmd = Nothing
   End Sub

   ''' <summary>Insert new Product and get new Identity</summary>
   Public Sub InsertItem()
      If ID <> 0 Then Exit Sub

      Dim cmd As New GenericCommand("SQLConn")
      cmd.CommandText = "Insert Into Products (Name,Description,Price,Status) Values (@Name,@Description,@Price,@Status)"
      cmd.AddParam("@Name", Name)
      cmd.AddParam("@Description", Description)
      cmd.AddParam("@Price", Price)
      cmd.AddParam("@Status", Status)
      ID = CInt(cmd.ExecuteIdentity())
      cmd = Nothing
   End Sub

   ''' <summary>Update Product</summary>
   Public Sub UpdateItem()
      If ID = 0 Then Exit Sub

      Dim cmd As New GenericCommand("SQLConn")
      cmd.CommandText = "Update Products set Name=@Name,Description=@Description,Price=@Price,Status=@Status where ID=@ID"
      cmd.AddParam("@Name", Name)
      cmd.AddParam("@Description", Description)
      cmd.AddParam("@Price", Price)
      cmd.AddParam("@Status", Status)
      cmd.AddParam("@ID", ID)
      cmd.ExecuteNonQuery()
      cmd = Nothing
   End Sub

   ''' <summary>Delete This product</summary>
   Sub DeleteItem()
      If ID = 0 Then Exit Sub

      Dim cmd As New GenericCommand("SQLConn")
      cmd.CommandText = "DELETE FROM Products WHERE ID=@ID"
      cmd.AddParam("@ID", ID)
      cmd.ExecuteNonQuery()
      cmd = Nothing

      Call Initialize()
   End Sub
End Class

and The Entity Collection Class
Public Class Products
   Inherits CollectionBase

   ''' <summary>PageSize=0 means no paging</summary>
   Public PageSize As Integer
   ''' <summary>PageIndex=0 means no paging</summary>
   Public PageIndex As Integer
   Private mPageCount As Integer
   ''' <summary>To Get Top records if larger than 0</summary>
   Public TopRecords As Integer

   Public Sub New()
      Call Initialize()
      PageSize = 0
      PageIndex = 0
      TopRecords = 0
   End Sub

   ''' <summary>clear collection</summary>
   Public Sub Initialize()
      mPageCount = 0
      List.Clear()
   End Sub

   ''' <summary>Gets or sets the element at the specified zero-based index</summary>
   Default Public Property Item(ByVal Index As Integer) As Product
      Get
         Return List.Item(Index)
      End Get
      Set(ByVal value As Product)
         List.Item(Index) = value
      End Set
   End Property

   ''' <summary>Adds an object to the end of the Collection</summary>
   Public Function Add(ByVal Obj As Product) As Integer
      Return List.Add(Obj)
   End Function

   ''' <summary>Select Products by Status, More Search Params can be added..</summary>
   Public Sub SelectItems(Optional ByVal Status As Product.ProductStatus = Product.ProductStatus.None)
      Call Initialize()

      Dim Tbl As DataTable
      Dim Top As String = ""
      If TopRecords > 0 Then Top = "TOP " & TopRecords
      If PageSize > 0 And PageIndex > 0 Then Top = "TOP " & (PageIndex * PageSize)

      Dim Cmd As New GenericCommand("SQLConn")
      Cmd.PageSize = PageSize
      Cmd.PageIndex = PageIndex
      Cmd.CountCommandText = "SELECT COUNT(*) FROM Products"
      Cmd.CommandText = "SELECT " & Top & " * FROM Products"
      If Status > 0 Then
         Cmd.CountCommandText += " where Status=@Status"
         Cmd.CommandText += " where Status=@Status"
         Cmd.AddParam("@Status", Status)
      End If
      Tbl = Cmd.ExecuteDataTable("Products")
      mPageCount = Cmd.PageCount

      Dim p As Product
      For Each Row As DataRow In Tbl.Rows
         p = New Product
         p.Populate(Row)
         Add(p)
         p = Nothing
      Next
      Tbl = Nothing
      Cmd = Nothing
   End Sub

   Public ReadOnly Property PageCount() As Integer
      Get
         Return mPageCount
      End Get
   End Property

   Protected Overrides Sub Finalize()
      MyBase.Finalize()
   End Sub
End Class

A Sample of usage to insert a product:
Dim p As New Product
p.Name = "Product1"
p.Status = Product.ProductStatus.Active
p.InsertItem()
Response.Write(p.ID & ":" & p.Name)
p = Nothing

And a Sample of usage to select list of products with paging
Dim ps As New Products
ps.PageSize = 10
ps.PageIndex = 2
ps.SelectItems(Product.ProductStatus.Active)
For i As Integer = 0 To ps.Count - 1
   Dim p As Product = ps(i)
   Response.Write(p.ID & ":" & p.Name & "<br>")
   p = Nothing
Next
Response.Write("PageCount: " & ps.PageCount)
ps = Nothing

Also note that the paging method used in the collection class is DataSet paging method, to read more about paging methods comparison and tweaking: please check [Paging in ASP and ASP.Net].

After I posted a previous article comparing between many methods of Paging in ASP and ASP.Net, I wanted to focus on how to tweak paging using DataSet as it is unpopular tip and it saves lots of time to return records in closer pages which usually gets more hits than further pages...

The method DbDataAdapter.Fill(dataSet As DataSet, startRecord As Integer, maxRecords As Integer, srcTable As String) specifies the start record and number of records to fill the DataSet with, and of course you need to run a count query first to calculate PageCount

'Count Query
Cmd.CommandText = "Select count(ID) from Users where.."
Count = CInt(Cmd.ExecuteScalar())
PageCount = Math.Ceiling(Count / PageSize)

'Select Query
cmd.CommandText = "Select Top " & (PageIndex * PageSize) & " * from Users where.. Order By.."

Dim DS As New DataSet
Dim DA As DbDataAdapter = PFactory.CreateDataAdapter()
DA.SelectCommand = Cmd
DA.Fill(DS, (PageIndex - 1) * PageSize, PageSize, SrcTable)
Dim DT As New DataTable= DS.Tables(SrcTable)
DA = Nothing
DS = nothing

For Each Row As DataRow In DT.Rows
'Do Something
Next
DT = Nothing

MSDN @ [ADO.Net & Paging Through a Query Result ] Says:

This might not be the best choice for paging through large query results because, although the DataAdapter fills the target DataTable or DataSet with only the requested records, the resources to return the entire query are still used ..
Remember that the database server returns the entire query results even though only one page of records is added to the DataSet.

That is true, At first testing the time results for this method was bad even for closer pages..

To tweak that: I added a top clause in the select query to select Top(PageSize * PageIndex), so if we want the 2nd page where page size is 10: the database returns only 20 records, and the DataSet is filled with the 2nd 10 records.. instead of letting the database to return the entire table. and this tweak makes DataSet timing chart similar to 'Top Clause' SQL paging method [ more on that @ Paging in ASP and ASP.Net]

Although I posted a long article comparing between many methods of Paging in ASP and ASP.Net, I wanted to highlight more on this method of paging using DataReader cause you won't see out there very often.. although it is simpler than SQL Paging methods and it is slightly faster than usual DataSet paging method..
It was inspired by the article [How do I page through a Recordset?] which Shows & compares between paging methods for classic ASP.

The function do paging by fetching the Top PageSize * PageIndex Records and then it loops to move the start record of current Page index..

' Paging Params (1-based)
Dim PageSize As Integer = 10
Dim PageIndex As Integer = 3
' Create+Open Conn on SQL Server 2005 Express
Dim Conn As New SqlConnection("Data Source=127.0.0.1\sqlexpress,1433;Initial Catalog=DB;Persist Security Info=False;User ID=user;Password=pass;Network Library=dbmssocn")
Conn.Open()
'Create SQL Command
Dim Cmd As New SqlCommand()
Cmd.Connection = Conn
'Execute Count Query To calculate PageCount
Cmd.CommandText = "SELECT COUNT(*) FROM users where status=1"
Dim Count As Integer = Cmd.ExecuteScalar()
Dim PageCount As Integer = Math.Ceiling(Count / PageSize)
'Query Records
'If you forget to add 'Top PageIndex * PageSize', Performance degrades badly in large tables
Cmd.CommandText = "SELECT Top " & (PageIndex * PageSize) & " * FROM users where status=1"
Dim rdr As SqlDataReader = Cmd.ExecuteReader()

'Move Reader to start record
For I As Integer = 0 To ((PageIndex - 1) * PageSize) - 1
  If Not rdr.Read Then Exit For
Next
'Loop the desired Records
'if you didn't Add Top clause you will notice that all records in table are fetched!!
While rdr.Read()
  'Do Something
End While
rdr.Close()
rdr = Nothing
Conn.Close()
Conn = Nothing
Cmd = Nothing

*If you forget to add Top (PageIndex * PageSize), Performance degrades badly in large tables