“SqlTransaction 已完成;它再也无法使用”解决方法

  当只是使用一次事务时，只用简单的事务就可以了

示例代码：

       SqlServerDataBase obj = new SqlServerDataBase();        SqlConnection conn = obj.DBconn();        conn.Open();        SqlTransaction myTrans;        myTrans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

        try        {            obj.Insert("insert into StuInfo (StuNumber,Name,Sex,Specialty) values('" + stuNumber + "','" + name + "','" + sex + "','" + specialty + "')", null);            obj.Insert("insert into StuSkill(StuNumber,Skill) values('" + stuNumber + "','" + skill + "')", null);            myTrans.Commit();            Response.Write("两条数据均插入到数据库中！");        }        catch (Exception ex)        {            try            {                myTrans.Rollback();            }            catch (SqlException sqlEx)            {                if (obj.DBconn() != null)                {                    Response.Write(sqlEx.GetType()+"数据库打开失败");                    Response.Redirect("submit.htm");                }            }        }        finally        {            obj.DBconn().Close();        }

  但是设计多个事务处理，比方使用for循环时，处理不当就会产生“SqlTransaction 已完成;它再也无法使用”的问题，主要就是因为SqlTransaction 使用了Commit()后就是不能在使用了，必须重新BeginTransaction()一个。

 批处理事务不应该把transaction事务放在for循环里，造成多个事务，这将导致错误. 应该把for放在transaction里，当成一个事务来处理，进行批处理

示例代码：

protected void btnDelete_Click(object sender, EventArgs e)  //删除    {        SqlServerDataBase del = new SqlServerDataBase();        string str = "";        SqlConnection conn = del.DBconn();        conn.Open();        SqlTransaction myTrans;        myTrans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

        ListBox1.Items.Clear();        try        {            for (int i = 0; i < GridView1.Rows.Count; i++)  //从GridView中选中checkbox            {                GridViewRow row = GridView1.Rows[i];                bool isChecked = ((CheckBox)row.FindControl("chkSelect")).Checked;                if (isChecked)                {                    ListBox1.Items.Add(GridView1.Rows[i].Cells[2].Text);                    //Column 1 is the StuNumber column                    str = GridView1.Rows[i].Cells[1].Text;  //获取删除行的StuNumber的值                    // Response.Write(str.ToString());                       del.Delete("delete from StuInfo where StuNumber = '" + str + "'", null);                    del.Delete("delete from StuSkill where StuNumber = '" + str + "'", null);//删除                }            }            myTrans.Commit();         }        catch (Exception ex)        {            try            {                myTrans.Rollback();            }            catch (SqlException sqlEx)            {                if (del.DBconn() != null)                {                    Response.Write(sqlEx.GetType() + "数据库打开失败");                    Response.Redirect("submit.htm");                }            }        }        finally        {            conn.Close();        }        BindData();    }}

注意：两者在使用上是有区别的！

另外在windows服务中，

windows的服务服务正常运行一段时间后，在ontimer中就无法再启动了，提示：此SqlTransaction已完成；它再也无法使用。有可能是 timer 时间间隔 太短，造成事务太频繁！也可能是Transaction两次提交的原因，需要注意一下timer的启用时机。

参考网站：<script src="http://wz.csdn.net/javascripts/vote.js" type="text/javascript"></script>

http://blog.csdn.net/kiki113/archive/2007/04/29/1591999.aspx 

http://topic.csdn.net/u/20070830/10/62719c5d-cb7a-47e5-87a3-89077d82dc77.html