当分配给命令的连接处于挂起的本地传输状态时，ExecuteReader 要求命令具有事务 [英] ExecuteReader requires command to have transaction when connection assigned to command is in pending local trans

问题描述

我必须在两个表中插入单个事务，必须执行的查询如下.其次在 SqlDataReader read = comm.ExecuteReader();

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);
           }

       }
    }
}

推荐答案

问题是您在与 cmd 相同的连接上执行 cmd1，因此该连接上有一个打开的事务，但您没有设置 cmd1.Transaction ... 所以解决方案是

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;

之前

cmd1.ExecuteNonQuery();

这篇关于当分配给命令的连接处于挂起的本地传输状态时，ExecuteReader 要求命令具有事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！