ASP practical class library-dataaccess

I made an ASP practical class library a few days ago.
-- Put asplib in the root directory
| -- System
| -- Adovbs. inc
| -- Util
| -- Configuration. asp
| -- Dataaccess. asp
| -- Validator. asp
| --......
| -- Control
| -- Dropdownlist. asp
| -- Datalist. asp
| -- DataGrid. asp
| -- Script
| -- DataGrid. js
| -- Images
| -- DataGrid
| --First.gif
| --Next.gif
| --....

First, let's take a look at the database operation class (dataaccess ):

<! -- # Include file = "../system/adovbs. Inc" -->
<%
'<Class>
'<Name> dataaccess </Name>
'<Description> <! [CDATA [database sequence class (Version 2.0); The executereader method returns the disconnected record set object instead of the custom datatable object. Each method ensures the most

Less database resources are consumed. Database Connection objects use client cursors so that record sets can use attributes such as recordcount. Added the debug function. Combined with class configuration to reduce

Strconnectionstring parameter (previously used as executereader (strconn, strcommadntext), but the coupling degree is increased! Author: Christmas pineapple package]> </description>
'<Attributes>
'</Attributes>
'<Methods>
'<Method name = "executereader (byval strcommandtext)" return = "recordset" comment = "queries the database and returns the disconnected record set pair

Like "/>
'<Method name = "executenonquery (byval strcommandtext)" return = "Boolean" comment = "perform database update/delete operations and return the number of affected rows

"/>
'<Method name = "executescalar (byval strcommandtext)" return = "variant" comment = "executes the query and returns the first in the returned result set.

The first column of the row. Ignore extra columns or rows; "/>
'<Method name = "createparameter (byval strname, byval intdatatype, byval intdirection, byval intsize, byval varvalue )"

Comment = "adding a parameter object to a parameter set in the class"/>
'<Method name = "getparametervalue (byval strparamname)" return = "variant" comment = "return the value of the specified parameter name"/>
'<Method name = "clearparameter ()" return = "variant" comment = "Clear parameter set"/>
'</Methods>
'</Class>

Class dataaccess

'Database connection object
Private connection _
'Database command object
Private command _
'Connection string
Private connectionstring _
'Debug
Private debug _

'Query the database and return the disconnected record set object.
Public Function executereader (byval strcommandtext)
If debug _ = false then
On Error resume next
End if
'Open the database connection
Call openconnection ()
'Set command
Call setcommand (strcommandtext)
Dim objrs, objoutrs
Set objrs = command _. Execute ()
Set objoutrs = server. Createobject ("ADODB. recordset ")
Objoutrs. Open objrs
'Return the disconnected record set
Set executereader = objoutrs
If err. Number <> 0 then
Err. Clear ()
Response. Write ("database read operation error ")
Response. End ()
End if
Objrs. Close ()
Set objrs = nothing
Call closeconnection ()
End Function

'Database update and deletion operations
Public Function executenonquery (byval strcommandtext)
If debug _ = false then
On Error resume next
End if
'Open the database connection
Call openconnection ()
'Set command
Call setcommand (strcommandtext)
Dim intrecordsaffected
Command _. Execute intrecordsaffected, adexecutenorecords
Executenonquery = intrecordsaffected
If err. Number <> 0 then
Err. Clear ()
Response. Write ("database update operation error ")
Response. End ()
End if
Call closeconnection ()
End Function

'Execute the query and return the first column in the first row of the returned result set. Ignore extra columns or rows
Public Function executescalar (byval strcommandtext)
If debug _ = false then
On Error resume next
End if
'Open the database connection
Call openconnection ()
'Set command
Call setcommand (strcommandtext)
Dim objrs
Set objrs = command _. Execute ()
If not objrs. EOF then
Executescalar = objrs (0)
End if
If err. Number <> 0 then
Err. Clear ()
Response. Write ("database read single value operation error ")
Response. End ()
End if
Objrs. Close ()
Set objrs = nothing
Call closeconnection ()
End Function

'Create Parameters
Public Function createparameter (byval strname, byval intdatatype, byval intdirection, byval intsize, byval varvalue)
If debug _ = false then
On Error resume next
End if
Dim objparam
If intdirection = 4 then
Set objparam = command _. createparameter (strname, intdatatype, intdirection)
Elseif intdirection = 2 then
Set objparam = command _. createparameter (strname, intdatatype, intdirection, intsize)
Else
Set objparam = command _. createparameter (strname, intdatatype, intdirection, intsize, varvalue)
End if
Command _. Parameters. append (objparam)
If err. Number <> 0 then
Err. Clear ()
Response. Write ("parameter creation failed ")
Response. End ()
End if
End Function

