CODEWITHSUNDEEP
c#database-connectionsql-server-2008-r2
StatsViaCsh
StatsViaCsh

Reputation: 2640

C#/ SQL Server error 'There is already an open DataReader associated with this Command which must be closed first.'

So, I'm getting the error as per my title line. Seems pretty self- explanatory, but my understanding is that objects within the "using" block are disposed of? This error appeared after another minor bug interrupted code execution, so perhaps I'm stuck with an open reader that I need to close or shut down? Any help would be appreciated?

public override long GetStatsBenchmark(String TableName)
{
    using (SqlCommand cmd = new SqlCommand("sprocReturnDataPointBenchmark", this.sqlConnection))
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        SqlParameter outputParameter = new SqlParameter
        {
            ParameterName = "@benchmark",
            Direction = System.Data.ParameterDirection.Output,
            SqlDbType = System.Data.SqlDbType.BigInt,
        };
        cmd.Parameters.Add(outputParameter);
        SqlParameter inputParameter = new SqlParameter
        {
            ParameterName = "@tblName",
            Direction = System.Data.ParameterDirection.Input,
            SqlDbType = System.Data.SqlDbType.NVarChar,
            Value = TableName
        };
        cmd.Parameters.Add(inputParameter);
        cmd.ExecuteNonQuery();

        return (long)outputParameter.Value;
    }
}

Upvotes: 0

Views: 1301

Answers (3)

AndreaCi
AndreaCi

Reputation: 829

You can add MARS (multiple active result sets) to your connection string, to allow multiple recordset (or similar) open at the same time.

Add

MultipleActiveResultSets=true;

or

MARS Connection=True;

to your connection string (http://www.connectionstrings.com/sql-server-2008) and this will allow multiple handles, but if the previous operation has been "interrupted" in an exception, try avoid using "USING" or use a try/catch inside it.

Upvotes: 0

mson
mson

Reputation: 7824

An issue with the using clause is that it does not provide a way to process exceptions in the implicit try/finally block that the compiler generates for you.

You can 1) wrap your using clause with a try/catch, or 2) manually code a try/catch/finally instead of using, calling Dispose in the finally block and adding exception handling in a catch block.

There are slight drawbacks to either technique, but either will work

Upvotes: 1

John Jeffery
John Jeffery

Reputation: 1020

I do not think that the code you have shown is the cause of the problem. While it is good practice to dispose of SqlCommand (IDbCommand) objects, in practice I have not found it to be necessary. What is necessary is to dispose of SqlDataReader (IDataReader) objects after you have finished using them. As the error message suggests, look for a usage of an SqlDataReader object in your code that is not being disposed. The exception may be thrown from the code you are displaying, but I would suspect that the cause is because of an SqlDataReader associated with the same SqlConnection used earlier in the program execution that has not been disposed.

Upvotes: 1

Related Questions