How to Implement Transaction Processing Using ASP

Use ASP to implement transaction processing and select the Blog from AppleBBS
Keyword: how to use ASP to implement Transaction Processing
Source

When developing a Web application, you must access the database without exception to query, insert, update, and delete data. Due to the impact of application logic, it is sometimes necessary to combine multiple database operation commands into a unit of work (transaction ). In a database, transactions refer to a group of logical operation units, which convert data from one state to another. To ensure data consistency in the database, the data should be operated in discrete logical units: when it is complete, Data Consistency can be maintained; when some operations in the unit fail, the entire transaction is ignored, and all operations after the starting point are returned to the starting state.

In fact, by default, each operation on the database is an implicit transaction. This article takes a typical user registration program as an example to introduce three methods to implement Transaction Processing Using ASP: solutions based on ASP database components, solutions based on internal database transaction processing mechanisms, and solutions based on MTS components.

Program functions
Create two tables in the SQL Server database: USER table and USERDOC table. The USER table stores the USER name and password of the registered USER, and the USERDOC table stores the personal data of the registered USER, which is indexed by the USER name. The definitions of the table USER and USERDOC are as follows:
Create Table USER (userName varchar (30), userPasswd varchar (30 ))
Create Table USERDOC (userName varchar (30), Age int, Sex int, PhoneNumber varchar (20), Address varchar (50 ))

When a USER requests registration, the ASP script first inserts the USER name and password into the USER table, and then inserts the USER's personal information (age, gender, contact number, and home address) into the USERDOC table ). At the same time, the application must ensure that each record in the USER table has a corresponding record in the USERDOC table.

Method 1
The Connection object in ASP's built-in ADO component can be used to implement transaction processing for database operations. Some methods of the Connection object are as follows:
● Connection. BeginTrans method: Start a transaction;
● Connection. CommitTrans method: completes/submits a transaction;
● Connection. RollBackTrans method: Undo/discard a transaction.
// Start a transaction operation
<% Conn. BeginTrans %>
<% SqlText = "Insert into USER (userName, userPasswd) values ('" %>
<% SqlText = sqlText & request ("usrName") & "','" & request ("usrPasswd") & "')" %>
<% Conn.exe cute (sqlText) %>
<% If conn. Errors. Count> 0 then %>
<% Conn. Errors. Clear %>
// If the data insertion operation fails, the transaction rolls back.
<% Conn. RollBackTrans %>
<% Response. redirdbms RegisterFail.html %>
<% End if %>
<% SqlText = "Insert into USERDOC (userName, Age, Sex, PhoneNumber, Address)" %>
<% SqlText = sqlText & "values ('" & request ("usrName") & "'," & request ("Age") %>
<% SqlText = sqlText & ", '" & request ("PhoneNum") & "', '" %>
<% SqlText = sqlText & request ("Address") & "')" %>
// Execute the second insert statement in the Transaction Unit
<% Conn.exe cute (sqlText) %>
<% If conn. Errors. Count> 0 then %>
<% Conn. Errors. Clear %>
// If the operation fails, the transaction rolls back
<% Conn. RollBackTrans %>
<% Response. redirdbms RegisterFail.html %>
<% End if %>
// If the entire transaction operation is executed correctly, the transaction is committed.
<% Conn. CommitTrans %>
// Go to the registration success page
<% Response. redirdbms RegisterOk.html %>

Method 2
You can use the internal transaction processing mechanism of the database system to write a stored procedure containing transactions on the database server to complete the transaction processing of data operations. At the same time, you can use the ADO component to call the stored procedure and determine whether the transaction processing is successful Based on The Returned Code of the stored procedure.

In the database system, each SQL statement is a transaction. Therefore, each statement can be completed or returned to the start point. However, if you want all the operations of a group of SQL statements to be completed or all of them are invalid, you need to use the transaction processor of the database to implement the operation.

