Mapping OUT from SQL in Dapper

February 2, 2023

Without using Dapper, this code returns the correct result of "true":

using(connection= new SqlConnection(connectionString))
{
    using(var cmd = connection.CreateCommand())
    {
        cmd.CommandText= query;
        cmd.CommandType= CommandType.StoredProcedure;
        cmd.CommandTimeout = commandTimeout;

        var pDeviceId = new SqlParameter
        {
            ParameterName = "@DeviceId",
            DbType = DbType.String,
            Size = 150,
            Direction = ParameterDirection.Input,
            Value = parameter.DeviceId
        };
        cmd.Parameters.Add(pDeviceId);

        var pResponse = new SqlParameter
        {
            ParameterName = "@Response",
            DbType = DbType.Boolean,
            Direction = ParameterDirection.Output,
        };
        cmd.Parameters.Add(pResponse);

        await connection.OpenAsync();

        int i = cmd.ExecuteNonQuery();
        var response = (bool)cmd.Parameters["@Response"].Value;

        return response;
    }
}

But when using Dapper, I can’t get this code to work. It always returns a value of "False":

using (connection = new SqlConnection(connectionString))
{
    using(var cmd = connection.CreateCommand())
    {
        var parameters = new DynamicParameters();
        parameters.Add("@DeviceId", parameter.DeviceId);
        parameters.Add("@Response", dbType: DbType.Boolean, direction: ParameterDirection.Output);

        var reply = (await connection.QueryAsync<bool>(
            query,
            param: parameters,
            commandType: System.Data.CommandType.StoredProcedure).ConfigureAwait(false)).FirstOrDefault();
        return reply;
    }
}

SOLUTION AS PER YONG SHUN

var parameters = new DynamicParameters();
                    parameters.Add("@DeviceId", parameter.DeviceId);
                    parameters.Add("@Response", dbType: DbType.Boolean, direction: ParameterDirection.Output);

                    var reply = (await connection.QueryAsync<bool>(
                        query,
                        param: parameters,
                        commandType: System.Data.CommandType.StoredProcedure).ConfigureAwait(false)).FirstOrDefault();
                    return parameters.Get<bool>("@Response");

Thank you.

>Solution :

From here,

var reply = (await connection.QueryAsync<bool>(
            query,
            param: parameters,
            commandType: System.Data.CommandType.StoredProcedure).ConfigureAwait(false)).FirstOrDefault();

this will get the value from the SELECT statement from the stored procedure, but not the value from the Output parameter.

To get the value from the output parameter, you should do as below:

await connection.ExecuteAsync(
    query,
    param: parameters,
    commandType: System.Data.CommandType.StoredProcedure);

var reply = parameters.Get<bool>("@Response");

References