Managing TableAdapter Timeouts

 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 \{

Set a breakpoint in the property Get routine on the Return statement and then run the code shown in Listing 1. When Visual Studio stops at the breakpoint, hover over the Me._Connection (this._commandCollection in C#) object. (The object gets written to the CommandCollection on initialization.) Click on the plus sign to expose the underlying property state of the _CommandCollection and SqlCommand objects it contains, as shown in Figure 2. 

Figure 2

What you're looking at here is the _commandTimeout property exposed behind the scenes in the generated code—that's the property you need to set to affect the query timeout value and that's what the partial class I'm about to create will do. A warning: It might be tempting, but don't try to change the generated code. It's an exercise in frustration because this code is overlaid whenever the TableAdapter designer is invoked.

Implementing Timeout Properties in a Partial Class

To create a partial class against the generated TableAdapter code, you'll need to go to Solution Explorer and right-click the TableAdapter .XSD file created by the TableAdapter Configuration Wizard and choose View Code. This opens a new tab (named after the data source) in the Visual Studio code editor that contains the following two lines of code:

Partial Class northwndDataSet
End Class

In this example, the TableAdapter is named northwndDataSet.xsd. That name is a clue to the name of the generated TableAdapters (there could be several). This process must be repeated for each and every TableAdapter and in each and every data set and data source your project uses (another argument for an easier way to set this important property).

      To build your custom partial class, follow these steps:

Implementing the DML CommandTimeout Properties and the SELECT Timeout Property

The code shown in Listing 2 illustrates the Partial class needed to implement the four CommandTimeout properties. Of course, this assumes you need to implement timeout properties for the InsertCommand, UpdateCommand and DeleteCommand Commands in the first place. Note that these are fairly simple property set/get routines that access the generated DML queries that are generated (once). That is, regardless of the number of Fill methods you create, all DML operations are done via these three commands when changes must be made to the Data Source table.

The SelectCommandTimeout property near the end of Listing 2 is another matter. In this case, you must reference the CommandCollection directly. If you step through to the underlying code, you'll discover that Me.Adapter.SelectCommand doesn't exist—not until CommandCollection is initialized. Simply referencing it does the trick. Because you know that the first Fill method's SelectCommand is placed in CommandCollection(0), you know where to find the SELECT command's query timeout setting. This means you can change the Timeout property for each Fill you want to alter. It's a pain, and it's not necessary to change each command in some cases. Yes, this is getting snaky.

Again, I think it would be a lot better if Microsoft made a few changes to TableAdapter to implement this correctly via a direct property setting in the designer. The approach I've shown here is far too expensive and delicate to be used without caution. But frankly, it's all we have.