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!
