Closing DataReader using ApplicationBlocks

[Paolo Pignatelli]

I have a Function in a Class File that uses ApplicationBlocks:

Public Function GetProductsByCategory(ByVal CategoryID As Integer)

Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim ProductsByCategory As SqlDataReader =
SqlHelper.ExecuteReader(myConnection, CommandType.StoredProcedure,
"ProductsByCategory", New SqlParameter("@CategoryID", CategoryID))

Return ProductsByCategory

End Function



I then access this in a different Tier...(instantiate, etc...) blah blah.

QUESTION: Am I closing the DataReader? I know that this code works, but
occasionally I get faults, and one explanation is that I have not closed
something .

Using a DataSet, (I believe) that I need not worry about this, but how much
am I sacrificing in efficiency by using a DataSet rather than a DataReader?

Any and all suggestions welcome, and

TIA,



Paolo

 

[Joe Fallon]

Your function retrieves a dr.

Your calling code is responsible for closing the dr.
You should always have a Try Finally block and close the dr in the finally
block.

e.g.
If Not dr Is Nothing Then dr.Close.

Also, you are not closing the connection when you close the dr.
The connection will only be closed if you use code like this when you create
the dr:
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

If you do not use code like the above thne include the cn close code in your
finally block.
e.g.
If Not cn Is Nothing Then cn.Close.

 

[Paolo Pignatelli]

Thank you extremely much.

Could you indicate please how the Try, Catch, finally construct would be
applied to my code example? I can not get the Finally construct to work.

 

[Joe Fallon]

Be sure that SqlHelper.ExecuteReader uses CommandBehavior.CloseConnection so
you need be concerned about closing the connection when you close the dr.

Your function signature is missing the As SqlDataReader :

Public Function GetProductsByCategory(ByVal CategoryID As Integer)

should be:

Public Function GetProductsByCategory(ByVal CategoryID As Integer) As
SqlDataReader
=============================

Dim dr as SqlDataReader

Try
dr=GetProductsByCategory(someID)
'use dr here
Finally
If Not dr Is Nothing Then dr.Close
End Try