执行ExecuteReader需要命令具有事务，当分配给命令的连接处于挂起本地事务状态时。

Question

我必须使用单个事务在两个表中插入数据，需要实现的查询如下。其次，在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);
           }

       }
    }
}

- Hassaan

---

新用户未回答直到8个小时。异常清楚地给出了答案。只需要执行sqlcommand对象的事务:D在SqlDataReader reader = comm.ExecuteReader();之前添加comm.Transaction = transaction; - Hassaan

Answer 1

问题在于您在与cmd相同的连接上执行了cmd1，因此该连接上存在一项未关闭的事务，但您没有设置cmd1.Transaction... 所以解决方案是

cmd1.Transaction = transaction;

cmd1.ExecuteNonQuery();

- PrfctByDsgn

Answer 2

您可能希望考虑切换到使用TransactionScope，它会隐式地用于其中的所有命令。您可以像这样使用它：

using(var scope = new TransactionScope())
{
   using(var conn = new SqlConnection(/*...*/))
   {
      //As many nested commands, etc, using the above connection.
      //but don't need to create a SqlTransaction object nor
      //in any way reference the scope variable
   }
   scope.Complete();
}

- Damien_The_Unbeliever

---

不要在不指定TransactionOptions的情况下使用new TransactionScope()：http://blogs.msdn.com/b/dbrowne/archive/2010/05/21/using-new-transactionscope-considered-harmful.aspx - Remus Rusanu

链接已失效 @RemusRusanu - kuklei