SQL transaction (Transaction) usage introduction and rollback instance

SQL transaction (Transaction) usage introduction and rollback instance

The   transaction (Transaction) is a unit of concurrency control and is a user-defined sequence of operations. These operations are either done or not, and are an inseparable unit of work. With transactions, SQL Server can bind a logically related set of operations so that the server maintains the integrity of the data   a failure occurs when multiple tables are updated. In order to maintain the integrity of the data, transaction rollback is required.   Display setup transactions   code begin try         Begin transaction                 INSERT INTO Shiwu (ASD) VALUES (' Aasdasda ');        commit transaction end Try begin catch               Select Error_number () as errornumber         rollback Transaction end Catch implicitly set transaction   code as follows set Implicit_transactions On --Start an implicit transaction  go begin try          INSERT INTO Shiwu (ASD) values (' AASDASDA ');         insert into Shiwu (ASD) VALUES (' AASDASDA ');  Commit Transaction; end Try begin CAtch          Select Error_number () as Errornumber rollback Transaction --ROLLBACK TRANSACTION  end Catch set implicit_transactions off; --Close implicit transaction  go display transaction The following statement cannot be used, an implicit transaction can be   code as follows alter Database; backup;  create Database; drop database; reconfigure; restore; update statistics; show transactions can be nested using the   code below--Create stored procedures  create Procedure qiantaoproc  @asd nchar (TEN)  as begin begin try begin transaction innertrans  Save Transaction SavePoint--Create transaction savepoint  insert into Shiwu (ASD) values (@asd);  commit transaction Innertrans  end try begin catch rollback Transaction SavePoint--rollback to save point  commit transaction innertrans  End Catch end go begin transaction outrans exec Qiantaoproc ' ASDASD ';  rollback transaction Outrans a transaction is nested, an exception occurs if a nested transaction has been rolled back when the outer transaction is rolled back. You need to use a transaction savepoint at this point. The following instance of SQL transaction rollback specifies when the   Transact-SQL   statement generates a run-time error,microsoft&reg;   SQL   server& #8482;   Whether to automatically rollback the current transaction scenario a:  code as follows set   Xact_abort   on--If an error is generated automatically gobegin   Traninsert   into   A & nbsp Values   (4) INSERT   into   B   VALUES   (5) COMMIT   tran   can also use the _connectionptr object's Methods: BeginTrans, CommitTrans, RollbackTrans, use this series function to judge and rollback. Once the BeginTrans method is called, the database will no longer commit any changes immediately before calling CommitTrans or RollbackTrans to end the transaction. Scenario two   code as follows begin Transactioninsert into A   values   (4)  -----The table contains triggers, update other tables if @ @error <> 0 &N bsp;--error    BEGIN     rollback transaction             endelse   BEGIN     commit transaction          END  SQL transaction combined with ASP. Two usage in the SQL server+. NET development environment, there are two ways to perform transaction operations and maintain database integrity; one is to use sqlserver/42850.htm Target=_blank > SQL stored procedure, and the other is a simple transaction in ADO; now, with a typical example of bank transfers, let's take a look at the usage of these two examples to see how the SQL stored procedure is doing the operation of the transaction: first create a table:  The code below CREATE database AAAA--Creates a table that contains theGouse aaaacreate table BB (ID int NOT null primary key,  --account moneys money    --transfer amount) insert INTO BB VALUES (' 1 ', ' 2000 ')--insert two data inserts into BB values (' 2 ', ' 3000 ') create a stored procedure with this table: CREATE PROCEDURE Mon-Creates a stored procedure, defines several variables @toid int ,    --account @fromid int,  --transfer out of his account @momeys money--The amount of the transfer Asbegin Tran--Start execution transaction  update BB Set [email& Nbsp;protected] WHERE [email protected]-Performs the first operation, transfers money, minus the amount of the transferred out update BB set [email protected] where [email& Nbsp;protected]--Perform a second operation, accept the amount of the transfer, increase     if @ @error <>0--to determine if any of the two statements has an error begin rollback Tran– begins the rollback of a transaction, the recovery of the transfer before the start of the status return 0endgo else  --How to execute a successful begin COMMIT Tran execute this transaction return 1endgo  Next look at how c#.net calls this stored procedure:     code as follows protected void Button1_Click (object sender, EventArgs e)     {         SqlConnection con =new SqlConnection (@ "Data source=. Sqlexpress;database=aaaa;uid=sa;pwd=jcx "); Connection string         SqlCommand cmD = new SqlCommand ("mon", con); Call the stored procedure         Cmd.commandtype = commandtype.storedprocedure;        con. Open ();        SqlParameter prar = new SqlParameter ();//Pass parameters         CMD. Parameters.addwithvalue ("@fromID", 1);        CMD. Parameters.addwithvalue ("@toID", 2);        CMD. Parameters.addwithvalue ("@momeys", Convert.ToInt32 (TextBox1.Text));   www.2cto.com          CMD. Parameters.Add ("@return", ""). Direction = parameterdirection.returnvalue;//Gets the return value of the stored procedure         CMD. ExecuteNonQuery ();        String value = cmd. parameters["@return"]. Value.tostring ();//assigns the return value to value        if (value = = "1")         {    &NB Sp       Label1.Text = "Add Success";       }        else    &NBSP ;   {  &nbsP         Label1.Text = "Add Failed";       } This is the addition of transactions in the stored procedure, and then to see not the database write SQL stored procedures, ADO. NET is the:  code for how transactions are handled protected void button2_click (object sender, EventArgs e)     {        SqlConnection con = new SqlConnection (@ "Data source=. Sqlexpress;database=aaaa;uid=sa;pwd=jcx ");        con. Open ();        sqltransaction Tran = con. BeginTransaction ();//First Instance SqlTransaction class, use this transaction is con this connection, use BeginTransaction this method to start executing this transaction         SqlCommand cmd = new SqlCommand ();        CMD. Connection = con;        CMD. Transaction = tran;        try        {            & nbsp;//Execute SqlCommand command in try{} block,            CMD. CommandText = "Update bb set moneys=moneys-" "+ Convert.ToInt32 (TextBox1.Text) +" ' Where id= ' 1 ' ";      &NB Sp    Cmd. ExecuteNonQuery ();            CMD. CommandText = "Update bb set moneys=moneys+ ' AA ' where id= ' 2 '";            CMD. ExecuteNonQuery ();            Tran. Commit ();//If all two SQL commands execute successfully, execute this method of commit, perform these actions    www.2cto.com              Label1.Text = "Add Success";       }        catch        {&NB Sp           Label1.Text = "Add Failure";            Tran. Rollback ();//How to perform unsuccessful, exception occurs, execute Rollback method, rollback to start of transaction operation;       }    } This is a simple example of how two transactions are used in different ways, and the ADO method looks simple, but he wants to use the same connection to perform these operations, which is cumbersome if you use a few databases to execute with a single transaction, but it's relatively straightforward to use SQL stored procedures.

SQL Transaction (Transaction) usage introduction and rollback instance