How to limit the wait time for SqlConnection.Open()?
I want to test if the connection string is correct, so I created a new SqlConnection, called its Open() method. But I have to wait a long time before it returns when the server/data_source part of connection string is wrong.
I tried adding connection timeout 开发者_开发百科to the connection string, it didn't work; I tried open the connection in another thread, then I call Thread.Abort() after several seconds. None of them worked.
So what's the correct way to do this? Thanks.
after researching, I found the solution ( http://www.improve.dk/blog/2008/03/10/controlling-sqlconnection-timeouts ), I modified it a little bit. If there's not exception thrown, then the connection string is valid.
var alive = true;
string error = null;
var success = false;
// ReSharper disable AccessToModifiedClosure
// ReSharper disable UseObjectOrCollectionInitializer
var thread = new Thread(() =>
{
try
{
var connection = new SqlConnection(connectionString);
connection.Open();
connection.Close();
if (alive)
success = true;
}
catch (SqlException ex)
{
if (alive)
error = ex.Message;
}
catch (ThreadAbortException)
{
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
});
// ReSharper restore AccessToModifiedClosure
// ReSharper restore UseObjectOrCollectionInitializer
thread.IsBackground = true;
var sw = Stopwatch.StartNew();
thread.Start();
var timeout = TimeSpan.FromSeconds(3);
while (sw.Elapsed < timeout)
thread.Join(TimeSpan.FromMilliseconds(200));
sw.Stop();
if (!success)
{
alive = false;
throw new Exception(error ?? "Connection timeout, please check the connection string.");
}
精彩评论