"There is already an open DataReader associated with this Command which must be closed first" explained
Short one.
This sometimes catches people out.
When reading a SqlDataReader and then after the read is done, there is an attempt to use the connection that was used for the reading.
The exception thrown is (in this case it is thrown when trying to update a table using the same connection that was used for the reader):
System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Let’s do this with an example. First create a table and insert some rows:
create table SqlDataReaderTable (cid int identity, ctext nvarchar(20))
insert into SqlDataReaderTable values ('Row One')
insert into SqlDataReaderTable values ('Row Two')
--drop table SqlDataReaderTable
Then create a new C# console application:
string cs = @"Data Source=<your server>;Integrated Security=SSPI;Initial Catalog=<your database>";
string sql = "SELECT cid, ctext FROM SqlDataReaderTable";
try
{
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("cid: {0}, ctext: {1}", rdr[0].ToString(), rdr[1].ToString());
}
// Now, trying to use the connection again will throw exception.
string update = "UPDATE SqlDataReaderTable SET ctext = 'New Value' WHERE cid = 1";
cmd.CommandText = update;
cmd.ExecuteNonQuery();
con.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
And run it, this will give the exception above.
So, what is happening? Most of the ADO.Net classes and design are disconnected. For example; setup your dataset, fill it and then close the connection. Now you can manipulate the data
and do whatever you like, then when you are done you simply commit the changes to the database, this will once again connect and commit and then disconnect.
However, the SqlDataReader (and the other DataReaders) is not disconnected. From:
".NET Framework Developer's Guide DataReaders (ADO.NET)"
https://msdn.microsoft.com/en-us/library/ms254509.aspx
“You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database.
Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader”
This means that as long as the reader is open it has an associated and opened connection. It also means that as long as the reader is open then the connection is dedicated to the
command that is used for the reader. Simple as that. So all you have to do in order to avoid this exception is to remember to close the reader when done.
Either do it explicitly:
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("cid: {0}, ctext: {1}", rdr[0].ToString(), rdr[1].ToString());
}
// Explicitly close the DataReader.
rdr.Close();
Or execute the whole thing in a Using statement (my recommendation):
SqlCommand cmd = new SqlCommand(sql, con);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Console.WriteLine("cid: {0}, ctext: {1}", rdr[0].ToString(), rdr[1].ToString());
}
}
Since the DataReader is a lightweight object that provides read-only, forward-only data, it is very fast (faster than for example DataAdapter.Fill()).
However, since it is read-only no updates can be done with a DataReader and since it is forward-only, a record that has been read can’t be returned to.
And as discussed above, it requires the exclusive use of an active connection as long as it is open.
".NET Framework Class LibrarySqlDataReader Class"
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx
".NET Framework Developer's GuideADO.NET