Closing connections

[David C]

I have some class functions that return SqlDataReader to fill DropDownLists.
Below is example. I have the RETURN line before the closing of reader and
connection. Are these getting closed or is there another way to do this?
For example, if I had it wrapped in a Using block? I am getting connection
pool timeouts and am only testing the site so I'm not sure why. Thanks in
advance.

David

Public Shared Function GetWICounties() As SqlDataReader
Dim conData As SqlConnection = New
SqlConnection(ConfigurationManager.ConnectionStrings("MCFICoreConnectionString").ConnectionString)
conData.Open()

Dim strSQL As String

strSQL = "SELECT CountyID, County" & _
" FROM dbo.WICounties" & _
" ORDER BY County"

Dim cmdSel As SqlCommand = New SqlCommand(strSQL, conData)
Dim dtr As SqlDataReader = cmdSel.ExecuteReader()
Return dtr
dtr.Close()
conData.Close()
End Function

 

[Mr. Arnold]

I have some class functions that return SqlDataReader to fill
DropDownLists. Below is example. I have the RETURN line before the closing
of reader and connection. Are these getting closed or is there another way
to do this? For example, if I had it wrapped in a Using block? I am getting
connection pool timeouts and am only testing the site so I'm not sure why.
Thanks in advance.

David

Public Shared Function GetWICounties() As SqlDataReader
Dim conData As SqlConnection = New
SqlConnection(ConfigurationManager.ConnectionStrings("MCFICoreConnectionString").ConnectionString)
conData.Open()

Dim strSQL As String

strSQL = "SELECT CountyID, County" & _
" FROM dbo.WICounties" & _
" ORDER BY County"

Dim cmdSel As SqlCommand = New SqlCommand(strSQL, conData)
Dim dtr As SqlDataReader = cmdSel.ExecuteReader()
Return dtr
dtr.Close()
conData.Close()
End Function

You don't return a datareader, and it looks like you left everything open
without closing them before you left.

You read the data with a datareader that is reading the data from the
resultset at the spot of the returned resultset, you instantiate a DTO (Data
Transfer Object of the required properties), you add the DTO
to List<T> and you return the List<T> and bind the List<T> (which is
bindable) to the control.

The return of the List<T> will be the last statement in the function with
reader and connection closed before you get to the return.

http://www.codeguru.com/vb/gen/vb_misc/oop/article.php/c7063/






__________ Information from ESET NOD32 Antivirus, version of virus signature database 4498 (20091011) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[David C]

You don't return a datareader, and it looks like you left everything open
without closing them before you left.

You read the data with a datareader that is reading the data from the
resultset at the spot of the returned resultset, you instantiate a DTO
(Data Transfer Object of the required properties), you add the DTO
to List<T> and you return the List<T> and bind the List<T> (which is
bindable) to the control.

The return of the List<T> will be the last statement in the function with
reader and connection closed before you get to the return.

http://www.codeguru.com/vb/gen/vb_misc/oop/article.php/c7063/






__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4498 (20091011) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

So if I change the one line to below will this be ok? Thanks

Dim dtr As SqlDataReader =
cmdSel.ExecuteReader(Data.CommandBehavior.CloseConnection)

 

[bruce barker]

unless there is an error, or your code that uses the reader does not
read all rows and resultsets.

returning a datareader is a bad practice (should return an object
collection in most cases) . if you do the caller shoudl use a using
statment.

-- bruce (sqlwork.com)

 

[Mr. Arnold]

So if I change the one line to below will this be ok? Thanks

Dim dtr As SqlDataReader =
cmdSel.ExecuteReader(Data.CommandBehavior.CloseConnection)

CloseConnection ---- When the command is executed, the associated Connection
object is closed when the associated DataReader object is closed. So, I
don't see this helping you the way you have coded things.

It's the same thing with the Using(connection).

If you leave out of the Using(connection) with a Return to leave the
function before you let the Using(connection) complete normally to close the
connection, then you have left things open like the code above.

If you have done the Return and left the Function before you close things,
then you have the same situation as before.

You can't return a closed datareader that I know about and use it elsewhere,
and that is your problem.

What I have told you about in returning a List<T> of DTO(s) after closing
all connections is what I would call best practice.

I also say if Returning something, then it should be the last statement in
the function.

You instantiate the return of Something as New so it's empty, so either way,
you have return something -- empty or it has data in it that you have
populated.

I also say that it should be in a try/catch/throw when dealing with the
database, another best practice.

This may help you, as I certainly purchased it to help me design better
solutions and code better.

http://www.dofactory.com/Framework/Framework.aspx


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4498 (20091011) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Gregory A. Beamer]

I have some class functions that return SqlDataReader to fill
DropDownLists. Below is example. I have the RETURN line before the
closing of reader and connection. Are these getting closed or is
there another way to do this? For example, if I had it wrapped in a
Using block? I am getting connection pool timeouts and am only testing
the site so I'm not sure why. Thanks in advance.

You cannot close a connection on a DataReader prior to working with the
data. The DataReader is a firehose cursor, from the database
perspective. It makes a nice analogy. When you think of a firehose, it
is running water as long as the valve is open (the connection). When you
close the valve, no more water, even if the fire is still raging.

If you have a routine that returns a DataReader, you are better to feed
the connection from a routine that will also close it, after you finish
using the data.

Peace and Grace,

 

[Gregory A. Beamer]

Return dtr
dtr.Close()
conData.Close()

Oops, I missed this.

The return will return the DataReader prior to closing, so nothing will
be closed here. The rest of my post still applies.

Peace and Grace,

 

[Scott M.]

I have some class functions that return SqlDataReader to fill
DropDownLists. Below is example. I have the RETURN line before the closing
of reader and connection. Are these getting closed or is there another way
to do this? For example, if I had it wrapped in a Using block? I am getting
connection pool timeouts and am only testing the site so I'm not sure why.
Thanks in advance.

David

Public Shared Function GetWICounties() As SqlDataReader
Dim conData As SqlConnection = New
SqlConnection(ConfigurationManager.ConnectionStrings("MCFICoreConnectionString").ConnectionString)
conData.Open()

Dim strSQL As String

strSQL = "SELECT CountyID, County" & _
" FROM dbo.WICounties" & _
" ORDER BY County"

Dim cmdSel As SqlCommand = New SqlCommand(strSQL, conData)
Dim dtr As SqlDataReader = cmdSel.ExecuteReader()
Return dtr
dtr.Close()
conData.Close()
End Function

As others have pointed out, you should not be returning a DataReader from a
method as a DataReader uses a "live" connection. If you start passing the
DataReader around and something goes wrong and the prgram doesn't return to
this method, your connection will not close.

Instead, you should capture the data in some object and return that object
from your method (this is exactly what a DataSet was designed for).

Lastly, because you wrote the DataReader's close and the Connection's close
after your return statement, those two lines of code will never be reached.

-Scott

 

[3P]

If you leave out of the Using(connection) with a Return to leave the
function before you let the Using(connection) complete normally to close
the connection, then you have left things open like the code above.

If you have done the Return and left the Function before you close
things, then you have the same situation as before.

This is not true. Dispose is always called.