Last post Jun 16, 2013 02:45 PM by carlos16
Sep 06, 2007 04:49 AM|chayady|LINK
I have a problem with "timeout expired. Thei timeout periode elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were use and max pool size was reached"
Then i explore and found out that i did not close my SqlDataReader, SqlDataAdapter, SqlCommand or my connection.
But i have a function that return a SqlDataReader.
Is this function will cause a connection problem?
Thanks in advance
Public Function GetDataReader(ByVal strSQL As String, ByVal DBCon As DB.DBConnection) As SqlDataReader
Dim MyCommand As SqlCommand = New SqlCommand(strSQL, DBCon.GetConnection())
If DBCon.GetConnection().State = ConnectionState.Closed Then
Dim dr As SqlDataReader = MyCommand.ExecuteReader()
Sep 06, 2007 04:59 AM|DMW|LINK
This function is indeed not closing the connection and is liable to cause you serious problems.
You should avoid returning a DataReader from a method, but should instead use it and Close it, along with the connection, inside one method. You can then sensibly wrap its usage in a Try ... Finally ... End Try block.
If you absolutely must return a DataReader from a method, then
1. When you call ExecuteReader, pass in the flag that indicates that when the reader is closed, the connection must also be closed, and
2. Make absolutely sure that you close the DataReader in the code that calls the method that returns the DataReader
Sep 06, 2007 05:15 AM|chayady|LINK
Ok, i also think this is the one causing the problem.
If change some code in the function into this one,
i suppose it close the connection, and i still can use the function without have to change any code that use this function,
Am i right or not?
Thanks for the advise.
Return dr (before i use this method)
GetDataReader = dr
Sep 06, 2007 05:30 AM|chayady|LINK
Sorry DMW, i just try change the function
but can not work
because i close the dr inside the function.
If the code that call the function, close the dr,
is the dr inside the function also close?
This the the code that call the function:
Public Sub ABC()
Dim MyConnection As New DB.DBConnection()
Dim dr As SqlDataReader
dr = CommonFunction.GetDataReader("Select * From table", MyConnection)
Is this code safe for preventing the connection pool to reach max?
Thanks for your time DMW,
i really appriciate it.
Sep 07, 2007 04:44 AM|DMW|LINK
OK. That's pretty close. Here's a rough layout of what I would do if I decided to write a method that returned a DataReader
Function GetReader( command as string ) as DbDataReader
Dim con As DbConnection = ... ' Get the connection information from web.config
Dim cmd As DbCommand = ... ' Prepare the command from the command string that's passed in
Dim reader As DbDataReader = cmd.ExecuteReader( CommandBehavior.CloseConnection )
' This tells the reader to close the connection when it is done
If con IsNot Nothing Then con.Close()
Throw ' Rethrow the exception because there's no way to recover from it
To use this method, I'd write
Dim reader As DbDataReader = Nothing
reader = GetReader( "SELECT ... " )
' Use the reader
If reader IsNot Nothing Then reader.Close()
The Try Catch block in the GetReader method ensures that the connection is closed if an exception is generated in the ExecuteReader method
The Try Finally bloc in the MethodThatUsesADatabase method ensures that the connection is closed if a valid DataReader is returned
There is an edge case that is not handled by this process. If an exception occurs after GetReader has been called but before the assignment to the reader variable is performed (in MethodThatUsesADatabase), then potentially you'll leave the connection open.
However, that probably means that you're either calling Thread.Abort() on the thread (in which case you deserve everything you get) or because the CLR is falling apart (in which case a DB connection is the least of your problems).
But as I mentioned in my earlier post, I prefer to write methods that
1. Open the connection
2. Execute the command, and
3. Close the connection
Otherwise you have to make sure that every developer that uses the GetReader method remembers to add the Try Finally block.
Sep 07, 2007 05:04 AM|chayady|LINK
Thanks for your explaination.
I agree with you 100%.
FYI, as i work as a team, and i am just moving to this company (in China),
and found the timeout problem,
i found the function that not closing the connection.
When i discuss with the rest of the team,
they strongly opposed my opinion.
But now, i am more confident with my opinion,
i now i am absolutely sure that this function causing the timeout problem.
I will change the code.
I will use your given code.
I will realy appriciate your help. [Yes]
Jun 16, 2013 02:45 PM|carlos16|LINK
iam having the exact problems and using method to return sqldataReader.. if you have solved the issue please posted.