TimeOuts in ADO.Net

Comments [0]

When writing .Net code (or code in any language for that matter) that updates a database, you need to be cognizant of the fact that it takes a finite amount of time to connect to a database and process any commands sent to the database.

ADO.Net permits you to set a TimeOut value on a Connection object and on a Command object.

The Command TimeOut property allows you to configure how long a command waits to successfully complete execution of a query. By default, a Command object will timeout after 30 seconds

It’s important to strike a good balance when setting timeout values.

Sometimes we expect a database action to take a long time and we want to give it time to complete before we pull the rug out, so to speak.

On the other hand, if a problem prevents a command from executing properly, it's useful to know this sooner so our application can handle it.

Changing a command timeout is simple. The Command object exposes a read/write ConnectionTimeout property. Set it to the number of seconds you wish the comand to wait on executing before aborting.

After the Command TimeOut period, if the command has not completed, an exception is thrown. However, the database server does not know this, so the command will continue to execute on the server - your application just won't know the results.

The Connection TimeOut is the amount of time the Connection will spend attempting to connect to a database before giving up and throwing an exception. The default Connection Timeout value is 15 seconds. On a slow network, it may take longer to connect, so you may wish to increase this value. However, if the application is unable to connect to the database - if the server is unavailable, for example - it's best to find this out sooner rather than later.

Changing the Connection Timeout is less obvious than changing the Command Timeout. The Connection class exposes a ConnectionTimeout property; But this property is read-only, so you cannot use it to change the timeout. To change a timeout, you must modify the connection string. Add or update the following to your connection string:
    Connection Timeout=XXX
where XXX is the number of seconds to wait for a connection to remain open before aborting all pending operations on that connection.

In your applications, it is important to strike the right balance when setting timeout properties.