错误:事务已完成,不再可用

本文关键字:不再 已完成 事务 错误 | 更新日期: 2023-09-27 18:23:57

//如何在C#中的一个事务中执行多个sql命令。。我是这样使用的,但它给了我错误。。请告诉我代码有什么问题。。

string[] files = Directory.GetFiles(dir);
foreach (string subfiles in files)
{
    con.Open();
    SqlTransaction myTrans=null;
    myTrans= con.BeginTransaction();

    try
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.Transaction = myTrans;
        cmd.CommandText = "select descr from genlookup where Code='SS_Purchase_No' and RecId=99998";
        SqlDataReader drr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        DataTable dt = new DataTable();
        //SqlDataAdapter da = new SqlDataAdapter(qry1, con);
        DataTable dtw = new DataTable();
        dtw.Load(drr);
        DataSet dsr = new DataSet();
        dsr.Tables.Add(dtw);
        //SqlDataAdapter darun = new SqlDataAdapter("select descr from genlookup where Code='SS_Purchase_No' and RecId=99998", con);
        //DataSet dsr = new DataSet();
        //darun.Fill(dsr);
        int run_no = Convert.ToInt32(dsr.Tables[0].Rows[0]["descr"].ToString());
        filename = Path.GetFileNameWithoutExtension(subfiles);
        string filenames = Path.GetFileName(subfiles);

        if (subfiles.Trim().EndsWith(".xlsx"))
        {
             strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='"Excel 12.0 Xml;HDR=YES;IMEX=1'";", subfiles);
        }
        else if (subfiles.Trim().EndsWith(".xls"))
        {
            strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='"Excel 8.0;HDR=Yes;IMEX=1'";", subfiles);
        }
        OleDbConnection exlcon = new OleDbConnection(strConn);
        exlcon.Open();
        string myTableName = exlcon.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();
        OleDbDataAdapter oledbadpt = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}] ", myTableName), exlcon);
        DataSet d_s = new DataSet();
        oledbadpt.Fill(d_s);
        exlcon.Close();

        for (int i = 7; i < d_s.Tables[0].Rows.Count - 1; i++)
        {
            PARTNER_ID = d_s.Tables[0].Rows[i]["F1"].ToString();
            RTV_LOCTN = d_s.Tables[0].Rows[i]["F3"].ToString();
            DateTime date1 = Convert.ToDateTime(d_s.Tables[0].Rows[i]["F13"]);
            string ddmm = date1.ToString("yyyyMMdd");
            string[] aa = Color_size.Split('/');
            // string colr="";
            string size = "";
            foreach (string ss in aa)
            {
                size = ss;
            }
            }
            con.Open();
            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = con;
            myCommand.Transaction = myTrans;
            myCommand.CommandText = "insert into HSR_Purch_RETURN(PARTNER_ID,RTV_LOCTN)" +
               " values('" + PARTNER_ID + "'," + "'" + RTV_LOCTN + "') ";
            myCommand.ExecuteNonQuery();
            //con.Open();
            //SqlCommand cmdd = new SqlCommand(insert, con);
            //int value1 = cmdd.ExecuteNonQuery();
            //values = string.Empty;
            con.Close();

            if ((shrwcode != "") && (flag == "F"))
            {
                string zz = "select  DistributionCenter,GLCountry,GLZone,GLState,GLCity  from showroommaster where ShowroomCode='" + shrwcode + "'";
                SqlDataAdapter da11 = new SqlDataAdapter(zz, con);
                DataSet ds11 = new DataSet();
                da11.Fill(ds11);
                string Dcenter = ds11.Tables[0].Rows[0]["DistributionCenter"].ToString();
                string GLCountry = ds11.Tables[0].Rows[0]["GLCountry"].ToString();
                string fff = "select isnull(max(EntSrlNo),0)+1 as EntSrlNo from IDTableExtd where ShowroomCode='" + shrwcode + "' and DocDate='" + RTV_DATE + "'";
                SqlDataAdapter das = new SqlDataAdapter(fff, con);
                DataSet dss = new DataSet();
                das.Fill(dss);
                SqlCommand extdcmd = new SqlCommand();
                extdcmd.Connection = con;
                extdcmd.Transaction = myTrans;
                string docpre = "PR" + RTV_DATE.Substring(2, 2);
                if (dss.Tables[0].Rows.Count > 0)
                {
                    slno = Convert.ToInt32(dss.Tables[0].Rows[0]["EntSrlNo"].ToString());
                    extdcmd.CommandText = "insert into IDTableExtd (ShowroomCode,TrnType,TrnCtrlNo,DocNoPrefix,docno,DocDate,EntSrlNo,StockNo,DistributionCenter,GLCountry,GLZone,GLState,GLCity,PartyType,PromoValue_LineLevel,DocQty,NetValue,BatchSrlNo)" +
                         "values ('" + shrwcode + "'," + "'2300'," + "'" + ddmm + "'," + "'" + docpre + "'," + "'" + ddmm + "'," + "'" + RTV_DATE + "'," + "'" + slno + "'," + "'" + TRN_STOCKNO + "'," + "'" + Dcenter + "'," + "'" + GLCountry + "'," + "'" + GLZone + "',"
                         + "'" + GLState + "'," + "'" + GLCity + "','10'," + '0' + ",'" + RTV_QTY + "'," + "'" + RTV_cost + "','0')";
                    con.Open();
                   // SqlCommand extdcmd = new SqlCommand(instableextd, con);
                    extdcmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        myTrans.Commit(); ///Error is getting after exceuting this line..

错误:事务已完成,不再可用

首先,您需要重构您的代码,并使用类似的东西:

using (var = new SqlConnection(_connectionstring))
{
    try
    {
        connection.Open();
        using(SqlTransaction transaction = connection.BeginTransaction())
        {
            using (SqlCommand command1= new SqlCommand(commandtext, connection, transaction ))
            {
                //Do something here
            }
            using (SqlCommand command2= new SqlCommand(commandtext, connection, transaction ))
            {
                //Do another stuff here
            }
            ...
            transaction .Commit();  
        }
    }
    catch (Exception Ex)
    {
        if (transaction != null) transaction .Rollback();
    }
}

(1)正如Joseph所说,使用Using语句重构代码,这有助于正确处理对象。

(2) 您的代码易于SQL注入,因此请使用SQLParameter

我展示了您的代码中的一个示例,使其完整。

con.Open();
            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = con;
            myCommand.Transaction = myTrans;
            myCommand.CommandText = "insert into HSR_Purch_RETURN(PARTNER_ID,RTV_LOCTN) values(@partnerId,@rtv)";
            myCommand.Parameters.Add(new SqlParameter("partnerId",PARTNER_ID));
            myCommand.Parameters.Add(new SqlParameter("rtv",RTV_LOCTN));
            myCommand.ExecuteNonQuery();
            //con.Open();
            //SqlCommand cmdd = new SqlCommand(insert, con);
            //int value1 = cmdd.ExecuteNonQuery();
            //values = string.Empty;
con.Close();