Mental Jetsam

By Peter Finch

Create nested SqlCommands on a single SqlConnection in C#

Posted by pcfinch on November 17, 2008

When trying to create multiple SqlCommand Objects on a single SqlConnection I got the following error.

There is already an open DataReader associated with this Command which must be closed first.

I was trying to use an SqlDataReader, from the first SqlCommand, and use the data in the records in that reader to perform another query by creating another [nested] SqlCommand object while the first reader was still running. After a little research the solution is very simple. Just add “MultipleActiveResultSets=True” to the connection string.

Data Source=server\PORTAL;Initial Catalog=mydb;User ID=user;Password=password;MultipleActiveResultSets=True

Warning… Don’t forget to Close() the inner SqlDataReader and Dispose() of the SqlCommand otherwise you may quickly run out of resources, or better still use the “using(…) { … }” statement. e.g.

using (SqlConnection connection = new SqlConnection(sConnectionString))
  using (SqlCommand command = connection.CreateCommand()) 
    command.CommandText = @"
      select USERNAME from USERS where GROUP = @GID";
    command.Parameters.Add("@GID", SqlDbType.Int);
    command.Parameters["@GID"].Value = nGroupId;
    using (SqlDataReader reader = command.ExecuteReader())
      while (reader.Read())
        String sUsername = reader.GetString(0); // USERNAME
        // ...


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: