Changing SQL Query Timeout NopCommerce 4.3

1 week ago
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();
1 week ago
Could you please provide the full error stack
1 week ago
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()"
1 week ago
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
1 week ago
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.
3 days ago
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.