Click here to Skip to main content
15,867,330 members
Articles / Programming Languages / SQL
Article

Change The Default CommandTimeout of LINQ DataContext

Rate me:
Please Sign up or sign in to vote.
4.96/5 (19 votes)
22 May 2008CPOL2 min read 205.6K   25   27
An article that shows a simple way of changing the default value of the DataContext CommandTimeout

Introduction

At times, the execution of a LINQ to SQL query may take a longer time and exceed the default value of the DataContext class's CommandTimeout property. The following article will present a way to achieve an application-wide CommandTimeout for your DataContext class.

The Problem

LINQ to SQL codes that invoke a long running database query/stored procedure end up with a System.Data.SqlClient.SqlException: Timeout expired Exception.

The Solution

The default value of the DataContext class's CommandTimeout is set to 30 seconds. Any database queries taking a longer time to complete than 30 seconds will throw a System.Data.SqlClient.SqlException: Timeout expired Exception.

One solution to this problem is to set the value of the CommandTimeout before each time a LINQ to SQL DataContext object is created and such a query is invoked. But the problem with this approach is, it will introduce code duplication and related issues.

We actually want to be able to change the default value of the CommandTimeout property and we want to do it efficiently. Fortunately enough, the Visual Studio 2008 auto generated DataContext subclasses provide an easy way to achieve this target using extensibility through partial methods.

The C# Code

Usually, an auto generated DataContext subclass has a few partial method declarations like the following:

C#
#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
//Other DataContext specific methods
#endregion

We are interested in the method called OnCreated() here. If you take a look into the constructors of the DataContext subclass, you will notice that this partial method is called from all the overloaded versions of the constructors in the end.

For those who are new to Partial Methods, please see this MSDN documentation. For a brief, partial methods are defined as extensibility points in a class. So, if there is no definition other than the declaration for that partial method [in another partial class], the call is just ignored. However, if there is an implementation, it will be invoked as a regular method.

So, we are going to define the body of the OnCreated() method in another partial class with the same full name as that of the auto generated class and set the desired timeout value there in the following way:

C#
partial class SampleDBDataContext : System.Data.Linq.DataContext
{
    partial void OnCreated()
    {
        //Put your desired timeout here.
        this.CommandTimeout = 3600;

        //If you do not want to hard code it, then take it 
        //from Application Settings / AppSettings
        //this.CommandTimeout = Settings.Default.CommandTimeout;
    }
}

This is it! This is simple because:

  1. It remains so silent that the clients of the DataContext class do not need to take care of the CommandTimeout value.
  2. The change is only at a single place, which means no code duplication.
  3. Placing this method in a separate file in a partial class makes sure this change will persist although the auto generated class may be overwritten when a change takes place in the DBML file.

History

  • 22nd May, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Other ThoughtWorks
Canada Canada
Consultant
Read my blog at http://smsohan.blogspot.com

Comments and Discussions

 
GeneralAlso solves this error message... Pin
Tate Antrim26-Jun-15 8:35
professionalTate Antrim26-Jun-15 8:35 
QuestionGood One Pin
Tittle Joseph14-Dec-13 7:54
Tittle Joseph14-Dec-13 7:54 
QuestionNice One! Pin
Nitesh Kejriwal20-Sep-12 5:13
professionalNitesh Kejriwal20-Sep-12 5:13 
GeneralMy vote of 5 Pin
peteSJ16-Mar-12 8:19
peteSJ16-Mar-12 8:19 
GeneralGood Job Pin
shahwolf18-Jan-12 12:53
shahwolf18-Jan-12 12:53 
Generalnice one Pin
lollypop5517-Oct-11 14:45
lollypop5517-Oct-11 14:45 
GeneralMy vote of 5 Pin
maniurzy5-Jul-11 9:20
maniurzy5-Jul-11 9:20 
GeneralMy vote of 5 Pin
Cajetan D'souza12-Apr-11 5:48
Cajetan D'souza12-Apr-11 5:48 
GeneralGreat work Pin
Cajetan D'souza12-Apr-11 5:37
Cajetan D'souza12-Apr-11 5:37 
GeneralMy vote of 5 Pin
Win Thu31-Oct-10 17:25
Win Thu31-Oct-10 17:25 
This article help me to solve the time out problem
GeneralAwesome! Pin
dwatkins@dirq.net26-Oct-10 5:15
dwatkins@dirq.net26-Oct-10 5:15 
GeneralRe: Awesome! Pin
S. M. SOHAN26-Oct-10 5:30
S. M. SOHAN26-Oct-10 5:30 
GeneralSQLClient command timeout Pin
BondRidhoo11-May-10 1:40
BondRidhoo11-May-10 1:40 
GeneralCommandTimeout type Pin
jeremy.wiebe16-Mar-10 3:38
jeremy.wiebe16-Mar-10 3:38 
GeneralRe: CommandTimeout type Pin
S. M. SOHAN16-Mar-10 4:30
S. M. SOHAN16-Mar-10 4:30 
GeneralHelpful Pin
bigRahn14-Jan-10 8:30
bigRahn14-Jan-10 8:30 
GeneralRe: Helpful Pin
S. M. SOHAN14-Jan-10 9:11
S. M. SOHAN14-Jan-10 9:11 
GeneralNice one, thanks Pin
Kiran Bheemarti10-Dec-09 4:10
Kiran Bheemarti10-Dec-09 4:10 
GeneralNice. Pin
leforgeyahoo3-Feb-09 4:16
leforgeyahoo3-Feb-09 4:16 
GeneralRe: Nice. Pin
S. M. SOHAN3-Feb-09 4:51
S. M. SOHAN3-Feb-09 4:51 
GeneralRe: Nice. Pin
leforgeyahoo3-Feb-09 6:17
leforgeyahoo3-Feb-09 6:17 
GeneralRe: Nice. Pin
SLaxman19-Aug-09 7:28
SLaxman19-Aug-09 7:28 
GeneralRe: Nice. Pin
SLaxman19-Aug-09 8:27
SLaxman19-Aug-09 8:27 
GeneralNice one... thanks Pin
PCoffey2-Feb-09 7:46
PCoffey2-Feb-09 7:46 
GeneralRe: Nice one... thanks Pin
S. M. SOHAN3-Feb-09 0:33
S. M. SOHAN3-Feb-09 0:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.