How to catch SQLServer timeout exceptions

by

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");
    }
}

More Related Contents:

  1. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  2. C# Equivalent of SQL Server DataTypes
  3. How to directly execute SQL query in C#?
  4. A property or indexer may not be passed as an out or ref parameter
  5. How to deploy application with sql server database on clients
  6. How to execute an SSIS package from .NET?
  7. SqlConnection SqlCommand SqlDataReader IDisposable
  8. Bulk Update in C#
  9. How can I catch a 404?
  10. Code to validate SQL Scripts
  11. MultipleActiveResultSets=True or multiple connections?
  12. EF 6 – How to correctly perform parallel queries
  13. SqlConnection.Close() inside using statement
  14. How to keep single SQL Server connection instance open for multiple request in C#?
  15. More Elegant Exception Handling Than Multiple Catch Blocks? [duplicate]
  16. Horrible performance using SqlCommand Async methods with large data
  17. Is it better to execute many sql commands with one connection, or reconnect every time?
  18. GO statements blowing up sql execution in .NET
  19. Is there a .NET equivalent to SQL Server’s newsequentialid()
  20. Know when to retry or fail when calling SQL Server from C#?
  21. How to Specify Primary Key Name in EF-Code-First
  22. Upload CSV file to SQL server
  23. Unable to load SqlServerSpatial.dll
  24. What is the equivalent of bigint in C#?
  25. “The operation is not valid for the state of the transaction” error and transaction scope
  26. Use SQL Server time datatype in C#.NET application?
  27. Should the order of LINQ query clauses affect Entity Framework performance?
  28. How do I connect to a SQL database from C#?
  29. Generating sql code programmatically
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment