Bala Singam

In my previous post I have suggested to increase the timeout value to prevent the
” Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding ” error. But after set the timeout value to 600 seconds I still get errors but this time “Thread was being aborted” error in my ASP.Net application.

This is because the ASP.Net web application itself have a timeout property which is by default is 180 seconds or 3 minutes which means its expect a response from the web server within the timeout or else it will abort the thread. So before the DB call timeout expired (which we set 600 sec) the ASP.NET hosting process aborts this thread after its timeout expired (default 180 sec).

So in order to avoid this we need to increase the httpRuntime executionTimeout to higher value than timeout for Data Access( Command Object or DataAdapter) .

How to set the http Runtime execution Timeout ?
Its easy to do this , open to web.config file of your web application. Under the system.web tag add this line

<httpRuntime executionTimeout="600" />

Its should be something similiar like this

<system.web>
    <httpRuntime executionTimeout="600" />
</system.web>

I’m been facing this timeout problem in my report query especially for rdlc report where the application need to provide the datasource for the report. The error prompt more frequently when the number of records increased in table, so most of time you won’t have this timeout error during your development or early stage of production. But when when production data increasing you will facing timeout error.

There is 2 thing i can suggest here to avoid this problem. First keep your query or view simple and fast enough. What i mean here is make sure you use the correct joins in query if you ever need one and only put the fields that you need in query. Number of column if too many in your query or view will also make the execution time longer. But sometimes we can’t avoid creating a query or view that take so much memory and execution time so when you arrive on this situation its a must to set the timeout property to longer time.

There are two main Timeout property in ADO.NET.

1. Connection Timeout for Connection
2. Timeout for Data Access( Command Object or DataAdapter)

How to set Connection Timeout property for sql connnection object ?

You can’t set timeout property in connection object because its a read only property

// you cannot do this
SqlConnection conn = new SqlConnection("server=Server;uid=sa;pwd=123456;database=myDB;Connection");
conn.ConnectionTimeout = 100;

The correct way to do this is set the timeout value in connection string like the example below.

// the correct way to set connection timeout
SqlConnection conn = new SqlConnection("server=Server;uid=sa;pwd=123456;database=myDB;Connection Timeout=90");

Next , How to set timeout property in DataAdapter or Command Object ?

Its pretty simple as shown below.

SqlDataAdapter sqlAdapter = new SqlDataAdapter();
sqlAdapter.SelectCommand = new SqlCommand(sqlQuery, conn);
sqlAdapter.SelectCommand.CommandTimeout = 0;

Setting the timeout value to 0 will makes adapter or command to wait for indefinite time before terminating the attempt to execute a command and generating an error.

The default timeout value for connection object is 15 seconds.
The default timeout value for DataAdapter or Command Object is 30 seconds

If you have better ways to overcome this timeout problem please leave your comments. It will be very helpful