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?

Uniformed

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.

Pinsky

Great helpful to fix my issue. :) – Quodlibet

Great fix. I changed this line: var sqlCommand = (IDbCommand[])commands; - Then it was quite! – Rossman

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

Peachy

Right, but thats what I'm trying to avoid doing... Individually it's easy to do... If you've got several hundred table adapters for several hundred datasets it's really not very viable. – Uniformed

I can't think what else you could do. There's no global CommandTimeout property. If there were, it would still somehow have to set the individual SqlCommand.CommandTimeout properties. – Peachy

Why can one not adjust the CommandTimeout property in the Dataset Designer? And why is it still 30 seconds when i change the Connection Timeout to 300 seconds? Btw, what's the difference among these? Thanks in advance... – Counsel

Tim, if you have a question, ask a separate question. This is not a discussion group, and comments are not threads. – Peachy

This approach did not work for me. SelectCommand is null until populated in a function like GetData. – Pinsky

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 :)

Uniformed

This is a fairly narrow basis on which to condemn typed DataSets. However, it's a great basis for a feature suggestion on Connect (connect.microsoft.com/visualstudio). Once you've posted the suggestion, edit this answer with the URL of the suggestion, so that others may vote for how important we feel it is. – Peachy

Well, this issue isn't the only basis for that statement... The single biggest issue I have with datasets is the lack of intelligent defaults... If your config file doesn't have the right connection string in it then it defaults to whatever connection string it was created with... My gut feeling on that issue is that in any situation where there is any ambiguity then the only correct response is to throw an exception and die... – Uniformed

I'll put the suggestion on Connect later this eve, I wasn't ignoring your suggestion :) – Uniformed

I find that strongly-typed DataSets work well for reports ... which is the only place I use them ... – Pinsky

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, "", "", ""))

Bilek

Warning : never edit the designer files manually. This is automatically generated code, everything you write in it can be overwritten by the Visual Studio when it will regenerate code. – Denbighshire

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;
        }
    }
}

Decani

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.

Knick

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
}

Leucoderma