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);
     }
   }
 }
}