Is it OK to use a DataReader after disposing the Command object?

Question

I have created a small class of helper methods for working against a database. I'm wondering about this method:

Shared Function GetReader(sql As String, con As OleDbConnection) As OleDbDataReader

   Dim cmd As New OleDbCommand(sql, con)
   Dim reader As OleDbDataReader = cmd.ExecuteReader
   cmd.Dispose()
   Return reader

End Function

Am I going to run into some kind of trouble disposing the Command object first, then using the DataReader?

I'd prefer to keep calling code tidy by not creating/disposing Command objects all the time (seems redundant when that can be wrapped up in the helper method above).

Answer 1

Am I going to run into some kind of trouble disposing the Command object first, then using the DataReader?

No

But this is a cleaner code:

Shared Function GetReader(sql As String, con As OleDbConnection) As OleDbDataReader

   Using cmd As New OleDbCommand(sql, con)
      Dim reader As OleDbDataReader = cmd.ExecuteReader
      'cmd.Dispose() 'Not needed here, will be invoked automatically
      Return reader
   End Using

End Function

Side notes:

• Consider using Using Statements always for making sure all resources have been released

• Consider disposing the connection and reader once you are not needing them anymore. And don't be scared of closing then re-opening the connection again as connections are kept in a pool by default, and you should close them as soon as possible - especially if your application is under some form of load -