When developing a web application, there is no exceptionally to access the database to complete the data of the data query, insert, update, delete and other operations. Affected by the application logic, sometimes you need to form a plurality of database operation instructions into a work unit (transaction). In the database, the so-called transaction refers to a set of logical operating units that convert the data from one state to another. To ensure consistency in the database, the data should be operated with discrete group of logic units: When it is all completed, the data consistency can be maintained; and when a part of the unit fails, the entire transaction will be ignored. All the operations from the starting point are returned to the start state.
In fact, each operation of the database is implicitted in the default mode. In this paper, a typical user registration process is an example, introducing three methods that utilize ASP implementation transaction processing: Solution based on ASP database components, a solution based on transaction processing mechanism within the database and a solution based on MTS components.
Program function
Two tables are built in the SQL Server database: the USER table and the USERDOC table. Where the User table is stored is a username and password of the registered user, the user-DOC table is stored in the registered user's personal data, and is indexed in the username. Here are the definitions of Table User and UserDoc:
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 the user requests to register, the ASP script first inserts the username and password into the USER table and inserts user personal information (age, gender, contact phone and home address in the UserDoc table). At the same time, the application must ensure that each record in the USER table has corresponding records in the UserDoc table.
method one
Transactional processing on database operations can be implemented using the Connection object in the ASP built-in ADO components. The part of the Connection object is as follows:
● Connection.Begintrans method: start a transaction;
● Connection.commitTrans Method: Complete / submit a transaction;
● Connection.rollbackTrans method: Undo / abandon one transaction.
// Start a transaction operation
<% Conn.begintrans%>
<% sqltext = "INSERT INTO User (username, userpasswd) VALUES ('"%>
<% sqltext = SqlText & Request ("USRNAME") & "','" "" "" ""%>
<% conn.execute (sqltext)%>
<% if conn.errors.count> 0 THEN%>
<% conn.errors.clear%>
// If the inserted data operation fails, the transaction roll forward forward.
<% conn.rollbacktrans%>
<% response.redirct registerfail.html%>
<% end if%>
<% sqltext = "INTO USERDOC (Username, Age, SEX, Phonenumber, Address"%> <% sqltext = SQLText & "Values (" USRNAME ") &" & Request ("AGE ")%>
<% sqltext = sqltext & "" "& requirements" "" "" ""%>
<% sqltext = SQLText & Request ("Address") & "')"%>
/ / Perform the second insertion statement in the transaction unit
<% conn.execute (sqltext)%>
<% if conn.errors.count> 0 THEN%>
<% conn.errors.clear%>
// If the operation fails, the transaction roll forward forward.
<% conn.rollbacktrans%>
<% response.redirct registerfail.html%>
<% end if%>
// If the entire transaction is executed correctly, the transaction is submitted.
<% Conn.committrans%>
// Turn to register successfully handled page
<% response.redirct registerok.html%>
Method Two
You can use the transaction mechanism within the database system, and the transaction processing for data operation is completed by writing a stored procedure containing transactions in the database server. At the same time, the stored procedure is called by the ADO component, and it is also possible to determine whether the transaction is executed according to the return code of the stored procedure.
In the database system, each SQL statement is a transaction. So you can guarantee that each statement is either completed or returned to the beginning. But if you want a set of SQL statements to be completed, all invalid, you need to use the transaction mechanism for the database.
The main code for generating stored procedures in the database is as follows:
Create Proc RegisterUser (@usrname varchar (30), @usrpasswd varchar (30), @ Age int, @phonenum varchar (20), @address varchar (50)) AS Begin
// Display definition and start a transaction
Begin TRAN
INSERT INTO User (Username, Userpasswd) Values (@ USRNAME, @ usrpasswd)
IF @@ Error <> 0
Begin
// Failure, then transaction rollback
Rollback TRAN
/ / Return the stored procedure and set the return code to fail
Return -1
end
INSERT INTO Userdoc (Username, Age, SEX, Phonenumber, Address)
VALUES (@ usrname, @ agn, @ phoneenum, @ address)
IF @@ Error <> 0
Begin
// Failure, then transaction rollback
Rollback TRAN
Return -1
end
// If the operation is correct, submit a transaction
Commit TRAN
Return 0nd
The main code for calling the data fixture stored procedure in the ASP script is as follows:
<% Set comm = server.createObject
("AdoDb.command")%>
<% Set comm.activeConnection = conn%>
<% Comm.commandtype = adcmdstoredProc%>
<% Comm.commandtext = "registeruser"%>
// Creating a stored procedure Returns 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, adpaMinput)%>
<% 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 ("agn")%>
<% Comm.parameters ("phonenum") = request ("phonenum")%>
<% Comm.parameters ("address") = request ("address")%>
<% Comm.execute%>
<% Retvalue = cint (Comm ("Retcode"))%>
/ / Returns the code according to the data set procedure to determine if the registration is successful
<% if return <0 THEN%>
<% response.Redirect registerfail.html%>
<% ELSE%>
<% response.redirectregisterok.html%>
<% end IF%> method three
When transaction processing is implemented using the transaction process using the MTS (Microsoft Transaction Server) component, it is necessary to note that the transaction under this mechanism cannot span multiple ASP pages. If a transaction requires an object from multiple components, it must The operation of these objects will be combined in an ASP page.
First, you need to add instruction @Transaction on the page to declare a ASP page as transactionality.
The @Transaction directive must be in the first line of a page, otherwise an error will occur. At the end of the ASP script processing, the current transaction ends.
<% @ Transaction = Required Language =
VB script%>
// Transaction execution successfully triggered events
<% Sub OntransActionCommit ()%>
<% response.redirectregisterok.html%>
<% End sub%>
// Transfer failure trigger event
<% Sub OntransactionAbort ()%>
<% response.Redirect registerfail.html%>
<% End sub%>
<% sqltext = "INSERT INTO User (username, userpasswd) VALUES ('"%>
<% sqltext = SqlText & Request ("USRNAME") & "','" "" "" ""%>
<% 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 (" USRNAME ") &" & Request ("age")%>
<% sqltext = sqltext & "" "& requirements" "" "" ""%>
<% sqltext = SQLText & Request ("Address") & "')"%>
<% conn.execute (sqltext)%>
<% if conn.errors.count> 0 THEN%>
<% conn.errors.clear%>
<% ObjectContext.Setabort%>
<% end if%>
<% ObjectContext.setComplete%>
compare plan
