Set CommandTimeout used in Strongly Typed DataSet TableAdapter?

Question

Preamble:

So, over the past 5 years or so various applications and tools have been written here at my company. Unfortunately many of the people who developed these applications used strongly typed datasets, I'm considering outlawing them in our shop now...

One of the larger processes that used strongly typed datasets is now timing out... I intend to rewrite the the whole process using nHibernate in the next few months but for the moment I need to change the timeout to allow our users to use the process, albeit slowly... Unfortunately Microsoft made the commandtimeout methods private so I can't access them directly.

The only solution I've come across so far is to create a partial class for each TableAdapter and include the timeout methods there...

This is quite clunky as it would mean adding partial classes for quite a few TableAdapters...

Anyone know of a more efficient way to handle this?

Answer 1

I "solved" this using reflection. (While the VS2010 model allows exposing the Adapter property, the SelectCommand, etc, will be null prior to GetData, for instance.)

The "ugly code but functional code" I am currently using:

void SetAllCommandTimeouts(object adapter, int timeout)
{
    var commands = adapter.GetType().InvokeMember(
            "CommandCollection",
            BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.NonPublic,
            null, adapter, new object[0]);
    var sqlCommand = (SqlCommand[])commands;
    foreach (var cmd in sqlCommand)
    {
        cmd.CommandTimeout = timeout;
    }
}

// unfortunately this still requires work after a TableAdapter is obtained...
var ta = new MyTableAdapter();
SetAllCommandTimeouts(ta, 120);
var t = ta.GetData();

It is not really possible to type adapter better (although perhaps to a Component), due to lack of common base/interfaces.

Happy coding.

Answer 2

You don't say what language you're using. The following is in VB.NET since I happened to find such an example first:

Namespace AdventureWorksPurchasingDSTableAdapters
    Partial Public Class SalesOrderHeaderTableAdapter
    Public Property SelectCommandTimeout() As Integer
        Get
        Return Adapter.SelectCommand.CommandTimeout
        End Get
        Set(ByVal value As Integer)
        Adapter.SelectCommand.CommandTimeout = value
        End Set
    End Property
    End Class
End Namespace

Answer 3

Ok, so far as I can tell there's no shortcut / workaround for these situations. Thanks to John for trying.

My best advice is don't use MS datasets outside of quick and dirty prototyping... When your application grows and needs to be expanded you've only got the dirty left :)

Answer 4

I solved this problem easily. I went into my dataset's designer code (dataset1.designer.vb) and found the following commands, Me._commandCollection(0), Me._commandCollection(1) to Me._commandCollection(5), because I have five commands total that execute against my SQL Server 2008 database. In each (0 through 5) of these commands I wrote Me._commandCollection(0).CommandTimeout = 60, where I change 0 to the next number for the four other commands. Each of the five commands has a block of code, of which, two blocks appear below to provide you an example.

Me._commandCollection = New Global.System.Data.SqlClient.SqlCommand(5) {}

Me._commandCollection(0) = New Global.System.Data.SqlClient.SqlCommand()

Me._commandCollection(0).Connection = Me.Connection

Me._commandCollection(0).CommandTimeout = 60

Me._commandCollection(0).CommandText = "SELECT MK_QR_SUB_AND_DETAIL.*" & _ "Global.Microsoft.VisualBasic.ChrW(13) & Global.Microsoft.VisualBasic.ChrW(10)" & _ "FROM MK_QR_SUB_AND_DETAIL"

Me._commandCollection(0).CommandType = Global.System.Data.CommandType.Text    

Me._commandCollection(1) = New Global.System.Data.SqlClient.SqlCommand()

Me._commandCollection(1).Connection = Me.Connection

Me._commandCollection(1).CommandTimeout = 60

Me._commandCollection(1).CommandText = "dbo.spQtrRptTesting_RunInserts_Step1of4"

Me._commandCollection(1).CommandType = Global.System.Data.CommandType.StoredProcedure

Me._commandCollection(1).Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@RETURN_VALUE", Global.System.Data.SqlDbType.Int, 4, Global.System.Data.ParameterDirection.ReturnValue, 10, 0, Nothing, Global.System.Data.DataRowVersion.Current, False, Nothing, "", "", ""))

Me._commandCollection(1).Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@pStartADate", Global.System.Data.SqlDbType.[Date], 3, Global.System.Data.ParameterDirection.Input, 10, 0, Nothing, Global.System.Data.DataRowVersion.Current, False, Nothing, "", "", ""))

Me._commandCollection(1).Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@pEndADate", Global.System.Data.SqlDbType.[Date], 3, Global.System.Data.ParameterDirection.Input, 10, 0, Nothing, Global.System.Data.DataRowVersion.Current, False, Nothing, "", "", ""))

Me._commandCollection(1).Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@pStartBDate", Global.System.Data.SqlDbType.[Date], 3, Global.System.Data.ParameterDirection.Input, 10, 0, Nothing, Global.System.Data.DataRowVersion.Current, False, Nothing, "", "", ""))

Me._commandCollection(1).Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@pEndBDate", Global.System.Data.SqlDbType.[Date], 3, Global.System.Data.ParameterDirection.Input, 10, 0, Nothing, Global.System.Data.DataRowVersion.Current, False, Nothing, "", "", ""))

Answer 5

I've decided to create a new class in the DataSet.cs file which derives the TableAdapter classes and in the constructor it checks the App.config for commandtimeouts. I'm also add the ability to specify a command time out for a specific table adapter and if that isn't present, then check for a global value.

public class ImprovedMyTableAdapter : MyTableAdapter
{
    public ImprovedMyTableAdapter()
        : base()
    {
        int parsedInt = int.MinValue;
        string appSettingValue = System.Configuration.ConfigurationManager.AppSettings["MyTableAdapter_CommandTimeout"];
        if (string.IsNullOrEmpty(appSettingValue))
            appSettingValue = System.Configuration.ConfigurationManager.AppSettings["CommandTimeout"];
        if (!string.IsNullOrEmpty(appSettingValue) && int.TryParse(appSettingValue, out parsedInt))
        {
            foreach (var command in this.CommandCollection)
                command.CommandTimeout = parsedInt;
        }
    }
}

Answer 6

I wouldn't mess with the DataSet designer's code directly b/c it will be changed if you ever update anything in the designer. Instead create a partial class for the table adapter and give it a constructor that accepts the command timeout parameter and calls the parameterless constructor.

Then loop through the CommandCollection and set the timeout to the passed in timeout argument.

Answer 7

It's old, but sometimes you get yourself on an old solution...

First of all, create this base class:

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

namespace PhotoReport.Data
{
    public class DataAdapterCustomBase : global::System.ComponentModel.Component
    {
        public void SetTimeout(int value)
        {
            SqlCommand[] innerCommands = this.GetType().InvokeMember(
                "CommandCollection",
                BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.NonPublic,
                null, this, null) as SqlCommand[];

            if (!ReferenceEquals(innerCommands, null))
            {
                foreach (SqlCommand cmd in innerCommands)
                {
                    cmd.CommandTimeout = value;
                }
            }
        }
    }
}

After that, in the DataSet designer, select your Table Adapter and change the BaseClass property to your Custom Base Class (in my case, PhotoReport.Data.DataAdapterCustomBase).

Finally, you can use:

using (svcServiceAppointmentsTableAdapter tableAdapter = new svcServiceAppointmentsTableAdapter()) 
{
    tableAdapter.SetTimeout(60);

    // do your stuff
}