By centralizing database code within ActiveX DLL's, you can simplify your development efforts, and decrease the learning curve for new developers. In this article, we will create a Visual Basic ActiveX DLL that controls database functionality for our Active Server Page (ASP) demo.
Why should I use COM?
Reason #1
By utilizing ActiveX DLL's, you can finally create that three-tiered application everyone talks about. By separating database functionality from ASP pages, you can minimize the changes needed within your ASP code.
Reason #2
If you are working on a large project, it might be a good idea to have the head guru construct and maintain a database class. By doing this, you have your best team member creating and maintaining your mission critical code. The database object will simplify database access for all developers. This will hopefully cut down on training, and time-consuming mistakes.
Tools used
The database access object was created in Visual Basic 6.0, utilizing Microsoft Data Access Components (MDAC) 2.6. You will notice that I am not calling setcomplete() or setabort(); I wanted to keep the code generic. Feel free to add your MTS or COM+ context code.
Class Information
The class contains some basic functions to get the creative juices flowing. The following information is given about each method.
fnSQL_RS(ByVal strSQL As String) - accepts a SQL statement and returns a record set.
fnSQL_XML(ByVal strSQL As String, ByVal strFilename As String) - accepts a SQL statement and filename, it saves the results as XML with the given filename. It returns TRUE if successful.
fnSP_RS(ByVal strSPName As String, ParamArray params() As Variant) - accepts the name of a stored procedure and array of parameters. It returns a record set.
fnSP(ByVal strSPName As String, ParamArray params() As Variant) - accepts the name of a stored procedure and array of parameters. It returns TRUE if the execution was successful.
The class also contains a property called ConnectionString, which is used to set connection information.
All the VB source code is included with this article; however, if you feel the need to do it from scratch, just create a new "ActiveX DLL" project, reference the required files, and start coding.
Visual Basic Code
Executing a SQL statement, returning a record set
Public Function fnSQL_RS(ByVal strSQL As String) As ADODB.Recordset
On Error GoTo ErrorHandler
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
'Create and set the connection information.
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = mvarm_ConnectionString
Conn.Open
'Create and set the record set information
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = Conn
rs.CursorLocation = adUseClient
rs.Source = strSQL
rs.Open strSQL, , adOpenForwardOnly, adLockReadOnly
Set fnSQL_RS = rs
rs.ActiveConnection = Nothing
Conn.Close
Set Conn = NothingExit Function
ErrorHandler:
Err.Raise Conn.Errors.Item(0).Number, _
Conn.Errors.Item(0).Source, _
Conn.Errors.Item(0).Description
Set rs = NothingSet Conn = NothingEnd Function
Executing a SQL statement, storing an XML file
(Note: function declaration line wrapped for readability.)
Public Function fnSQL_XML(ByVal strSQL As String,
ByVal strFilename As String) As BooleanOn Error GoTo ErrorHandler
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
'Create and set the connection information.
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = mvarm_ConnectionString
Conn.Open
'Create and set the record set information
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = Conn
rs.CursorLocation = adUseClient
rs.Source = strSQL
rs.Open strSQL, , adOpenForwardOnly, adLockReadOnly
rs.Save strFilename, adPersistXML
'A little clean up work
Conn.Close
Set rs = NothingSet Conn = Nothing
fnSQL_XML = TrueExit Function
ErrorHandler:
Err.Raise Err.Number, Err.Source, Err.Description
Set rs = NothingSet Conn = Nothing
fnSQL_XML = FalseEnd Function
Executing a stored procedure, returning a record set
(Note: function declaration line wrapped for readability.)
Public Function fnSP_RS(ByVal strSPName As String,
ParamArray params() As Variant) As ADODB.Recordset
On Error GoTo ErrorHandler
Dim cmd As ADODB.Command
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
'Create and set the connection information.
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = mvarm_ConnectionString
Conn.Open
'Create and set the command object
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = Conn
cmd.CommandText = strSPName
cmd.CommandType = adCmdStoredProc
'Create and set the Record set object
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = Conn
rs.CursorLocation = adUseClient
'Call function to set params and execute
prSetParams cmd, params
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
Set fnSP_RS = rs
'Clean up work
rs.ActiveConnection = Nothing
Conn.Close
Set Conn = NothingExit Function
ErrorHandler:
Err.Raise Conn.Errors.Item(0).Number, _
Conn.Errors.Item(0).Source, _
Conn.Errors.Item(0).Description
Set cmd = NothingSet Conn = NothingEnd Function
Executing a stored procedure with no results
(Note: function declaration line wrapped for readability.)
Public Function fnSP(ByVal strSPName As String,
ParamArray params() As Variant) As BooleanOn Error GoTo ErrorHandler
Dim cmd As ADODB.Command
Dim Conn As ADODB.Connection
'Create and set the connection information.
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = mvarm_ConnectionString
Conn.Open
'Create and set the command object
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = Conn
cmd.CommandText = strSPName
cmd.CommandType = adCmdStoredProc
'Call function to set params and execute
prSetParams cmd, params
cmd.Execute , , ADODB.adExecuteNoRecords
'Clean up
Conn.Close
Set cmd = NothingSet Conn = Nothing
fnSP = TrueExit Function
ErrorHandler:
Err.Raise Conn.Errors.Item(0).Number, _
Conn.Errors.Item(0).Source, _
Conn.Errors.Item(0).Description
Set cmd = NothingSet Conn = Nothing
fnSP = FalseEnd Function
ASP Code
The below samples use the PUBS database. This database is installed as part of the SQL7 and SQL2K installation. I will not bore you with another lecture on how to register a ActiveX DLL on your MTS or COM+ server; however, make sure that they are registered prior to running this code.
Executing a SQL statement, returning a recordset
<%
Dim obj
Dim rs
'Create an instance of the class
set obj = Server.CreateObject("DataAccess.clsDataAccess")
'Set the connection string
obj.ConnectionString = "Provider=SQLOLEDB.1;Data Source=;" _
& "Initial Catalog=PUBS;User id=;Password=;"
'Set execute and create our recordset
set rs = obj.fnSQL_RS("SELECT * FROM AUTHORS")
set obj = nothing
'Display something
While not rs.EOF
Response.Write(Trim(rs("AU_LNAME")) + ", " + rs("AU_FNAME") + "<br>")
rs.MoveNext
Wendset rs = nothing
%>
Executing a SQL statement, creating an XML file
<%
Dim obj
Dim retVal
Dim strXMLFileName
'Create an instance of the class
set obj = Server.CreateObject("DataAccess.clsDataAccess")
'Set the connection string
obj.ConnectionString = "Provider=SQLOLEDB.1;Data Source=;" _
& "Initial Catalog=PUBS;User id=;Password=;"
'Set execute and create our record set
strXMLFileName = Server.Mappath("demo.xml")
retval = obj.fnSQL_XML( _
"SELECT AU_LNAME, AU_FNAME, PHONE FROM AUTHORS",strXMLFileName)
set obj = nothing
'Display something
If retval = True then
Response.Write("The XML File was created.")
Else
Response.Write("Could not create the XML file.")
End If
%>
Executing a stored procedure, returning a record set
<%
Dim obj
Dim rs
'Create an instance of the class
set obj = Server.CreateObject("DataAccess.clsDataAccess")
'Set the connection string
obj.ConnectionString = "Provider=SQLOLEDB.1;Data Source=;" _
& "Initial Catalog=PUBS;User id=;Password=;"
'Set execute and create our record set.
'This SP comes as part of the PUBS install
set rs = obj.fnSP_RS("byroyalty", _
Array("@percentage", 3, adParamInput, 4, 40))
set obj = nothing
'Display something
Response.Write "It's not much, but you get the picture<br><br>"
While not rs.EOF
Response.Write rs("AU_ID")+ "<br>"
rs.MoveNext
Wend
set rs = nothing
%>
Executing a stored procedure with no results Note: You must create the following SP in the PUBS database to run this sample.
create procedure spAuthorInsert(@au_id varchar(11),
@au_lname varchar(40),@au_fname varchar(20),
@phone varchar(20),@address varchar(40),
@city varchar(20),@state char(2),
@zip char(5),@contract smallint)
as begininsert into authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract)
values
(@au_id, @au_lname, @au_fname, @phone, @address, @city,
@state, @zip, @contract)
end
<%
Dim obj
Dim retVal
Dim rs
'Create an instance of the class
set obj = Server.CreateObject("DataAccess.clsDataAccess")
'Set the connection string
obj.ConnectionString = "Provider=SQLOLEDB.1;Data Source=;" _
& "Initial Catalog=PUBS;User id=;Password=;"
'Set execute and create our record set.
'This SP is in the PUBS database
retVal = obj.fnSP("spAuthorInsert", _
Array("@au_id", 200, adParamInput, 11,"111-22-3333"), _
Array("@au_lname", 200, adParamInput, 40, "Wilson"), _
Array("@au_fname", 200, adParamInput, 20, "Carvin"), _
Array("@phone", 200, adParamInput, 20, "253-900-0001"), _
Array("@address", 200, adParamInput, 40, "1502 Main Street"), _
Array("@city", 200, adParamInput, 20, "Gig Harbor"), _
Array("@state", 129, adParamInput, 2, "WA"), _
Array("@zip", 129, adParamInput, 5, "98332"), _
Array("@contract", 2, adParamInput, 4, 1))
set obj = nothing
'Display something
If retval = True Then
Response.Write "The record was inserted"
Else
Response.Write "Could not insert the record"
End If
%>
Summary
I hope I have spawned a few new ideas with all this source code =). If you have any questions, comments, or problems please do not hesitate to send me an e-mail.
