title: C# : there is already an open DataReader associated with this command
body: <p>I currently have a problem with multiple <code>SqlDataReader</code> and commands in a single method. This method should delete a customer + its related address, networks, ipaddresses... </p>
  
  <p>When I execute the code, I get an error</p>
  
  <blockquote>
    <p>There is already an open DataReader associated with this Command which must be closed first</p>
  </blockquote>
  
  <p>So I googled a little bit and read, that the </p>
  
  <pre><code>using(SqlDataReader....)
  </code></pre>
  
  <p>and adding <code>MultipleActiveResultSets=True</code> to the connection string should help.</p>
  
  <p>I'm using SQL Server 2014, I heard that there is a problem with SQL Server 2005, so that shouldnt be a problem..</p>
  
  <p>But it still doesn't work... </p>
  
  <p>The exception is thrown at </p>
  
  <pre><code>var addressId = (int)command.ExecuteScalar();
  </code></pre>
  
  <p>Connection string:</p>
  
  <pre><code>Data Source=.\\DATABASE;Initial Catalog=customer;Persist Security Info=True;MultipleActiveResultSets=True;User ID=sa;Password=xxxxxxxx!
  </code></pre>
  
  <p>Code:</p>
  
  <pre><code>public static Boolean ExecuteDeleteCutomer(string customerId) {
      using (SqlConnection connection = new SqlConnection(new DatabaseConnection().ConnectionString)) {
          connection.Open();
  
          SqlCommand command = connection.CreateCommand();
          SqlTransaction transaction;
          SqlDataReader locationReader;
          SqlDataReader networkReader;
          SqlDataReader ipaddressReader;
  
          // Start a local transaction to delete a customer and related table entries.
          transaction = connection.BeginTransaction("StartTransaction DeleteCustomer");
  
          // Must assign both transaction object and connection
          // to Command object for a pending local transaction
          command.Connection = connection;
          command.Transaction = transaction;
  
          try {
              //First get the locations of selected customer
              command.Parameters.AddWithValue("@customerId", customerId);
              command.CommandText =
                      "SELECT l_id from location where c_id = @customerId";
              locationReader = command.ExecuteReader();
  
              using (locationReader) { //save location ids in a reader
                  while (locationReader.Read()) {
                      var locationID = locationReader.NextResult();
                      command.Parameters.AddWithValue("@locationId", locationID);
                      command.CommandText =
                          "SELECT a_id from address where l_location = @locationId";
                      var addressId = (int)command.ExecuteScalar(); // get address ID to delete later
  
                      command.Parameters.AddWithValue("@addressId", addressId);
                      command.CommandText = "SELECT n_id from network where n_location = @locationId";
  
                      using (networkReader = command.ExecuteReader()) { // save networks in a reader;
                          while (networkReader.Read()) {
                              var networkId = networkReader.NextResult();
                              command.Parameters.AddWithValue("@networkId", networkId);
                              command.CommandText = "SELECT ip_id from ipaddress where n_id = @networkId";
  
                              using (ipaddressReader = command.ExecuteReader()) { // get ipaddressId ID to delete later
                                  while (ipaddressReader.Read()) {
                                      var ipaddressId = ipaddressReader.NextResult();
                                      command.Parameters.AddWithValue("@ipId", ipaddressId);
                                      command.CommandText = "Delete from ipaddress where ip_id = @ipId; ";
                                      command.ExecuteScalar();
                                  }
                              }
  
                              command.CommandText = "Delete from network where n_id = @networkId; ";
                              command.ExecuteScalar();
                          }
                      }
  
                      command.CommandText = "Delete from location where l_id = @locationID; ";
                      command.ExecuteScalar();
                  }
              }
  
              command.CommandText = "Delete from customer where c_id = @customerId; ";
              command.ExecuteScalar();
  
              // Attempt to commit the transaction.
              transaction.Commit();
  
              return true;
          } 
          catch (Exception ex) {
              Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
              Console.WriteLine("  Message: {0}", ex.Message);
  
              // Attempt to roll back the transaction.
              try {
                  transaction.Rollback();
              } 
              catch (Exception ex2) {
                  // This catch block will handle any errors that may have occurred
                  // on the server that would cause the rollback to fail, such as
                  // a closed connection.
                  Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                  Console.WriteLine("  Message: {0}", ex2.Message);
              }
  
              return false;
          }
      }
  }
  </code></pre>
  
  <p>Thanks!</p>