ASP database operation class

Today, I am here to provide you with an idea of database operations. These codes are constantly completed and corrected in the long-term ASP application, it has already been used to complete many projects. It should be said that it is no problem to directly use it. Of course, I have limited capabilities. I hope you can discuss them together.

Note: This post code is of the VBScript version. In addition, it is recommended that you have a certain foundation for handwritten ASP.

First, let's briefly introduce some features of this class:

You can operate on multiple different types of databases at the same time.
You do not need to consider the differences in data types, and you do not need to add a single quotation mark to a complex field.
The call is very simple. Generally, only one line of code is required for the main operations on the database.
Supports mssql transaction rollback.
You can automatically generate and output SQL statements to facilitate debugging.
Usage:

1. Modify the 1st behavior in the clsDbctrl. asp file in your own database location (for the modification method, refer to the CreatConn function description below ). To connect to multiple databases, you can add them in the same format.

2. Include this asp file in the newly created asp file. For example:
<! -- # Include file = "Inc/clsDbctrl. asp" --> or:
<! -- # Include virtual = "/Inc/clsDbctrl. asp" -->

3. Use the following code to apply this class:
One database connection:

<%
OpenConn () 'Open the database connection
Dim db: Set db = New DbCtrl 'create an object
'Your Code Here ......
Co (db): CloseConn () 'releases the object and closes the database connection.
%>

Or (one or more database connections ):

<%
Dim db1: Set db1 = New DbCtrl: db1.dbConn = Oc ()
Dim db2: Set db2 = New DbCtrl: db2.dbConn = Oc (B)
'Your Code Here ......
Co (db1): Co (db2)
%>

4. For specific operation examples, refer to the code examples in each function description.

Method and attribute Overview (detailed usage and examples are as follows ):

Reference:
CreatConn: generate a database connection string
Establish database connection at Oc
Co release object
OpenConn open default database connection
CloseConn closes the default database connection
DbCtrl. dbConn attribute, used to obtain the database connection to be operated. The default value is Conn.
DbCtrl. dbErr attribute, read-only, output captured error information
DbCtrl. Version attribute, read-only, program Version information
DbCtrl. AutoId method to automatically obtain the unique serial number
DbCtrl. GetRecord method to obtain a set of qualified records
DbCtrl. GetRecordBySql method to obtain a record set based on SQL statements
DbCtrl. GetRecordDetail method, based on the detailed data of a specified record
DbCtrl. AddRecord method to add a new record
DbCtrl. UpdateRecord method, used to update records based on specified conditions
DbCtrl. DeleteRecord method to delete records that meet the conditions
DbCtrl. ReadTable method, used to obtain the content of other fields in a record based on the specified conditions
DbCtrl. C method, disable record set object
DbCtrl. wGetRecord,
DbCtrl. wAddRecord,
DbCtrl. wUpdateRecord,
DbCtrl. wDeleteRecord: these four methods are used to obtain the corresponding SQL statement for the corresponding operation (w is added before ).

Parameter conventions:

Because ASP does not have an Arguments object and dynamic parameters cannot be used, in the Code of this class, Array (Array) is used to achieve this effect. Some parameters in this class can use arrays (specified in the parameter description), but the following format should be used when using Arrays:

Array ("Field1: Value1", "Field2: True", "Field3: 100 ")

Yes, it is a bit like the json format. If variables are involved, it will be like this:

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

It can be said that almost all content related to database fields in this class can be set with the above array format or get content. The biggest feature here is that you do not need to consider the field type during use. After a field is followed by a colon, you can keep up with the corresponding value. If you often write ASP programs by hand, you will soon feel the charm of using this method. In addition to data types, it is easy to add and delete conditions at any time. If you still don't understand how to use it, you don't need to turn it off. There are many examples below to illustrate this problem.

It is not difficult to encapsulate ASP database operations. I believe you have done similar code or borrowed encapsulated code from others before. As you know, once an error occurs in the encapsulated code, troubleshooting is troublesome. Generally, the simpler the encapsulation operation, the more complicated the troubleshooting is. When I write these codes, I have done my best to consider how to troubleshoot errors if errors occur. I can output SQL statements to troubleshoot errors at any time while trying to simplify the database operation code.

At last, it should be noted that the ASP database operations involved in this article are not suitable for large data. As you know, it is better to use stored procedures and other things to operate large data, in the future, I will consider encapsulating the operations on the stored procedure. There is another efficiency problem. To pursue high efficiency, we should still consider COM + and so on with ASP. So we should declare again that this class applies to small and medium ASP projects.

