ADO.Net DataReader error: Already an Open DataReader

Question

Hello everyone,

I am using C# + .Net 3.5 + VSTS 2008 + ADO.Net + SQL Server 2008. And I am sharing one single SQL Connection object (TestDBConnection variable in my below sample) within my application.

The exception I met with is, "There is already an open DataReader associated with this Command which must be closed first.." Any ideas what is wrong?

The patterns within my application which I am using are all like this, i.e. sharing the single db connection object TestDBConnection, and using the single TestDBConnection variable to create command on it and execute store procedure.

        using (SqlCommand testCommand = new SqlCommand())
        {
            testCommand.Connection = TestDBConnection;
            testCommand.CommandType = CommandType.StoredProcedure;
            testCommand.CommandText = "prc_AddOrderStatus";
            testCommand.Parameters.Add("@orderID", SqlDbType.NVarChar).Value = orderID;
            testCommand.ExecuteNonQuery();
        }

thanks in advance, George

Answer 1

Don't share the connection, use connection pooling instead. If you are doing two things at the same time on the connection, you might want to look into MARS.

For a test add this to your connection string: ;MultipleActiveResultSets=True; and see if this "fixes" the error. A lot of people believe you should avoid using MARS, so this is something to consider.

Answer 2

George, is it possible that the exception is telling you the truth? Are there any other commands that you've started but not yet finished?

Answer 3

using (sqlConnection theconnection = new sqlconnection(initialise it))
{
 using (SqlCommand testCommand = new SqlCommand())
        {
            testCommand.Connection = theConnection
            testCommand.CommandType = CommandType.StoredProcedure;
            testCommand.CommandText = "prc_AddOrderStatus";
            testCommand.Parameters.Add("@orderID", SqlDbType.NVarChar).Value = orderID;
            testCommand.ExecuteNonQuery();
        }
}

is the pattern that i use in multi threaded cases with no problems at all.

Incidently this is connection pooling.