Changing SQL Query Timeout NopCommerce 4.3

Hello,

How do i change the SQL query timeout in NopCommerce 4.3?

I'm trying to execute a query which constantly gives me a timeout exception. I need this query to be executed the way it is, so i have tried changing the connection timeout, without succes.

So far i have tried:
- Adding 'Connection Timeout=300' to my connectionstring in /App_Data/dataSettings.json
- Set '"SQLCommandTimeout": 300,' in appsettings.json
Both did not work.

Example of what i'm trying to execute

var result = _myRespository.ToList();

Could you please provide the full error stack

Thanks for your reply.

Error message: "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

Stacktrace: "   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at LinqToDB.Data.DbCommandProcessor.DbCommandProcessorExtensions.ExecuteReaderExt(IDbCommand cmd, CommandBehavior commandBehavior)\r\n   at LinqToDB.Data.DataConnection.ExecuteReader(IDbCommand command, CommandBehavior commandBehavior)\r\n   at LinqToDB.Data.DataConnection.ExecuteReader(CommandBehavior commandBehavior)\r\n   at LinqToDB.DataContext.QueryRunner.ExecuteReader()\r\n   at LinqToDB.Linq.QueryRunner.<ExecuteQuery>d__10`1.MoveNext()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at Nop.Services.Tuynder.TND_CarPartsService.GetProductsForCarPartsCatalogFilter(TND_CarPartsCatalogFilter filter, List`1& brands, Int32& pages) in C:\\Users\\rikma\\Source\\Repos\\TND_Webshop\\Webshop_4.3\\Libraries\\Nop.Services\\Tuynder\\TND_CarPartsService.cs:line 284\r\n   at Nop.Web.Controllers.CatalogController.CarPartsCatalog(TND_CarPartsCatalogSeoUrlRecord catalogSeoUrlRecord, String path, Int32 model, Int32 type, Int32 sorting, String filter_text, String filter_brand, Int32 page, String message) in C:\\Users\\rikma\\Source\\Repos\\TND_Webshop\\Webshop_4.3\\Presentation\\Nop.Web\\Controllers\\CatalogController.cs:line 200\r\n   at Nop.Web.Controllers.CatalogController.CarParts(String path, Int32 model, Int32 type, Int32 sorting, String filter_text, String filter_brand, Int32 page, String message) in C:\\Users\\rikma\\Source\\Repos\\TND_Webshop\\Webshop_4.3\\Presentation\\Nop.Web\\Controllers\\CatalogController.cs:line 168\r\n   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()"

I understand, the problem is here, we fixed it and for 4.30 version but only on GitHub, you may clone this branch with changes or made necessary changes yourself

Thanks for your help. I decided to make the necessary changes myself as I already have quite a bit of custom code in my solution.

However, in order to get it fully working, I also had to update the GetTable() method in the BaseDataProvider, to look like this:

public virtual ITable<TEntity> GetTable<TEntity>() where TEntity : BaseEntity
{
    var dataContext = new DataConnection(LinqToDbDataProvider, CurrentConnectionString, AdditionalSchema)
    {
        CommandTimeout = DataSettingsManager.SQLCommandTimeout
    };
    return dataContext.GetTable<TEntity>();
}

My timeout issues are now resolved. Thanks for your help once again.

The code in my previous post, that I added myself, introduced a bug where too many connections were created.

Instead of creating a

DataConnection

in the GetTable() method, I had to create a

DataContext

All issues are resolved now.