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++)
{
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();
}
VB.NET
Dim tn as
cn.BeginTransaction
Try
da.Update
tn.Commit
Catch ex as System.Exception
tn.Rollback
End Try