0

I'm getting a 'The wait operation timed out' exception while making a method call to SqlDataAdapter.Update(DataTable).

Other people have suggested that I change the CommandTimeout property of the SqlCommand being performed, but since I'm simply calling the update function, I'm not aware of any commands being exposed where I can set the timeout property for. Changing the DataContext's CommandTimeout property doesn't seem to make a difference either.

I've also read that running sp_updatestats on the db before hand will fix the problem but that doesn't seem to help in this case.

For some context I'm trying to update a table's contents by:

  • loading its contents into a DataTable
  • updating the DataTable's values with those from a second dataset
  • calling the Update function on the set returned by DataTable.GetChanges

If there's something wrong with the way I'm approaching the problem or am I looking at the wrong things?

James Salas
  • 176
  • 1
  • 5
  • How much data are you loading into the DataTable? – Bob Horn Jun 02 '14 at 23:32
  • The amount of data changes depending on the inputs, but in the specific case I'm testing there are about 20000 rows of data. – James Salas Jun 03 '14 at 14:19
  • I'm guessing that's your issue. Do you need to update 20,000 rows all at once? – Bob Horn Jun 03 '14 at 17:59
  • In other words, using a DataTable is how many beginners update data. It would be better to update the data as it happens, so you don't have to update 20,000 rows at once. But, if you have no choice, the DataTable approach isn't the way to go. – Bob Horn Jun 03 '14 at 18:45
  • In this case yeah, what other approaches would you suggest? Is the reason to avoid the DataTable method the fact that separate commands are generated for each row? – James Salas Jun 03 '14 at 18:51
  • You definitely don't want a SQL call for every updated row. "If you are working with a SqlDataAdapter for your updates, you can set the UpdateBatchSize property of the DataAdapter to something else than the default of 1." See http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_27245397.html. Also see http://stackoverflow.com/a/20635881/279516. – Bob Horn Jun 03 '14 at 19:01

0 Answers0