Time Out Exception While Executing an Sql Command

Problem :

System.Exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Cause : 

sqlcommand has a property called commandTimeout. Its default value is 30 seconds. If the query execution time is more than this value, It will throw above exception.

Solution :

Just increase the timeout value and you are done.

Solution if using typed DataSet :

This is a bit tricky. When you create an adapter using typed dataset in visual studio designer, It creates the classes and methods automatically for you. Ironically, timeout property is not accessible to you... because CommandCollection is protected !.

You may tweak the code and make it public or even increase the timeout value in the designer itself. But all the changes to this file are lost once you make any slight in the designer ans save it.

So what to do ?

Create a partial class of the adapter in a separate file. Make sure you use the same namespace.
Create public a property that has a getter and setter for timeout value.

for example,


public int SelectQueryTimeout{
    get{
    return this.CommandCollection[0].commandTimeout;
    }
    set{
    this.CommandCollection[0].commandTimeout= value;
    }
}


Now you have complete access to timeout value of the command.

I just don't understand why would Microsoft chose not to allow developer to change the timeout simply without having to go through all this.