DapperLib / DapperLib/Dapper

Sql Exception thrown trying to exec a stored proc claiming missing param that isnt missing, works for a period of time then stops

Open
#1,891 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

net 7 web app
Dapper 2.0.123

My application recently started throwing sql exceptions when executing a stored procedure with dapper that has parameters. The procedure is only called in one place in my code and I added some logging to make sure params are being set. The weirdest part is the code runs fine for a few hours and then the error starts happening. A reset of the appPool fixes the issue until it happens again. I would appreciate any guidance on the issue. The original code was only using dynamic params, I refactored to use a dictionary to see if anything would change. It did not.

public async Task<List<GroupEntity>> GroupEntityList(
        string user,
        string searchGroupEntityName,
        string searchGNPI,
        int? searchOrganization,
        string searchSortColumn,
        string searchSortDirection)
    {
        string storedProcedure = "[DATA].[sp_UI_GroupEntity]";

        Dictionary<string, object> dictionary = new Dictionary<string, object>
        {
            { "@Action", 1 },
            { "@User", user },
            { "@searchGroupEntityName", searchGroupEntityName },
            { "@searchGNPI", searchGNPI },
            { "@searchOrganization", searchOrganization },

            { "@searchSortColumn", searchSortColumn },
            { "@searchSortDirection", searchSortDirection }
        };

        try
        {
            _logger.LogInformation("Attempting to use stored proc:{storedProcedure} with parameters:{dictionary}", storedProcedure, JsonConvert.SerializeObject(dictionary));

            using SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("MPRI"));
            connection.Open();

            IEnumerable<GroupEntity> result = await connection.QueryAsync<GroupEntity>(storedProcedure, new DynamicParameters(dictionary),
                commandType: CommandType.StoredProcedure);
            {

                return result.ToList();
            }
        }
        catch (Exception e)
        {
            _logger.LogError(e, e.Message, dictionary, user, searchGroupEntityName, searchGNPI);
            throw;
        }
    }

Information from the logger statement in the try block:

Attempting to use stored proc:"[DATA].[sp_UI_GroupEntity]" with parameters:"{"@Action":1,"@User":"someusername","@searchGroupEntityName":null,"@searchGNPI":null,"@searchOrganization":null,"@searchSortColumn":null,"@searchSortDirection":null}"

Error Message:
System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'sp_UI_GroupEntity' expects parameter '@Action', which was not supplied.
at System.Data.SqlClient.SqlCommand.<>c.b__126_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 418
at MPRI.Repository.GroupEntityRepository.GroupEntityList(String user, String searchGroupEntityName, String searchGNPI, Nullable1 searchOrganization, String searchSortColumn, String searchSortDirection) in C:\Source\repos\ASPNET_MPRI\MPRI\Repository\R_GroupEntity.cs:line 55 at MPRI.Controllers.GroupEntityController.Index(String searchGroupEntityName, String searchGNPI, String searchSortColumn, String searchSortDirection, Nullable1 searchPageNumber, Nullable1 resetFilter) in C:\Source\repos\ASPNET_MPRI\MPRI\Controllers\C_GroupEntity.cs:line 150 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT`1.ProcessRequestAsync()
ClientConnectionId:8545e7f4-3f46-4bee-a856-9f7e0a5b2a83
Error Number:201,State:4,Class:16

Providing the create part of the stored proc jic:

CREATE PROCEDURE [DATA].[sp_UI_GroupEntity]
(
  @Action TINYINT
 ,@User VARCHAR(50)
 ,@GroupEntityID INT = NULL
 ,@GroupEntityName VARCHAR(100) = NULL
 ,@GroupEntityTypeID INT = NULL
 ,@GNPI BIGINT = NULL
 ,@StartDate DATE = NULL
 ,@EndDate DATE = NULL
 ,@GroupEntityPhone VARCHAR(15) = NULL
 ,@GroupEntityFax VARCHAR(15) = NULL
 ,@GroupEntityBillingName VARCHAR(200) = NULL
 ,@Comments VARCHAR(4000) = NULL
 ,@Annotation VARCHAR(4000) = NULL
 ,@searchGNPI VARCHAR(15) = NULL
 ,@searchGroupEntityName VARCHAR(100) = NULL
 ,@searchOrganization INT = NULL
 ,@searchActiveFlag BIT = NULL
 ,@searchSortColumn VARCHAR(100) = NULL
 ,@searchSortDirection VARCHAR(50) = NULL
)

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the supplied call in MPRI.Repository.GroupEntityRepository.GroupEntityList and the stored procedure definition, then inspect Dapper's SqlMapper.Async.cs around line 418. Reproduce the intermittent failure with the logged parameters and determine whether the missing @Action originates in Dapper, SQL Server, or the application. Done requires a confirmed cause and a narrowly scoped fix or reproducible upstream report.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.