C2953d47b6de83f3217b48c3584fab1c

As TableAdapters are are automatically generated by DataSet editor, setting the CommandTimeout property for TableAdapter's internal commands is a common problem (see http://www.google.com/search?q=tableadapter+commandtimeout). The solution is a partial class of the TableAdapter that adds new functionality, just as the code below.

My problem with this is that it doesn't scale well. I have several TableAdapters in my DataSet, and I don't want to repeat this code for each of them. I've been thinking for a while but I haven't found a solution: the main problem to refactor this code is that the Adapter and CommandCollection properties are private.

Anybody has a good idea? If I find an elegant solution, I'll post it here.

Thanks.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
namespace DataSetTableAdapters {

    partial class MyTableAdapter {

        public int InsertCommandTimeout {
            get { return (this.Adapter.InsertCommand.CommandTimeout); }
            set { this.Adapter.InsertCommand.CommandTimeout = value;  }
        }

        public int UpdateCommandTimeout {
            get { return (this.Adapter.UpdateCommand.CommandTimeout); }
            set { this.Adapter.UpdateCommand.CommandTimeout = value;  }
        }

        public int DeleteCommandTimeout {
            get { return (this.Adapter.DeleteCommand.CommandTimeout); }
            set { this.Adapter.DeleteCommand.CommandTimeout = value;  }
        }

        public int SelectCommandTimeout {
            get { return (this.CommandCollection[0].CommandTimeout); }

            set {
                for (int i = 0; (i < this.CommandCollection.Length); i = (i + 1)) {
                    if ((this.CommandCollection[i] != null)) {
                        ((global::System.Data.SqlClient.SqlCommand)(this.CommandCollection[i])).CommandTimeout = value;
                    }
                }
            }
        }
    }
}

Refactorings

No refactoring yet !

Cd40128e044f39d7063b5cfdeace80f6

volothamp

July 2, 2008, July 02, 2008 14:22, permalink

1 rating. Login to rate!

Fast solution using reflection and generics. For this reason, it will be slower than subclassing and could lead to security problems.

Notice that this solution has got a singleton generic pattern in it: it stores the singleton variable in a static class. I prefer it in this way, since I don't want to create TableAdapter in every class I need it.

Bye.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
using System;
using System.Data.SqlClient;
using System.Reflection;
using TableAdapterPublic.DataSet1TableAdapters;


namespace TableAdapterPublic {
    public class Accessor<T> where T : new() {
        private static T value;

        public static SqlCommand InsertCommand {
            get { return GetDataAdapter().InsertCommand; }
        }

        public static SqlCommand UpdateCommand {
            get { return GetDataAdapter().UpdateCommand; }
        }

        public static SqlCommand DeleteCommand {
            get { return GetDataAdapter().DeleteCommand; }
        }

        public static T GetInstance() {
            if (value == null)
                value = new T();
            return value;
        }

        private static SqlDataAdapter GetDataAdapter() {
            return GetProperty("Adapter") as SqlDataAdapter;
        }

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

        private static Object GetProperty(String s) {
            return typeof (T).GetProperty(s, BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance).GetValue(GetInstance(), null);
        }
    }

    public class Program {
        public static void Main() {
            Accessor<MyAdapter>.DeleteCommand.CommandTimeout = 2;
            foreach (var c in Accessor<MyAdapter>.SelectCommand())
                Console.WriteLine(c.CommandTimeout);
            Console.ReadKey();
        }
    }
}
C2953d47b6de83f3217b48c3584fab1c

Auron

July 2, 2008, July 02, 2008 16:23, permalink

No rating. Login to rate!

Thank you very much for your contribution. You made me check some advanced concepts on generics and reflection :D I find using reflection a bit like "hacking your own code", but it seems to be the only way to do a little of refactoring in this case. I've just adapted your solution to the philosophy behind the initial code (I hope you don't mind!). 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
namespace DataSetTableAdapters {

    partial class MyTableAdapter {
        public int InsertCommandTimeout {
            get { return TableAdapterAccessor<MyTableAdapter >.GetInsertCommand(this).CommandTimeout;  }
            set { TableAdapterAccessor<MyTableAdapter >.GetInsertCommand(this).CommandTimeout = value; }
        }

        public int UpdateCommandTimeout {
            get { return TableAdapterAccessor<MyTableAdapter >.GetUpdateCommand(this).CommandTimeout;  }
            set { TableAdapterAccessor<MyTableAdapter >.GetUpdateCommand(this).CommandTimeout = value; }
        }

        public int DeleteCommandTimeout {
            get { return TableAdapterAccessor<MyTableAdapter >.GetDeleteCommand(this).CommandTimeout;  }
            set { TableAdapterAccessor<MyTableAdapter >.GetDeleteCommand(this).CommandTimeout = value; }
        }

        public int SelectCommandTimeout {
            get { return TableAdapterAccessor<MyTableAdapter >.GetSelectCommand(this).CommandTimeout;  }
            set { TableAdapterAccessor<MyTableAdapter >.GetSelectCommand(this).CommandTimeout = value; }
        }
    }

