For ASP users, the simpler the database operation is, the more time they have to consider the logic and application code, and the efficiency will be higher.
Today I am here to provide you with an idea of database operation. These codes are constantly completed and modified in my long-term ASP application. I have also completed many projects with it. It should be said that there is no problem in using it directly. . Of course, my abilities are limited, so I hope everyone can discuss it together.
Note: The codes in this post are all VBScript versions. In addition, it is best that you already have a certain foundation in handwriting ASP.
First, let me briefly introduce some features of my class:
Multiple databases of different types can be operated simultaneously.
There is no need to consider the difference in data types at all, and there is no need to think about whether to add single quotes to character fields.
The call is very simple, and the main operation on the database generally requires only one line of code.
Support mssql transaction rollback.
SQL statements can be automatically generated and output to facilitate debugging.
How to use:
1. Modify the first line in the clsDbctrl.asp file to your own database location (refer to the CreatConn function description below for the modification method). If you need to connect to multiple databases, you can add them yourself, with the same format.
2. Include this asp file in your new asp file. like:
<!--#include file="Inc/clsDbctrl.asp" --> Or:
<!--#include virtual="/Inc/clsDbctrl.asp"-->
3. Use the following code to apply this class:
A database connection:
<%
OpenConn() 'Open database connection
Dim db: Set db = New DbCtrl 'Create object
'Your Code Here...
Co(db) : CloseConn() 'Release the object and close the database connection
%>
Or (one or more database connections):
<%
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. For specific operation examples, please refer to the code examples in each function description.
Overview of methods and properties (detailed usage and examples are below):
Quote:
CreateConn generates database connection string
Oc establishes database connection
Co release object
OpenConn opens the default database connection
CloseConn closes the default database connection
dbCtrl.dbConn property, obtains the database connection to be operated, the default value is Conn
dbCtrl.dbErr property, read-only, outputs the captured error message
dbCtrl.Version property, read-only, program version information
dbCtrl.AutoId method, automatically obtain the unique serial number
dbCtrl.GetRecord method to obtain a record set that meets the conditions
dbCtrl.GetRecordBySql method, obtains the record set based on the sql statement
dbCtrl.GetRecordDetail method, based on the detailed data of a specified record
dbCtrl.AddRecord method, add a new record
dbCtrl.UpdateRecord method, updates records according to specified conditions
dbCtrl.DeleteRecord method, delete records that meet the conditions
The dbCtrl.ReadTable method obtains the contents of other fields in a record based on specified conditions.
dbCtrl.C method, close the record set object
dbCtrl.wGetRecord,
dbCtrl.wAddRecord,
dbCtrl.wUpdateRecord,
dbCtrl.wDeleteRecord These four methods are sql statements that obtain the corresponding operations (preceded by w)
Parameter convention:
Since ASP does not have an Arguments object and cannot use dynamic parameters, in the code of this class, Array is used to achieve this effect. Some parameters in this class can use arrays (as noted in the parameter description), but the following format should be followed when using arrays:
Array("Field1:Value1", "Field2:True", "Field3:100")
Yes, it is a bit like the json format. If variables are involved, then it is 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 use the above array format to set conditions or obtain content. The biggest feature here is that you don’t need to consider the type of the field when using it. Just follow the field with a colon and then the corresponding value. If you often write ASP programs by hand, you will soon feel the charm of using this method. In addition to the data type, it is also very convenient to add and delete conditions at any time. It doesn't matter if you don't understand how to use it yet, there are many examples below to illustrate this problem.
It is actually not difficult to encapsulate the database operations of ASP. I believe that you have made similar code before or borrowed other people's encapsulated code. But as everyone knows, once an error occurs using encapsulated code, debugging is a troublesome matter. Generally speaking, the simpler the encapsulated operation, the more complicated the debugging will be. When I wrote these codes, I tried my best to consider how to troubleshoot errors if something goes wrong. While simplifying the code for users to operate the database as much as possible, I can output SQL statements at any time to troubleshoot errors.
Finally, 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 to operate large data. In the future, I will consider the operation of stored procedures. Also encapsulated. There is also an issue of efficiency. If you want to pursue high efficiency, you should still consider COM+ and so on when using ASP. So again, this category is suitable for small and medium-sized ASP projects.
Okay, here are the detailed instructions for use:
one. Database connection
Considering the usage habits of most people, a public process is used for database connection, so everyone needs to modify it in the code. If you have already established a database connection, just comment out these lines. The code has built-in four database connection methods: MSSql, Access, MySQL, and Oracle. Of course, you can also add or delete them in the source code. Modify example:
Dim a : a = CreatConn(0, "TestData", "localhost", "username", "userpassword")
Dim b : b = CreatConn(1, "Data/%TestDb%.mdb", "", "", "")
To explain, the first parameter can be a string. If you are using Access, you can enter both a relative path and an absolute path in the second parameter. If you have a password, you can also enter it in the fifth parameter, such as:
Dim c : c = CreatConn("ACCESS", "E:/MyWeb/Data/%TestDB%.mdb", "", "", "mdbpassword")
Related functions:
Prototype: CreateConn(dbType, strDB, strServer, strUid, strPwd)
Function: Establish database connection string
Return value: String
parameter:
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 can use absolute or relative paths)
strServer: String database server address, please leave it blank for Access.
strUid: String database user name, please leave it blank for Access.
strPwd: String database password
Prototype: Oc(connStr)
Function: Open database connection
Return value: Object database connection object
parameter:
connStr: String database connection string, generated by the CreateConn function
Prototype: Co(obj)
Function: close object
parameter:
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: Close the default database connection object named Conn
Parameters: None
2. Database operations
The following is the function description of this database operation class. It should be regarded as a manual. Please look at the application examples.
Prototype: dbCtrl.dbConn(objConn)
Function: Get the database connection object
parameter:
objConn: Object The database connection object that has been established
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 object named Conn on the page.
Prototype: dbCtrl.AutoID(TableName)
Function: Automatically obtain unique serial number (automatic numbering)
Return value: Integer
parameter:
TableName: String The name of the data table whose unique serial number needs to be obtained
Example:
Dim newId
newId = db.AutoId("TestTable")
Response.Write(newId)
Prototype: dbCtrl.GetRecord(TableName,FieldsList,Condition,OrderField,ShowN)
Function: Get the record set that meets the conditions
Return value: Object record set object
parameter:
TableName : String table name
FieldsList: String field names, separated by commas, leave blank to display all fields
Condition: String or Array query condition. If it is an array, it should follow the previous parameter convention.
orderField: String sorting method
ShowN: Integer gets the number of records, equivalent to Select Top N in SQL
Example:
Dimrs
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)
For the above example, specifying the conditions in the following array format 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 statement to view the sql statement generated by this function:
Response.Write(db.wGetRecord("TestTable","fId,fName,fAge", Array("fSex:Male","IsActive:1"), "fName Asc", 0))
As you can see, just add a w before the original function.
Prototype: dbCtrl.GetRecordBySQL(strSelect)
Function: Obtain record set based on sql statement
Return value: Object record set object
parameter:
strSelect: String SQL statement used to generate recordset
Example:
Dimrs
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: Based on detailed data of a specified record
Return value: Object record set object
parameter:
TableName : String table name
Condition: String or Array query condition. If it is an array, it should follow the previous parameter convention.
Example:
Dim rs,Id
Id = Request.QueryString("id")
Set rs = db.GetRecordDetail("TestTable","Id=" & id)
'Your Code here...
db.C(rs)
Description: As you can already see, this is most commonly used to open a detailed page (such as a news content page)
Prototype: dbCtrl.AddRecord(TableName, ValueList)
Function: Add a new record
Return value: ID number of the new record (success) or 0 (failure)
parameter:
TableName : String table name
ValueList: Array Fields and values inserted into the table can only be arrays and should follow the previous parameter conventions
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("Add record successfully! The automatic numbering ID of this record is "& result)
End If
You see, you really don’t need to consider the type of the field.
If you want to see the fields and values in the code more clearly, you can also write it like this, but it depends on your preference:
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:
Response.Write(db.wAddRecord("TestTable",Array("Name:"&fName, "Sex:"&fSex, "WorkYear:"&fWorkYear, "Birthday:"&fBirth, "IsActive:True")))
Note: The returned Id value uses a relatively stupid method, which does not guarantee accuracy when the amount of concurrent data is large. Use with caution.
Prototype: dbCtrl.UpdateRecord(TableName, Condition, ValueList)
Function: Update records based on specified conditions
Return value: 1 (success) or 0 (failure)
parameter:
TableName : String table name
Condition: String or Array update condition, if it is an array, it should follow the previous parameter convention
ValueList: String or Array updated fields and values. If it is an array, it should follow the previous parameter convention.
Example:
Dim fName, fWorkYear
fName = "Wang San Tan"
fWorkYear = 10
Dim result
result = db.UpdateRecord("TestTable", "UId = 1308", Array("Name:"&fName, "WorkYear:"&fWorkYear))
If result<>0 Then
Response.Write("Update data successfully!")
End If
In addition, you can use the following statement to view the sql statement generated by this function:
Response.Write(db.wUpdateRecord("TestTable", "UId = 1308", Array("Name:"&fName, "WorkYear:"&fWorkYear)))
Prototype: dbCtrl.DeleteRecord(TableName,IDFieldName,IDValues)
Function: Delete records that meet the conditions
Return value: 1 (success) or 0 (failure)
parameter:
TableName : String table name
IDFieldName: String The name of the Id field of the table
IDValues: String or Array deletion condition, which can be multiple ID numbers separated by commas. If it is an array, it should follow the previous parameter convention.
Example:
Dim ids, result
ids = Request.Form("selectid") 'You can assume that the values obtained here are 12, 34, 256, 314 (the values submitted by the check box are like this)
result = db.DeleteRecord("TestTable", "UId", ids)
If result<>0 Then
Response.Write("Delete data successfully!")
End If
Of course, you can also specify other conditions using strings or arrays, such as:
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:
Response.Write(db.wDeleteRecord("TestTable", "UId", ids))
Prototype: dbCtrl.ReadTable(TableName, Condition, GetFieldNames)
Function: Get the contents of other fields in a record based on specified conditions
Return value: String (GetFieldNames is a single field) or Array (GetFieldNames is multiple fields)
parameter:
TableName : String table name
Condition: String or Array query condition. If it is an array, it should follow the previous parameter convention.
GetFieldNames: String A single field name or multiple field names separated by commas
Example:
Dim uid, result
uid = rs("postid") 'Assume this is the user id value in a record set on the page
result = db.ReadTable("UserTable","UId=" & uid, "UserName")
Response.Write("Publisher:" & result)
I have to say that in actual applications, this function is probably the most used. What you see above is a very common example. From one value to get another field in another table whose value is equal to that value. The value of (a bit hard to pronounce, but that’s exactly what it means). The following example will show you how to get multiple corresponding values based on this value.
Dim uid, result
uid = rs("postid") 'Assume this is the user id value in a record set on the page
result = db.ReadTable("UserTable","UId=" & uid, "UserName,UserSex,UserAge")
Response.Write("Publisher:" & result(0) & "<br /> Gender: " & result(1) & "<br /> Age: " &result(2))
As you can see, it's that simple. Entering multiple field names results in an array.
Prototype: dbCtrl.C(objRs)
Function: Close the record set object
parameter:
objRs: Object A recordset object on the page
Example:
Dimrs
Set rs = db.GetRecordDetail("TestTable","Id=123" )
'Your Code here...
db.C(rs)
This function has been used in many examples above, so I won’t explain it much. It is equivalent to rs.close: set rs = nothing.
clsDbctrl.rar