Control TableAdapter Command Timeout Globally

Question

I have a DataSet with a QueriesTableAdapter. In order to control the SqlCommand.CommandTimeout I've added a partial class called QueriesTableAdapter with a public method called ChangeTimeout.

partial class QueriesTableAdapter
{
    public void ChangeTimeout(int timeout)
    {
        foreach (System.Data.SqlClient.SqlCommand cmd in CommandCollection)
        {
            cmd.CommandTimeout = timeout;
        }
    }
}

For every DataSet I have that has a QueriesTableAdapter, I can set the CommandTimeout prior to executing.

using (NameSpace.DataSet.DataSetTableAdapters.QueriesTableAdapter ta =
new NameSpace.DataSet.DataSetTableAdapters.QueriesTableAdapter())
{
    ta.ChangeTimeout(3600);
    ta.DoSomething();
}

This works well is most cases because the "QueriesTableAdapter" is named for you in the DataSet designer. The problem I'm running into is the TableAdapters that are uniquely named. For example, if I have a DataTable called Person and a TableAdaper called PersonTableAdapter, I have to write a PersonTableAdapter partial class in the same way I wrote the QueriesTableAdaper class. I have hundreds of DataTables with unique TableAdapter names. I don't want to create a partial class for each of those. How can I get to the underlying SqlCommand objects of a partial class in a global way?

Solution 1

for some reason, my adapter's .selectcommand was null so i ended up having to go through the CommandCollection object instead, so i thought i'd post my small change based on the previous answer above.

includes:

using System.ComponentModel;
using System.Reflection;

code:

private void ChangeTimeout(Component component, int timeout)
        {
            if (!component.GetType().Name.Contains("TableAdapter"))
            {
                return;
            }

            PropertyInfo adapterProp = component.GetType().GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance);
            if (adapterProp == null)
            {
                return;
            }           

            SqlCommand[] command = adapterProp.GetValue(component, null) as SqlCommand[];

            if (command == null)
            {
                return;
            }

            command[0].CommandTimeout = timeout;            
        }

Solution 2

All generated TableAdapters inherit from Component. Therefore, you could write a method like this that uses reflection to extract the adapter property:

    private void ChangeTimeout(Component component, int timeout)
    {
        if (!component.GetType().Name.Contains("TableAdapter"))
        {
            return;
        }

        PropertyInfo adapterProp = component.GetType().GetProperty("Adapter", BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance);
        if (adapterProp == null)
        {
            return;
        }

        SqlDataAdapter adapter = adapterProp.GetValue(component, null) as SqlDataAdapter;
        if (adapter == null)
        {
            return;
        }

        adapter.SelectCommand.CommandTimeout = timeout;
    }

You then can call it like this:

MyTableAdapter ta = new MyTableAdapter();
this.ChangeTimeout(ta,1000);

I'm assuming that since you're using typed DataSet's that you're still in .NET 2.0 which is why I didn't bother making this an extension method.

Solution 3

BFree and mark's similar solutions work well with reflection. Below is a slight refinement that I think yields neater code.

You can also change the base class that the TableAdapter uses in the DataSet designer. You can change your TableAdapter's base class to MyTableAdapterBaseClass or similar to provide the functionality you need. You can make this change quickly on all your TableAdapters by doing a 'Find in Files' and replace on your DataSets' .xsd files.

Instead of BFree's method on the caller with signature:

private void ChangeTimeout(Component component, int timeout)

you can then create a method on the callee TableAdapter's base class with signature:

public void ChangeTimeout(int timeout)

Comments

[Billy Coover]

BFree, where would I add this method?

[BFree]

Anywhere you want really. Ideally, you'd probably create a static helper class, and add this as a static method in that class.

[Alex]

An alternative option for where to put the method is in my answer.

[Riaan]

Replaced the last line command[0].CommandTimeout = timeout; with the following to cater for multiple methods in TableAdapter: foreach (SqlCommand sc in command) { sc.CommandTimeout = timeout; }