By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

Closing DataReader using ApplicationBlocks

P: n/a
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("C onnectionString"))

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
Nov 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
--
Joe Fallon


"Paolo Pignatelli" <Pa***@DotNetStore.com> wrote in message
news:e1**************@TK2MSFTNGP10.phx.gbl...
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("C onnectionString"))

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

Nov 19 '05 #2

P: n/a
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" <jf******@nospamtwcny.rr.com> wrote in message
news:un**************@TK2MSFTNGP14.phx.gbl...
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.
--
Joe Fallon


"Paolo Pignatelli" <Pa***@DotNetStore.com> wrote in message
news:e1**************@TK2MSFTNGP10.phx.gbl...
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("C onnectionString"))

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


Nov 19 '05 #3

P: n/a
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
--
Joe Fallon

"Paolo Pignatelli" <Pa***@DotNetStore.com> wrote in message
news:OZ**************@tk2msftngp13.phx.gbl...
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" <jf******@nospamtwcny.rr.com> wrote in message
news:un**************@TK2MSFTNGP14.phx.gbl...
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.
--
Joe Fallon


"Paolo Pignatelli" <Pa***@DotNetStore.com> wrote in message
news:e1**************@TK2MSFTNGP10.phx.gbl...
>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("C onnectionString"))
>
> 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
>
>



Nov 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.