关于 事务回滚处理

Question

先谢谢各位了：我现在有一个保存方法如下，当运行到下面蓝色标记的语句，也就是第一个for循环第二次循环的时候，就会出现异常，当运行异常中的rollback（）方法时，提示该事务已经完成，不能再使用了。
问题：
（一）：问什么会提示该事务已经完成，不能再使用了？（二）：问什么运行到第一个for循环的第二次循环的时候，就出现了异常？
请大家帮忙看看，项目比较着急，再次不胜感激。    protected void saveBtn_Click(object sender, EventArgs e)
    {
        DBHelper.BeginTran();
        try
        {
           
            //主表信息
            Mps_prtbindproducemst bindMst = new Mps_prtbindproducemst();
            bindMst.SysNo = this.txtSysno.Text;
            bindMst.OrderNo = this.txtOrderNo.Text;
            bindMst.BusinessDate = tool.ToDateTime(this.txtBussinessDate.Text);
            bindMst.BookintoDate = tool.ToDateTime(this.txtBookIntoDate.Text);
            bindMst.PrintingNo = this.txtPrintingNo.Text;
            bindMst.PrintingName = this.txtPrintingName.Text;
            bindMst.LinkMan = this.txtLinkMan.Text;
            bindMst.LinkPhon = this.txtLinkPhon.Text;
            bindMst.BooksNo = this.txtBooksNo.Text;
            bindMst.FillPsn = this.txtFillPsn.Text;
            bindMst.FillDate = tool.ToDateTime(this.txtFillDate.Text);
            bindMst.PrintQty = tool.ToInt(this.txtPrintQty.Text);
            bindMst.SpinThickness = tool.ToInt(this.txtSpinThickNess.Text);
            bindMst.FrontLeport = tool.ToDecimal(this.txtFrontLePort.Text);
            bindMst.BackLeport = tool.ToDecimal(this.txtBackLePort.Text);
            bindMst.PrintType = this.txtPrintType.SelectedValue;
            bindMst.BindType = this.txtBindType.SelectedValue;
            bindMst.FlodType = this.txtFlodType.Text;
            bindMst.BoundBag = tool.ToInt(this.txtBoundBag.Text);
            bindMst.PageOrder = this.txtPageOrder.Text;
            bindMst.BindOrder = this.txtPrintBindOrder.Text;
            Mps_prtbindproducemstManager.AddMps_prtbindproducemst(bindMst);
            //原材料信息
            for (int i = 0; i < Convert.ToInt32(this.HiddenField1.Value); i++)
            {
                Mps_prtbindproducedetitem item = new Mps_prtbindproducedetitem();
                item.SysNo = this.txtSysno.Text;
                item.LineId = Convert.ToInt32((itemSelRepeater.Items[i].FindControl("txtLineId") as TextBox).Text);
                item.Size = (itemSelRepeater.Items[i].FindControl("txtSize") as TextBox).Text;
                item.Format = (itemSelRepeater.Items[i].FindControl("txtFormat") as TextBox).Text;
                item.Chromatic = (itemSelRepeater.Items[i].FindControl("txtChromatic") as TextBox).Text;
               
                Mps_prtbindproducedetitemManager.AddMps_prtbindproducedetitem(item);           }            //工艺明细信息
            for (int j = 0; j < Convert.ToInt32(this.HiddenField2.Value); j++)
            {
                Mps_prtbindproducedetprocess process = new Mps_prtbindproducedetprocess();
                process.SysNo = this.txtSysno.Text;
                process.LineId = Convert.ToInt32((processSelRepeater.Items[j].FindControl("txtLineId") as TextBox).Text);
                               process.Craft = (processSelRepeater.Items[j].FindControl("txtCraft") as DropDownList).SelectedValue;
                process.Price = tool.ToDecimal((processSelRepeater.Items[j].FindControl("txtPrice") as TextBox).Text);
                process.Money = tool.ToDecimal((processSelRepeater.Items[j].FindControl("txtMoney") as TextBox).Text);
                process.Res = (processSelRepeater.Items[j].FindControl("txtRes") as TextBox).Text;                Mps_prtbindproducedetprocessManager.AddMps_prtbindproducedetprocess(process);
            }            //印装次序信息
            for (int k = 0; k < Convert.ToInt32(HiddenField3.Value); k++)
            {
                Mps_prtbindproducedetbindorder bindorder = new Mps_prtbindproducedetbindorder();
                bindorder.SysNo = this.txtSysno.Text;
                bindorder.LineId = Convert.ToInt32((bindOrderRepeater.Items[k].FindControl("txtLineId") as TextBox).Text);
                bindorder.BindOrder = (bindOrderRepeater.Items[k].FindControl("txtBindOrder") as TextBox).Text;
                if (bindorder.BindOrder.Equals(""))
                {
                    break;
                }
               
                Mps_prtbindproducedetbindorderManager.AddMps_prtbindproducedetbindorder(bindorder);
            }            //送货方式信息
            for (int m = 0; m < Convert.ToInt32(HiddenField4.Value); m++)
            {
                Mps_prtbindproducedetdelivery delivery = new Mps_prtbindproducedetdelivery();
                delivery.SysNo = this.txtSysno.Text;
                delivery.LineId = Convert.ToInt32((deliveryRepeater.Items[m].FindControl("txtLineId") as TextBox).Text);
                delivery.Printing = (deliveryRepeater.Items[m].FindControl("txtPrinting") as TextBox).Text;
                if (delivery.Printing.Equals(""))
                {
                    break;                }
                delivery.Qty = tool.ToInt((deliveryRepeater.Items[m].FindControl("txtQty") as TextBox).Text);
                delivery.LinkMan = (deliveryRepeater.Items[m].FindControl("txtLinkMan") as TextBox).Text;
                delivery.LinkPhon = (deliveryRepeater.Items[m].FindControl("txtLinkPhon") as TextBox).Text;
               
                Mps_prtbindproducedetdeliveryManager.AddMps_prtbindproducedetdelivery(delivery);            }
            DBHelper.Commit();
        }catch(Exception sqlMessage){
            DBHelper.RollBack();
            this.RegisterStartupScript("","<script>alert('保存失败！')</script>");
            Response.Write(sqlMessage.Message);
        }
    }下面是我的dbhelper类中的事务： public static class DBHelper
    {        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["wcsy_xiron"].ConnectionString;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                return connection;
            }
        }
        static private SqlCommand cmd;
        static private bool isTran = false;
        static SqlTransaction tran;
        static public void BeginTran()
        {
            if (isTran == false)
            {
                isTran = true;
                tran = Connection.BeginTransaction();
                cmd = new SqlCommand();
                cmd.Transaction = tran;
            }
            else
            {
                throw new Exception("不能重复启动事务，请先关闭事务");
            }
        }
        static public void RollBack()
        {
            try
            {
                tran.Rollback();
            }
            catch (SqlException sqlEx)
            {
                if (Connection != null)
                {
                    Console.Write(sqlEx.GetType() + "数据库打开失败");                }
            }
            finally {
                connection.Close();
                isTran = false;
            }
            
        }        static public void Commit()
        {
            tran.Commit();
            isTran = false;
        }
 public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            try
            {
                if (!isTran)
                {
                    cmd = new SqlCommand();
                }
                cmd.CommandText = sql;
                cmd.Connection = Connection;
                cmd.Parameters.AddRange(values);
                System.Diagnostics.Debug.WriteLine(sql);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return 1;
                 
            }
            catch (Exception e) {
                return 0;
                Console.Write(e.Message);
            
            }
            finally
            {
                cmd.Connection.Close();
                Connection.Close();
            }
        }

