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("C onnectionStringKamtechLogin"))

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.CloseConne ction)

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)

...


"Ronald S. Cook" <rc***@westinis.com> wrote in message
news:uw**************@TK2MSFTNGP04.phx.gbl...

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("C onnectionStringKamtechLogi
n"))
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.CloseConne ction)

Return dr

End Function

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

Thanks,

Ron

[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.
...
"Ronald S. Cook" <rc***@westinis.com> wrote in message
news:uw**************@TK2MSFTNGP04.phx.gbl...

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("C onnectionStringKamtechLogi
n"))
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.CloseConne ction)

Return dr

End Function

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

Thanks,

Ron

[Peter Bromberg [C# MVP]]

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

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


"Ronald S. Cook" wrote:

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("C onnectionStringKamtechLogin"))

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.CloseConne ction)

Return dr

End Function

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

Thanks,

Ron

[Mark Rae]

"Peter Bromberg [C# MVP]" <pb*******@yahoo.nospammin.com> wrote in message
news:C1**********************************@microsof t.com...

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...

[Peter Bromberg [C# MVP]]

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


"Mark Rae" wrote:

"Peter Bromberg [C# MVP]" <pb*******@yahoo.nospammin.com> wrote in message
news:C1**********************************@microsof t.com...

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...