ExecuteNonQuery 要求该命令在我的代码中有一个事务错误

我在 cmd.ExecuteNonQuery上得到以下错误。

”ExecuteNonQuery 要求命令在 分配给该命令的连接位于挂起的本地事务中。 命令的 Transaction 属性尚未初始化。”

这是我的代码:

  //if (hdRefresh.Value.Length > done.Value.Length || done.Value == "1")
//{
//    //Write Your Add Customer Code here > Response.Write("true")
//    done.Value = hdRefresh.Value;
//}
//else
//{
//    Response.Redirect("~/Cashier/BTBill.aspx");
//    return;
//}


if (IsClosedToDay())
{
ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "Warning", "<script>alert('Day Closing has been Performed ')</script>", false);
return;
}


DateTime dateFeomDB = getdate();
// by atizaz
if (HDD.Value == "" || HDD.Value == null)
{
ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "Warning", "<script>alert('No Transaction Found')</script>", false);
return;
}
//
SqlConnection scon = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ToString());
Common.BillTransaction bill1 = new Common.BillTransaction();
ProcessUpdateBalandUnAuthBal insertBalance = new ProcessUpdateBalandUnAuthBal();
Common.Currency currencyy = new Common.Currency();
ProcessAuthorizeTokenByBillNo authorize = new ProcessAuthorizeTokenByBillNo();
BillTransaction bill = new BillTransaction();
scon.Open();
SqlTransaction sqlTrans = scon.BeginTransaction();
try
{
string strforxml = HDD.Value;
XmlDocument docXml = new XmlDocument();




#region Read In To Sender Controlls


#region Common Information
Contact con = new Contact();
con.Title = ddlTitle.SelectedItem.Text;
con.FirstName = TextBox1.Text.Trim();
con.LastName = TextBox9.Text.Trim();
con.ConTactNo = txtCell.Text == "" ? SqlString.Null : txtCell.Text;
con.Country = ddlCountry.SelectedItem.Text;
con.CustomerType = ddlCustomerType.SelectedItem.Text;
con.CustTypeID = int.Parse(ddlCustomerType.SelectedValue);
con.CountryID = Int32.Parse(ddlCountry.SelectedValue);
con.sqlTransaction = sqlTrans;
if (Scitytxt.Value != "")
{
try
{
con.City = Scitytxt.Value;
con.CityID = Int32.Parse(Scityval.Value);
}
catch (Exception)
{ }
}
else
{
con.City = SqlString.Null;// Scitytxt.Value;
con.CityID = SqlInt32.Null;// Int32.Parse(Scityval.Value);
con.Address = "";
}
//con.City = ddlCity.SelectedItem.Text;
//con.CityID = int.Parse(ddlCity.SelectedValue);
con.Address = TextBox10.Text;
#endregion


#region Check For NIC and Passport


if (txtNIC.Text != "" || txtPassport.Text != "")
{
SqlDataReader rdrsender;


if (txtNIC.Text != "")
{
con.NIC = txtNIC.Text;
}
else
{
con.NIC = SqlString.Null;
}
if (txtPassport.Text != "")
{
con.Passport = txtPassport.Text;
}
else
{
con.Passport = SqlString.Null;
}
ProcessSearchContactInContactInfo srchSender = new ProcessSearchContactInContactInfo();
srchSender.Contact = con;
srchSender.Invokewith5parameters();
rdrsender = srchSender.ResultSet;


#region If record Doesnot Exist In response of NIC Passport
if (!rdrsender.Read())
{
rdrsender.Close();
rdrsender.Dispose();
//  con.sqlTransaction = sqlTrans;
ProcessAddContact InsertnewSenderInfo = new ProcessAddContact();
// InsertnewSenderInfo.sqlTransaction = sqlTrans;
InsertnewSenderInfo.Contact = con;
InsertnewSenderInfo.Invoke();


//  sender1 = InsertnewSenderInfo.ResultSet;
//  Sender_ID.Value = sender1[13].ToString();
}
#endregion
#region If Record Exists
else
{
con.CustomerID = Int32.Parse(rdrsender["Customer_ID"].ToString());
rdrsender.Close();
rdrsender.Dispose();
}
#endregion
}
#endregion


#region If Customer Donot Have NIC And/OR Passport
else// this executes when both Pasport and NIC are Null
{
con.NIC = SqlString.Null;
con.Passport = SqlString.Null;
ProcessAddContact InsertnewSenderInfo = new ProcessAddContact();
InsertnewSenderInfo.Contact = con;
InsertnewSenderInfo.Invoke();


DataSet ds = new DataSet();
int a = con.CustomerID;
StringReader inforeader = new StringReader("<CusTable><CusInfo><Relation_Type></Relation_Type><HusbandFather_Name></HusbandFather_Name><Address_Present></Address_Present><Address_Other></Address_Other><Phone_No_Office></Phone_No_Office><Cell_No></Cell_No><Fax_No></Fax_No><Date_Of_Birth></Date_Of_Birth><NTN_No></NTN_No><Nationality></Nationality><Occupation></Occupation><Relation_With_Financial_Institution></Relation_With_Financial_Institution><Other_Relation_With_Financial_Institution></Other_Relation_With_Financial_Institution><Business_Relation></Business_Relation></CusInfo></CusTable>");
ds.ReadXml(inforeader);
ds.GetXml();
SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() + " WHERE Customer_ID=" + a + "", scon);
cmd.ExecuteNonQuery();


//  sender1 = InsertnewSenderInfo.ResultSet;
//  Sender_ID.Value = sender1[13].ToString();
}

