C#代码中如何使用事务？

Question

题目是这样的：
现有一张查询页面，能够查询出状态为未送审和已审核的两种状态的数据，页面上有个批次审核的按钮，当我选中未送审和已审核这两种状态的数据时，点击批次审核按钮，会调用同一只存储过程，大家知道，状态为已审核的单据，是不可能进行再审核的，所以我就想用事务来，将这些状态分为两种的单据，作为一个整体，要么都执行，要么都不执行，所以想到了事务，但具体这么用，我还不明白，望：各位大侠指点迷津，感激不尽！！

Answer 1

 SqlConnection conn = new SqlConnection();
        conn.ConnectionString = SQLHelp.SQLHelp.SQLConnString;
        SqlCommand cmd = new SqlCommand();
        conn.Open();
        cmd.Connection = conn;
//定义一个事物的开始
        cmd.Transaction = conn.BeginTransaction();
        try
        {
                      foreach (DataRow dr in dt.Rows)
            {
                cmd.CommandType = CommandType.Text;
                string delstring = "delete from Sales_Customer_Relationship where account_id=@account_id and customer_no=@customer_no and channel=@channel";
                cmd.CommandText = delstring;
                cmd.Parameters.Add(new SqlParameter("@account_id", ddl_sales_from.SelectedItem.Text));
                cmd.Parameters.Add(new SqlParameter("@customer_no", dr["customer_no"].ToString()));
                cmd.Parameters.Add(new SqlParameter("@channel", dr["channel"].ToString()));
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
                       cmd.CommandType = CommandType.Text;
            string updstring = "update Sales_Customer_Relationship set account_id=@to_account_id where account_id=@from_account_id";
            cmd.CommandText = updstring;
            cmd.Parameters.Add(new SqlParameter("@to_account_id", ddl_sales_to.SelectedItem.Text));
            cmd.Parameters.Add(new SqlParameter("@from_account_id", ddl_sales_from.SelectedItem.Text));
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();                      foreach (DataRow drow in dt1.Rows)
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.stp_Upd_Forecast_Sls_Account";
                cmd.Parameters.Add(new SqlParameter("@parChannel", drow["channel"].ToString()));
                cmd.Parameters.Add(new SqlParameter("@parYear_Month", DateTime.Now.ToString("yyyy/MM")));
                cmd.Parameters.Add(new SqlParameter("@parCustomer_No", drow["customer_no"].ToString()));
                cmd.Parameters.Add(new SqlParameter("@parAccountId", ddl_sales_to.SelectedItem.Text));
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            cmd.Transaction.Commit();
        }
        catch (Exception ex)
        {
            cmd.Transaction.Rollback();
            throw ex;
        }
        finally
        {            if (conn != null)
            {
                conn.Dispose();
            }
            if (cmd != null)
            {
                cmd.Dispose();
            }
        }

Answer 2

代码中用Connection对象BeginTransaction返回一个Transaction对象，利用这个对象基于这个链接来提交数据。

Answer 3

写个能审核一条记录的存储过程
然后 循环页面上你要的记录,也可以把审核状态传给存储过程(存储过程无视该记录就可以了)  
循环执行存储过程  把执行这些存储过程 写在一个ado.net的事物里头  发生错误了  回滚 否则commit 

Answer 4

现在的问题是，连接数据库的sqlConnection,已经被封装好了，没法调用的事物，怎么搞？？

Answer 5

 public class TranSactionHelper
    {
        /// <summary>
        /// DbCommands执行事务
        /// </summary>
        /// <param name="dbCommands">要执行的DbCommand</param>
        /// <returns></returns>
        public static string Updata(List<DbCommand> dbCommands)
        {
            string retrun_message = "none";
            if (dbCommands == null || dbCommands.Count == 0)
            {
                return "没有要执行操作";
            }
            using (DbConnection dbConnection = SFWS.NPS.Substructure.DB.NP.CreateConnection())
            {
                try
                {
                    dbConnection.Open();
                    using (DbTransaction dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Serializable))
                    {
                        try
                        {
                            foreach (DbCommand dbCommand in dbCommands)
                            {
                                if (dbCommand != null)
                                {
                                    SFWS.NPS.Substructure.DB.NP.ExecuteNonQuery(dbCommand, dbTransaction);
                                }
                            }
                            dbTransaction.Commit();
                            retrun_message = "ok";
                        }
                        catch (Exception ex)
                        {
                            retrun_message = ex.Message;
                            dbTransaction.Rollback();
                            WebLogger.Log("事务更新出错！", ex);                        }
                    }
                }
                catch (Exception ex)
                {
                    WebLogger.Log("打开连接失败！", ex);
                    retrun_message = ex.Message;
                }
                finally
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch
                    {
                        //return false;
                    }
                   
                }
            }            return retrun_message;
        }        public static string Updata(List<DbCommand> dbCommands, Database db)
        {
            string retrun_message = "none";
            if (dbCommands == null || dbCommands.Count == 0)
            {
                return "没有要执行操作";
            }
            using (DbConnection dbConnection = db.CreateConnection())
            {
                try
                {
                    dbConnection.Open();
                    using (DbTransaction dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Serializable))
                    {
                        try
                        {
                            foreach (DbCommand dbCommand in dbCommands)
                            {
                                db.ExecuteNonQuery(dbCommand, dbTransaction);
                            }
                            dbTransaction.Commit();
                            retrun_message = "ok";
                        }
                        catch (Exception ex)
                        {
                            retrun_message = ex.Message;
                            dbTransaction.Rollback();
                            WebLogger.Log("事务更新出错！", ex);                        }
                    }
                }
                catch (Exception ex)
                {
                    WebLogger.Log("打开连接失败！", ex);
                    retrun_message = ex.Message;
                }
                finally
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch
                    {
                        //return false;
                    }                }
            }            return retrun_message;
        }

