"David C" <> wrote in message
news:OrWEX%...
>
> "Mr. Arnold" <MR. > wrote in message
> news:...
>>
>> "David C" <> wrote in message
>> news:...
>>>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.ConnectionStrin gs("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.
>>
>
> So if I change the one line to below will this be ok? Thanks
>
> Dim dtr As SqlDataReader =
> cmdSel.ExecuteReader(Data.CommandBehavior.CloseCon nection)
>
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
Similar Threads
