Geek asks and answers

Commit error. There is already an open DataReader associated with this Command, which should be closed first

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

This is the code I'm using -

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 already exists an associated DataReader open with this command, which should be closed first.

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

Thank.

+3
source share
3 answers

You do not close the reader after use:

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

In addition, you do not need to use ExecuteReaderfor a query that does not return records.

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

using -statement, :

using(SqlConnection conn = getAWorkingDbConnection())
using (SqlCommand sqlCmd = new SqlCommand("SELECT * FROM GlassTable", conn))
{
    sqlCmd.CommandTimeout = 0;
    conn.Open();
    using (SqlDataReader dataReader = sqlCmd.ExecuteReader())
    {
        while (dataReader.Read())
        { 
            // do something useful ...
        }
    }
    sqlCmd.CommandText = "DROP TABLE GlassTable";
    sqlCmd.ExecuteNonQuery();
}
+2

.

- using :

using (var dataReader = sqlCmd.ExecuteReader())
{
      //Stuff
}

, , Close() try...finally.

+1

More articles:


All Articles