Data Access Layer for Classic ASP

Data Access layer(DAL) is is a layer of code which provides simplified access to data stored in persistent storage of some kind (DBMS, XML,..)

DAL would return a reference to an object [in terms of object-oriented programming(OOP)] with its attributes instead of a recordset of fields from a database table. This allows presentation layer to be created with a higher level of abstraction, and make project database independent.

So, instead of using SQL commands such as insert, delete, and update to access a specific table in a database, a method would be called from inside the class, which would return an object containing the requested values. instead of making a query into a database to fetch all users from several tables the application can call a single method from a DAL which encapsulate those database calls.

As an example of DAL, we'll use a simple Table of 'Users', here is the SQL Create script:
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] [varchar](100) NOT NULL DEFAULT (''),
[Email] [varchar](100) NOT NULL DEFAULT (''),
[Password] [varchar](20) NOT NULL DEFAULT (''),
[LastLogin] [smalldatetime] NOT NULL DEFAULT ('1/1/1900'),
[Status] [tinyint] NOT NULL DEFAULT (0)
)

We will create 2 classes for users table, Object Class and Object Collection Class with support of paging

Object Class
'few needed ADO Constants, instead of including adovbs.asp
Const adCmdText = &H0001
Const adCmdTableDirect = &H0200
Const adCmdFile = &H0100
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adLockReadOnly = 1
Const adLockOptimistic = 3
Const adExecuteNoRecords = &H00000080
Const adUseClient = 3

''''''''''''''' Object class '''''''''''''''
Class User
  ' Object Attributes / Table Fields
  Public ID 'as Long
  Public Name 'as String
  Public Email 'as String
  Public Password 'as Strng
  Public LastLogin 'as Date
  Public Status 'as byte

  Private Sub Class_Initialize()
    Initialize
  End Sub

  'default values
  Public Sub Initialize()
    ID=0
    Name=""
    Email = ""
    Password = ""
    LastLogin = Now()
    Status=0
  End Sub

  'Select user by ID
  Public Sub SelectUser(UserID)
    Initialize

    if UserID=0 then exit Sub

    Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
    Dim SQL : SQL= "SELECT * From users where ID="& UserID
    rs.Open SQL, ConnStr ,adOpenForwardOnly, adLockReadOnly,adCmdText
    If Not rs.EOF Then
      ID = CLng(Rs("ID"))
      Name = Rs("Name")
      Email = Rs("Email")
      Password = Rs("Password")
      LastLogin = Cdate(rs("LastLogin"))
      Status = cbyte(Rs("Status"))
    End If
    rs.Close
    Set rs = Nothing
  End Sub

  'Insert New User and get new ID
  Public Sub InsertUser()
    if ID<>0 then exit Sub

    Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
    rs.Open "Users", ConnStr ,adOpenKeyset,adLockOptimistic,adCmdTableDirect
    rs.AddNew
    Rs("Name") = Name
    Rs("Email") = Email
    Rs("Password") = Password
    rs("LastLogin") = LastLogin
    Rs("Status") = Status

    rs.update
    ID = CLng(Rs("ID"))
    Rs.Close
    Set rs = Nothing
  End Sub

  'Update User
  Public Sub UpdateUser()
    if ID=0 then exit Sub

    Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
    Dim SQL : SQL= "SELECT * From users where ID="& ID
    rs.Open SQL, ConnStr ,adOpenForwardOnly,adLockOptimistic,adCmdText
    If Not rs.EOF then
      Rs("Name") = Name
      Rs("Email") = Email
      Rs("Password") = Password
      rs("LastLogin") = LastLogin
      Rs("Status") = Status
      rs.update
    End If
    Rs.Close
    Set rs = Nothing
  End Sub

  'delete user
  Public Sub DeleteUser()
    if ID=0 then exit Sub

    Dim conn : Set conn = server.CreateObject("ADODB.Connection")
    conn.Execute "Delete From users where ID="& ID, ,adExecuteNoRecords
    Conn.Close
    Set Conn = Nothing

    Initialize
  End Sub
End Class

Object Collection Class
Class Users
  'private Array to hold users Objects
  Private arr
  ''''' Paging Variables
  Private mPageCount 'as Long
  Public PageIndex 'as Long
  Public PageSize 'as Long

  'for getting top records if larger than 0
  Public TopRecords 'as Long

  'destroy arr
  Private Sub Class_Terminate()
    Erase arr
  end Sub

  Private Sub Class_Initialize()
    Initialize
    TopRecords = 0
    PageIndex = 0
    PageSize = 0
  end Sub

  'Initialize collection
  Public Sub Initialize()
    redim Arr(-1)
    mPageCount = 0
  End Sub

  'return Upper Bound of objects array
  Public Property get UpperBound()
    UpperBound = UBound(arr)
  End Property

  'return object at index i
  Public Property Get ElementAt(i)
    set ElementAt = arr(i)
  End Property

  Public Property Get PageCount()
    PageCount = mPageCount
  End Property

  'Select users by Status (you can add more search parameters or create another select method)
  Public Sub SelectUsers(Status)
    Initialize

    Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
    'Do paging
    If PageSize>0 And PageIndex>0 Then
      RS.CursorLocation = adUseClient
      RS.CacheSize = PageSize
      RS.PageSize = PageSize
    End if
    
    If TopRecords>0 Then rs.MaxRecords = TopRecords

    Dim Top
    If TopRecords>0 Then Top = " top "& TopRecords &" "
    Dim SQL : SQL= "SELECT "& Top &" * From users"
    If Status>0 Then SQL = SQL & " where Status="& Status

    rs.Open SQL, ConnStr, adOpenForwardOnly,adLockReadOnly,adCmdText
    ' if paging : move to PageIndex and get page count
    If Not rs.EOF And PageSize>0 And PageIndex>0 Then
      RS.AbsolutePage = PageIndex
      mPageCount = RS.PageCount
    End If

    Dim i : i=0
    'loop until EOF or Paging Size reached
    while Not rs.EOF And (PageSize=0 Or i<PageSize)
      'create Object and set Values then add to collection
      Dim u : Set u = New User
      u.ID = CLng(Rs("ID"))
      u.Name = Rs("Name")
      u.Email = Rs("Email")
      u.Password = Rs("Password")
      u.LastLogin = cdate(rs("LastLogin"))
      u.Status = cbyte(Rs("Status"))

      ReDim Preserve arr(i)
      Set arr(i) = u
      set u = Nothing

      rs.MoveNext
      i=i+1
    Wend
    rs.Close
    Set rs = Nothing
  End Sub
End Class

Sample Usage
Dim Usrs : Set Usrs = New Users
Usrs.PageIndex = 1
Usrs.PageSize = 10
Call Usrs.SelectUsers(0)

Dim i,Usr
For i=0 To Usrs.UpperBound
  Set Usr = Usrs.ElementAt(i)
  'Do something
  Set Usr = Nothing
Next
Set Usrs = Nothing

Note that the paging method used in the collection class is the usual ADO method,since it is not the optimal or the fastest,Please check my article on [Paging in ASP and ASP.Net] for better paging methods..

This Data access layer can be later extended to be also a Business Logic Layer(BLL) by enforcing business rules that may apply (permissions, validations, formatting..)

Also if you have a lot of DAL classes, DAL can be moved to ActiveX DLL (Com Object) to improve performance..[25+ ASP Tips to Improve Performance and Style]

Tags: ADO , ASP , DAL , OOP , Paging , performance , scalability