Now, we will provide detailed instructions:

1. Database Connection

Considering the usage habits of most people, the public process is used for database connection, so you need to modify it in the Code. If you have already established a database connection, just comment out the lines. The Code has built-in Connection Methods for MSSql, Access, MySQL, and oracle4. Of course, you can add or delete them in the source code. For example:

Dim a: a = CreatConn (0, "TestData", "localhost", "username", "userpassword ")
Dim B: B = CreatConn (1, "Data/% TestDb %. mdb ","","","")

Note that the 1st parameters can be strings. If Access is used, the relative path and absolute path of the 2nd parameters can be entered. If a password exists, you can also enter the following in the 5th parameters, for example:

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

Related functions:

Original Type: CreatConn (dbType, strDB, strServer, strUid, strPwd)
Function: Create a database connection string
Return Value: String
Parameters:
DbType: Integer or String connection 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 (both absolute and relative paths can be used for Access)
StrServer: String database server address. Leave Access blank.
StrUid: String database username. Leave Access blank.
StrPwd: String Database Password

Original Type: Oc (connStr)
Function: Open the database connection
Returned value: Object database connection Object
Parameters:
ConnStr: String database connection String generated by the CreatConn Function

Original Type: Co (obj)
Function: Close the object
Parameters:
Obj: name of the Object to be closed

Original Type: OpenConn
Function: When the default database connection is enabled, a connection object named Conn is automatically created.
Parameter count: None

Original Type: CloseConn
Function: Disable the default database connection object named Conn.
Parameter count: None

Ii. Database Operations

The following is a description of the functions of the database operation class. It should be a manual. For more information, see the example.

Original Type: dbCtrl. dbConn (objConn)
Function: Obtain the database connection Image
Parameters:
ObjConn: Object database connection Object
For example:
Dim db: Set db = New DbCtrl
Db. dbConn = Oc (CreatConn (1, "E: \ WebSite \ MySite \ Data \ % TestDb %. mdb ","","",""))
Co (db)

Description: This attribute is optional. If this attribute is not specified, the default data connection is the database connection image called Conn on the page.

Original Type: dbCtrl. AutoID (TableName)
Function: automatically obtains the unique serial number (automatic serial number)
Return Value: Integer
Parameters:
TableName: name of the data table whose String requires a unique serial number
For example:
Dim newId
NewId = db. AutoId ("TestTable ")
Response. Write (newId)

Original Type: dbCtrl. GetRecord (TableName, FieldsList, Condition, OrderField, ShowN)
Skill: obtain a qualified record set
Returned value: Object Record set Object
Parameters:
TableName: String table name
FieldsList: String field name, separated by commas (,). If it is left blank, it is all fields.
Condition: String or Array query Condition. If it is an Array, the preceding parameter conventions should be observed.
OrderField: String sorting method
ShowN: number of records retrieved by Integer, equivalent to Select Top N in SQL
For example:
Dim rs
Set rs = db. GetRecord ("TestTable", "fId, fName, fAge", "fSex = 'male' 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)

In the preceding example, specifying conditions using the following array is equivalent:

Set rs = db. wGetRecord ("TestTable", "fId, fName, fAge", Array ("fSex: Male", "IsActive: 1"), "fName Asc", 0)

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

Response. write (db. wGetRecord ("TestTable", "fId, fName, fAge", Array ("fSex: Male", "IsActive: 1"), "fName Asc", 0 ))

As you can see, you only need to add a w before the original function.

Original Type: dbCtrl. GetRecordBySQL (strSelect)
Skill: obtain a record set based on SQL statements
Returned value: Object Record set Object
Parameters:
StrSelect: SQL statement used to generate a record set
For example:
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)

Original Type: dbCtrl. GetRecordDetail (TableName, Condition)
Function: detailed data of a specified record
Returned value: Object Record set Object
Parameters:
TableName: String table name
Condition: String or Array query Condition. If it is an Array, the preceding parameter conventions should be observed.
For example:
Dim rs, Id
Id = Request. QueryString ("id ")
Set rs = db. GetRecordDetail ("TestTable", "Id =" & id)
'Your Code here...
Db. C (rs)

Note: just as you have already seen, this is most often used to open a detailed page (such as a news content page)

