Giving Your Database Updates Enough Time
My client was having a problem processing some very large batches of data in their online application: Their SQL updates were timing out. I offered to look at ways of fixing the problem (more on that in a later tip) but, in the meantime, the client asked me to see about "fixing the timeout problem."
To give the updates more time to complete, my client already tried playing with the ConnectionTimeout value in the connection string used to connect to the database. However, that value just controls how long ADO.NET will wait when opening a connection -- it has no effect on the time allowed for an update statement to complete.
To fix the update time, you need to set either the CommandTimeout property on the ADO.NET Command object or, if you're using Entity Framework and LINQ, the CommandTimeout property on the ObjectContext object.
In ADO.NET, the code looks like this:
Dim cmdUpdateStatus As New SqlCommand
cmdUpdateStatus.CommandTimeout = 120
With LINQ and Entity Framework with the ObjectContext, the code looks like this:
Dim doc As New MyObjectContext
doc.CommandTimeout = 120
With LINQ and Entity Framework with the DbContext, the code is a little more complicated:
Dim dbc = New MyDbContext
Dim oc As ObjectContext CType(dbc, IObjectContextAdapter).ObjectContext
oc.CommandTimeout = 120
Setting CommandTimeout to 0 will cause your application to wait until your update command completes, however long that takes. As tempting as that option is, you could wait forever, so I don't recommend it: set a value, however big, for your timeout so that you know it will, eventually, come to an end.
Posted by Peter Vogel on 12/18/2014