    internal static class TableAdapterAccessor<TableAdapter> {

        public static global::System.Data.SqlClient.SqlCommand GetInsertCommand(TableAdapter tableAdapter) {
            return GetDataAdapter(tableAdapter).InsertCommand;
        }

        public static global::System.Data.SqlClient.SqlCommand GetUpdateCommand(TableAdapter tableAdapter) {
            return GetDataAdapter(tableAdapter).UpdateCommand; 
        }

        public static global::System.Data.SqlClient.SqlCommand GetDeleteCommand(TableAdapter tableAdapter) {
            return GetDataAdapter(tableAdapter).DeleteCommand; 
        }

        public static global::System.Data.SqlClient.SqlCommand GetSelectCommand(TableAdapter tableAdapter) {
            return GetCommandCollection(tableAdapter)[0];
        }

        private static global::System.Data.SqlClient.SqlDataAdapter GetDataAdapter(TableAdapter tableAdapter) {
            return typeof(TableAdapter).GetProperty("Adapter", global::System.Reflection.BindingFlags.NonPublic | global::System.Reflection.BindingFlags.GetProperty | global::System.Reflection.BindingFlags.Instance).GetValue(tableAdapter, null) as global::System.Data.SqlClient.SqlDataAdapter;
        }

        private static global::System.Data.SqlClient.SqlCommand[] GetCommandCollection(TableAdapter tableAdapter) {
            return typeof(TableAdapter).GetProperty("CommandCollection", global::System.Reflection.BindingFlags.NonPublic | global::System.Reflection.BindingFlags.GetProperty | global::System.Reflection.BindingFlags.Instance).GetValue(tableAdapter, null) as global::System.Data.SqlClient.SqlCommand[];
        }
    }
}
Cd40128e044f39d7063b5cfdeace80f6

volothamp

July 3, 2008, July 03, 2008 07:27, permalink

2 ratings. Login to rate!

I didn't understand your code at all, sorry.

Didn't you say that you don't want to repeat the same code for all the TableAdapter classes? So why are you writing all this boilerplate code?

I think you misunderstand the use of the class I wrote for you, once you've got the generic Accessor class, you can use the properties hidden in the TableAdapter without writing any code.

Or maybe there is something that I miss?

You've added a lot of explicit namespaces. Maybe you have copied it from the dataset generated classes? You don't need it, and they uglify the code a lot.

But most of all, you have copied and pasted the reflecting accessor method for both the properties.

That's BAD, I'm sorry.

Did you recognize that the GetDataAdapter and GetCommandCollection are the same method, except for the name of the property and the type you cast it. Use the parameters!

I'm sorry, but I will give you a 1 star to your refactoring, but I'll wait until you tell me what I can't understand.

Bye. :)

1
2
3
4
Accessor<MyAdapter>.DeleteCommand.CommandTimeout = 2; // Use it like this

var mydataset = new DataSet();
Accessor<MyAdapter>.GetInstance().Fill(mydataset);
C2953d47b6de83f3217b48c3584fab1c

Auron

July 3, 2008, July 03, 2008 10:19, permalink

No rating. Login to rate!

Oh, yes, you're right. I didn't see the whole point of your code and I realize I finally made a mess. My apologies but...

As you put it, you have to implement Accessor as a public static class and use it every time you want to set the command timeout and then fill the associated table. At the same time, you could create TableAdapters on your own, but you couldn't modify the command timeout.

So, it's like if you have two flavours of TableAdapters for each MyTableAdapter implemented in your code:

* A kind of "singleton" instance MyTableAdapter stored in the Accessor<MyTableAdapter> class: you can access this TableAdapter's SQLCommands through the accessor.
* Every time you make new MyTableAdapter in your code aouside the Accessor(with the 'new' keyword), you'll have a new "instance" of MyTableAdapter. You can't access to its SQLCommands.

That's a big downside in my opinion. It would be great if all TableAdapters could be automatically extended, but "there is no silver bullet", I suppose :)

Another downside is that you give access to all the internal commands of the singleton TableAdapter, and I think this commands shouldn't be reachable at all. My following code solves this issue.

> You've added a lot of explicit namespaces. Maybe you have copied it from the dataset generated classes? You don't need it, and they uglify the code a lot.

Yes, I know. I did it because it makes the code more fail-proof, if someone copy that code in his codebase, it will work, no matter what namespaces he had created :)

> But most of all, you have copied and pasted the reflecting accessor method for both the properties.
> That's BAD, I'm sorry.
> Did you recognize that the GetDataAdapter and GetCommandCollection are the same method, except for the name of the property and the type you cast it. Use the parameters!

Yes, you're right. I've already solved that (see code below).

> I'm sorry, but I will give you a 1 star to your refactoring, but I'll wait until you tell me what I can't understand.

I'm OK with that. In fact, I don't know if I should delete it or not.

Thanks.

This is the code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace MyDataSetTableAdapters {

    internal static class TableAdapterAccessor<TableAdapter> where TableAdapter : new() {

        private static TableAdapter instance = new TableAdapter();

        public static TableAdapter Adapter { get { return instance; } }

        public static int InsertCommandTimeout {
            get { return InsertCommand.CommandTimeout;  }
            set { InsertCommand.CommandTimeout = value; }
        }

        public static int UpdateCommandTimeout {
            get { return UpdateCommand.CommandTimeout; }
            set { UpdateCommand.CommandTimeout = value; }
        }

        public static int DeleteCommandTimeout {
            get { return DeleteCommand.CommandTimeout;  }
            set { DeleteCommand.CommandTimeout = value; }
        }

        public static int SelectCommandTimeout {
            get { return SelectCommand.CommandTimeout; }
            set { SelectCommand.CommandTimeout = value; }
        }

        private static SqlCommand InsertCommand {
            get { return DataAdapter.InsertCommand; }
        }

        private static SqlCommand UpdateCommand {
            get { return DataAdapter.UpdateCommand; }
        }

        private static SqlCommand DeleteCommand {
            get { return DataAdapter.DeleteCommand; }
        }

        private static SqlCommand SelectCommand {
            get { return CommandCollection[0]; }
        }

        private static SqlDataAdapter DataAdapter {
            get { return GetPropertyValue("Adapter") as SqlDataAdapter; }
        }

        private static SqlCommand[] CommandCollection {
            get { return GetPropertyValue("CommandCollection") as SqlCommand[]; }
        }

        private static object GetPropertyValue(string propertyName) {
            return typeof(TableAdapter).GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance).GetValue(Adapter, null);
        }
    }
}

How to use it.

1
2
3
TableAdapterAccessor<MyTableAdapter>.Adapter.ConnectionString = "...";                 // whatever...
TableAdapterAccessor<MyTableAdapter>.SelectCommandTimeout = 300;
TableAdapterAccessor<MyTableAdapter>.Adapter.Fill(...);
Cd40128e044f39d7063b5cfdeace80f6

volothamp

July 3, 2008, July 03, 2008 12:21, permalink

1 rating. Login to rate!

Ok, now I see your point.

But I think that I've confused you a bit using Generics, because they aren't the best solution here. I've used it to incorporate a Generic Singleton pattern, but they are not useful if you have alreay declared the table adapters in your class.

I understand it since you used the name "TableAdapter" for a Type variable, instead of a classic T or K :)

In this case, a standard class is the best approach.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
using System;
using System.Data.SqlClient;
using System.Reflection;

namespace TableAdapterPublic
{
    public class Accessor
    {
        public Object Value { get; set; }

        public SqlCommand InsertCommand
        {
            get { return GetDataAdapter().InsertCommand; }
        }

        public SqlCommand UpdateCommand
        {
            get { return GetDataAdapter().UpdateCommand; }
        }

        public SqlCommand DeleteCommand
        {
            get { return GetDataAdapter().DeleteCommand; }
        }

        private SqlDataAdapter GetDataAdapter()
        {
            return GetProperty("Adapter") as SqlDataAdapter;
        }

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

        private Object GetProperty(String s)
        {
           PropertyInfo prop =  Value.GetType().GetProperty(s, BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance);
           if (prop != null)
               return prop.GetValue(Value, null);
           throw new Exception(String.Format("This class hasn't got a {0} property", s));
        }
    }
}

Usage

1
2
3
4
5
6
7
8
9
    public class Program {
        public static void Main() {
            MyAdapter t = new MyAdapter();
            var a = new Accessor { Value = t };
            a.DeleteCommand = 10;
            Console.ReadKey();
        }
    }
C2953d47b6de83f3217b48c3584fab1c

Auron

July 3, 2008, July 03, 2008 13:59, permalink

No rating. Login to rate!

You see, I don't find using generics as bad. Between your code and mine, I think I (and anyone) can reach to a consensus according to our needs. Thank you very much :).

A058baa1c5763b128db4b2ca743b69bf

TC @KIN

September 22, 2008, September 22, 2008 08:13, permalink

No rating. Login to rate!

For our assumption:
Most of Command can finished in 30 seconds.
Only some task with heavy SQL job will cause command timeout.
So why donot we create a helper for TableAdapter's CommandTimeout?
You 2 are well program on the insert/update/delete/select.
How about SP? the following can well preform on TableAdapter
For the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public static void SetTableAdapterTimeout(object tableAdapter)
        {
            SetTableAdapterTimeout(tableAdapter, 300);
        }
        public static void SetTableAdapterTimeout(object tableAdapter, int timeout)
        {
            Type adapterType = tableAdapter.GetType();
            PropertyInfo commandsProperty = adapterType.GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.Instance);
            IDbCommand[] commands = (IDbCommand[])commandsProperty.GetValue(tableAdapter, null);
            foreach (IDbCommand command in commands)
            {
                command.CommandTimeout = timeout;
            }
        }
C2953d47b6de83f3217b48c3584fab1c

Auron

September 22, 2008, September 22, 2008 12:21, permalink

No rating. Login to rate!

I think our solutions did take into account stored procedures (in the TableAdapter's CommandCollection). Nevertheless, your approach seems also correct to me (although without the beauty of generics :P).

Your refactoring





Format Copy from initial code

or Cancel