Setting TimeOut on Typed DataSet TableAdapter

If you have a SQL query you’ve added to a TableAdapter that takes longer than 30 seconds to run, you’ll get a timeout error. You’ll also find that there’s no timeout property to set, so what do you do?

Fortunately, in .NET 2.0, you have partial classes. I won’t give a tutorial of partial classes in this article, but in short, it’s a way for you to add to an existing class (even one that’s already compiled), without having to inherit a new class from it. So, using a partial class, we can expose the protected timeout properties that we normally wouldn’t have access to. The code image below shows how to do that. Just type this anywhere in your project, replacing my namespaces and class names with yours. If you’re using .NET 1.0 or 1.1, you’ll have to inherit a new class from the one you want to set timeouts on. In your new class, you’ll add similar code below, but you don’t have TableAdapter’s in .NET 1.0 or 1.1. This post is specifically about .NET 2.0, but with a little work, you should be able to adapt it to 1.0 or 1.1. The key is inheritance.


namespace MyNameSpace.MyDataSetTypeTableAdapters
{
public partial class MyTableAdapter
{
public int InsertCommandTimeout
{
get { return this.Adapter.InsertCommand.CommandTimeout; }
set { this.Adapter.InsertCommand.CommandTimeout = value; }
}
public int UpdateCommandTimeout
{
get { return this.Adapter.UpdateCommand.CommandTimeout; }
set { this.Adapter.UpdateCommand.CommandTimeout = value; }
}
public int DeleteCommandTimeout
{
get { return this.Adapter.DeleteCommand.CommandTimeout; }
set { this.Adapter.DeleteCommand.CommandTimeout = value; }
}
public int SelectCommandTimeout
{
get { return this.CommandCollection[0].CommandTimeout; }
set
{
for (int x = 0; x < this.CommandCollection.Length; x++)
if (this.CommandCollection[x] != null)
((System.Data.SqlClient.SqlCommand)this.CommandCollection[x]).CommandTimeout = value;
}
}
}
}

9 Replies to “Setting TimeOut on Typed DataSet TableAdapter”

  1. Logging in: Yep, I get a lot of spam comments and, unbelievably, plenty of hate mail, on a simple tech support site. Amazing!

    Anyway, here's how to solve your problem. Change the namespaces and type names below to reflect yours.

    namespace DeleteMe2.MyDataSetTypeTableAdapters
    {
    public class MyDerivedTableAdapter: aspnet_UsersTableAdapter
    {
    public MyDerivedTableAdapter() : base()
    {
    this.CommandCollection[0].CommandTimeout = 300;
    }
    }
    }

    Then, change your ObjectDataSource's TypeName property to use this newly derived type. Now your report viewer and ObjectDataSource will instantiate an instance of your derived TableAdapter and will execute YOUR constructor, which sets the timeout to 5 minutes in my example above.

  2. Alex,

    You need to implement the partial class presented in this article. After you do that, there are several events you can hook into on your page. The most obvious of which is the Page_Load(…) event handler. You'll have access to your object data source from the code behind in the Page_Load event. In that data source object is a reference to the TableAdapter. You can explicitly set the newly exposed property there, in the Page_Load(…) event. This will happen before your visual component triggers the query.

  3. Thanks for the prompt reply!

    This is a .net 2.0 app, although I'm learning 3.5 so will give that a go also.

    My tableadapter is used by an objectdatasource and a .rdlc file via a reportviewer, so I'm not explicitly creating the tableadapter – that's why I have the problem with when to set the timeout – because I'm not calling Fill or anything myself. Is there an event which I can hook into, which fires after the class has been created but before the select is performed?

    (Hope this question isn't too clueless!)

    Cheers,
    Alex.

  4. Alex,

    As long as you set the property before you execute an insert, update, delete, or select that requires the extra time, you should be good. That means, before calling any of the methods on the TableAdapter that talks to the database. Those would be the .Update(…), .FillByXXX(…) and .GetDataByXXX(…) methods.

    To gain access to the private commands, just implement a partial class (same name as your TableAdapter), like I did in this article, and create your own, public command properties that get and set the private ones. Then, from your app code that uses your class, you've got public access to those private commands.

    If you're targeting .NET 3.0 or higher, the timeout and the command objects are already exposed in the TableAdapter's .adapter property.

  5. When would you use the property to set the value, for example in the Select case? I'm a bit of a noob and understand conceptually what you're doing, but I don't know how to go about actually implementing it. I'm using VB, and because I'm doing a website and not an app I don't get the nice .vb files in the project but instead have to look at the temporary files hidden away on the C drive to see what code is created. In that file there's a method called InitCommandCollection(). I want access to the private array of sql commands _commandCollection once that method has been called but have no idea how to do this. If you can set me straight here (in either c#, which I'm learning, or vb.net, which I know a little better, then I'd really appreciate it.

    Cheers,
    Alex.

  6. You’re Welcome!

    Unfortunately, I’ve not had a need yet for Reporting Services. Is there a Reporting services user out there that can answer anonymous’ question?

  7. Hi I’m using tableadapters as the data source for sql server reports and have encountered the 30 second timeout problem. Thanks for posting you partial classes solution but SSRS instatiates the tableadapter for you so how do I call the partial class with a new timeout value?

Leave a Reply