错误:事务已完成，不再可用
本文关键字：不再 已完成 事务 错误 | 更新日期: 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();