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))
{
  connection.Open();
  using (SqlCommand command = connection.CreateCommand()) 
  {
    command.CommandText = @"
      select USERNAME from USERS where GROUP = @GID";
    command.Parameters.Add("@GID", SqlDbType.Int);
    command.Prepare();
    command.Parameters["@GID"].Value = nGroupId;
    using (SqlDataReader reader = command.ExecuteReader())
    {
      while (reader.Read())
      {
        String sUsername = reader.GetString(0); // USERNAME
        // ...
      }
    }
  }
}

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: