Click here to download the listings for this article.
When it comes time to change the timeout of a Visual Studio-generated TableAdapter Fill method, it would be very nice if there were an exposed property in the designer to set an upper limit on the length of time the query should take. Unfortunately, there isn't, and there isn't likely to be one for many years (2012 at least). So you're on your own when you set CommandTimeout for the SELECT and DML queries you've constructed to build and update your TableAdapter.
TableAdapter has become a mainstream class to access rowsets generated from queries against SQL Server and other backend databases. Unfortunately, TableAdapter doesn't expose the CommandTimeout property of any of the generated queries that the TableAdapter manages. Sure, the ConnectionTimeout key is provided in the ConnectionString to govern how long the application waits to connect, but there's no key/value pair to set the Command timeout. By default, the generated Command objects invoked by the Fill method timeout in 30 seconds. Yes, that's long enough to return half the information in the database, but when working with comprehensive DML operations or more complex (or clumsy) queries, it might not be long enough.
On the other hand, there are many cases where I know that a query that takes longer than a couple of seconds isn't working correctly. Again, I don't have an opportunity with the Visual Studio-generated code to tune this timeout. Fortunately, there's a reasonable way to build on this functionality to your generated code—that's what I'm going to show you here.
Looking Behind the Scenes in the Generated Code
To get the Fill method Command class to timeout when you want it to, you'll need to build a partial class to extend the TableAdapter functionality. Ordinarily that would be easy, but there are a few gotchas that can blow up in your face, if you're not careful. First, consider that the TableAdapter's generated code includes a CommandCollection that contains all of the Fill methods for each DataSet it creates. So, if one creates a TableAdapter against the NorthWind Customers data table (as shown in Figure 1), a CustomersTableAdapter is created behind the scenes along with its own CommandCollection.
Figure 1
I expect most developers understand the process of creating a TableAdapter—if you need a refresher, I discussed how to run the TableAdapter Configuration Wizard in a past article (InstantDoc ID 99192) and a helpful overview is available on the MSDN site at bit.ly/9C4SGe. In the Customers TableAdapter in Figure 1, the CommandCollection has two methods: Fill and GetData. The GetData methods aren't exposed in the CommandCollection.
Generating the CommandCollection
One tricky part of this process is that the TableAdapter CommandCollection isn't built until the Fill method is executed for the first time. This isn't an insurmountable problem. I'll start by writing some code (shown in Listing 1) to instantiate the TableAdapter and execute the Fill method.
Before you execute this code, open the .VB (or .CS) file generated by the TableAdapter Configuration Wizard that implements the TableAdapter class. In my example, it's northwndDataSet.Designer.VB. Use Edit, Find and Replace, Quick Find to locate
Protected ReadOnly Property CommandCollection() As Global.System.Data.SqlClient.SqlCommand()
In C#, look for
protected global::System.Data.SqlClient.SqlCommand\[] CommandCollection {