Attempt on SqlConnection.BeginOpen

[steinmr]

I have a need for a SqlConnection.Open that times out in an
predictable matter (i.e. if the server does not answer, I do not have
to wait for TCP or NIC timeouts to find out). The "Connection Timeout"
in the connectionstring will not help, as this only applies to the
connection to the SQL Server, not to the actual TCP port.

Therefore I have had a crack at it myself, and would appreciate any
constructive comments on the implementation. The method
BeginOpen(SqlConnection,int) will take an SqlConnection and a timeout
(in ms), and will try to open it. If it for any reason cannot, it will
return within the specified timeout. If there is an exception in
connection before the timeout occurs, it will throw that, otherwise it
will throw a custom Exception.

There is an 4-5 ms overhead by doing this, but I'm willing to
sacrifice this for a predictable timeout.

Any comments appreciated...

private void BeginOpen(SqlConnection connection, int msTimeout)
{
// Start timeout timer here, it includes all work done in this
method.
// This assures that this method uses no more than msTimeout to
complete
DateTime connectionAbortTime =
DateTime.Now.AddMilliseconds(msTimeout);
// Transfer ambient transaction to thread
Transaction currentTransaction = Transaction.Current;
// Save the thread exception (if any)
Exception threadException = null;
// Signifies that we could connect and communicate with the database
bool connectSuccess = false;

Thread workerThread = new Thread(
delegate()
{
try
{
Transaction.Current = currentTransaction;
connection.Open(); // This might hang indefinatly
connection.ChangeDatabase(connection.Database);
connectSuccess = true;
}
catch (Exception ex)
{
threadException = ex;
}
}
);
workerThread.IsBackground = true; // Make sure application tears it
down when it exits
workerThread.Name = "SqlConnection.BeginOpen";

// Start work and wait for the thread to finish or it times out
workerThread.Start();
while (connectionAbortTime > DateTime.Now)
if (workerThread.Join(1)) // Check if thread is finished
break;

// For some reason, the connection could not be opened, throw an
exception
if (!connectSuccess)
{
if (threadException != null) // Encountered an exception before
timeout
throw threadException;
else // Either timed out OR did not have time to save exception in
threadException
throw new SqlConnectionTimeoutException("Could not connect to
server within timeout");
}
}

public class SqlConnectionTimeoutException : Exception
{
public SqlConnectionTimeoutException(string message) : base(message)
{}
}

 

[William Vaughn]

I see any number of issues here. First, consider that for ASP architecture you need connection pooling. When first opening a connection you could encounter blocking states which include:
a.. The NIC cannot see the Ethernet. The network interface card has a fixed timeout that can range from 30 seconds and up. You cannot get around this blocking issue as this is tied to the fundamental architecture of TCP/IP. It permits IT to make Ethernet cabling changes that do not affect the physical transport layers. However, the .NET Framework can detect if the Network is down and exposes classes to do so.
b.. The server service is stopped or paused. This is normal during maintenance and a natural state when the system is being booted or shut down. Again, there are Factory classes that can show the system state of any server that's exposed by the SQL Browser service--that is, server services that have been programmed to be visible to the network. This assumes that the NIC is working.
c.. The server service is overloaded and cannot accept further connections. This is different than the full pool situation. Some server services are programmed to accept a limited number of connections and no more. In this case the connection might be opened and then closed on the server. Some servers are simply too busy to process the new connection request so the connection request times out on the client. This timeout condition is very different than the full pool variety but might easily lead to a full pool.
d.. The connection pool is full. I've written a long whitepaper on this issue (it's on my blog). If the application domain is pushing the server harder than it can process the requests, the pool can overflow. It can overflow through inept design where connections are not closed but perhaps orphaned--waiting for the GC to close them. When the pool is full, the pooling mechanism will wait for N (Timeout) seconds before reporting that there are no connections. In this case a timeout either means a server overload or simply that there are 99 orphaned connections in the pool. There are performance counters to detect this state.
This is discussed in depth in Chapter 9 of my book.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________