Answer 1

报什么异常了，还有你的Mps_prtbindproducedetitemManager.AddMps_prtbindproducedetitem方法怎么写的？怀疑是你事物用法弄错了，你在BeginTran里开启事物，然后你在AddMps_prtbindproducedetitem里调用了ExecuteCommand方法，ExecuteCommand方法执行完Connection就被关闭了，所以事物就结束了

Answer 2

这是方法：
 public static void AddMps_prtbindproducedetitem(Mps_prtbindproducedetitem mps_prtbindproducedetitem)
{
            string sql =
"INSERT mps_prtbindproducedetitem (sysNo, lineId, object, qty, size, format, chromatic, printLaw, wareHouse, paperSupply, itemName, spec, weight, paperQty, paperUnit, addReleaseRate, addReleaseQty, paperSumQty, paperPrice, paperMoney, res)" +
"VALUES (@sysNo, @lineId, @object, @qty, @size, @format, @chromatic, @printLaw, @wareHouse, @paperSupply, @itemName, @spec, @weight, @paperQty, @paperUnit, @addReleaseRate, @addReleaseQty, @paperSumQty, @paperPrice, @paperMoney, @res)";

sql += " ; SELECT @@IDENTITY";            try
            {
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@sysNo", mps_prtbindproducedetitem.SysNo),
new SqlParameter("@lineId", mps_prtbindproducedetitem.LineId),
new SqlParameter("@object", mps_prtbindproducedetitem.Object),
new SqlParameter("@qty", mps_prtbindproducedetitem.Qty),
new SqlParameter("@size", mps_prtbindproducedetitem.Size),
new SqlParameter("@format", mps_prtbindproducedetitem.Format),
new SqlParameter("@chromatic", mps_prtbindproducedetitem.Chromatic),
new SqlParameter("@printLaw", mps_prtbindproducedetitem.PrintLaw),
new SqlParameter("@wareHouse", mps_prtbindproducedetitem.WareHouse),
new SqlParameter("@paperSupply", mps_prtbindproducedetitem.PaperSupply),
new SqlParameter("@itemName", mps_prtbindproducedetitem.ItemName),
new SqlParameter("@spec", mps_prtbindproducedetitem.Spec),
new SqlParameter("@weight", mps_prtbindproducedetitem.Weight),
new SqlParameter("@paperQty", mps_prtbindproducedetitem.PaperQty),
new SqlParameter("@paperUnit", mps_prtbindproducedetitem.PaperUnit),
new SqlParameter("@addReleaseRate", mps_prtbindproducedetitem.AddReleaseRate),
new SqlParameter("@addReleaseQty", mps_prtbindproducedetitem.AddReleaseQty),
new SqlParameter("@paperSumQty", mps_prtbindproducedetitem.PaperSumQty),
new SqlParameter("@paperPrice", mps_prtbindproducedetitem.PaperPrice),
new SqlParameter("@paperMoney", mps_prtbindproducedetitem.PaperMoney),
new SqlParameter("@res", mps_prtbindproducedetitem.Res)
};

                    DBHelper.ExecuteCommand(sql, para);
//return GetMps_prtbindproducedetitemBySysNo(newId);
            }
            catch (Exception e)
            {
Console.WriteLine(e.Message);
                throw e;
            }
}

Answer 3

您的意思是说，当我执行完commit()方法后才关闭数据库连接，是吗？

Answer 4

问题很明显了，就像我在1楼说的，执行事物操作的时候，数据连接不能改变，而你在执行ExecuteCommand的时候就把连接关闭了，肯定会出错的

Answer 5

可以到网上下一个DBHelper类，里面就包含正确处理事物的方法，比如一些开源的项目，像petshop等