Is it ok to dipose a DbCommand before iterating the DbDataReader

Question

I have a simple application that needs to execute certain queries to get the database schema information. I have written a simple method that executes a query and returns a reader, something like this -

public static DbDataReader ExecuteQuery(DbConnection connection,string sql)
{
   DbCommand command = connection.CreateCommand();
   command.CommandText = sql;

   using(command)
   {
      return command.ExecuteReader();    
   }
}

The calling code does close the connection and dispose the reader and connection appropriately.

My question - Is it ok/right to dipose the command instance (as is done via the using block) before iterating the reader? I do not expect any OUT parameters to be populated after closing the reader. Does the ADO.NET API have any strict guidelines regarding this?

Swordtail

Answer 1

When you leave the using block in your method command is closed and disposed, if you are able to use the reader from the caller means it still work.

Commands are a mean to execute statements against a connection but do not hold any data this is the reason why this works. As long as the connection is open you can use your reader.

PS. there is also a nice overload of ExecuteReader which instructs the Reader to close the connection directly for you on disposal, useful when connection is created locally like you do with the command and not passed from outside.

Dottiedottle

This sounds a bit like a trial and error approach though, can you guarantee that readers from all ADO.NET implementations ignore the command's state? It is technically possible to make it dependent, as the command is the only object able to create the reader. – Oscan

@Davide - ya I know the 'using' is disposing the command and hence the question. The problem is that the code seems to work for certain drivers but not all! – Swordtail

No is not try error approach is the way so many data layers are done. Mostly command object is creared and with using or not disposed in the method anyway and reader is accessible and usable outside. If this did not work there would be so many broken DAL out there! – Dottiedottle

actually, the command holds the parameter values; and out / return parameter values are only updated at the end of the TDS stream, i.e. when the reader gets to the end. So technically, the reader does talk back to state of the command. However, I suspect it'll work fine either way. – Utterance

Marc if you want to read an out param you surely are still in the method and command is still there as you wont be able to access it from calling method if you only got back the reader. – Dottiedottle

@Davide - As Marc mentioned, the OUT/return parameters are populated with appropriate values only after the reader is closed, which obviously happens somewhere outside in the calling code. So although I can access the command in the method the parameters won't have the right value. Side note - OUT/return parameters are of no interest in my use case – Swordtail