CODEWITHSUNDEEP
c#.netsql-server
DinosaurTom
DinosaurTom

Reputation: 212

IDbConnection Open hangs C#

My program execution hangs on .Open() method longer than specified 2 seconds timeout when connecting to SQL-Server.

I managed to reproduce the problem when machine that database is on is offline or restartig. When machine that database is on get up the execution of program un-hangs.

How can I force to throw exception or whatever when 2 second timeout exceed?

I tried OleDB and SqlConnection provider, no difference.

I can ping the machine before connecting to it but there is still case that ping will be successful (let's say 1 second before machine shutdown) and then Open connection hangs...

Example code provided below.

public static IDbConnection GetConnection(IDbConnection connection)
{
    connection.ConnectionString = "connectionString; Connection Timeout=2;";
    connection.Open();
    return connection;
}

Upvotes: 3

Views: 1429

Answers (1)

Adriano Repetti
Adriano Repetti

Reputation: 67128

Connection Timeout property is just the time for the connection to be created, everything after that (and there is a lot to do after connection is established) does not count and it may take indefinitely (unless there is another timeout I'm not aware of).

What you can do is to execute your own code in a separate thread with a watchdog to limit total time execution to two seconds. Using tasks it is pretty easy:

const int HardConnectionTimeoutInMilliseconds = 2000;
if (!Task.Run(() => connection.Open()).Wait(HardConnectionTimeoutInMilliseconds))
    return null; // Timeout!

Just for completeness this is old-style code for this:

Thread worker = new Thread(delegate()
{
    connection.Open();
});
worker.Start();

if (!worker.Join(TimeSpan.FromSeconds(2)))
     return null;

Be careful with so short timeout: for a TCP connection two seconds are always a too short time and if you're using Windows authentication with AD then it may takes longer than you expect.

In my opinion you have to live with this lag (15 to 30 seconds are a safe and reasonable time for TCP connection with integrated security). You may also want to wait more and retry (because errors may be temporary, see Know when to retry or fail when calling SQL Server from C#?), note that situation you're describing (server is going down) is pretty unusual then IMO it shouldn't affect normal operations. If it's an issue for your UI then you should make your program parallel (to keep UI responsive).

Upvotes: 2

Related Questions