transaction

1.example 1

SqlTransaction tn ;  //declare a transaction
const string sql = "INSERT INTO Employees1(EmpID) VALUES (@UserID)";SqlConnection cn = new SqlConnection
("data source=AUG-SQLSRV;initialcatalog=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();
     }

2.Example 2

 private static void Demo1()
   {
      SqlConnection db = new SqlConnection("connstringhere");
      SqlTransaction transaction;

      db.Open();
      transaction = db.BeginTransaction();
      try
      {
         new SqlCommand("INSERT INTO TransactionDemo (Text) VALUES ('Row1');", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO TransactionDemo (Text) VALUES ('Row2');", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO CrashMeNow VALUES ('Die', 'Die', 'Die');", db, transaction)
            .ExecuteNonQuery();
         transaction.Commit();
      }
      catch (SqlException sqlError)
      {
         transaction.Rollback();
      }
      db.Close();
   }

3.Example 3

SqlConnection myConnection = new SqlConnection("...");
myConnection.Open();

// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.Transaction = myTrans;

try
{
  myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
  myCommand.ExecuteNonQuery();
  myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
  myCommand.ExecuteNonQuery();
  myTrans.Commit();
  Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
  try
  {
    myTrans.Rollback();
  }
  catch (SqlException ex)
  {
    if (myTrans.Connection != null)
    {
      Console.WriteLine("An exception of type " + ex.GetType() +
                        " was encountered while attempting to roll back the transaction.");
    }
  }

  Console.WriteLine("An exception of type " + e.GetType() +
                    "was encountered while inserting the data.");
  Console.WriteLine("Neither record was written to database.");
}
finally
{
  myConnection.Close();
}