How can I change the table adapter's command timeout
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.)
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);
}
}
}
I had a couple of issues with using Mitchell Gilman's solution that I was eventually able to workaround.
First of all, I needed to make sure to use the right namespace. It took me a while to figure out that Designer file for the xsd data set actually contains two namespaces, one for the data set in general and one for the table adapters. So the first thing is to note is that the namespace for the table adapter should be used, not for the data set in general.
Secondly, the commandcollection may not always be initialized when the timeout command is used for the first time. To work around this, I called the InitCommandCollection command if this was the case.
So the adapted solution I used was
namespace xxx.xxxTableAdapters
partial class FooTableAdapter
{
/**
* <summary>
* Set timeout in seconds for Select statements.
* </summary>
*/
public int SelectCommandTimeout
{
set
{
if (this.CommandCollection == null)
this.InitCommandCollection();
for (int i = 0; i < this.CommandCollection.Length; i++)
if (this.CommandCollection[i] != null)
this.CommandCollection[i].CommandTimeout = value;
}
}
}
Hope that's helpful to people!