Error with QueryMultiple using a stored procedure

[celluj34]

Hello!

I've been tracking this issue for a while, and got some insight that can help. Internally, you're using a Dictionary via the ParameterInfos class to store the parameters. This has some problems when executing a stored procedure that returns multiple result sets, as referenced in this Dapper issue. As per the most recent comment, if you instead use DynamicParameters instead of a Dictionary, the problem is avoided.

Would it be possible to use the DynamicParameters object instead of a dictionary when sending the command to Dapper?

[Drizin]

Hi @celluj34 - can you share a test case?

[celluj34]

Sure thing, I will try to put one together next week.

[celluj34]

Easier than I expected:

        [Test]
        public void TestStoredProcedureQueryMultiple()
        {
            //AdventureWorks does not contain a proc which returns multiple result sets, so create our own
            cn.CommandBuilder($@"CREATE OR ALTER PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo_Twice]
    @BusinessEntityID [int],
    @NationalIDNumber [nvarchar](15),
    @BirthDate [datetime],
    @MaritalStatus [nchar](1),
    @Gender [nchar](1)
AS
BEGIN
    EXEC [HumanResources].[uspUpdateEmployeePersonalInfo] @BusinessEntityID = @BusinessEntityID,
                                                          @NationalIDNumber = @NationalIDNumber,
                                                          @BirthDate = @BirthDate,
                                                          @MaritalStatus = @MaritalStatus,
                                                          @Gender = @Gender

    EXEC [HumanResources].[uspUpdateEmployeePersonalInfo] @BusinessEntityID = @BusinessEntityID,
                                                          @NationalIDNumber = @NationalIDNumber,
                                                          @BirthDate = @BirthDate,
                                                          @MaritalStatus = @MaritalStatus,
                                                          @Gender = @Gender
END
")
              .Execute();

            var q = cn.CommandBuilder($"[HumanResources].[uspUpdateEmployeePersonalInfo_Twice]")
                .AddParameter("BusinessEntityID", businessEntityID)
                .AddParameter("NationalIDNumber", nationalIDNumber)
                .AddParameter("BirthDate", birthDate)
                .AddParameter("MaritalStatus", maritalStatus)
                .AddParameter("Gender", gender);
            
            var gridReader = q.QueryMultiple(commandType: CommandType.StoredProcedure);

            gridReader.Read<dynamic>();
            gridReader.Read<dynamic>();
        }

The error that is thrown:

System.NotSupportedException : The member Comparer of type System.Collections.Generic.IEqualityComparer`1[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] cannot be used as a parameter value
   at Dapper.SqlMapper.LookupDbType(Type type, String name, Boolean demand, ITypeHandler& handler) in /_/Dapper/SqlMapper.cs:line 356
   at Dapper.SqlMapper.CreateParamInfoGenerator(Identity identity, Boolean checkForDuplicates, Boolean removeUnused, IList`1 literals) in /_/Dapper/SqlMapper.cs:line 2488
   at Dapper.SqlMapper.GetCacheInfo(Identity identity, Object exampleParameters, Boolean addToCache) in /_/Dapper/SqlMapper.cs:line 1709
   at Dapper.SqlMapper.GridReader.ReadImpl[T](Type type, Boolean buffered) in /_/Dapper/SqlMapper.GridReader.cs:line 151
   at DapperQueryBuilder.Tests.CommandBuilderTests.TestStoredProcedureQueryMultiple() in C:\Users\jgc\source\repos\DapperQueryBuilder\src\DapperQueryBuilder.Tests\CommandBuilderTests.cs:line 182

[celluj34]

@Drizin Any thoughts on how to work around this problem? It seems to be a problem specific to Dapper, but maybe using DynamicParameters is better in the long run?

[Drizin]

@celluj34 In my last commit I've updated the QueryMultiple to get a DynamicParameters and it seems to be working.
Can you confirm?

[celluj34]

Yes, I've confirmed it looks much better, thank you!

[Drizin]

New package was published. Can you confirm it's ok so we can close this?

[celluj34]

Yeah the new package is working great, thank you for fixing it!