Cannot get rid of error "There is already an open DataReader associated with this Command which must be closed first"

Question

I am pulling data from four different tables on the same database, and am attempting to merge these data into a single HTML object. The problem is I keep getting the "open DataReader" exception above.

Things I have tried:

• Closing the reader with reader.Close() after each if(reader.HasRows) block
• Declaring the SqlCommand and SqlDataReader locally to the try/catch
• Adding a finally statement at the end of each try/catch that closes the reader (this one complained that the reader variable hasn't been initialized)

None of the above work. All of the solutions I have found say to use MultipleActiveResultSets, but I don't actually want multiple result sets.

What are best practices to use in a situation like this? Declare four different reader variables with four different names, reader1, reader2, etc?

Edit

Here's a sanitized and shortened version of what I'm doing

string connectionString = ConfigurationManager.ConnectionStrings["database"].ToString();
DataTable dt = new DataTable();
SqlCommand cmd;
SqlDataReader reader;
SqlConnection conn;

using (conn = new SqlConnection(connectionString))
{
    try
    {
        conn.Open();
        cmd = new SqlCommand("SELECT * FROM [table-one]", conn);
        reader = cmd.ExecuteReader();
        if (reader.HasRows)
        {
            // Get a temporary copy of the data in a data table
            dt.Load(reader);

            // Do database things on table-one
        }
    }
    catch (Exception ex)
    {
        // Exception caught
        ThrowException(ex);
    }

    try
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM [table-two] WHERE UserID = @uID", conn);
        cmd.Parameters.Add(new SqlParameter("uID", User_ID));
        SqlDataReader reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            // Get a temporary copy of the data in a data table
            dt.Load(reader);

            // Do database things on table-two
        }
    }
    catch (Exception ex)
    {
        // Exception caught
        ThrowException(ex);
    }

Answer 1

Here you are calling second data reader without closing your first data reader object .use this for close relevant transactions belongs to your relevant data reader

cmd = new SqlCommand("SELECT * FROM [table-one]", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
    if (reader.HasRows)
    {
        // Get a temporary copy of the data in a data table
        dt.Load(reader);

        // Do database things on table-one
    }
    reader.Close();  
}

Answer 2

You do not close SqlDataReader. Always call Close when done reading or execute the whole thing in a Using statement.

Example

DataTable dt = new DataTable();
//SqlCommand cmd;
//SqlDataReader reader;
//SqlConnection conn;

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd;

    conn.Open();

    cmd = new SqlCommand("SELECT * FROM [table-one]", conn);
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            // Get a temporary copy of the data in a data table
            dt.Load(reader);

            // Do database things on table-one
        }
    } 

    cmd = new SqlCommand("SELECT * FROM [table-two] WHERE UserID = @uID", conn);
    cmd.Parameters.Add(new SqlParameter("uID", User_ID));
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            // Get a temporary copy of the data in a data table
            dt.Load(reader);

            // Do database things on table-two
        }
    }

}

Answer 3

In general there are two reasons for this error (at least I haven't noticed any other so far):

The first is not properly closing a Reader. This can be resolved pretty easily with using:

using (SqlConnection connection = new SqlConnection("connectionString"))
{
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "select getdate()";
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                //do your stuff...
            }
        }
    }
}

The second reason is concurrency. If you want to read additional data, from the same thread or another thread, like the following:

using (SqlConnection connection = new SqlConnection("connectionString"))
{
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "select columns from accounts";
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                //fetch additional data for each record.
                using (SqlCommand innerCommand = connection.CreateCommand())
                {
                    innerCommand.CommandText = "select * from sales where account_id = @account_id";
                    using (SqlDataReader innerReader = innerCommand.ExecuteReader())
                    {
                        while (innerReader.Read())
                        {
                            //read sales data    
                        }
                    }
                }
            }
        }
    }
}

You need to enable MultipleActiveResultSets. This way SqlServer allows several open dataReader on a single connection. From your code it seems that you're running into the first issue...

Answer 4

You need a separate connection to the database for each command/reader you are using. Please post the code which has the problem, it will make it easier to give a more specific answer.

If you are combining data from four tables, why not do that in one SQL statement? You will then only need one connection and one command.

Although you say

I don't actually want multiple result sets

running four separate queries will give four separate results.

EDIT

You have declared the SQLSqlCommand outside your try/catch blocks and it does not appear to be closed/cleaned up. Move the declaration in to the block where it's used:

//SqlCommand cmd;       <<-- remove
//SqlDataReader reader; <<-- remove
SqlConnection conn;

using (conn = new SqlConnection(connectionString))
{
    try
    {
        conn.Open();

        // Add the variable declarations here
        SqlCommand cmd = new SqlCommand("SELECT * FROM [table-one]", conn);
        SqlDataReader reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            // Get a temporary copy of the data in a data table
            dt.Load(reader);

            // Do database things on table-one
        }
    }
    catch (Exception ex)
    {
        // Exception caught
        ThrowException(ex);
    }
    ...

Answer 5

Instead of this:

    cmd = new SqlCommand("SELECT * FROM [table-one]", conn);
    reader = cmd.ExecuteReader();
    if (reader.HasRows)
    {
        // Get a temporary copy of the data in a data table
        dt.Load(reader);

        // Do database things on table-one
    }

try:

using (cmd = new SqlCommand("SELECT * FROM [table-one]", conn))
{
     using (reader = cmd.ExecuteReader())
     {
         if (reader.HasRows)
         {
             // Get a temporary copy of the data in a data table
             dt.Load(reader);
         }
     }
}

// Do database things on table-one

I.e. close/dispose each reader before doing any more database stuff.