How can I change the table adapter's command timeout

Question

I'm using Visual Studio 2008 with C#.

I have a .xsd file and it has a table adapter. I want to change the table adapter's command timeout.

Thanks for your help.

Answer 1

With some small modifications csl's idea works great.

partial class FooTableAdapter
{
  /**
   * <summary>
   * Set timeout in seconds for Select statements.
   * </summary>
   */
  public int SelectCommandTimeout
  {
    set
    {
            for (int i = 0; i < this.CommandCollection.Length; i++)
                if (this.CommandCollection[i] != null)
                 this.CommandCollection[i].CommandTimeout = value;
    }
  }
}

To use it, just set this.FooTableAdapter.CommandTimeout = 60; somewhere before the this.FooTableAdapter.Fill();

---

If you need to change the timeout on a lot of table adapters, you could create a generic extension method and have it use reflection to change the timeout.

/// <summary>
/// Set the Select command timeout for a Table Adapter
/// </summary>
public static void TableAdapterCommandTimeout<T>(this T TableAdapter, int CommandTimeout) where T : global::System.ComponentModel.Component
{                
    foreach (var c in typeof(T).GetProperty("CommandCollection", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.Instance).GetValue(TableAdapter, null) as System.Data.SqlClient.SqlCommand[])
        c.CommandTimeout = CommandTimeout;
}

Usage:

this.FooTableAdapter.TableAdapterCommandTimeout(60);
this.FooTableAdapter.Fill(...);

This is a little slower. And there is the possibility of an error if you use it on the wrong type of object. (As far as I know, there is no "TableAdapter" class that you could limit it to.)

Answer 2

I have investigated this issue a bit today and come up with the following solution based on a few sources. The idea is to create a base class for the table adapter too inherit which increases the timeout for all commands in the table adapter without having to rewrite too much existing code. It has to use reflection since the generated table adapters don't inherit anything useful. It exposes a public function to alter the timeout if you want to delete what i used in the constructor and use that.

using System;
using System.Data.SqlClient;
using System.Reflection;

namespace CSP
{
    public class TableAdapterBase : System.ComponentModel.Component
    {
        public TableAdapterBase()
        {
            SetCommandTimeout(GetConnection().ConnectionTimeout);
        }

        public void SetCommandTimeout(int Timeout)
        {
            foreach (var c in SelectCommand())
                c.CommandTimeout = Timeout;
        }

        private System.Data.SqlClient.SqlConnection GetConnection()
        {
            return GetProperty("Connection") as System.Data.SqlClient.SqlConnection;
        }

        private SqlCommand[] SelectCommand()
        {
            return GetProperty("CommandCollection") as SqlCommand[];
        }

        private Object GetProperty(String s)
        {
            return this.GetType().GetProperty(s, BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance).GetValue(this, null);
        }
    }
}