Creating a Transaction


Creating a Transaction

The two types of transactions are implicit and explicit. Each SQL statement runs in its own implicit transaction. This means if you don't explicitly create a transaction, a transaction is implicitly created for you on a statement-by-statement basis. This ensures that a SQL statement that updates many rows is either completed as a unit or rolled back.

Creating a Transaction Using T-SQL

An explicit transaction is one that you create in your program. You can explicitly create a transaction in T-SQL by using the following script.

image from book

SQL: Explicit Transaction

image from book

The SQL TRY/CATCH block is used to catch any errors and roll back the transaction. This code sets XACT_ABORT to on, which ensures that all errors under severity level 21 are handled as transaction abort errors. Severity level 21 and higher is considered fatal and stops code execution, which also rolls back the transaction.

The scope of the transaction is limited to the statements in the TRY block, which can include calls to other stored procedures.

Creating a Transaction Using the ADO.NET DbTransaction Object

Another way to create an explicit transaction is to put the transaction logic in your .NET code. The DbConnection object has the BeginTransaction method, which creates a DbTransaction object. The following code snippet shows how this is done.

image from book

Visual Basic

Private Sub Button1_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles Button1.Click    Dim cnSetting As ConnectionStringSettings = _       ConfigurationManager.ConnectionStrings("NorthwindString")    Using cn As New SqlConnection()       cn.ConnectionString = cnSetting.ConnectionString       cn.Open()       Using tran As SqlTransaction = cn.BeginTransaction()          Try             'work code here             Using cmd As SqlCommand = cn.CreateCommand()                cmd.Transaction = tran                cmd.CommandText = "SELECT count(*) FROM employees"                Dim count As Integer = CInt(cmd.ExecuteScalar())                MessageBox.Show(count.ToString())             End Using             'if we made it this far, commit             tran.Commit()          Catch xcp As Exception             tran.Rollback()             'cleanup code             MessageBox.Show(xcp.Message)          End Try       End Using    End Using End Sub 
image from book

image from book


private void button1_Click(object sender, EventArgs e) {    ConnectionStringSettings cnSetting =       ConfigurationManager.ConnectionStrings["NorthwindString"];    using (SqlConnection cn = new SqlConnection())    {       cn.ConnectionString = cnSetting.ConnectionString;       cn.Open();       using (SqlTransaction tran = cn.BeginTransaction())       {          try          {             //work code here             using (SqlCommand cmd = cn.CreateCommand())             {                cmd.Transaction = tran;                cmd.CommandText = "SELECT count(*) FROM employees";                int count = (int)cmd.ExecuteScalar();                MessageBox.Show(count.ToString());             }             //if we made it this far, commit             tran.Commit();          }          catch (Exception xcp)          {             tran.Rollback();             //cleanup code             MessageBox.Show(xcp.Message);          }       }    } } 
image from book

In this code, a SqlConnection object is created and opened, and then the connection object is used to create a transaction object by executing the BeginTransaction method. The try block does the work and commits the transaction. If an exception is thrown, the catch block rolls back the transaction. Also notice that the SqlCommand object must have its Transaction property assigned to the connection's transaction.

The scope of the transaction is limited to the code within the try block, but the transaction was created by a specific connection object, so the transaction cannot span to a different connection object.


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON © 2008-2017.
If you may any questions please contact us: