ExecuteQueryin linq :Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Question

I got an error while executing the datacontext.ExecuteCommand(objectname)

Error:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. Sample code:

Datacontext context = new Datacontext();
tablename tb=new tablename();

string DeleteQuery="delete from table A";
context.ExecuteCommand<tb>(DeleteQuery);
DeleteQuery=string.empty;


string InsertQuery="Insert into table B(a,b,c,d)Select table from C Union All Select Table from D";
context.ExecuteCommand<tb>(InsertQuery);
InsertQuery=string.empty;

here while executing insert query i m getting an error System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

The error Page as below

Server Error in '/Portal' Application.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

Source Error:

Line 914: where ft.IsDeleted = 0 and ym.IsDeleted = 0 and ym.IsApproved = 1"); Line 915: Line 916: dc.ExecuteQuery(InsertCommand); Line 917: InsertCommand = string.Empty; Line 918:

Source File: d:\Website\IDCCircle_Staging\Portal\Default.aspx.cs Line: 916

Stack Trace:

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2062078 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5050204 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +86 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.ExecuteReader() +12 System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +1266 System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +113 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +344 System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) +83 System.Data.Linq.DataContext.ExecuteQuery(String query, Object[] parameters) +265 _Default.NHPgridbind() in d:\Website\IDCCircle_Staging\Portal\Default.aspx.cs:916 _Default.Page_Load(Object sender, EventArgs e) in d:\Website\IDCCircle_Staging\Portal\Default.aspx.cs:97 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35 System.Web.UI.Control.OnLoad(EventArgs e) +91 System.Web.UI.Control.LoadRecursive() +74 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

---

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.272

Solution

If table C and D contain a lot of rows, it may just be taking longer to run the insert than the default command timeout (30 seconds). Add this line before your insert command:

context.CommandTimeout = 240  // set timeout to 4 minutes

By the way, you should be disposing of the context when you have finished with it. The easiest way to do this is:

using (Datacontext context = new Datacontext())
{
    // your code goes here
}