告诉我代码中的问题是什么以及如何解决它。

116943 次浏览

您已经启动了一个在调用 cmd.ExecuteNonQuery()之前未提交的事务。

cmd.ExecuteNonQuery();之前写下 cmd.Transaction = sqlTrans;

它将确保 Now ExecuteNonQuery()将在同一个事务中执行,并且还将能够看到在同一个事务中对数据库所做的所有修改。

你得换一下这条线

SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() +
" WHERE Customer_ID=" + a + "", scon);

以这种方式

SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() +
" WHERE Customer_ID=" + a + "", scon, sqlTrans);

错误消息准确地说明了问题。 在代码到达该行之前,您已经打开了一个事务,并且该事务仍然在错误点处处于打开状态

.....
scon.Open();
SqlTransaction sqlTrans = scon.BeginTransaction();
.....

现在,需要通知连接有一个打开的事务时执行的每个 SqlCommand。事务不会由 Framework 自动设置。

可以使用 SqlCommand 构造函数,如上所述,也可以在执行命令之前设置 cmd.Transaction属性。

警告1

即使您直接从连接本身创建 SqlCommand,也需要为当前命令设置事务。

   SqlCommand cmd = scon.CreateCommand();
cmd.Transaction = sqlTrans; // Required when inside a transaction

警告2

在使用查询文本更新/插入/删除/选择数据库时,不惜一切代价避免 使用字符串连接。使用参数。这将消除奇怪或无效字符的问题,最重要的是,将防止 < a href = “ http://en.wikipedia.org/wiki/SQL _ jet”rel = “ norefrer”> Sql 注入攻击

string sqlText = "update Contact_Info set CustInfo=@info WHERE Customer_ID=@id";
SqlCommand cmd = new SqlCommand(sqlText, scon, sqlTrans);
cmd.Parameters.AddWithValue("@info", ds.GetXml());
cmd.Parameters.AddWithValue("@id",a);
cmd.ExecuteNonQuery();

此外,另一个建议是不要使用 AddWithValue,虽然方便,但是这个方法有很多问题,如 我的答案就在这里所解释的

如果你有这个代码:

SqlTransaction sqlTrans = scon.BeginTransaction();

那你还应该有这个:

cmd.Transaction = sqlTrans;

他们一起工作。

对于我来说,dapper 的 ExecuteAsync 有两个可选参数,而事务没有被识别,所以我必须这样命名参数:

var result = await a_mConn.ExecuteAsync(sql: a_sSqlQuery,transaction: a_mTransaction);