控制连接超时

 1425  2016/05/20 16:13:00


首先连接超时分为三种,
TCP Connection to SQL Server -> SqlConnection.Open -> SqlCommand.Execute
先说第二种超时,sqlcon打开,我们不能直接设置connectiontimeout,只能在连接字符串中设置:
Data Source=server;Initial Catalog=databaseUser Id=username;Password=password;Connect Timeout=3
此设置默认时间为妙,而不是毫秒。默认是三十秒,可以设置为3秒。
注意:此处不能设置conn.open()超时回应的时间。因为conn.open(),不只是sqlserver响应尝试连接数据库的时间,其中还有Tcp请求的时间.所以如果 
你设置Connect Timeout=2,在连接不到server的情况下,报告超时的时间也远远超过2s。因为此处设置的只是在sqlserver必须回应一个连接 
尝试的时间。但是如果你根本就无法连接到server,这个设置也就无从谈起。

第三种超时比较简单,就是sql语句在数据库中的执行时间。通过 SqlCommand.CommandTimeout就可以进行设置。

第一种超时就是tcp请求的超时,这个是没有办法通过设置属性实现的。但是我们必须要控制它,因为一个连接可能几十秒之后才会回应你超时 
了,这是难以忍受的。

下面是一个例子:
public static class SqlExtensions
{
public static void QuickOpen(this SqlConnection conn, int timeout)
{
// We'll use a Stopwatch here for simplicity. A comparison to a stored DateTime.Now value could also be used
Stopwatch sw = new Stopwatch();
bool connectSuccess = false;

// Try to open the connection, if anything goes wrong, make sure we set connectSuccess = false
Thread t = new Thread(delegate()
{
try
{
sw.Start();
conn.Open();
connectSuccess = true;
}
catch { }
});

// Make sure it's marked as a background thread so it'll get cleaned up automatically
t.IsBackground = true;
t.Start();

// Keep trying to join the thread until we either succeed or the timeout value has been exceeded
while (timeout > sw.ElapsedMilliseconds)
if (t.Join(1))
break;

// If we didn't connect successfully, throw an exception
if (!connectSuccess)
throw new Exception("Timed out while trying to connect.");
}
}

参考链接:http://improve.dk/archive/2008/03/10/controlling-sqlconnection-timeouts.aspx

本文转自:http://www.cnblogs.com/weixing/p/5512496.html