ASP operation database class, make data operation simple

For the user of ASP, the simpler the database operation, the more time to consider the logic and application code, and the efficiency will be higher. Today, I am here to provide you with a database operation of the idea, the code is my long-term ASP application in the continuous completion and correction, has been used to complete a lot of projects, it should be said that directly to use is no problem. Of course, my ability is limited, I hope we can discuss together.

Description:This post code is a VBScript version. In addition, the best you have a certain basis for handwritten ASP.

let me briefly introduce some of the characteristics of this class:

1. Can operate several different types of databases at the same time.
2. Do not have to consider the difference of data type, no longer need not think of character fields plus single quotes.
3. The call is very simple, and the main operation of the database typically requires only one line of code.
4. Support MSSQL transaction rollback.
5. Can automatically generate and output SQL statements for easy debugging.


How to use:

1. Modify the 1th behavior in the clsdbctrl.asp file your own database location (modify method refer to the Creatconn function description below). If you need to connect multiple databases, you can add them in the same format.

2. Include this ASP file in your new ASP file. Such as:
<!--#include file= "inc/clsdbctrl.asp"--> or:
<!--#include virtual= "/inc/clsdbctrl.asp"-->

3. Apply this class using the following code:
A database connection:

Reference:

<%
Openconn () ' Open database connection
Dim Db:set db = New Dbctrl ' Build object
' Your Code here ...
Co (db): Closeconn () Release object, close database connection
%>

or (one or more database connections):

Reference:

<%
Dim db1:set db1 = New DbCtrl:db1.dbConn = Oc (a)
Dim db2:set DB2 = New DbCtrl:db2.dbConn = Oc (b)
' Your Code here ...
Co (DB1): Co (DB2)
%>

4. Examples of specific operations can refer to the code examples in each function description.

Methods and Properties Overview ( detailed usage and examples below ):

Reference:

Creatconn Build Database connection string
Oc Establish database connection
Co Release Objects
Openconn Open the default database connection
Closeconn closes the default database connection
Dbctrl.dbconn property, gets the database connection to manipulate, and the default value is conn
Dbctrl.dberr property, read-only, output-captured error message
Dbctrl.version properties, read-only, program version information
Dbctrl.autoid method to automatically obtain a unique serial number
Dbctrl.getrecord method to obtain a record set that meets the criteria
Dbctrl.getrecordbysql method to obtain a record set based on SQL statement
Dbctrl.getrecorddetail method, according to the detailed data of a specified record
Dbctrl.addrecord method, add a new record
Dbctrl.updaterecord method, new record based on specified criteria
Dbctrl.deleterecord method to delete a qualifying record
Dbctrl.readtable method that gets the contents of another field in a record based on a specified condition
Dbctrl.c method to close the Recordset object
Dbctrl.wgetrecord,
Dbctrl.waddrecord,
Dbctrl.wupdaterecord,
Dbctrl.wdeleterecord These 4 methods are to obtain the corresponding action (preceded by W) of the SQL statement

parameter conventions:

because the ASP does not have arguments objects and cannot use dynamic parameters, the array (array) is used in the code of this class to achieve this effect. Some of the parameters in this class can be used in arrays (as noted in the parameter descriptions), but the following format should be used when using arrays:

Reference:

Array (" field1:value1", " field2:true", " field3:100")

Yes, a bit like JSON format, if it involves variables, that's it:

Array (" Field1:" & Value1, " Field2:" & Value2, " Field3:" & Value3)

It can be said that almost all of the database field-related content in this class can be set with the above array format or get the content. The biggest feature here is that you don't have to think about the type of field when you use it, and then follow a colon after the field, followed by the corresponding value on the line. If you often write ASP programs, you will soon feel the charm of using this method, in addition to the data type not to consider, it is also easy to add and delete conditions at any time. If you don't understand how to use it, there are many examples to illustrate this problem.

It is not difficult to encapsulate ASP's database operations, and I believe you have done similar code or borrowed other people's encapsulated code before. However, as you know, the use of encapsulated code once the error, row error is a more troublesome thing, generally speaking, the simpler the encapsulation operation is more complex. When I write this code, I have done my best to consider how to troubleshoot errors if I make a mistake, and can output SQL statement troubleshooting whenever possible to simplify the user's code to manipulate the database.

Finally, it needs to be explained that the ASP database operation involved in this paper is not suitable for large data, as you know, it is better to manipulate large data or use stored procedures and so on, and then I will consider the operation of the stored procedures in the package. There is also a problem of efficiency, to pursue efficiency, the ASP or should consider COM + and so on, so again, this class applies to small and medium ASP projects.

All right, here's the detailed instructions for using:

one. Database Connection

Take into account the majority of the use of the database connection using a common process, so you need to modify the code, if you have established a database connection, the lines of comments off the line. The code has built-in MSSQL,ACCESS,MYSQL,ORACLE4 database connection, of course, you can also add or remove the source code. Modifications such as:

