Click here to Skip to main content
Click here to Skip to main content

ClsDatabase - A new ADO database wrapper class for ASP

, 3 Jan 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
ClsDatabase - A new ADO database wrapper class for ASP

Sample Image - maximum width is 600 pixels

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.

Following table is a comparison between old access mode and new wrapper mode. ClsDatabase provides a C++ style like function for ADO database operation. It's very useful for me, and I hope it's useful for you too.
Old access mode New wrapper mode
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

y_yy2008

China China
No Biography provided

Comments and Discussions

 
GeneralInteresting Wrapper PinmemberBryan Ray3-Jan-07 12:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 3 Jan 2007
Article Copyright 2007 by y_yy2008
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid