Let's Talk about .. Visual Stuido 2005-Error The timeout period elapsed prior to obtaining a connection from the pool

“Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. “

P'Beer told me that this problem is normally the result of our forgotten coding. We usually use SQLHelper to connect to the database. Inside SQLHelper, there are many methods. We often use ExecuteReader and forget to close the connection. This method doesn't automatically close the connection, because it returns object as SqlDataReader which has to open the connection while it's reading. Thus we have to close by ourselves, otherwise, the connections will stuck inside the pool til max pool size. In fact, dot net provides a garbage collector to collect the unused connections, however, we may have to wait for an hour. Therefore, the best way to do is closing connection everytime we finish execute reading.

[VB.Net]
Dim dr as SqlDataReader = SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, ”StroreProcedureName”, SqlParameter())
If dr.Read() Then
‘Do something
End If

dr.close()

As you see, we don't close the connection directly, but SqlDataReader instead since by colsing SqlDataReader, it closes the connection automatically.

Special Thanks to P'Beer Adisorn for this knowledge.