Programmatic SQL command timeout using a SqlDataSource (C#)

• June 24, 2009
• James Skemp
• article

Granted, in the case where this came up, I should have moved away from a SqlDataSource, but I wrote it using one, and a re-write isn't possible at the moment.

So, I had to find a way yesterday to programmatically set the command timeout of a select, using a SqlDataSource (that was programmatically written).

After much research, I found that you have to set the timeout on selecting. After opening a dummy document to see if I could determine how it was normally done, I came up with something like the following:

SqlDataSource dataSource = new SqlDataSource();
// code removed
dataSource.Selecting += GenericSelecting;
// code removed

The GenericSelecting is as follows:

protected void GenericSelecting(object sender, SqlDataSourceSelectingEventArgs e) {
	e.Command.CommandTimeout = 6000;
}

Excessive timeout, yes, but it works.

Why does this need to be done? Well, even if you set a timeout on the connection string that only determines how long the initial connection to Sql will go before it times out. The commands against that data source, however, are outside of that timeout. Which means that if your Sql takes more than 30 seconds (IIRC), you get a nice timeout error.

In this case the stored procedure was pretty massive (granted, it wasn't taking 6000 seconds), so a timeout had to be used.

• c#
• mssql
• .net