ExecuteReader requires command to have transaction when connection assigned to command is in pending local trans

Question

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

       }
    }
}

Answer 1

You might want to consider switching to using TransactionScope which is then used implicitly for all commands within it. You'd use it something like:

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

Answer 2

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