SQL transaction (Transaction) usage Introduction and rollback instance _ goto

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. The display set transaction code follows begin try   Www.2cto.com begin Transactioninsert into Shiwu (ASD) VALUES (' AASDASDA '); commit Transactionend trybegin Catchselect error_number () as Errornumberrollback transactionend catch implicitly set transaction code as follows set Implicit_ Transactions on; --Start the implicit transaction gobegin Tryinsert into Shiwu (ASD) VALUES (' AASDASDA '), insert into Shiwu (ASD) VALUES (' AASDASDA '); commit Transac Tion;end trybegin Catchselect error_number () as Errornumberrollback transaction; --ROLLBACK TRANSACTION end Catchset implicit_transactions off; --close implicit transaction go Show transaction The following statement cannot be used, and an implicit transaction can be code as follows alter database;backup;   Www.2cto.com create Database;drop database;reconfigure;restore;update statistics; show transactions can be nested using code as follows--Create stored procedure creation procedure QIANTAOPROC@ASD nchar (10) Asbeginbegin trybegin Transaction Innertranssave Transaction SavePoint--Create a transaction save point insert INTO Shiwu (ASD) values (@asd); cOmmit transaction innertransend trybegin catchrollback transaction SavePoint--rollback to save point commit transaction Innertransend Catchendgobegin transaction outransexec qiantaoproc ' ASDASD '; rollback transaction Outrans transaction nesting, when the outer transaction is rolled back, An exception occurs if a transaction within the nested has been rolled back. You need to use a transaction savepoint at this point. The following instance of SQL transaction rollback specifies that when the    transact-sql   statement produces a run-time error,microsoft&reg;   sql   server& #8482 Whether;   automatically rolls back the current transaction scenario one: The code is as follows set   xact_abort   on--If an error is generated automatically rollback Gobegin    traninsert   into   a   values   (4) insert   into    b   values   (5) commit   tran  www.2cto.com  can also use _ Methods for Connectionptr objects: BeginTrans, CommitTrans, RollbackTrans, using this series of functions to determine 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 is as follows begin Transactioninsert into a   values  (4)   -----This table contains triggers, update other tables if @ @error < > 0 -Error occurred    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 how the SQL stored procedure accomplishes the operation of the transaction: first create a table: code as follows Database AAAA--Create a table containing the user's account number and money gouse aaaacreate table BB (ID int NOT null primary key, --account Moneys money &nbsp ; --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--Create a stored procedure, define several variables @toid int,   --The account that receives the transfer @fromid int, --turns out his account @momeys money--The amount of the transfer Asbegin Tran --Start execution transaction  update BB set [email protected] where [email protected]-Perform the first operation, transfer money, minus the amount of the transferred out update BB set [ Email protected] WHERE [email protected]-Perform the second operation, accept the amount of the transfer, increase   www.2cto.com  if @ @error <>0--judging if any one of the two statements has an error begin rollback tran– start the rollback of the transaction, the resumed transfer starts before the status return 0endgo  else  --How to perform a successful begin commit Tran perform the operation of this transaction return 1endgo  take a 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 ();//Transfer 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")          {            Label1.Text = "add Success";        }        else         {            Label1.Text = "Add Failed";       } This is to add a transaction in the stored procedure, and then to look at the database is not written SQL stored procedures, ADO. NET is how to handle transactions: The code is as follows 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        {             //Execute SqlCommand command in try{} block,             cmd. CommandText = "Update bb set moneys=moneys-'" + convert.toint32 (TextBox1.Text) + "' Where id= ' 1 '";    &nbsp ;        cmd. ExecuteNonQuery ();             cmd. CommandText = "Update bb set moneys=moneys+ ' AA ' where id= ' 2 '";          & nbsp;  cmd. ExecuteNonQuery ();            Tran. Commit ();//If all two SQL commands execute successfully, execute this method of commit, perform these operations    www.2cto.com              Label1.Text = "Add Success";       }         catch        {             Label1.Text = "Add Failed";             Tran. Rollback ();//How to perform unsuccessful, an exception occurs, the Rollback method is executed, and the rollback to the start of the transaction operation;       }     }