Reference:

Dim a:a = creatconn (0, "TestData", "localhost", "username", "UserPassword")
Dim b:b = creatconn (1, "Data/%testdb%.mdb",

"", "", "")

To illustrate, the 1th argument can be a string. If you are using Access, the 2nd parameter input relative path and absolute path are OK, and if you have a password, you can enter it in the 5th parameter, for example:

Reference:

Dim c:c = Creatconn ("ACCESS", "E:\MyWeb\Data\%TestDB%.mdb", "" "," "," Mdbpassword ")

Related functions:

prototype: Creatconn (DbType, strDB, strserver, Struid, strpwd)
Function: Establish database connection string
return value: String
Parameters
Dbtype:integer or String Connection database type
(0 or "MSSQL")-Microsoft SQL Server
(1 or "access")-Microsoft Office ACCESS
(2 or "MySQL")-MySQL Server
(3 or "Oracle")-ORACLE Server
Strdb:string database name or database address (Access uses an absolute or relative path)
Strserver:string database server address, access please leave blank
Struid:string database user name, access please leave blank
Strpwd:string Database Password

prototype: Oc (CONNSTR)
Function: Open database connection
Return value: Object Database Connection object
Parameters
Connstr:string database connection string, generated by the Creatconn function

prototype: Co (obj)
Features: Closing objects
Parameters
Obj:object the name of the object to be closed

prototype: Openconn
Function: Open the default database connection and automatically create a connection object named Conn
Parameters: None

prototype: Closeconn
Function: Turn off default database connection object with name Conn
Parameters: None

two. Database Operations

Here is the database operation class function Description, should be a manual, please see more examples of applications.

prototype: Dbctrl.dbconn (objconn)
Features: Getting database connection objects
Parameters
Objconn:object database connection objects that have been established
Example

Reference:

Dim Db:set db = New Dbctrl
Db.dbconn = Oc (Creatconn (1, "E:\WebSite\MySite\Data\%TestDb%.mdb", "", "", "))
Co (db)

Note: This property is optional, and if you do not specify this property, the default data connection is the database connection object named Conn on the page

prototype: dbctrl.autoid (tablename)
Function: Automatically get unique serial number (AutoNumber)
return value: Integer
Parameters
Tablename:string the name of the data table that needs to be given a unique serial number
Example

Reference:

Dim NewId
NewId = db. Autoid ("TestTable")
Response.Write (newId)

prototype: Dbctrl.getrecord (Tablename,fieldslist,condition,orderfield,shown)
Function: To obtain a record set that meets the criteria
Return value: Object record set objects
Parameters
tablename:string Table Name
fieldslist:string field names, separated by commas, left blank for all fields
Condition:string or array query criteria, if the array should follow the preceding argument conventions
Orderfield:string Sorting method
Shown:integer gets the number of records, equivalent to Select top N in sql
Example

Reference:

Dim RS
Set rs = db. Getrecord ("TestTable", "Fid,fname,fage", "fsex= ' man" and IsActive = 1 "," FName Asc ", 0)
While not rs.eof
Response.Write ("Name is:" & RS (1) & "Age is:" & RS (2) & "<br/>")
Rs.movenext ()
Wend
Db. C (RS)

For the above example, the following array method is used to specify that the condition is equivalent:

Reference:

Set rs = Db.wgetrecord ("TestTable", "Fid,fname,fage", Array ("Fsex: Male", "isactive:1"), "FName Asc", 0)

In addition, you can use the following statement to view the SQL statement generated by this function:

Reference:

Response.Write (Db.wgetrecord ("TestTable", "Fid,fname,fage", Array ("Fsex: Male", "isactive:1"), "FName Asc", 0

)

As you can see, just add a W to the previous function.

prototype: Dbctrl.getrecordbysql (strselect)
Function: Get recordset from SQL statement
Return value: Object record set objects
Parameters
strselect:string The SQL statement used to generate the recordset
Example

Reference:

Dim RS
Set rs = db. Getrecordbysql ("Select a.ID, A.lastname, B.group from User a innerjoin depart B on a.groupid = B.groupid")
' Your Code here ...
Db. C (RS)

prototype: Dbctrl.getrecorddetail (tablename, Condition)
Function: The detailed data of a specified record according to a particular section
Return value: Object record set objects
Parameters
tablename:string Table Name
Condition:string or array query criteria, if the array should follow the preceding argument conventions
Example

Reference:

Dim RS, Id
id = request.querystring ("id")
Set rs = db. Getrecorddetail ("TestTable", "id=" & Id)
' Your Code here ...
Db. C (RS)

Description: As you can see, this is most commonly used when opening a detailed page (such as a news content page)

prototype: Dbctrl.addrecord (tablename, ValueList)
Function: Add a new record
Return value: The ID number (successful) or 0 (failed) of the new record
Parameters
tablename:string Table Name
Valuelist:array inserts a table's fields and values, only an array and should follow the previous argument conventions
Example

Reference:

Dim FName, Fsex, Fworkyear, Fbirth
FName = "The two altar of the king"
Fsex = "Male"
Fworkyear = 12
Fbirth = Cdate ("1981-10-23")
Dim result
result = db. AddRecord ("TestTable", Array ("Name:" &fname, "Sex:" &fsex, "workyear:" &fworkyear, "Birthday:" &fbirth , "Isactive:true"))
If result<>0 Then
Response.Write ("Add record success!") The AutoNumber ID for this record is & result)
End If

See, really do not consider the type of field is what drop.
If you want the fields and values in the code to see more clearly, you can write this, but by the good of you:

Reference:

result = db. AddRecord ("TestTable", Array ("Name:" & Fname,_
"Sex:" & Fsex,_
"Workyear:" & Fworkyear,_
"Birthday:" & fbirth,_
"Isactive:true"))

In addition, you can use the following statement to view the SQL statement generated by this function:

Reference:

Response.Write (Db.waddrecord ("TestTable", Array ("Name:" &fname, "Sex:" &fsex, "Workyear:" &fworkyear , "Birthday:" &fbirth, "Isactive:true"

))

Note: The return of the ID value is a relatively stupid method, and can not guarantee the high volume of concurrent data when the accuracy, use caution.

prototype: Dbctrl.updaterecord (tablename, Condition, ValueList)
Function: New record based on specified criteria
return value: 1 (successful) or 0 (failed)
Parameters
tablename:string Table Name
Condition:string or array to update the condition if the array should follow the preceding argument conventions
Valuelist:string or array updated fields and values, if the array should follow the preceding argument conventions
Example

Reference:

Dim FName, Fworkyear
FName = "Wang San Altar"
Fworkyear = 10
Dim result
result = db. UpdateRecord ("TestTable", "UId = 1308", Array ("Name:" &fname, "Workyear:" &fworkyear))
If result<>0 Then
Response.Write ("Update data Successful!") ")
End If

In addition, you can use the following statement to view the SQL statement generated by this function:

Reference:

Response.Write (Db.wupdaterecord ("TestTable", "UId = 1308", Array ("Name:" &fname, "Workyear:" &fworkyear)) )

prototype: Dbctrl.deleterecord (tablename,idfieldname,idvalues)
Function: Delete Qualifying records
return value: 1 (successful) or 0 (failed)
Parameters
tablename:string Table Name
Name of the ID field of the idfieldname:string table
Idvalues:string or array deletion criteria, which can be multiple ID numbers separated by commas, if the array should follow the preceding argument conventions
Example

Reference:

Dim IDs, result
ids = Request.Form ("Selectid") ' can assume that the values obtained here are 12, 34, 256, 314 (the value of the check box is the same)
result = db. DeleteRecord ("TestTable", "UId", IDS)
If result<>0 Then
Response.Write ("Delete data successful!") ")
End If

Of course, you can also use strings or arrays to specify other conditions, such as:

Reference:

Result

= db. DeleteRecord ("TestTable", "UId", "IsActive = 0 and FirstName = ' Tom '")

In addition, you can use the following statement to view the SQL statement generated by this function:

Reference:

Response.Write (Db.wdeleterecord ("TestTable", "UId", IDs))

prototype: dbctrl.readtable (tablename, Condition, Getfieldnames)
Function: Gets the contents of other fields in a record according to a specified condition
return value: String (Getfieldnames to a single field) or Array (Getfieldnames to multiple fields)
Parameters
tablename:string Table Name
Condition:string or array query criteria, if the array should follow the preceding argument conventions
Getfieldnames:string a single field name or multiple field names separated by commas
Example

Reference:

Dim uid, result
UID = RS ("PostID") ' assumes that this is the user ID value of a recordset on the page
result = db. Readtable ("Usertable", "uid=" & UId, "UserName")
Response.Write ("Publisher:" & result)

I have to say, in practical applications, this function may be the most used, you see above is a very common example, from a value to get another table in a field value equal to the value of the value of another field (a bit clumsy, but that is the meaning). The following example will show you how to get multiple values based on this value.

Reference:

Dim uid, result
UID = RS ("PostID") ' assumes that this is the user ID value of a recordset on the page
result = db. Readtable ("Usertable", "uid=" & UId, "Username,usersex,userage")
Response.Write ("Publisher:" & Result (0) & "<br/> Sex:" & Result (1) & "<br/> Age:" &result (2))

As you can see, it's as simple as entering multiple field names to get an array.

prototype: DBCTRL.C (OBJRS)
Function: Close Recordset object
Parameters
Objrs:object a Recordset object on the page
Example

Reference:

Dim RS
Set rs = db. Getrecorddetail ("TestTable", "id=123")
' Your Code here ...
Db. C (RS)

Many of the above examples are used, not much explained, which is equivalent to rs.close:set rs = Nothing.