通过统一事务来控制对数据库进行多次操作

通过统一事务来控制对数据库进行多次操作，发生错误时可以对前面的所有操作进行回滚。

　下面是一段循环调用带Output的存储过程代码

            int result = 0;
            SqlConnection conn = null;
            SqlTransaction transaction = null;
            try
            {
                bool isAudit = true;
                //加载并读取数据库连接配置文件
                  XmlOperate xmlop = new XmlOperate();
                DataTable dt = xmlop.XmlToTable(AppDomain.CurrentDomain.BaseDirectory + "dbconfig.xml", "Config");
                string dbType = dt.Rows[0]["DbType"].ToString();
                string connectionString = dt.Rows[0]["ConnectionString"].ToString();
                switch (dbType.ToLower())
                {
                    case "sqlserver":        //SQL Server连接方式
                        conn = new SqlConnection(connectionString);
                        break;
                }
                SqlCommand MyCommand = new SqlCommand(sqlText, conn);//sqlText（存储过程名称）
                conn.Open();
                transaction = conn.BeginTransaction();//启动事物
                   MyCommand.Transaction = transaction;
                MyCommand.CommandType = CommandType.StoredProcedure;

                foreach (DataGridViewRow dgRow in dgDetails.Rows)
                {
                    if (dgRow.IsNewRow)
                        continue;
                    if (dgRow.Cells["条码"].Value.ToString().Trim() == "")
                        continue;
                    string barcode = dgRow.Cells["条码"].Value.ToString().Trim();

                    MyCommand.Parameters.Clear();

                        MyCommand.Parameters.AddWithValue("@iType", iType);
                        MyCommand.Parameters.AddWithValue("@ncNo", ncNo);
                        MyCommand.Parameters.AddWithValue("@billNo", billNo);
                        MyCommand.Parameters.AddWithValue("@customerCode", customerCode);
                        MyCommand.Parameters.AddWithValue("@barCode", barcode);
                        MyCommand.Parameters.AddWithValue("@date", date);
                        MyCommand.Parameters.Add("@returnMsg", SqlDbType.VarChar, 500).Direction = ParameterDirection.Output;
                        MyCommand.Parameters.Add("@returnVal", SqlDbType.Int).Direction = ParameterDirection.Output;
                                        
                    MyCommand.ExecuteNonQuery();
                    returnMsg = "条码：" + barcode + "。" + MyCommand.Parameters["@returnMsg"].Value.ToString();
                    result = Convert.ToInt32(MyCommand.Parameters["@returnVal"].Value.ToString());//returnVal = 0 则不允许审核
                    if (result == 0)
                    {
                        transaction.Rollback();//发生回滚
                        isAudit = false;
                        break;
                    }
                }
                if (isAudit)
                {
                    transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                transaction.Rollback();//发生回滚
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;