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.
Best Answer
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:Furthermore, you don't have to use
ExecuteReader
on a query that does not return records.