此 SqlTransaction 已完成；它再也无法使用 ?????

Question

 public static bool TranModel(int[] empids, string id, string mname, string uname, string names, DateTime stime, DateTime etime, string re)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
        {
           
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            SqlTransaction myTran = connection.BeginTransaction();
            try
            {
               
            SqlParameter[] parameter ={
            new SqlParameter("@mname",SqlDbType.VarChar),
            new SqlParameter("@uname",SqlDbType.VarChar),
            new SqlParameter("@names",SqlDbType.VarChar),
            new SqlParameter("@stime",SqlDbType.DateTime),
            new SqlParameter("@etime",SqlDbType.DateTime),
            new SqlParameter("@re",SqlDbType.VarChar),
            new SqlParameter("@id",SqlDbType.VarChar),
            new SqlParameter("@content",SqlDbType.VarChar)
        };
                parameter[0].Value = mname;
                parameter[1].Value =uname;
                parameter[2].Value = names;
                parameter[3].Value = stime;
                parameter[4].Value = etime;
                parameter[5].Value = re;
                parameter[6].Value = id;
               
                parameter[7].Value="负责人："+uname+re;
                string strsql = "insert into MeetProcess(x_id,m_name,u_name,u_names,m_stime,m_etime,m_re)" +
                    "values(@id,@mname,@uname,@names,@stime,@etime,@re)";
                SqlCommand cmd1 = new SqlCommand();
                cmd1.Connection = connection;
                cmd1.Transaction = myTran;
                foreach (SqlParameter p in parameter)
                {
                    cmd1.Parameters.Add(p);   
                }
                cmd1.CommandText = strsql;
                cmd1.ExecuteNonQuery();
                connection.Close();
                for (int i = 0; i < empids.Length; i++)
                {
                    string strsql2 = "";
                    if (empids[i] != 0)
                    {
                         strsql2 = "insert into cpSchedule(EmpID,Subject,SchType,Content,PrivFlag,FDate,TDate,togehter)values" +
                            "(" + empids[i] + ",@mname,'项目流程',@content,'1',@stime,@etime,@names)";
                    }
                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    if(strsql2!="")
                    cmd1.CommandText = strsql2;
                    cmd1.ExecuteNonQuery();
                    connection.Close();
                }
                myTran.Commit();
                return true;
            }
            catch (Exception ex)
            {
                string mss = ex.Message;
                myTran.Rollback();
                return false;
            }
        }
    }

Answer 1

                connection.Close(); 要放在myTran.Commit();  
后面

Answer 2

那么for循环里的打开很多链接，没有在for内显示关闭，没什么问题吧？

Answer 3

连接用完在关闭，没有必要不停的open，比较消耗资源

Answer 4

既然用Transaction，只要开一个Connection就可以了，不需要开多个connection。在最外面打开Connection和Transaction, 在最后先Commit transaction 或者rollback transaction，在connection close.一个Transaction只能在一个Connection的生命周期里。如果需要多个Connection，可以考虑使用TransactionScope.

Answer 5

打开一个事物 tr 以后，只传 tr 就可以了 ，conn 可以从 tr.connection中获取。。没个事物 con 只能打开一次~~

Answer 6

我对上面做的修改就是你说的把connection.Close();放在了myTran.Commit();后面，myTran.Commit();我没有动 

Answer 7

你最外层已经加了 using 了 close不写也没关系 本回复纯属接分

Answer 8

我的意思是你把cmd1也加上这个事务．

Answer 9

cmd1.Transaction = myTran; 
这不是加上了么？

Answer 10

没必要用Transaction吧
你不就是想要集体提交，错误集体不提价吗
我给你改了改，意思就是把要提交的sql一起提交
哪里有问题还请指出 public static bool TranModel(int[] empids, string id, string mname, string uname, string names, DateTime stime, DateTime etime, string re) 
    { 
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) 
        { 
          
           
            System.Text.StringBuuilde sb = new Sestem.Test.StringBuilde();
              
            SqlParameter[] parameter ={ 
            new SqlParameter("@mname",SqlDbType.VarChar), 
            new SqlParameter("@uname",SqlDbType.VarChar), 
            new SqlParameter("@names",SqlDbType.VarChar), 
            new SqlParameter("@stime",SqlDbType.DateTime), 
            new SqlParameter("@etime",SqlDbType.DateTime), 
            new SqlParameter("@re",SqlDbType.VarChar), 
            new SqlParameter("@id",SqlDbType.VarChar), 
            new SqlParameter("@content",SqlDbType.VarChar) 
        }; 
                parameter[0].Value = mname; 
                parameter[1].Value =uname; 
                parameter[2].Value = names; 
                parameter[3].Value = stime; 
                parameter[4].Value = etime; 
                parameter[5].Value = re; 
                parameter[6].Value = id;
                parameter[7].Value="负责人："+uname+re; 
                sb.Aqqend( "insert into MeetProcess(x_id,m_name,u_name,u_names,m_stime,m_etime,m_re)" + 
                    "values(@id,@mname,@uname,@names,@stime,@etime,@re);"); 
                SqlCommand cmd1 = new SqlCommand(); 
                cmd1.Connection = connection;  
                foreach (SqlParameter p in parameter) 
                { 
                    cmd1.Parameters.Add(p);  
                } 
                cmd1.CommandText = strsql; 
                cmd1.ExecuteNonQuery(); 
                connection.Close(); 
                
                for (int i = 0; i < empids.Length; i++) 
                { 
                    string strsql2 = ""; 
                    if (empids[i] != 0) 
                    { 
                        sb.Aqqend( "insert into cpSchedule(EmpID,Subject,SchType,Content,PrivFlag,FDate,TDate,togehter)values" + 
                            "(" + empids[i] + ",@mname,'项目流程',@content,'1',@stime,@etime,@names)";); 
                    } 
                    
                } 
                
             try 
            { 
                if (connection.State == ConnectionState.Closed) 
                { 
                connection.Open();
                cmd1.CommandText = strsql; 
                cmd1.ExecuteNonQuery(); 
                }
                 return true;             } 
            catch (Exception ex) 
            { 
                string mss = ex.Message; 
                return false; 
            } 
        } 
    }

Answer 11

这段代码，如果插入MeetProcess不成功，插入cpSchedule也不成功么？

Answer 12

我只知道事务可以做到这点，   System.Text.StringBuuilde sb=new Sestem.Test.StringBuilde();   我没用过，不明白是什么意思？