2

My TableAdapter is defined in an ADO.NET data set. My stored procedure is executed in 50 seconds in SSMS. When I'm trying to execute it in application I get timeout.

How to set timeout connection value for TableAdapter?

Quality Catalyst
  • 6,531
  • 8
  • 38
  • 62
Piotr Bogusz
  • 21
  • 1
  • 2

2 Answers2

3

There are two places where we can set timeouts.

  1. SqlConnection
  2. SqlCommand

SqlConnection has ConnectionTimeout property. It specifies the time ADO.Net waits for when trying to connect to the server. If no connection is made to the Server (network down/server not running etc), an exception is thrown.

SqlCommand has CommandTimeout property. It specifies the wait time for the results to come back once the command is executed (you call Execute/ExecuteNoQuery etc). If no results are returned in specified time, it throws an exception. By default, the command timeout is 30 seconds. So, if the command is taking 50 seconds, you will get an exception.

For SqlConnection and SqlCommand it is simple, simply set the value of the property on the objects.

For the TableAdapter, you have two ways to set ConnectionTimeout (both lead to same output). First is to set in the connection string. Second is setting it in the Advanced properties dialogue when you setup connection for the adapter.

For CommandTimeout, you need to access the specific command from the adapter which is timing out. If the select command is blowing up, you can use this:

var ta = new YouTableAdapter();
ta.Adapter.SelectCommand.CommandTimeout = 100;
TheVillageIdiot
  • 40,053
  • 20
  • 133
  • 188
  • I set connection timeout in the connection string. It worked. But today I got "timeout connection" after 30 seconds like before. I don't know how can I solve this.. – Piotr Bogusz May 23 '17 at 06:59
  • Have you tried setting `Adapter.SelectCommand.CommandTimeout`? Can you check what timeout is it giving for `Connection` for `SelectCommand`? It can be any reason, but have you thought about paging your data to load it faster? Or setting up some indices on the server? – TheVillageIdiot May 23 '17 at 10:01
  • As I understand you cannot set CommandTimeout for typed TableAdapter because there is no public property exposed. I've extended partial TableAdapter class and set CommandTimeout value. Then I called this property from the windows form class using this TableAdapter. – Piotr Bogusz May 24 '17 at 12:15
  • Well, I don't use `TableAdapter` in my code. I had added a data source and copied the lines from `VS`. So it means that it give you the different commands to set properties. **NOTE** You cannot set **`CommandTimeout`** on `TableAdapter` itself. You have to pick a command like `Select`, `Update` etc and set timeout on that. – TheVillageIdiot May 24 '17 at 13:53
0

You need to change the time-out on the connection string and not the table adapter:

Connection timeout for SQL server

Good luck.

Laird Streak
  • 397
  • 5
  • 8
  • Thanks. It did the job. – Piotr Bogusz May 22 '17 at 23:23
  • Yesterday it worked. Today I got timeout like before- after 30 sec. – Piotr Bogusz May 23 '17 at 06:55
  • you can increase it from 30 seconds upwards not realy advisable if data is taking so long could be a relationship issue in the underlying data or just found a weird behaviour when debugging an issue IIS express caps out at 3GB ram memory usage :( Try changing it up and look at your table indexing. – Laird Streak May 24 '17 at 01:11