ClsDatabase - A new ADO database wrapper class for ASP

• Download demo project - 11.22 KB
• Download source - 3.53 KB

Introduction

This wrapper class of ADO database is designed to make it easier or more simplified of manipulating database for ASP programming. I hope this article/class useful for some people.

When I develop ASP Webmail of AA Mail Server, which is a database based mail server shareware, I was sick of so many inconvenient, repetitious and not intuitionistic ADO recordset operations. So I designed a new wrapper class for ADO database operation with Scripting.Dictionary object.

Set cnn _
   = Server.CreateObject("ADODB.Connection") 
Set rs _
   = Server.CreateObject("ADODB.Recordset") 
cnn.Open _
   "Provider=Microsoft.Jet.OLEDB.4.0;" &_
   "Data Source=dbtest.mdb" 
rs.Open cnn, _
   "select * from student " &_
   "where userid='Maria@123.com'" 
If Not rs.Eof Then 
    'Actions when record existing.... 
    'Response.Write rs("username") 
End If 
rs.Close 
cnn.Close 
Set rs=Nothing 
Set cnn=Nothing 

Set Gdbc = New ClsDatabase
Gdbc.ConnectOpen("dbtest.mdb")
Set Student = Gdbc.Init("student")
Student("userid") = "Maria@123.com"
bReadOK = Gdbc.Read(varStudent)
If bReadOK Then
    'Actions when record exists....
    'Response.Write Student("username")
End If
Set Student=Nothing
Gdbc.ConnectClose
Set Gdbc=Nothing

Features of ClsDatabase

ClsDatabase provides you a C++ style like function for ADO database operation. ClsDatabase automatically handles primary keys of your tables and uses BuildList/BuildPage to substitute for "MoveNext/MovePrevious" operations. Following are major methods of ClsDatabase.

• ConnectOpen(byVal strDBName)
• ConnectClose
• Init(byVal strTable)
• Read(byRef varTable)
• Add(byVal valVarDic)
• Update(byVal valVarDic)
• Delete(byVal strTable, byVal strCondition)
• BuildList(byRef arrTable(), byVal strTable, byVal strCondition)
• ReadbyIndex(byRef varTable, byVal nIndex, byVal strCondition)
• BuildPage(byRef arrTable(), byVal strTable, byVal strCondition, byVal nPageIndex, byVal nMsgsPerPage, byRef nPageCount)

Using the code

It's very easy and intuitionistic to use ClsDatabase to manipulate database. You can learn how to do from below 5 examples or demo project. You may need to modify Windows XP access control for dbtest.mdb, otherwise, your ASP codes cannot access database (Normally you can achieve this by right clicking the folder in Windows Explorer and then changing access control of the folder in Security tab).

At the begin of ASP

First, you need to include the class file. Then, create a new instance of ClsDatabase and connect to specified database. Demo project is suitable for Access database only, you can easily change ClsDatabase yourself for SQL Server or other databases.

<-- #include file="ClsDatabase.inc" -->
<%
    Dim Gdbc    
    Set Gdbc = new ClsDatabase        
    Gdbc.ConnectOpen("dbtest.mdb")
%>

Example 1. Add a record into database

Dim bAddOK, varStudent1    
    
Set varStudent1 = Gdbc.Init("student")
    
varStudent1("userid") = "Peter@123.com"
varStudent1("username") = "Peter"
varStudent1("score") = 98
    
bAddOK = Gdbc.Add(varStudent1)

Example 2. Read a record with specified key

Dim bReadOK, varStudent2    
    
Set varStudent2 = Gdbc.Init("student")
    
varStudent2("userid") = "Maria@123.com"
    
bReadOK = Gdbc.Read(varStudent2)

Example 3. Update a record (should read first)

Dim bUpdateOK, varStudent3    
    
Set varStudent3 = Gdbc.Init("student")

varStudent3("userid")= "Peter@123.com"
    
bReadOK = Gdbc.Read(varStudent3)
    
If bReadOK Then
    varStudent3("score") = 100
    bUpdateOK = Gdbc.Update(varStudent3)
End If

Example 4. Build record list with specified condition

Dim i, nCount, strCondition, arrStudent
    
strCondition = "score>=90 order by score"
    
nCount = Gdbc.BuildList(arrStudent, "student", strCondition)
    
For i = 0 To nCount-1
   Response.Write arrStudent(i)("userid") & " : "
   Response.Write arrStudent(i)("username") & " : "
   Response.Write arrStudent(i)("score") "<br>"
Next

Example 5. Delete record with specified condition

Dim bDeleteOK, strDelCondition
    
strDelCondition = "userid='Peter@123.com'"
    
bDeleteOK = Gdbc.Delete("student", strDelCondition)    

At the end of ASP

Now you can close database connection and release resource. It's better to release above "varStudent1", "varStudent2" and "varStudent3" as well as "Gdbc" (e.g. Set varStudent1=Nothing).

Gdbc.ConnectClose
Set Gdbc=Nothing

Points of Interest

ClsDatabase has been used in our product AA Mail Server and worked very well. You are freely to use ClsDatabase. If you want to learn more about ClsDatabase, you can download and install AA Mail Server for trial or study. We would be appreciated if any bugs or suggestion for ClsDatabase or AA Mail Server.