Cleaning the connection pooling in ADO.NET 1.1

[PikeStrider]

Sometimes connections in the pool become invalid. This could happen
when sql server is restarted, but the application isn't.

The following method seems to work quite well to "clean" the connection
pool. Is there some obvious thing I am overlooking?

A connection is cleaned by opening it and closing it, whether it is
valid or not. This means that you must have all connections in the pool
open at once to gurantee that they are all cleaned.

Public Shared Sub CleanConnectionPool(ByVal connectionString As
String)
Dim maxConnectionPoolSize As Int32 = 100
openAndCloseConnections(connectionString,
maxConnectionPoolSize)
End Sub

Private Shared Sub openAndCloseConnections(ByVal connectionString
As String, ByRef countConnectionsOpenAndClose As Integer)
If countConnectionsOpenAndClose > 0 Then
'meaningfully connect to the database
Dim dr As SqlDataReader = Nothing
Dim cmdText As String = "SELECT date=getdate()"
Dim cn As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(cmdText, cn)
Try
cn.Open()
dr = cmd.ExecuteReader
If Not dr Is Nothing Then
dr.Read()
End If
Catch ex As System.InvalidOperationException
'this happens when we get a timeout waiting for
'a connection - this means our connection pool is
clean!
countConnectionsOpenAndClose = 0
Catch ex As Data.SqlClient.SqlException
'A general network error indicates that an invalid
'connection has been found, which is
'what we expect to happen, so do nothing.
Finally
countConnectionsOpenAndClose =
countConnectionsOpenAndClose - 1
openAndCloseConnections(connectionString,
countConnectionsOpenAndClose)
If Not dr Is Nothing Then
dr.Close()
End If
'leave each connection open until all have been opened.
cn.Close()
End Try
End If
End Sub

 

[Miha Markic [MVP C#]]

Hi,

Another workaround is to create a new connection using connectionstring+" "
(each time you add a blank) which will create a new physicall connection.