Bad to use datareader in middle tier?

[Ronald S. Cook]

In my middle tier, I, of course, often return data to the presentation
layer. When needing a forward-only, read-only list, I think of the
SqlDataReader (of course). However, since you can't close the connection
before returning the data, the line at the end of the function is what I've
found to use (i.e. CommandBehavior.CloseConnection). HOWEVER, I've heard
this sometimes doesn't always work properly and I should stick to using
DataTables instead.



Have you heard this? Or, do you think the below is just fine?



*****************************************************

Public Function UserList(ByVal iclientno As Integer) As SqlDataReader



'Return the list of userid from the user table based on a client number.



Dim dbConnection As New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringKamtechLogin"))

Dim spCommand As New SqlCommand("spGetUsers", dbConnection)

spCommand.CommandType = CommandType.StoredProcedure



Dim paramClientNo As New SqlParameter("@ClientNo", SqlDbType.Int)

spCommand.Parameters.Add(paramClientNo)

paramClientNo.Direction = ParameterDirection.Input

paramClientNo.Value = iclientno



dbConnection.Open()



Dim dr As SqlDataReader =
spCommand.ExecuteReader(CommandBehavior.CloseConnection)



Return dr



End Function

*****************************************************



Thanks,

Ron

 

[sloan]

See my two blogs:

http://spaces.msn.com/sholliday/ 6/5/2006 , 5/24/2006

My preference is to return
DataSets (typed ususally)

or a collection (or <List> in 2.0) of objects. for that simple reason....
the lack of a guaranteed .Close()

(too risky in my opinion)

...

 

[sloan]

PS.

I don't think it bad to ~use/consume them in the biz layer.

I think its a bad idea to push them on up to the presentation layer.


I wouldn't say I'd 100% never to do it. You have to evaluate who writes
your presentation code.

If its just you, then ..... maybe.

Its its not just you, then..... I'd err on the side of not passing them up.


...

 

[Guest]

I agree with sloan. One of the most common posts we get on our forums (and
also that I see here) is running out of connection pool because of rampant
DataReaders that have never been closed.
If you want, you can convert the DatarReader into a DataTable, return that,
and close the reader and the connection before your data ever gets outside
the method it was created in.
Peter

 

[Mark Rae]

I agree with sloan. One of the most common posts we get on our forums (and
also that I see here) is running out of connection pool because of rampant
DataReaders that have never been closed.

Sure, but that doesn't necessarily mean that DataReaders in and of
themselves are bad just because some developers don't know how to close them
properly...

 

[Guest]

Mark,
I didn't say DataReaders were "bad"; I even suggested using a DataReader to
get a DataTable and then closing the connection before returning same. We
all are aware that the "non-disconnected" behavior of the DataReader lends
itself to developer error.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com