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. The program feature creates two tables in the SQL Server database: USER Table and 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 is the definition of Table User and UserDoc: Create Table User (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 will first insert the username and password into the USER table and then insert 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. Methods A transactional process for database operations can be implemented using the Connection object in the ASP built-in ADO component. 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 a transaction.
// Start a transaction operation <% conn.begintrans%> <% sqltext = "INTO User (username, userpasswd) Values ('"%> <% sqltext = SQLText & Request ("USRNAME") & "', '" & Request ("USRPASSWD") & "')"%> <% conn.execute (sqltext)%> <% if conn.errors.count> 0 THEN%> <% conn.errors.clear%> // If inserted The operation failed, the transaction rolls forward to <% 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 "&") "%> // executes 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 roll forward <% conn.rollbackTrans%> <% response.redirct registerfail.html% > <% End if%> // If the entire transaction is performed correctly, submit a transaction <% conn.committrans%> // Steering Registration Success Process Page <% response.redirct registerok.html%> Method 2 can be used inside the database system The transaction mechanism, complete transaction processing for data operations 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 to generate the stored procedure 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 // operation failed, then transaction rollback rollback TRAN // Returns the stored procedure, And set the return code for transaction failure Return -1 End Insert Into Userdoc (Username, Age, SEX, Phonenumber, Address) Values (@ usrname, @ agn, @ phoneenum, @ address) if @@ error <> 0 begin //// The operation failed, the transaction rollback rollback trrough roll -1 end // If the operation is executed correctly, the main code of the Submit transaction Commit TRAN RETURN 0 END in the ASP script is as follows: <% SET Comm = Server.createObject ( "AdoDb.command")%> <% set comm.activeconnection = conn%> <% comm.commandtype = adcmdstoredProc%> <% comm.comMandText = "registerUser"%> // Create a stored procedure Return parameter object <% set retcoDe = Comm.CreateParameter ("Retcode", Adineger, AdParamReturnValue%> // Create a stored procedure input parameter object <% set usrname = comm.createParameter ("USRNAME", Advarchar, Adparaminput, 30)%> <% set usrpwd = communication .CreateParameter ("USRPASSWD", Advarchar, Adpaaminput, 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%> <% return = cint (Comm ("Retcode")%> // Returns the code according to the data stock procedure to determine if the registration is successful <% If return.Redirect registerfail.html%> <% else%> <% response.redirect registerok.html%> <% END IF%> Method three utilizes MTS (Microsoft Transaction Server) component When the transaction mechanism is implemented, it is necessary to pay special attention to the transaction, the transaction under this mechanism cannot span multiple ASP pages, and if a transaction requires an object from multiple components, the operation of these objects must be combined A 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 Trigger Events <% Sub OntraSactionCommit ()%> <% Response.Redirect Registerok.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 conve.errors.count> 0 THEN%> <% conn . Errors.clexT.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%> The program is compared to a flexible perspective, selecting the method of using the ASP database component with a certain advantage: either use the ADO database component to complete the transaction It is also possible to customize your own database components according to actual needs (as long as the ASP component is written). If you consider from the perspective of the reliability of the database transaction, the transaction stored procedure in the database is better.
