4

I have read now something about SQL Server timeout exceptions. I found these two links:

how to detect sql server timeout from .NET application without using catch Exception

How to catch SQLServer timeout exceptions

So -2 is a client timeout and when I'm understanding this right, one can use this for a server connection timeout and a command timeout. -1 and -3 are server network errors.

Now. Is it somehow possible in C# to catch an exception and distinguish between a server connection timeout and a command timeout?

Thank you very much!

EDIT: for example here:

https://msdn.microsoft.com/en-us/library/cc645611.aspx

-2 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2).

how to detect if a timeout period elapsed prior to a) completion of the operation or b) if the server is not responding? This is the problem.

Community
  • 1
  • 1
Canox
  • 557
  • 1
  • 7
  • 20
  • Does the connection terminate if the command times out? if not - you have your answer – BugFinder Feb 14 '17 at 09:47
  • Connection Timeout means SQL Server don't respond to your app call. Command Timeout means SQL Server tried to execute the command but cannot complete it in time – jean Feb 14 '17 at 09:48
  • Yes it does. I would need to handle this and throw different exceptions. – Canox Feb 14 '17 at 09:48
  • I know what they mean but when a connection timeout occured I would need to detect if it was a server connection timeout or a command timeout. – Canox Feb 14 '17 at 09:51

1 Answers1

1

Yes, you can. Note connection timeout occurs when your DBMS doesn't respond to your app call while a command timout means the DBMS reponded. In fact, it sends a "fail" in order to your drive throws an exception. While in the command timout it received a explicit error code from the server in the connection timeout your app cannot know what happened (maybe someone pulled the network cable, maybe the server got shutdown) but the bowels of the driver/framework will tell you what happened using the number property .

The code below will look for the exception number property and check if it's a command timeout.

try
{
   //your .net C# code here 
}    
catch (SqlException ex)
{
    if (ex.Number == -2 && (Regex.IsMatch(sqlEx.Message, "[tT]ime\-{0,1}out")) {
        Console.WriteLine ("Command Timeout occurred")
    };
}

Also you can parse the exception text to get when it's another kind of command error, like a FK violation for example. Just do a regex matching "timeout" word.

references: MSDN

MSDN Forum

How to catch SQLServer timeout exceptions

Community
  • 1
  • 1
jean
  • 4,159
  • 4
  • 31
  • 52
  • Ok thanks for the explanation but when I check if it is -2 and then would do an else it would catch every single exception and not only the server connection timeout or other connection timeouts - Right? – Canox Feb 14 '17 at 10:05
  • @Canos youa re correct, edit to explain in this case you can parse the exception message – jean Feb 14 '17 at 10:08
  • parse the exception message? Which would be the right one? – Canox Feb 14 '17 at 10:11