SQL Transaction usage and rollback instance

When updating multiple tables, an execution fails. Transaction rollback is required to maintain data integrity.
Display set transactions

The code is as follows:	Copy 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 transactions

The code is as follows:	Copy code
Set implicit_transactions on; -- start implicit transactions 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; -- roll back the transaction End catch Set implicit_transactions off; -- disable implicit transactions Go

Show that the following statements of the transaction are not available, and the implicit transaction can

The code is as follows:	Copy code
Alter database; Backup; Create database; Drop database; Reconfigure; Restore; Update statistics;

Show that transactions can be nested

The code is as follows:

Copy code

-- Create a stored procedure Create procedure qiantaoProc @ Asd nchar (10) As Begin Begin try Begin transaction innerTrans Save transaction savepoint -- create a transaction save point Insert into shiwu (asd) values (@ asd ); Commit transaction innerTrans End try Begin catch Rollback transaction savepoint -- roll back to the Save Point Commit transaction innerTrans End catch End Go Begin transaction outrans Exec qiantaoProc 'asdasd '; Rollback transaction outrans

Transaction nesting. When an outer transaction is rolled back, an exception occurs if the nested transaction has been rolled back. In this case, you need to use the transaction storage point. Example

SQL transaction rollback

Specifies whether Microsoft & reg; SQL Server & #8482; automatically rolls back the current transaction when a running error occurs in a Transact-SQL statement.

Solution 1:

The code is as follows:	Copy code
SET XACT_ABORT ON -- Automatic rollback if an error occurs GO BEGIN TRAN Insert into a values (4) Insert into B VALUES (5) COMMIT TRAN

You can also use the _ ConnectionPtr object method: BeginTrans, CommitTrans, and RollbackTrans. Use this series of functions to judge and roll back. Once the BeginTrans method is called, the database will no longer commit any changes made immediately before calling CommitTrans or RollbackTrans to end the transaction.
Solution 2

The code is as follows:	Copy code
BEGIN TRANSACTION Insert into a values (4) ----- This table contains triggers and updates other tables IF @ error <> 0 -- an error occurs. BEGIN ROLLBACK TRANSACTION          END ELSE BEGIN COMMIT TRANSACTION        END

SQL transactions combined with asp.net

In SQL server +. in the. net development environment, there are two ways to complete transaction operations to maintain the data integrity of the database; one is to use sqlserver/42850.htm target = _ blank> SQL stored procedures, the other is in ADO. NET is a simple transaction processing. Now we use a typical bank transfer example to illustrate the usage of the two examples. Let's first look at how the SQL Stored procedure completes the transaction operation: first, create a table:

The code is as follows:

Copy code

Create database aaaa -- create a table that contains the user's account and money count gouse aaaacreate table bb (ID int not null primary key, -- account moneys money -- transfer amount) insert into bb values ('1', '20140901') -- insert two pieces of data into bb values ('2', '20160901') to create a stored procedure using this table: Create procedure mon -- create a stored procedure and define several variables @ ToID int, -- the account that receives the transfer @ FromID int, -- transfer out your account @ Momeys money -- transfer amount As Begin tran -- start transaction execution   Update bb set moneys = moneys-@ momeys where ID = @ fromID-the first operation to be executed. The money is transferred, minus the amount transferred. Update bb set moneys = moneys + @ momeys where ID = @ toID -- perform the second operation, accept the transfer amount, and increase   If @ error <> 0 -- determines if either of the two statements has an error Begin rollback tran-start to execute the transaction rollback, before the recovery transfer starts Return 0 End Go   Else -- how to successfully execute both Begin commit tran Return 1 End Go

 

Next let's take a look at how C #. net calls this stored procedure:

   

The code is as follows:

Copy code

Protected void button#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 the parameter Cmd. Parameters. AddWithValue ("@ fromID", 1 ); Cmd. Parameters. AddWithValue ("@ toID", 2 ); Cmd. Parameters. AddWithValue ("@ momeys", Convert. ToInt32 (TextBox1.Text ));   Cmd. Parameters. Add ("@ return", ""). Direction = ParameterDirection. ReturnValue; // obtain the return value of the stored procedure Cmd. ExecuteNonQuery (); String value = cmd. Parameters ["@ return"]. Value. ToString (); // value the returned value to the Value. If (value = "1 ")         { Label1.Text = "added successfully ";         } Else         { Label1.Text = "failed to add ";        } }

This is to add a transaction to the stored procedure. Let's take a look at how ADO. NET handles the transaction instead of writing the SQL Stored Procedure to the database:

The code is as follows:

Copy code

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 the SqlTransaction class. This transaction uses the con connection and the BeginTransaction method to start executing this transaction. SqlCommand cmd = new SqlCommand (); Cmd. Connection = con; Cmd. Transaction = tran; Try         { // Execute the sqlcommand in the try {} block, Cmd. CommandText = "update bb set moneys = moneys-'" + Convert. ToInt32 (TextBox1.Text) + "'Where ID = '1 '"; Cmd. ExecuteNonQuery (); Cmd. CommandText = "update bb set moneys = moneys + 'A' where ID = '2 '"; Cmd. ExecuteNonQuery (); Tran. Commit (); // if both SQL commands are successfully executed, execute the commit method to perform these operations.   Label1.Text = "added successfully ";         } Catch         { Label1.Text = "failed to add "; Tran. Rollback (); // if an exception occurs, execute the rollback method and roll back to the beginning of the transaction operation;         }       }

This is a simple example of different transaction usage, ADO. the NET transaction processing method looks simple, but he wants to use the same connection to execute these operations. If he wants to use several databases to execute these operations with one transaction at the same time, it will be complicated, however, it is relatively simple to use SQL Stored procedures.