[翻譯]如何在C#和ado.net中使用事務

一般情況

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();
     }

在SQLhelper中使用transaction

public static DataSet GetICContact(string ICID)
        ...{
            SqlConnection conn = GetConnection();
            SqlTransaction tn=null ;
            try
            ...{
                if (conn.State!=ConnectionState.Open)
                ...{
                    conn.Open();
                }
                tn = conn.BeginTransaction();
                SqlParameter[] arParams = new SqlParameter[1];

                if (System.Configuration.ConfigurationSettings.AppSettings["CacheMSSQLParameters"].ToLower() == "true")
                ...{
                    arParams = SqlHelperParameterCache.GetSpParameterSet(GetConnectionString(),
                        "usp_IC_Contact");

                    arParams[0].Value = ICID;
                }
                else
                ...{
                    arParams[0] = new SqlParameter("@ICID", SqlDbType.NVarChar, 50);
                    arParams[0].Direction = ParameterDirection.Input;
                    arParams[0].Value = ICID;
                }

            
                DataSet ds = SqlHelper.ExecuteDataset(tn, CommandType.StoredProcedure, "usp_IC_Contact", arParams);
                tn.Commit();
                return ds;
            }
            catch (System.Exception e)
            ...{
                tn.Rollback();
                return null;    
            }
            finally
            ...{    
                if(conn.State != ConnectionState.Closed)...{conn.Close();}
                conn.Dispose();
                tn.Dispose();
            }
        }