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

clinqsql-server-2008

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

Best Answer

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
}
Related Topic