Connection Timeout vs. Command Timeout

I was doing some work with gridviews and mssql and encountered a very difficult issue that took pointed, dedicated research to resolve. The issue was that the page with the gridview would time out after thirty seconds, even though the timeout property was set in the SQLDATASOURCE connection string to "0", or infinite. Remote queries were set on the SQL server instance to run until complete (Again, as "0"). IIS was set to break the connection at 600 seconds. Plenty of time all around for a query that completes at worst in ~40 seconds.

I dug through forum after forum that insisted that the timeout issue was due to the Connection Timeout property, but no amount of tweaking made any difference; the query still timed out at exactly thirty seconds. I tested setting a two minute threshold of the httpRuntime in web.config, which made no difference. After some creative Googling, I discovered the existance of the CommandTimeout property, which cannot be set from the gridview. The difference between ConnectionTimeout and CommandTimeout is this:

• ConnectionTimeout = maximum time in seconds allowed to make a connection to a datasource
• CommandTimeout = maximum time in seconds allowed to complete a command (Say, a query for example)

See for details -http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

I created the following function, hooked to the OnSelecting handler of the gridview, which finally did the trick and allowed the query to complete (Granted, 5000 seconds is a lot of time and is used as an example - roughly 83 minutes - use your discretion for this value!)

<script runat="server">

protected void SqlDataSourceSelectingEventHandler(object sender, SqlDataSourceSelectingEventArgs e)

{

e.Command.CommandTimeout = 5000; 

}

</script>