1

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;
    }
}
Yong Shun
  • 35,286
  • 4
  • 24
  • 46
Ibanez1408
  • 4,550
  • 10
  • 59
  • 110
  • 1
    Can you show the Stored Proc that you used? – Yong Shun Feb 02 '23 at 06:29
  • Hi @Ibanez, I think you should use `ExecuteAsync` instead of `QueryAsync` as you just want the value from the output parameter, but don't need the result from the SELECT statement. Check out my latest answer. Thanks. – Yong Shun Feb 02 '23 at 06:52
  • Please don't add your solution to your question. If the answer you have accepted doesn't contain a full answer then feel free to add your own. – Dale K Feb 02 '23 at 09:36

1 Answers1

3

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

  1. Dapper/ProcedureTest.cs (TestDateTime2LosePrecisionInDynamicParameters method)

  2. Using Parameters With Dapper (Dapper Output Parameter section)

Yong Shun
  • 35,286
  • 4
  • 24
  • 46