'Get the parameter value of the specified parameter name
Public Function getparametervalue (byval strparamname)
If debug _ = false then
On Error resume next
End if
Getparametervalue = command _. parameters (strparamname). Value
If err. Number <> 0 then
Err. Clear ()
Getparametervalue = ""
End if
End Function

'Clear Parameters
Public Function clearparameter ()
Dim I
For I = 0 to command _. Parameters. Count-1
Command _. Parameters. Delete (0)
Next
End Function

'Initialize the database connection object
Private sub openconnection ()
If debug _ = false then
On Error resume next
End if
If not isnull (connection _) then
Set connection _ = server. Createobject ("ADODB. Connection ")
Connection _. cursorlocation = aduseclient
Connection _. Open (connectionstring _)
If err. Number <> 0 then
Err. Clear ()
Closeconnection (connection _)
Response. Write ("database connection error ")
Response. End ()
End if
End if
End sub

'Close the database connection object
Private sub closeconnection ()
If not connection _ is nothing then
Connection _. Close ()
Set connection _ = nothing
End if
End sub

'Initialize the database command object
Private sub setcommand (byval strcommandtext)
Command _. activeconnection = connection _
If command _. Parameters. Count> 0 then
Command _. commandtype = 4
Else
Command _. commandtype = 1
End if
Command _. Prepared = true
Command _. commandtext = strcommandtext
End sub

'Close database command object
Private sub closecommand ()
If not command _ is nothing then
SET command _ = nothing
End if
End sub

'Initialization class
Private sub class_initialize ()
On Error resume next
Connectionstring _ = new configuration. getappsetting ("connectionstring ")
SET command _ = server. Createobject ("ADODB. Command ")
If new configuration. getsystemsetting ("debug") = "true" then
Debug _ = true
Else
Debug _ = false
End if
End sub
 
'Destroy class
Private sub class_terminate ()
Call closecommand ()
Call closeconnection ()
End sub

End Class
%>

In the previous version, all methods have strconnectionstring parameters, such as executereader (strconnectionstring, strcommandtext );
I still recommend the previous version of dataaccess. (because copy can be used, you do not need to configure the file)
However, I am a lazy, so I added a configuration file (asp_web.config) to reduce the strconnectionstring parameter and added the debug function;
Asp_web.config:
<? XML version = "1.0" encoding = "UTF-8"?>
<Configuration>
<Deleetask>
<Add key = "connectionstring" value = "provider = sqloledb.1; persist Security info = false; user id = sa; Pwd = xxxx; initial catalog = XXXXX; data Source = localhost "/>
<Add key = "admin_ticket" value = "chcw_admin"/>
<Add key = "uploadimage_maxsize" value = "102400"/>
</Appsettings>
<System>
<Debug value = "true"/>
</System>
</Configuration>

In actual tests, the executereader method is used to obtain the data ratio Rs. in open SQL, Conn takes data a little slowly, but it greatly reduces database resource consumption because executereader returns a disconnected record set;

Let's take a look at the example:
'First include
<! -- # Include virtual = "/asplib/util/configuration. asp" -->
<! -- # Include virtual = "/asplib/util/dataaccess. asp" -->

--- Executereader method ---
<%
Dim objrs
Set objrs = new dataaccess. executereader ("select top 20 newsid, newstitle, updatetime from news where =" & newscategoryid)
While not objrs. EOF
Response. Write (objrs (0) & objrs (2) & "<br/> ")
Objrs. movenext
Wend
Objrs. Close ()
Set objrs = nothing
%>
<%
'Stored procedure
Dim objrs, objdba
Set objdba = new dataaccess
Objdba. createparameter "@ newscategoryid", adinteger, adparaminput, 4,1
Set objrs = new dataaccess. executereader ("getnewsbycategoryid ")
While not objrs. EOF
Response. Write (objrs (0) & objrs (2) & "<br/> ")
Objrs. movenext
Wend
Objrs. Close ()
Set objrs = nothing
%>

---- Executenonquery method -----
<%
Dim intrecordsaffected
Intrecordsaffected = new dataaccess. executenonquery ("Update News set newstitle = 'new title' where newsid = 10 ")
Response. Write ("updated rows:" & intrecordsaffected)
%>
<%
'Stored procedure
Dim objdba, intrecordsaffected
Set objdba = new dataaccess
Objdba. createparameter "@ intnewsid", adinteger, adparaminput, 4,10
Objdba. createparameter "@ strnewstitle", advarchar, adparaminput, 50, "New title"
Intrecordsaffected = objdba. executenonquery ("news_updatetitle ")
If intrecordsaffected = 0 then
Response. Write ("update failed! ")
Else
Response. Write ("Update successful! ")
End if
Set objdba = nothing
%>

----- Executescalar method ----
<%
Dim intreturn
Intreturn = new dataaccess. executescalar ("select count (*) from News ")
Response. Write ("Total number of records:" & intreturn)
%>