June 19, 2015

How to Increase SQL Command Timeout

Sometimes I have seen while running a sql script from .NET code that the Sql Command times out. The solution is simple - we just need to increase the timeout. However, we should really think on why the timeout increase is needed and whether we should change the query - like, by reducing the number of rows returned - to minimise the time taken.

Before starting the discussion, what is Sql Command? It's a call to the database to get data or to update data. We can either directly execute the sql script or call a stored procedure by passing in the relevant parameters. In case, we figure we cannot change the sql query and our only option is to increase the timeout, then we can write the following code.

  1. System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
  2. cmd.CommandText = "select top 10 from tblNames";
  3. cmd.CommandTimeout = 20;
  4. //TODO ...


There is also a connection timeout concept which is for sql connection and can be set as part of the connection string within the app.config file. Changing the connection timeout would have no effect on the sql command as it is separate.

0 comments:

Reference: Shahed Kazi at AspNetify.com