Doing Transactions in C# and ADO.NET Easier than you thought by William Ryan

You can't do much in database programming without employing transactions.  When multiple things need to happen together or not happen at all,  you pretty much have to employ transaction processing.  Writing client side code on your own to simulate transactions, without using native Database support would be almost impossible (at best it would be miserable).  Like many other features that commercial RDBMS systems provide, transactions are something that you don't want to build yourself.  Fortunately for programmers, ADO.NET makes transaction processing a dream.

In this example, I have a table called Employees1 and I have a field called EmpID.  I'm going to iteratively place a value, then 5 incremental values, then one additional value into the table.  Now, if the table is empty, or no EmpID's exist in the range or 314-320 or 325, everything will insert fine.  If I have an existing value in that range, I'll raise an exception.  When I raise the exception, I'll manually fail my assertion so I know what happened, then roll back the entire transaction.  Perhaps there are times when you would just want to go ahead and keep processing regardless of if you are violating a key constraint of the like. If that was the case, my code would be much different and I'd probably want to wrap each Command.ExecuteNonQuery .  But for cases where you want all or nothing..this approach is simple and powerful and easy to implement.  I've commented my code as I go through it so it should be clear what I'm doing.

Enjoy!

SqlTransaction tn ;  //declare a transaction const string sql = "INSERT INTO Employees1(EmpID) VALUES (@UserID)"; SqlConnection cn = new SqlConnection("data source=AUG-SQLSRV;initial catalog=HumanResources;integrated security=SSPI");          try{if(cn.State != ConnectionState.Open){cn.Open();}} //If we throw an exception on Open, which is a 'risky' operation //manually make the assertino fail by setting it to false and use   //ex.ToString() to get the information about the exception. catch (SqlException ex){Debug.Assert(false, ex.ToString());}   //Instantiate command with CommandText and Connection and t       //transaction    tn = cn.BeginTransaction();     SqlCommand cmd = new SqlCommand(sql, cn,tn);     cmd.Parameters.Clear();     cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = 314;    try   {                //You can test for records affected, in this case we know it   //would be at most one record.      int i = cmd.ExecuteNonQuery();    //If successful, commit the transaction   //Loop 5 times and just add the id's incremented each time      for(int x=0; x<5; x++)<BR>       {           cmd.Parameters["@UserID"].Value = (315 + x);           cmd.ExecuteNonQuery();       }        cmd.Parameters["@UserID"].Value = (325);        cmd.ExecuteNonQuery();        tn.Commit();    }       catch(SqlException ex){           Debug.Assert(false, ex.ToString()); //If it failed for whatever reason, rollback the //transaction          tn.Rollback(); //No need to throw because we are at a top level call and //nothing is handling exceptions      }       finally{          //Check for close and respond accordingly         if(cn.State != ConnectionState.Closed){cn.Close();}          //Clean up my mess          cn.Dispose();           cmd.Dispose();           tn.Dispose();      }