ExecuteNonQuery要求该命令在我的代码中出现事务错误
本文关键字：事务 错误 代码 我的 命令 ExecuteNonQuery | 更新日期: 2023-09-27 18:34:44
我在cmd.ExecuteNonQuery上收到以下错误。
"ExecuteNonQuery要求命令在以下情况下具有事务 分配给命令的连接位于挂起的本地事务中。 命令的事务属性尚未初始化。
这是我的代码：
//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();
}
告诉我我的代码中有什么问题以及如何解决它。
您需要更改此行
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 都需要通知这一点。事务不是由框架自动设置的。
如上所述，可以使用 SqlCommand 构造函数，也可以在执行命令之前设置 cmd.Transaction 属性。
警告 1
即使直接从连接本身创建 SqlCommand 也是如此，也需要为当前命令设置事务。
SqlCommand cmd = scon.CreateCommand();
cmd.Transaction = sqlTrans; // Required when inside a transaction
警告 2
在使用查询文本更新/插入/删除/选择数据库时，不惜一切代价避免使用字符串串联。使用参数。这将消除奇怪或无效字符的问题，最重要的是，将防止SqlInjection攻击
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，虽然很方便，但这种方法有很多问题，正如我在这里的回答中所解释的那样
您启动了一个在调用cmd.ExecuteNonQuery()之前未提交的事务。
只需在cmd.ExecuteNonQuery();之前写下cmd.Transaction = sqlTrans;
它将确保Now ExecuteNonQuery()将在同一事务中执行，并且还能够在同一事务中看到对数据库所做的所有修改。
如果您有此代码：
SqlTransaction sqlTrans = scon.BeginTransaction();
那么你也应该有这个：
cmd.Transaction = sqlTrans;
他们一起工作。
对我来说
，dapper 的 ExecuteAsync 需要 2 个可选参数，并且无法识别事务，所以我不得不像这样命名参数：
var result = await a_mConn.ExecuteAsync(sql: a_sSqlQuery,transaction: a_mTransaction);