How to catch SQLServer timeout exceptions

Question

I need to specifically catch SQL server timeout exceptions so that they can be handled differently   I know I could catch the SqlException and then check if the message string Contains  Timeout  but was wondering if there is a better way to do it   try         some code   catch  SqlException ex         if  ex Message Contains  Timeout                     handle timeout           else                throw

User · Answer

Whats the value for the SqlException ErrorCode property  Can you work with that   When having timeouts  it may be worth checking the code for -2146232060   I would set this up as a static const in your data code

User · Answer

Updated for c  6       try                  some code           catch  SqlException ex  when  ex Number    -2      -2 is a sql timeout                  handle timeout         Very simple and nice to look at

User · Answer

I am not sure but when we have execute time out or command time out The client sends an  ABORT  to SQL Server  then simply abandons the query processing  No transaction is rolled back  no locks are released  to solve this problem I Remove transaction in Stored-procedure and use SQL Transaction in my  Net Code To manage sqlException

User · Answer

here  http   www tech-archive net Archive DotNet microsoft public dotnet framework adonet 2006-10 msg00064 html  You can read also that Thomas Weingartner wrote      Timeout  SqlException Number    -2  This is an ADO NET error code    General Network Error  SqlException Number    11   Deadlock  SqlException Number    1205  This is an SQL Server error code            We handle the  General Network Error  as a timeout exception too  It only occurs under rare circumstances e g  when your update insert delete query will raise a long running trigger

User · Answer

When a client sends ABORT  no transactions are rolled back  To avoid this behavior we have to use  SET XACT ABORT ON https   docs microsoft com en-us sql t-sql statements set-xact-abort-transact-sql view sql-server-ver15

User · Answer

To check for a timeout  I believe you check the value of ex Number  If it is -2  then you have a timeout situation   -2 is the error code for timeout  returned from DBNETLIB  the MDAC driver for SQL Server  This can be seen by downloading Reflector  and looking under System Data SqlClient TdsEnums for TIMEOUT EXPIRED    Your code would read   if  ex Number    -2           handle timeout     Code to demonstrate failure   try       SqlConnection sql   new SqlConnection   Network Library DBMSSOCN Data Source YourServer 1433 Initial Catalog YourDB Integrated Security SSPI         sql Open         SqlCommand cmd   sql CreateCommand        cmd CommandText    DECLARE  i int WHILE EXISTS  SELECT 1 from sysobjects  BEGIN SELECT  i   1 END       cmd ExecuteNonQuery       This line will timeout       cmd Dispose        sql Close      catch  SqlException ex        if  ex Number    -2            Console WriteLine   Timeout occurred

[c#] How to catch SQLServer timeout exceptions

Examples related to c#

Examples related to .net

Examples related to sql-server

Examples related to error-handling