The main code for generating a stored procedure in a database is as follows:
Create proc RegisterUser (@ usrName varchar (30), @ usrPasswd varchar (30), @ age int, @ PhoneNum varchar (20), @ Address varchar (50) as begin
// Display the definition and start a transaction
Begin tran
Insert into USER (userName, userPasswd) values (@ usrName, @ usrPasswd)
If @ error <> 0
Begin
// If the operation fails, the transaction is rolled back.
Rollback tran
// Return the stored procedure and set the return code as a transaction operation failure
Return-1
End
Insert into USERDOC (userName, age, sex, PhoneNumber, Address)
Values (@ Usrname, @ age, @ PhoneNum, @ Address)
If @ error <> 0
Begin
// If the operation fails, the transaction is rolled back.
Rollback tran
Return-1
End
// If the operation is correct, the transaction is committed.
Commit tran
Return 0
End
The main code for calling a database stored procedure in an ASP script is as follows:
<% Set Comm = server. CreateObject
("ADODB. Command") %>
<% Set Comm. ActiveConnection = conn %>
<% Comm. CommandType = adw.storedproc %>
<% Comm. CommandText = "RegisterUser" %>
// Create a stored procedure return parameter object
<% Set RetCode = Comm. CreateParameter
("RetCode", adInteger, adParamReturnValue) %>
// Create a stored procedure input parameter object
<% Set usrName = Comm. CreateParameter ("usrName", adVarchar, adParamInput, 30) %>
<% Set usrPwd = Comm. CreateParameter
("UsrPasswd", adVarchar, adParamInput, 30) %>
<% Set age = Comm. CreateParameter ("age", adInteger, adParamInput) %>
<% Set PhoneNum = Comm. CreateParameter
("PhoneNum", adVarchar, adParamInput, 20) %>
<% Set Address = Comm. CreateParameter ("Address", adVarchar, adParamInput, 50) %>
<% Comm. Parameters. Append usrName %>
<% Comm. Parameters. Append usrPwd %>
<% Comm. Parameters. Append age %>
<% Comm. Parameters. Append PhoneNum %>
<% Comm. Parameters. Append Address %>
<% Comm. Parameters ("usrName") = request ("usrName") %>
<% Comm. Parameters ("usrPasswd") = request ("usrPasswd") %>
<% Comm. Parameters ("age") = request ("age") %>
<% Comm. Parameters ("PhoneNum") = request ("PhoneNum") %>
<% Comm. Parameters ("Address") = request ("Address") %>
<% Comm. Execute %>
<% RetValue = Cint (Comm ("RetCode") %>
// Check whether the registration is successful based on the Code returned by the database Stored Procedure
<% If RetValue <0 then %>
<% Response. Redirect RegisterFail.html %>
<% Else %>
<% Response. Redirect RegisterOk.html %>
<% End if %>

Method 3
When using the Transaction processing mechanism of the MTS (Microsoft Transaction Server) component to process transactions, note that transactions under this mechanism cannot span multiple ASP pages, if a transaction needs objects from multiple components, the operations on these objects must be combined in an ASP page.

First, add the command @ TRANSACTION on the top to declare an ASP page as transactional.

@ TRANSACTION command must be the first line on the first page; otherwise, an error will occur. When ASP Script Processing ends on the page, the current transaction ends.
<% @ TRANSACTION = Required Language =
VB Script %>
// Event triggered by successful transaction execution
<% Sub OnTransactionCommit () %>
<% Response. Redirect RegisterOk.html %>
<% End Sub %>
// Trigger event of failed transaction execution
<% Sub OnTransactionAbort () %>
<% Response. Redirect RegisterFail.html %>
<% End Sub %>
<% SqlText = "Insert into USER (userName, userPasswd) values ('" %>
<% SqlText = sqlText & request ("usrName") & "','" & request ("usrPasswd") & "')" %>
<% Conn.exe cute (sqlText) %>
<% If conn. Errors. Count> 0 then %>
<% Conn. Errors. Clear %>
<% ObjectContext. SetAbort %>
<% End if %>
<% SqlText = "Insert into USERDOC (userName, Age, Sex, PhoneNumber, Address)" %>
<% SqlText = sqlText & "values ('" & request ("usrName") & "'," & request ("Age") %>
<% SqlText = sqlText & ", '" & request ("PhoneNum") & "', '" %>
<% SqlText = sqlText & request ("Address") & "')" %>
<% Conn.exe cute (sqlText) %>
<% If conn. Errors. Count> 0 then %>
<% Conn. Errors. Clear %>
<% ObjectContext. SetAbort %>
<% End if %>
<% ObjectContext. SetComplete %>

COMPARISON OF SOLUTIONS
From a flexible perspective, choosing the ASP database component method has certain advantages: You can use the ADO database component to complete transaction processing, and you can also choose the actual needs, customize your own database components (as long as the ASP Component writing specifications are met ). If you consider the reliability of database transaction processing, it is better to use the internal transaction processing stored procedures of the database. In this way, you can directly use the database transaction mechanism to complete the logical transaction processing of the application, which is secure and reliable, and reduces the Data Interaction Between the Web server and the database server. This is especially important for distributed database systems. The transaction processing method using MTS components has the following advantages: the MTS server directly controls and manages the completion and revocation of operations on the components (the components registered in MTS, it has good expansion space and application prospects. It can give full play to the technical advantages of MTS, enhance the fault tolerance of network applications, and improve the dynamic performance of IIS Web servers.