Parameter not provided to the stored procedure when I use Dapper DynamicParameter from C# dictionary

Question

I'm using Dapper DynamicParameters in order to send a parameters dictionary from my .NET Core application to SQL Server, but I get an error:

Parameter @cityName1 is not provided

But when I trace the query, I can see it is present.

This is the code :

Fill in the dictionary:

Dictionary<string, object> cities = new Dictionary<string, object>();
cities.Add("@cityName1", "New-York");
cities.Add("@cityId1", 123);

The call to Dapper:

DynamicParameters dbArgs = new DynamicParameters();

foreach (var pair in cities)
{
   dbArgs.Add(pair.Key, pair.Value);
}

return await connection.QueryAsync<Provider>(SQL_GET_LINKS_BY_CITIES, dbArgs);

The stored procedure:

CREATE PROCEDURE [dbo].[hp_GetLinksByCities]
    (@cityName1 NVARCHAR(25),
     @cityId1 INT)
AS
BEGIN
    -- code
END

The trace from the profiler:

exec sp_executesql N'[dbo].[hp_GetLinksByCities]',N'@cityName1 nvarchar(4000),@cityId1 int',@cityName1=N'Marseille',@cityId1=1970

The error

Original (in French):

La procédure ou fonction 'hp_GetLinksByCities' attend le paramètre '@cityName1', qui n'a pas été fourni.

Translated to English:

The stored procedure or function 'hp_GetLinksByCities' is expecting a '@cityName1' parameter which was not supplied

When I executed this code in SSMS, I got the same error, but with this syntax, it's working fine :

exec [dbo].[hp_sp_GetDirectoryPageLinksByCities] @cityName1=N'Marseille',@cityId1=1970

Usually, Dapper sends order that way and It's working very well. Why does it act differently this time ?

I have no clue of what's wrong... do I need to use the DynamicParameter differently?

Thanks for any help :)

Answer 1

You're missing commandType: CommandType.StoredProcedure in the call to Dapper, so it is issuing it as a vanilla text query, which means it is issuing just hp_GetLinksByCities by itself as a raw command, which doesn't pass down the args.

Note: we've discussed interpreting single word commands as stored procedures automatically, but to date: we haven't made that change - so the command-type needs to be specified explicitly.