Using ASP to implement transaction processing methods--application techniques

The method of realizing transaction processing by using ASP Select the Blog from Applebbs
The method of using ASP to implement transaction processing for keywords
Source

When developing Web applications, there is no exception to the need to access the database in order to complete the query, insert, UPDATE, delete and other operations of the data. Influenced by the application logic, it is sometimes necessary to compose multiple database operation instructions into a unit of work (transaction). In a database, a transaction is a set of logical units of operations that transform data from one state to another. To ensure consistency of data in the database, data should be manipulated using discrete groups of logical units: when it is all complete, the consistency of the data can be maintained, and when a part of the unit fails, the entire transaction is ignored and all operations from the start point back to the start state.

In fact, each operation of the database in the default mode is an implied transaction. Taking a typical user registration procedure as an example, this paper introduces three methods of transaction processing using ASP: solution based on ASP database component, solution based on transaction processing mechanism within database and solution based on MTS component.

Program features
Create two tables in the SQL Server database: The user table and the Userdoc table. The user table contains the username and password of the registered user, and the Userdoc table contains the personal data of the registered user and is indexed by the user name. The following is the definition of table user and Userdoc:
Create Table USER (userName varchar, userpasswd varchar (30))
Create Table Userdoc (userName varchar), age int,sex int,phonenumber varchar (?), address varchar (50))

When the user requests registration, the ASP script inserts the username and password into the user table, and then inserts the user's personal information (age, sex, telephone number, home address, etc.) in the Userdoc table. At the same time, the application must also ensure that each record in the user table has a corresponding record in the Userdoc table.

Method One
Using the Connection object in the ASP built-in ADO component can realize the transactional processing to the database operation. Some of the methods for connection objects are as follows:
Connection.begintrans method: Start a transaction;
Connection.committrans method: Complete/submit 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.execute (sqltext)%>
<% if Conn. Errors.count>0 then%>
<% Conn. Errors.clear%>
If the insert data operation fails, the transaction rolls forward
<% Conn. RollbackTrans%>
<% Response. REDIRCT 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.execute (sqltext)%>
<% if Conn. Errors.count>0 then%>
<% Conn. Errors.clear%>
If the operation fails, the transaction rolls forward
<% Conn. RollbackTrans%>
<% Response. REDIRCT registerfail.html%>
<% End If%>
Commits the transaction if the entire transaction operation is performed correctly
<% Conn.committrans%>
Turn to register successful processing page
<% Response. REDIRCT registerok.html%>


Method Two
The transaction processing mechanism within the database system can be used to complete the transaction of data operations by writing stored procedures containing transactions in the database server. At the same time, using the ADO component to invoke the stored procedure, you can also judge whether the transaction is executed successfully according to the return code of the stored procedure.

In a database system, each SQL statement is a transaction. It is therefore possible to ensure that each statement is either completed or returned to the starting point. However, if you want a set of SQL statements to be complete or all invalid, you need to use the transaction processing mechanism of the database to implement.

The main code for generating stored procedures in a database is as follows:
Create proc RegisterUser (@usrName varchar (), @usrPasswd varchar (@age int, @PhoneNum varchar (), @Address varchar ( ) as Begin
Show definition and start a transaction
BEGIN Tran
Insert into USER (USERNAME,USERPASSWD) VALUES (@usrName, @usrPasswd)
If @ @error <>0
Begin
Operation failed, transaction rollback
Rollback Tran
Returns the stored procedure and sets the return code to fail the transaction operation
Return-1
End
Insert into Userdoc (username,age,sex,phonenumber,address)
VALUES (@Usrname, @age, @PhoneNum, @Address)
If @ @error <>0
Begin
Operation failed, transaction rollback
Rollback Tran
Return-1
End
Commits the transaction if the operation is performed correctly
Commit Tran
return 0
End
The main code to invoke a database stored procedure in an ASP script is as follows:
<% Set Comm=server. CreateObject
("Adodb.command")%>
<% Set Comm.activeconnection=conn%>
<% Comm.commandtype=adcmdstoredproc%>
<% comm.commandtext= "RegisterUser"%>
To create a stored procedure to return a 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,%>)
<% 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"))%>
Determine if registration is successful based on database stored procedure return code
<% if retvalue< 0 then%>
<% Response. Redirect registerfail.html%>
<% Else%>
<% Response. Redirect registerok.html%>
<% End If%>


Method Three
When transactions are implemented using the transaction processing mechanism of the MTS (Microsoft Transaction Server) component, it is particularly noteworthy that transactions under this mechanism cannot span multiple ASP pages if a transaction requires objects from multiple components, You must combine operations on these objects in an ASP page.

First you need to add the instruction @transaction to the top of the page to declare an ASP page as transactional.

@TRANSACTION instruction must be on the first line of a page, or an error will occur. The current transaction ends when the ASP script processing at the end of the page.
<%@ transaction=required language=
VB Script%>
Transaction execution successful trigger event
<% Sub OnTransactionCommit ()%>
<% Response. Redirect registerok.html%>
<% End Sub%>
Things fail to trigger events
<% Sub OnTransactionAbort ()%>
<% Response. Redirect registerfail.html%>
<% End Sub%>
<% sqltext= "Insert into USER (USERNAME,USERPASSWD) VALUES ('"%>
<% Sqltext=sqltext & Request ("Usrname") & "', '" &request ("usrpasswd") & "')"%>
<% Conn.execute (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.execute (sqltext)%>
<% if Conn. Errors.count>0 then%>
<% Conn. Errors.clear%>
<% ObjectContext.SetAbort%>
<% End If%>
<% ObjectContext.SetComplete%>


Scheme comparison
From a flexible point of view, the choice of ASP database components of the method has a certain advantage: not only the use of ADO database components to complete the transaction, but also in accordance with the actual needs, customization of their own database components (as long as the ASP components to meet the code can be written). If you consider the reliability of database transaction processing, it is better to adopt transaction processing stored procedures inside the database. This can directly use the database transaction mechanism to complete the application's logical transaction processing, secure and reliable, and reduce the data interaction between the Web server and the database server. This is particularly important for distributed database systems. The advantage of the transaction processing method of MTS is that the operation of the component (which is registered in MTS) is completed and undone by the MTS server, which has good expansion space and application foreground, can give full play to the technical advantages of MTS, enhances the fault tolerant performance of the network application, and enhances the IIS Dynamic performance of the Web server.