When performing queries against a SQL Server database, there are a couple of methods readily available. However, an option is missing.
The primary timeout value is that of SqlConnection.ConnectionTimeout. This specifies how long time the SQL Server service has to respond to a connection attempt. You cannot set this value directly, you’ll have to set it as part of the connection string:
Note that the value is expressed in seconds, not milliseconds. The default value is 30 seconds. Secondly, we can use the SqlCommand.CommandTimeout value. This sets the timeout value of a specific query running on SQL Server. The problem with these two is that we’re missing a point in the pipeline, which goes:
TCP Connection to SQL Server -> SqlConnection.Open -> SqlCommand.Execute
The last two are covered, but if for some reason the SQL Server is dead, taken off the network, totally overloaded, we may get a timeout on the TCP level - and this could take a while. We currently have no way of controlling this timeout besides a server wide network level setting. Often, it’s not desirable to have your application potentially spending several minutes before receiving a TCP timeout - or sometimes simply wait indefinitely. We need some way to control this.
What I present below is an example of a SqlConnection extension method called QuickOpen (in lack of a better name, it isn’t quicker, it simply fails quicker). It’ll take a timeout parameter in milliseconds, after which it’ll throw a simple Exception. You can modify this to a more proper exception, this is just to show the point. Overall, using this method will introduce a slight delay (a couple of ms), so use it only when necessary, or when a couple of ms per SqlConnection.Open doesn’t matter.