HELP: Custom DataReader Function - Tricky issue

[VB Programmer]

I have a function that returns a datareader. The problem is that in the
function's FINALLY I close the datareader and set it to nothing. But,
BEFORE the Finally I "Return" the datareader (in the Try).

The problem is, by the time it's "returned" it says I already closed it. Any
ideas how I can overcome this? How can I clean up the objects nicely WHILE
returning it back to the caller????

Example:

Private Sub x
Dim dr as SqlDataReader
....
....
dr = GetDr(...) ' FAILS BECAUSE THE DATAREADER IS CLOSED BY NOW!
....
End Sub

Private Function GetDr(...) as SqlDataReader
Dim MyDataReader as SqlDataReader
Try
.....
....
Return MyDataReader
Catch
Finally
MyDataReader.Close
MyDataReader = Nothing
End Function

 

[Marina]

You can't. That's why you don't want to be returning datareaders. Because
then you are relying on the client to close the datareader and the
underlying connection.

Use a datatable or dataset instead.

 

[John Saunders]

I have a function that returns a datareader. The problem is that in the
function's FINALLY I close the datareader and set it to nothing. But,
BEFORE the Finally I "Return" the datareader (in the Try).

The problem is, by the time it's "returned" it says I already closed it. Any
ideas how I can overcome this? How can I clean up the objects nicely WHILE
returning it back to the caller????

Why are you returning it to the caller? So the caller can use it? But he
can't use it if it's closed!

And, btw, you don't need to set it to Nothing! It's a total, worthless,
waste of time and energy, so stop doing it! The variable itself is going to
disappear w hen you exit the function!

 

[JohnG]

Don't close the datareader in the GetDr function. Close the datareader in
the Sub X when you finish reading it.

 

[Natty Gur]

Hi,

1) As others say don’t close the dataReader on the "server" side let the
"client" do the work just use the CommandBehavior to set that the
connection will be closed when the datareader will be closed:

System.Data.SqlClient.SqlDataReader = oc.ExecuteReader
(System.Data.CommandBehavior.CloseConnection );

2) I personally prefer Data Access Layer (DAL) classes create by
programmers or any ORM tool over datasets. I won’t recommend to switch
to DataSet or to return DataReader to presentation layer (since both of
them holds database structure). You can create your own data structure
fill it by iterating readerReader and return the data structure to the
caller.

Natty Gur, CTO
Dao2Com Ltd.
34th Elkalay st. Raanana
Israel , 43000
Phone Numbers:
Office: +972-(0)9-7740261
Fax: +972-(0)9-7740261
Mobile: +972-(0)58-888377