ExecuteReader需要命令有交易时连接分配到的命令是在未决本地反 [英] ExecuteReader requires command to have transaction when connection assigned to command is in pending local trans
本文介绍了ExecuteReader需要命令有交易时连接分配到的命令是在未决本地反的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个表单交易，查询，它必须执行低于插入。其次越来越例外位于的 SqlDataReader的阅读= comm.ExecuteReader（）; 的
公共无效SqlExecuteNonQuery（客户OBJ）
{
//查询字符串=DECLARE @_customerID INT;
字符串QUERY1 =INSERT INTO客户（客户名称，customerSex，电子邮件）VALUES（'+ obj.name +，+ obj.sex +，+ obj.Email +'）;
//字符串query2 =SET @_customerID = @@身份;
字符串query3 =INSERT INTO CustomerDetails的（的customerID，customerAddress，customerPhone）VALUES（+ obj.id +，+ obj.address +，+ obj.phone +'）;
字符串CS =了ConnectionName;
使用（SqlConnection的康恩=新的SqlConnection（CS））
{
conn.Open（）;
使用（SqlCommand的命令=新的SqlCommand（选择电子邮件从客户那里电子邮件='+ obj.Email +'，康涅狄格州））
{
SqlDataReader的读卡器= Command.ExecuteReader却（）;
尝试
{
如果（reader.Read（））
{
抛出新的异常（用户的电子邮件已经存在）;
}
其他
{
reader.Close（）;
使用（CMD的SqlCommand = GetCommand（QUERY1，康涅狄格州））
{
的SqlTransaction交易;
交易= conn.BeginTransaction（）;
尝试
{
cmd.Transaction =交易;
cmd.ExecuteNonQuery（）;
使用（SqlCommand的通讯=新的SqlCommand（从客户选择，其中的customerID电子邮件='+ obj.Email +'，康涅狄格州））
{
SqlDataReader的阅读= comm.ExecuteReader（）;
尝试
{
而（read.Read（））
{
obj.id =（INT）阅读[0];
}
使用（SqlCommand的CMD1 = GetCommand（query3，康涅狄格州））
{
尝试
{
cmd1.ExecuteNonQuery（）;
}
赶上（例外EX1）
{
Console.WriteLine（COMIT异常类型：{0}，ex1.GetType（））;
Console.WriteLine（错误插入 - {0}，ex1.Message）;
尝试
{
transaction.Rollback（）;
}
赶上（例外EX2）
{
Console.WriteLine（回滚异常类型：{0}，ex2.GetType（））;
Console.WriteLine（消息：{0}，ex2.Message）;
}
}
}
器transaction.commit（）;
Console.WriteLine（全成交易）;
}
赶上（例外前）
{
Console.WriteLine（错误类型：ex.GetType（））;
Console.WriteLine（消息：，ex.Message）;
}
最后 {
read.Close（）;
}
}
}
赶上（例外前）
{
Console.WriteLine（COMIT异常类型：{0}，ex.GetType（））;
Console.WriteLine（错误插入 - {0}，ex.Message）;
尝试
{
transaction.Rollback（）;
}
赶上（例外EX2）
{
Console.WriteLine（回滚异常类型：{0}，ex2.GetType（））;
Console.WriteLine（消息：{0}，ex2.Message）;
}
}
最后
{
transaction.Dispose（）;
}
}
}
}
赶上（例外前）
{
Console.WriteLine（ex.Message）;
}
}
}
}
解决方案
问题是你在相同的连接CMD执行CMD1所以该连接上打开的事务，但您没有设置cmd1.Transaction ...等等的解决办法是
cmd1.Transaction =交易;
在
cmd1.ExecuteNonQuery（）;
i have to insert in two tables with single transaction, query which have to implement are below. secondly getting exception at SqlDataReader read = comm.ExecuteReader();
public void SqlExecuteNonQuery(Customer obj)
{
//string query = "DECLARE @_customerID int ";
string query1 = "INSERT INTO customer (customerName,customerSex,Email) VALUES ('" + obj.name + "','" + obj.sex + "','" + obj.Email + "') ";
//string query2 = "SET @_customerID =@@identity ";
string query3 = "INSERT INTO customerDetails(customerID,customerAddress,customerPhone) VALUES (" + obj.id + ",'" + obj.address + "','" + obj.phone + "') ";
string CS = ConnectionName;
using (SqlConnection conn = new SqlConnection(CS))
{
conn.Open();
using (SqlCommand command = new SqlCommand("SELECT Email FROM Customer where Email ='" + obj.Email + "'", conn))
{
SqlDataReader reader = command.ExecuteReader();
try
{
if (reader.Read())
{
throw new Exception("User already exist for the email");
}
else
{
reader.Close();
using (SqlCommand cmd = GetCommand(query1, conn))
{
SqlTransaction transaction;
transaction = conn.BeginTransaction();
try
{
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
using (SqlCommand comm = new SqlCommand("Select customerID from Customer where email = '" + obj.Email + "'", conn))
{
SqlDataReader read = comm.ExecuteReader();
try
{
while (read.Read())
{
obj.id = (int)read[0];
}
using (SqlCommand cmd1 = GetCommand(query3, conn))
{
try
{
cmd1.ExecuteNonQuery();
}
catch (Exception ex1)
{
Console.WriteLine("Comit Exception Type: {0}", ex1.GetType());
Console.WriteLine("error in inserting - {0}", ex1.Message);
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
Console.WriteLine("RollBack Exception Type: {0}", ex2.GetType());
Console.WriteLine("Message: {0}", ex2.Message);
}
}
}
transaction.Commit();
Console.WriteLine("Successfull transaction");
}
catch (Exception ex)
{
Console.WriteLine("Error type:", ex.GetType());
Console.WriteLine("Message:", ex.Message);
}
finally {
read.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine("Comit Exception Type: {0}", ex.GetType());
Console.WriteLine("error in inserting - {0}", ex.Message);
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
Console.WriteLine("RollBack Exception Type: {0}", ex2.GetType());
Console.WriteLine("Message: {0}", ex2.Message);
}
}
finally
{
transaction.Dispose();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
解决方案
problem is you execute cmd1 on the same connection as cmd so there is an open transaction on that connection but you don't set cmd1.Transaction ... so solution would be to
cmd1.Transaction = transaction;
before
cmd1.ExecuteNonQuery();
这篇关于ExecuteReader需要命令有交易时连接分配到的命令是在未决本地反的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
