c#系统.InvalidOperationException:这个OracleTransaction已经完成;它已经不能

本文关键字：不能 系统 这个 OracleTransaction InvalidOperationException | 更新日期: 2023-09-27 18:13:46

我有以下错误:

   System.InvalidOperationException: This OracleTransaction has completed; it is no longer usable

试图插入数据库

下面是代码: 
    OracleTransaction myTrans = null;   
    OracleCommand cmd = new OracleCommand("INSERT INTO ZTMP_SAM_TB_ELAB_PDR " + 
    "  VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14)", connection);
    myTrans = connection.BeginTransaction();
    cmd.Transaction = myTrans; 
    /*
    ( :facilityid,:taxid,
    :plantcode,:capdr,:fullmetkeym2,
    :corrmetidm2,:stopreadingm2,
    :corrstopreadm2,:fullmetkeym1,
    :corrmetidm1,:stopreadingm1,:corrstopreadm1,
    :stopreadtimem2,:stopreadtimem1)
    */
    file = new System.IO.StreamReader(path+FileUpload1.FileName);
    while((line = file.ReadLine()) != null)
    {
       string[] split = line.Split(',');

       cmd.Parameters.Add("1", OracleType.VarChar, 64).Value = split[0];
       cmd.Parameters.Add("2", OracleType.VarChar, 64).Value = "";
       cmd.Parameters.Add("3", OracleType.VarChar, 64).Value = "";
       cmd.Parameters.Add("4", OracleType.Number).Value = Convert.ToInt32(split[1]);
       cmd.Parameters.Add("5", OracleType.VarChar, 64).Value = split[6];
       cmd.Parameters.Add("6", OracleType.VarChar, 64).Value = split[7];
       cmd.Parameters.Add("7", OracleType.Number).Value = Convert.ToInt32(split[8]);
       cmd.Parameters.Add("8", OracleType.Number).Value = Convert.ToInt32(split[9]);
       cmd.Parameters.Add("9", OracleType.VarChar, 80).Value = split[2];
       cmd.Parameters.Add("10", OracleType.VarChar, 80).Value = split[3];
       cmd.Parameters.Add("11", OracleType.Number).Value = Convert.ToInt32(split[4]);
       cmd.Parameters.Add("12", OracleType.Number).Value = Convert.ToInt32(split[5]);
       DateTime date1,date2;
       DateTime.TryParseExact(split[10], "dd/MM/yyyy", null, System.Globalization.DateTimeStyles.None, out date1);
       DateTime.TryParseExact(split[11], "dd/MM/yyyy", null, System.Globalization.DateTimeStyles.None, out date2);
       cmd.Parameters.Add("13", OracleType.DateTime).Value = date1;
       cmd.Parameters.Add("14", OracleType.DateTime).Value = date2;
      try
        {
           cmd.ExecuteNonQuery();
           myTrans.Commit();    
        }   
       catch(Exception ex){
        myTrans.Rollback(); 
        Label1.Text = ex.Message;
        //"Si e' verificato un errore nell'inserimento dei dati nella tabella ZTMP_SAM_TB_ELAB_PDR";
        }
       /*Per ogni elemento esegui l'insert*/
    }

c#系统.InvalidOperationException:这个OracleTransaction已经完成;它已经不能

问题在于，假设您想要提交所有插入的行，那么while循环会多次提交事务

// ...
file = new System.IO.StreamReader(path+FileUpload1.FileName);
try
{
  while((line = file.ReadLine()) != null)
  {
    // Parameters...
    cmd.ExecuteNonQuery();
  }
  myTrans.Commit();     
}                   
catch(Exception ex)
{
  myTrans.Rollback(); 
  // ...
}

每个循环调用一个commit。这将在第二次循环时失败。您可以将提交移出循环或仅使用TransactionScope类

using(TransactionScope ts = new TransactionScope())
using(OracleConnection connection = new OracleConnection(cnstring))
{
    bool errorFound = false;
    OracleCommand cmd = new OracleCommand("INSERT INTO ZTMP_SAM_TB_ELAB_PDR " + 
                "  VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14)", connection);
    ....
    file = new System.IO.StreamReader(path+FileUpload1.FileName);
    while((line = file.ReadLine()) != null)
    {
       ....
       try
       {
           cmd.ExecuteNonQuery();
       }   
       catch(Exception ex)
       {
          Label1.Text = ex.Message;
          errorFound = true;
          break; 
       }
    }
    if(!errorFound) ts.Complete();
}

退出using块而不调用complete将自动回滚插入

试试这个:

bool flag=true;
OracleTransaction myTrans = null;   
OracleCommand cmd = new OracleCommand("INSERT INTO ZTMP_SAM_TB_ELAB_PDR " + 
"  VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14)", connection);
connection.open();//if not done already.
myTrans = connection.BeginTransaction();
cmd.Transaction = myTrans; 
file = new System.IO.StreamReader(path+FileUpload1.FileName);
while((line = file.ReadLine()) != null)
{
   string[] split = line.Split(',');

   cmd.Parameters.Add("1", OracleType.VarChar, 64).Value = split[0];
   cmd.Parameters.Add("2", OracleType.VarChar, 64).Value = "";
   cmd.Parameters.Add("3", OracleType.VarChar, 64).Value = "";
   cmd.Parameters.Add("4", OracleType.Number).Value = Convert.ToInt32(split[1]);
   cmd.Parameters.Add("5", OracleType.VarChar, 64).Value = split[6];
   cmd.Parameters.Add("6", OracleType.VarChar, 64).Value = split[7];
   cmd.Parameters.Add("7", OracleType.Number).Value = Convert.ToInt32(split[8]);
   cmd.Parameters.Add("8", OracleType.Number).Value = Convert.ToInt32(split[9]);
   cmd.Parameters.Add("9", OracleType.VarChar, 80).Value = split[2];
   cmd.Parameters.Add("10", OracleType.VarChar, 80).Value = split[3];
   cmd.Parameters.Add("11", OracleType.Number).Value = Convert.ToInt32(split[4]);
   cmd.Parameters.Add("12", OracleType.Number).Value = Convert.ToInt32(split[5]);
   DateTime date1,date2;
   DateTime.TryParseExact(split[10], "dd/MM/yyyy", null, System.Globalization.DateTimeStyles.None, out date1);
   DateTime.TryParseExact(split[11], "dd/MM/yyyy", null, System.Globalization.DateTimeStyles.None, out date2);
   cmd.Parameters.Add("13", OracleType.DateTime).Value = date1;
   cmd.Parameters.Add("14", OracleType.DateTime).Value = date2;
  try
    {
       cmd.ExecuteNonQuery();          
    }   
   catch(Exception ex)
    {
      myTrans.Rollback(); 
      Label1.Text = ex.Message;  
      flag=false; 
      break; 
    }
}
if(flag)
  myTrans.Commit(); 
connection.close();

你在while循环中提交事务，这就是为什么在第一次迭代之后，事务提交了两次，这就是为什么你得到异常。

应该在while循环完成后提交事务，如上面的代码所示。

相关文章：