Working with Transactions

When we first started working with transactions, we ran into a few steps that we needed to incorporate into our code to get a transaction to run. There are a few steps that you need to initiate to process an insert, update, or delete in a transaction. Here are the steps to complete a successful transaction:

  1. The first thing you will come across is the SqlTransaction class. You can get this only by calling the BeginTransaction method on the Connection object.

  2. The connection object passes back a SqlTransaction object with all the necessary information to complete your transaction.

  3. Now you can process your database update just as you would normally do.

  4. Finally, when you have completed the database operation, you can call commit, or, if there is a problem, you can roll back the transaction. These points are illustrated in Listings 16.6 and 16.7.

Listing 16.6. Transactions Example (C#)
SqlConnection myConnection = new SqlConnection("Data Source=localhost;initial
 catalog=Pubs;persist security info=False;user id=sa;workstation id=DIGITAL-LAPTOP;packet
 size=4096"); 
     myConnection.Open(); 

     SqlCommand myCommand = new SqlCommand(); 

     SqlTransaction myTrans; 
     myCommand.Connection = myConnection; 

     // Start the transaction 
myTrans = 
myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"JobTransaction"); 
     // Assign transaction object for a pending local transaction 
     myCommand. Transaction = myTrans; 

     try 
     {
     myCommand.CommandText = "SET IDENTITY_INSERT Jobs ON "; 
     myCommand.ExecuteNonQuery(); 

     myCommand.CommandText = "Insert into Jobs (Job_id, Job_Desc) VALUES (1000, 'MS.NET
 Programmer')"; 
     myCommand.ExecuteNonQuery(); 

     myCommand.CommandText = "Insert into Jobs (Job_id, Job_Desc) VALUES (1001, 'QA
 Tester')"; 
     myCommand.ExecuteNonQuery(); 
     myTrans.Commit(); 

     Response.Write("The Transaction completed successfully."); 
     } 
     catch(Exception ex) 
     {
     myTrans.Rollback("JobTransaction"); 
     Response.Write("There was an error during the Insert process.<br>"); 
     Response.Write(ex. ToString()); 
     } 
     finally 
     {
     myConnection.Close(); 
     }

Listing 16.7. transactions example (Visual Basic .NET)
Dim myConnection = new SqlConnection("Data Source=localhost;initial catalog=Pubs;persist
 security info=False;user id=sa;workstation id=DIGITAL-LAPTOP;packet size=4096") 

myConnection.Open() 

Dim myCommand = new SqlCommand() 

Dim myTrans as SqlTransaction 
myCommand.Connection = myConnection 

       ' Start the transaction 
myTrans = 
myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"JobTransaction") 
       ' Assign transaction object for a pending local transaction 
myCommand. Transaction = myTrans 


       try 
             myCommand.CommandText = "SET IDENTITY_INSERT Jobs ON " 
             myCommand.ExecuteNonQuery() 

             myCommand.CommandText = "Insert into Jobs (Job_id, Job_Desc) 
VALUES (1000, 'MS.NET Programmer')" 

             myCommand.ExecuteNonQuery() 
             myCommand.CommandText = "Insert into Jobs (Job_id, Job_Desc) 
VALUES (1001, 'QA Tester')" 

             myCommand.ExecuteNonQuery() 
             myTrans.Commit() 
             Response.Write("The Transaction completed successfully.") 

       Catch Ex as Exception 

       myTrans.Rollback("JobTransaction") 
       Response.Write("There was an error during the Insert process.<br>") 
       Response.Write(ex. ToString()) 

       finally 

             myConnection.Close() 
       end try

You might notice a few things in these examples. First, you might be wondering about the statement "SET IDENTITY_INSERT Jobs ON". We did this because the job_id field is an Identity field and because we already have a unique identity; we needed to make sure that the unique identity stayed in sync throughout the database. We must use the "SET IDENTITY_INSERT" statement to accomplish this; otherwise, it will throw an exception.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset