Distinguish between SQL Server Timeout Exceptions and SqlCommand Timeout Exceptions in C#

Question

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.

Does the connection terminate if the command times out? if not - you have your answer — BugFinder
– BugFinder, Commented Feb 14, 2017 at 9: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
– jean, Commented Feb 14, 2017 at 9:48
Yes it does. I would need to handle this and throw different exceptions. — Canox
– Canox, Commented Feb 14, 2017 at 9: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
– Canox, Commented Feb 14, 2017 at 9:51

Community · Accepted Answer · 2017-05-23 12:09:56Z

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

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?
@Canos youa re correct, edit to explain in this case you can parse the exception message

Collectives™ on Stack Overflow

Distinguish between SQL Server Timeout Exceptions and SqlCommand Timeout Exceptions in C#

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related