BALA SINGAM

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