.NET Tips and Tricks

Blog archive

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 at 10:05 AM


comments powered by Disqus

Featured

  • VS Code Now Has Apple Silicon Builds for Native Mac Development

    Goodbye Rosetta, hello M1. Visual Studio Code has been updated with new builds that let it run natively on machines with Apple Silicon (M1), the company's own ARM64 chips.

  • Visual Studio 2019 for Mac v8.9 Ships with .NET 6 Preview 1 Support

    During its Ignite 2021 online event for IT pros and developers this week, Microsoft shipped Visual Studio 2019 for Mac v8.9, arriving with out-of-the-box support for .NET 6 Preview 1, which the company also released recently.

  • Analyst: TypeScript Now Firmly in Top 10 Echelon (Ruby, Not So Much)

    RedMonk analyst Stephen O'Grady believes TypeScript has achieved the rare feat of firmly ensconcing itself into the top 10 echelon of his ranking, now questioning how high it might go.

  • Black White Wave IMage

    Neural Regression Using PyTorch: Training

    The goal of a regression problem is to predict a single numeric value, for example, predicting the annual revenue of a new restaurant based on variables such as menu prices, number of tables, location and so on.

Upcoming Events