Original Type: dbCtrl. AddRecord (TableName, ValueList)
Skill: Add a new record
Returned value: ID (successful) or 0 (failed) of the new record)
Parameters:
TableName: String table name
ValueList: The field and value of the inserted Table by Array. It can only be an Array and must comply with the previous parameter conventions.
For example:
Dim fName, fSex, fWorkYear, fBirth
FName = "Wang Ertan"
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 ("record added successfully! The automatic Id of this record is "& result)
End If

See it. You really don't have to consider what type of field is.
If you want to see more clearly the field and value in the code, you can also write it like this, but it is best for you:

Result = db. AddRecord ("TestTable", Array ("Name:" & fName ,_
"Sex:" & fSex ,_
"WorkYear:" & fWorkYear ,_
"Birthday:" & fBirth ,_
"IsActive: True "))

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

Response. write (db. wAddRecord ("TestTable", Array ("Name:" & fName, "Sex:" & fSex, "WorkYear:" & fWorkYear, "Birthday:" & fBirth, "IsActive: true ")))

Note: The returned Id value is a stupid method, which cannot guarantee the accuracy when the number of concurrent data is large. Use it with caution.

Original Type: dbCtrl. UpdateRecord (TableName, Condition, ValueList)
Function: Updates records based on specified conditions.
Return Value: 1 (successful) or 0 (failed)
Parameters:
TableName: String table name
Condition: String or Array update Condition. If it is an Array, it should follow the previous parameter conventions.
ValueList: String or Array updated field and value. If it is an Array, it should follow the previous parameter conventions.
For example:
Dim fName, fWorkYear
FName = "Wang santan"
FWorkYear = 10
Dim result
Result = db. UpdateRecord ("TestTable", "UId = 1308", Array ("Name:" & fName, "WorkYear:" & fWorkYear ))
If result <> 0 Then
Response. Write ("data update successful! ")
End If

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

Response. Write (db. wUpdateRecord ("TestTable", "UId = 1308", Array ("Name:" & fName, "WorkYear:" & fWorkYear )))

Original Type: dbCtrl. DeleteRecord (TableName, IDFieldName, IDValues)
Function: delete a qualified record
Return Value: 1 (successful) or 0 (failed)
Parameters:
TableName: String table name
IDFieldName: name of the Id field of the String table
IDValues: String or Array deletion condition. It can be multiple idnumbers separated by commas. If it is an Array, it should follow the previous parameter conventions.
For example:
Dim ids, result
Ids = Request. Form ("selectid") 'can be assumed that the value obtained here is 12 and 34,256,314 (the values submitted by the check box are the same)
Result = db. DeleteRecord ("TestTable", "UId", ids)
If result <> 0 Then
Response. Write ("data deleted! ")
End If

Of course, you can also specify other conditions using strings or arrays, such:

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

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

Response. Write (db. wDeleteRecord ("TestTable", "UId", ids ))

Original Type: dbCtrl. ReadTable (TableName, Condition, GetFieldNames)
Function: obtains the content of other fields in a record based on the specified conditions.
Return Value: String (GetFieldNames is a single field) or Array (GetFieldNames is multiple fields)
Parameters:
TableName: String table name
Condition: String or Array query Condition. If it is an Array, the preceding parameter conventions should be observed.
GetFieldNames: String names of a single field or multiple field names separated by commas
For example:
Dim uid, result
Uid = rs ("postid") 'suppose this is the user ID value in a record set on the page
Result = db. ReadTable ("UserTable", "UId =" & uid, "UserName ")
Response. Write ("Sender:" & result)

I have to say that in practical applications, this function may be the most used. What you see above is a very common example, obtain the value of a field in another table from a value that is equal to the value of another field (this is a bit difficult, but it does mean this ). The following example shows how to obtain multiple values based on this value.

Dim uid, result
Uid = rs ("postid") 'suppose this is the user ID value in a record set on the page
Result = db. ReadTable ("UserTable", "UId =" & uid, "UserName, UserSex, UserAge ")
Response. write ("Sender:" & result (0) & "<br/> Gender:" & result (1) & "<br/> age: "& result (2 ))

As you can see, in this simple way, input multiple field names to get an array.

Original Type: dbCtrl. C (objRs)
Function: Close the record set object
Parameters:
ObjRs: a record set Object on the Object page
For example:
Dim rs
Set rs = db. Fig ("TestTable", "Id = 123 ")
'Your Code here...
Db. C (rs)

This function is used in many examples above and will not be explained much. It is equivalent to rs. close: set rs = nothing.
ClsDbctrl.rar