Attempt on SqlConnection.BeginOpen

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
Mon Feb 25 12:28:06 CST 2008

This is a multi-part message in MIME format.

------=_NextPart_000_0010_01C87799.1C03C590
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

--=20
_________________________________________________________________________=
_
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)
_________________________________________________________________________=
___________________
<steinmr@gmail.com> wrote in message =
news:0dc4159c-211b-497c-96bc-8fb945734429@q33g2000hsh.googlegroups.com...=

>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.
>=20
> 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.
>=20
> There is an 4-5 ms overhead by doing this, but I'm willing to
> sacrifice this for a predictable timeout.
>=20
> Any comments appreciated...
>=20
> 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 =3D
> DateTime.Now.AddMilliseconds(msTimeout);
> // Transfer ambient transaction to thread
> Transaction currentTransaction =3D Transaction.Current;
> // Save the thread exception (if any)
> Exception threadException =3D null;
> // Signifies that we could connect and communicate with the database
> bool connectSuccess =3D false;
>=20
> Thread workerThread =3D new Thread(
> delegate()
> {
> try
> {
> Transaction.Current =3D currentTransaction;
> connection.Open(); // This might hang indefinatly
> connection.ChangeDatabase(connection.Database);
> connectSuccess =3D true;
> }
> catch (Exception ex)
> {
> threadException =3D ex;
> }
> }
> );
> workerThread.IsBackground =3D true; // Make sure application tears it
> down when it exits
> workerThread.Name =3D "SqlConnection.BeginOpen";
>=20
> // 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;
>=20
> // For some reason, the connection could not be opened, throw an
> exception
> if (!connectSuccess)
> {
> if (threadException !=3D 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");
> }
> }
>=20
> public class SqlConnectionTimeoutException : Exception
> {
> public SqlConnectionTimeoutException(string message) : base(message)
> {}
> }
------=_NextPart_000_0010_01C87799.1C03C590
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6001.18000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV>I see any number of issues here. First, =
consider=20
that for ASP architecture you need connection pooling. When first =
opening a=20
connection you could encounter blocking states which =
include:</DIV>
<UL>
<LI>The NIC cannot see the Ethernet. The =
network=20
interface card has a fixed timeout that can range from 30 seconds and =
up. You=20
cannot get around this blocking issue as this is tied to the =
fundamental=20
architecture of TCP/IP. It permits IT to make Ethernet cabling changes =
that do=20
not affect the physical transport layers. However, the .NET Framework =
can=20
detect if the Network is down and exposes classes to do =
so.</LI>
<LI>The server service is stopped or =
paused. This is=20
normal during maintenance and a natural state when the system is being =
booted=20
or shut down. Again, there are Factory classes that can show the =
system state=20
of any server that's exposed by the SQL Browser service--that is, =
server=20
services that have been programmed to be visible to the network. This =
assumes=20
that the NIC is working.</LI>
<LI>The server service is overloaded and =
cannot accept=20
further connections. This is different than the full pool situation. =
Some=20
server services are programmed to accept a limited number of =
connections and=20
no more. In this case the connection might be opened and then closed =
on the=20
server. Some servers are simply too busy to process the new connection =
request=20
so the connection request times out on the client. This timeout =
condition is=20
very different than the full pool variety but might easily lead to a =
full=20
pool.</LI>
<LI>The connection pool is full. I've =
written a long=20
whitepaper on this issue (it's on my blog). If the application domain =
is=20
pushing the server harder than it can process the requests, the pool =
can=20
overflow. It can overflow through inept design where connections are =
not=20
closed but perhaps orphaned--waiting for the GC to close them. When =
the pool=20
is full, the pooling mechanism will wait for N (Timeout) seconds =
before=20
reporting that there are no connections. In this case a timeout either =
means a=20
server overload or simply that there are 99 orphaned connections in =
the pool.=20
There are performance counters to detect this state.</LI></UL>
<DIV>This is discussed in depth in Chapter 9 =
of my=20
book.</DIV>
<DIV> </DIV>
<DIV>hth</DIV>
<DIV> --=20
 _____________________________________________________________________=
_____ William=20
R. Vaughn President and Founder Beta V Corporation Author, Mentor, =
Dad,=20
Grandpa Microsoft MVP (425) 556-9205  (Pacific =
time) Hitchhiker=92s=20
Guide to Visual Studio and SQL Server (7th=20
Edition) _____________________________________________________________=
_______________________________</DIV>
<DIV><<A =
href=3D"mailto:steinmr@gmail.com"><FONT=20
face=3DArial size=3D2>steinmr@gmail.com</A>> wrote=20
in message <A=20
href=3D"news:0dc4159c-211b-497c-96bc-8fb945734429@q33g2000hsh.googlegroup=
s.com"><FONT=20
face=3DArial=20
size=3D2>news:0dc4159c-211b-497c-96bc-8fb945734429@q33g2000hsh.googlegrou=
ps.com</A><FONT=20
face=3DArial size=3D2>...</DIV>>I =
have a need for=20
a SqlConnection.Open that times out in an > predictable matter =
(i.e. if=20
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=20
will not help, as this only applies to the > connection to the SQL =
Server,=20
not to the actual TCP port. > > Therefore I have had a =
crack at it=20
myself, and would appreciate any > constructive comments on the=20
implementation. The method > BeginOpen(SqlConnection,int) will =
take an=20
SqlConnection and a timeout > (in ms), and will try to open it. If =
it for=20
any reason cannot, it will > return within the specified timeout. =
If there=20
is an exception in > connection before the timeout occurs, it will =
throw=20
that, otherwise it > will throw a custom Exception. > =
 > There=20
is an 4-5 ms overhead by doing this, but I'm willing to > =
sacrifice this=20
for a predictable timeout. > > Any comments =
appreciated... >=20
 > private void BeginOpen(SqlConnection connection, int =
msTimeout) >=20
{ > // Start timeout timer here, it includes all work done in =
this >=20
method. > // This assures that this method uses no more than =
msTimeout=20
to > complete > DateTime connectionAbortTime =3D >=20
DateTime.Now.AddMilliseconds(msTimeout); > // Transfer ambient =
transaction=20
to thread > Transaction currentTransaction =3D =
Transaction.Current; >=20
// Save the thread exception (if any) > Exception threadException =
=3D=20
null; > // Signifies that we could connect and communicate with =
the=20
database > bool connectSuccess =3D false; > > Thread=20
workerThread =3D new Thread( > delegate() > { > =
try >=20
{ > Transaction.Current =3D currentTransaction; > =
connection.Open();=20
// This might hang indefinatly >=20
connection.ChangeDatabase(connection.Database); > connectSuccess =
=3D=20
true; > } > catch (Exception ex) > { > =
threadException =3D=20
ex; > } > } > ); > workerThread.IsBackground =3D =
true; //=20
Make sure application tears it > down when it exits >=20
workerThread.Name =3D "SqlConnection.BeginOpen"; > > // =
Start work=20
and wait for the thread to finish or it times out >=20
workerThread.Start(); > while (connectionAbortTime >=20
DateTime.Now) > if (workerThread.Join(1)) // Check if thread is=20
finished > break; > > // For some reason, the =
connection=20
could not be opened, throw an > exception > if=20
(!connectSuccess) > { > if (threadException !=3D null) // =
Encountered=20
an exception before > timeout > throw =
threadException; > else=20
// Either timed out OR did not have time to save exception in >=20
threadException > throw new SqlConnectionTimeoutException("Could =
not=20
connect to > server within timeout"); > } > } > =
 >=20
public class SqlConnectionTimeoutException : Exception > { > =
public=20
SqlConnectionTimeoutException(string message) : base(message) > =
{} >=20
}</BODY></HTML>

------=_NextPart_000_0010_01C87799.1C03C590--

Attempt on SqlConnection.BeginOpen

Re: Attempt on SqlConnection.BeginOpen by William