Answer 6

    /// <summary>
        /// 
        /// </summary>
        /// <param name="sqls"></param>
        /// <returns></returns>
        public static string Updata(List<string> sqls)
        {            string retrun_message = "none";
            if (sqls == null || sqls.Count == 0)
            {
                return "没有要执行操作";
            }            using (DbConnection dbConnection = SFWS.NPS.Substructure.DB.NP.CreateConnection())
            {                try
                {
                    dbConnection.Open();
                    using (DbTransaction dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Serializable))
                    {
                        try
                        {                            foreach (var sqltemp in sqls)
                            {
                                if (sqltemp.Trim() != string.Empty)
                                {
                                    SFWS.NPS.Substructure.DB.NP.ExecuteNonQuery(dbTransaction, CommandType.Text,
                                                                                       sqltemp);
                                }
                            }
                            dbTransaction.Commit();
                            retrun_message = "ok";
                        }
                        catch (Exception ex)
                        {
                            retrun_message = ex.Message;
                            dbTransaction.Rollback();
                            WebLogger.Log("事务更新出错！", ex);                        }                    }
                }
                catch (Exception ex)
                {
                    WebLogger.Log("打开连接失败！", ex);
                    retrun_message = ex.Message;
                }
                finally
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch
                    {
                        //return false;
                    }                }
            }
            return retrun_message;
        }        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sp"></param>
        /// <param name="sp_parameter"></param>
        /// <returns></returns>
        public static string Updata(List<string> sql, List<string> sp, object[] sp_parameter)
        {
            string retrun_message = "none";
            if (sp.Count != sp_parameter.Length)
            {
                throw new Exception("需要执行的存储过程,的参数列表和存储过程的数量不同!");
            }
            using (DbConnection dbConnection = SFWS.NPS.Substructure.DB.NP.CreateConnection())
            {                try
                {
                    dbConnection.Open();
                    using (DbTransaction dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Serializable))
                    {
                        try
                        {
                            //执行语句
                            foreach (var sqltemp in sql)
                            {
                                if (sqltemp.Trim() != string.Empty)
                                {
                                    SFWS.NPS.Substructure.DB.NP.ExecuteNonQuery(dbTransaction, CommandType.Text,
                                                                                       sqltemp);
                                }
                            }
                            //执行存储过程
                            for (int i = 0; i < sp.Count; i++)
                            {
                                string sp_temp = sp[i];
                                object[] obj = (object[])sp_parameter[i];
                                SFWS.NPS.Substructure.DB.NP.ExecuteNonQuery(dbTransaction, sp_temp, obj);
                            }                            dbTransaction.Commit();
                            retrun_message = "ok";
                        }
                        catch (Exception ex)
                        {
                            retrun_message = ex.Message;
                            dbTransaction.Rollback();
                            WebLogger.Log("事务更新出错！", ex);                        }                    }
                }
                catch (Exception ex)
                {
                    retrun_message = ex.Message;
                    WebLogger.Log("打开连接失败！", ex);
                }
                finally
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch
                    {
                        //return false;
                    }                }
            }
            return retrun_message;
        }

Answer 7

  /// <summary>
        /// 
        /// </summary>
        /// <param name="sp"></param>
        /// <param name="sp_parameter"></param>
        /// <returns></returns>
        public static string Updata(List<string> sp, object[] sp_parameter)
        {
            string retrun_message = "none";
            if (sp.Count != sp_parameter.Length)
            {
                throw new Exception("需要执行的存储过程,的参数列表和存储过程的数量不同!");
            }
            using (DbConnection dbConnection = SFWS.NPS.Substructure.DB.NP.CreateConnection())
            {                try
                {
                    dbConnection.Open();
                    using (DbTransaction dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Serializable))
                    {
                        try
                        {
                            //执行存储过程
                            for (int i = 0; i < sp.Count; i++)
                            {
                                string sp_temp = sp[i];
                                object[] obj = (object[])sp_parameter[i];
                                SFWS.NPS.Substructure.DB.NP.ExecuteNonQuery(dbTransaction, sp_temp, obj);
                            }                            dbTransaction.Commit();
                            retrun_message = "ok";
                        }
                        catch (Exception ex)
                        {
                            retrun_message = ex.Message;
                            dbTransaction.Rollback();
                            WebLogger.Log("事务更新出错！", ex);                        }                    }
                }
                catch (Exception ex)
                {
                    retrun_message = ex.Message;
                    WebLogger.Log("打开连接失败！", ex.Message);
                }
                finally
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch
                    {
                        //return false;
                    }                }
            }
            return retrun_message;
        }
    }