Pooled SQLConnection - Server Disconnection Recovery

[Zohar Massad]

I am using SQLConnections, which I know is inheritantly pooled, in my
Serviced Component (COM+) DAL, to access SQL Server (duh).
I am trying to handle the known problem where if the SQL Server is
stopped (disconnected) and subsequently restarted (reconnected) the
first SQLConnection fails with "General network error. Check your
network documentation."
According to what I've read up, once the first connection fails, the
rest of the zombied connections in the pool are supposedly cleared. So
I catch the error, and try to create a new, second, SQLConnection. The
problem is that sometimes the second connection *also* fails. Its not
consistent, but the first time my DAL is loaded the second connnection
always fails, afterwhich it is usually, but not always OK (sometime
the second new connection succeeds, and sometimes it takes a few more
tries)

When I finish using the connection I always call Close and Dispose. I
also tried calling GC.Collect(System.GC.MaxGeneration); and
GC.WaitForPendingFinalizers(); in my catch, but this did not help.

Can someone please recommend the proper course of action to take when
receiving a network error? It seems wrong to throw an error when the
SQL Server is back online.

Thanks,
Zohar

Below is my pseudo-code:

void ReadData(ref DataObject data, bool retry)
{
try
{
using(SqlConnection SQLConn = new SqlConnection(…))
{
SQLConn.Open();
//do work here
SQLConn.Close();
}
catch(SqlException SQLE)
{
if (retry)
{
GC.Collect(System.GC.MaxGeneration);
GC.WaitForPendingFinalizers();

//try once more
ReadData(ref data, false);
}
}
}
}

 

[Pablo Castro [MS]]

Actually, in current versions of SqlClient, we don't clean up the connection
pool when we see the first connection failing because the server was
restarted or any similar reason (yeah, I know...). FWIW, we fixed that in
Whidbey and we'll fail only the first time.

As for alternate solutions, there are not many options for this. One is to
keep calling Open/Execute until it succeeds - at that point all the bad
connections are gone. The other is to force SqlClient to create another
connection pool (for example, if you add an space at the end of the
connection string, it will be considered a different connection string and
thus a different pool will be used).

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Zohar Massad]

... keep calling Open/Execute until it succeeds - at that point all the bad
connections are gone.
--

Thanks for clearing this up.
What exactly should I look for in the SQLException to make sure it is
this specific situation? Otherwise I might get into an endless loop
when the server is down or there is another permanent SQL problem.

Zohar Masad
PFS - Tradertools