Sohan's Blog

Living the Developer's Life

How to Set Default Application Wide CommandTimeout in LINQ to SQL DataContext Subclasses

Sometimes you have written long running stored procedures and you need to invoke the stored procedures through your LINQ to SQL class. By default the SqlCommand has a timeout value of 30 seconds and in some instances you may need a longer value to complete your long running database operations. In a situation like this, if you are using the default time out value, you may encounter the following exception scenario,

Error: System.Data.SqlClient.SqlException: Timeout expired

Today I was looking for a solution after I encountered the above exception. In my project I am using LINQ to SQL classes to execute my stored procedures which sometimes may exceed the 30 seconds timeout value to complete the operations. I wanted to set the default timeout for my LINQ to SQL DataContext subclass generated by Visual Studio 2008 and wanted a clean approach. I came up with the following solution and think it may help some of you with similar needs.

Firstly, I can set the command timeout in the following way,

MyDataContext context = new MyDataContext();

context.CommandTimeout = 300; //Value in seconds

However, if I do it this way, then I need to do it at every place where I want the timeout value to be changed from the default value of 30 seconds. And I wanted to avoid this annoying approach. Fortunately, the auto generated DataContext subclass provides me a very simple way to get around to this problem through partial methods.

If you take a look into the auto generated DataContext subclass’s code, you will notice the presence of the following partial methods at the top-

#region Extensibility Method Definitions
partial void OnCreated();

//Some other partial Methods depending on your db

#endregion

This OnCreated() method is placed to provide you extensibility with the context constructor logic. Anytime you invoke any one of the constructors of your DataContext subclass, this method is called. However, since this is a partial method without a body, this call has no effect unless I define the body myself!

So, we are going to utilize this method. To do so, we will write another partial class along with this generated partial class (the one at the *.designer.cs file) and implement the partial void OnCreated() method in our newly added partial class where we will set our desired CommandTimeout value.

public partial class MyDataContext : System.Data.Linq.DataContext
{
partial void OnCreated()
{
//Set the timeout value to 300 = 5 Min.
base.CommandTimeout = 300;
}
}

So, we just set the value of CommandTimeout Property of the base DataContext class with just the addition of this new partial class. Also, we got rid of the annoying ‘change everywhere’ scenario in this approach. Thanks to the creators of the code generator, who were wise enough to foresee our needs, for providing us with a way to take such a clean approach!

By the way, I was interested to see a handy use of the partial method in C# ever since I first learned about it. I am happy that, I found a real life use myself!

Comments

Anonymous
thanks for thw simple solution of timeout problem,i was trying to use async thread but this is more simple.
Anonymous
cool, will keep trying to get more info on that.

BTW my hunch is that it's unlikely that these things differ when i use c#.
Sohan
Its hard to say why. Make sure you are using the right partial class, in the right namespace and the method name is actually the name of the partial method that is called from the auto generated class.

Your VB code looks good to me otherwise.
Anonymous
Can you please help me with the VB version as well

I tried converting this code into VB and these are the results
Partial Class SampleDBDataContext
Inherits System.Data.Linq.DataContext
Private Partial Sub OnCreated()
'Put your desired timeout here.
Me.CommandTimeout = 3600

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

This doesnt seem to work, any clues?
D4rkTiger
Hello,

I read your article but I can't realize the solution it should be given.

Could you please help me to implement your solution ? How do you specify your new DataContext to the LINQ request ?

Thanks
hussain
Thanks u so much