Fixing error - There is already an open DataReader associated with this Command which must be closed first

Question

In a C# script, I try to read a table with a SqlDataReader object and then drop the table. Its really that simple.

This is the code I use -

SqlConnection conn = getAWorkingDbConnection();//Always gives me a good connection
SqlCommand sqlCmd = new SqlCommand();
SqlDataReader dataReader;

sqlCmd.CommandTimeout = 0;
sqlCmd.Connection = conn;

sqlCmd.CommandText = "SELECT * FROM GlassTable";
dataReader = sqlCmd.ExecuteReader();

//Code to read rows with SqlDataReader and print them to a file.

sqlCmd.CommandText = "DROP TABLE GlassTable";
sqlCmd.ExecuteReader();// BAD !!!

I get this error - System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

I saw the API for the ExecuteReader method, but it does not answer my problem. Why does this error happen and how do I fix it ?

Thanks.

Answer 1

Your problem is that you are not disposing of the objects you are using. For that purpose is better to always use using structure, since it will guarantee you that everything is gonna is disposed of. Try the code below:

sqlCmd.CommandText = "SELECT * FROM GlassTable";
using (dataReader = sqlCmd.ExecuteReader())
{
    //Code to read rows with SqlDataReader and print them to a file.
}

Furthermore, you don't have to use ExecuteReader on a query that does not return records.

sqlCmd.CommandText = "DROP TABLE GlassTable";
int recordsAffected = sqlCmd.ExecuteNonQuery();