Get all stored procedure output parameters using generic type in dapper

user_2066282 0
Viewed 78

I will basically be asking this question again, 6 years later: Automatically mapping output parameters with Dapper

I have an interface called IDataAccess that would encapsulate any framework that is working with the database. It has couple of methods, just for calling stored procedures.

All my 'service' classes (AccountService, CustomerService, ProductService, ...) will be injected with an instance of this IDataAccess and will only be using it's methods (so no dapper classes in them, otherwise it wouldn't be a loose coupleing).

I've created a class that implements this interface and is working with an MSSQL database called SqlDataAccess.

Sounds fine and dandy, but I can't seem to get output parameters for stored procedures from dapper generically.

Here's what I've tried:

public void CallProcedure<I, O>(
    string storedProcedure,
    I inputParameters,
    O outputParameters,
    string connectionId = "Default")
{
    using IDbConnection connection =
        new SqlConnection(_config.GetConnectionString(connectionId));

    var dynamicParameters = new DynamicParameters();

    if (inputParameters != null)
        dynamicParameters.AddDynamicParams(inputParameters);

    if (outputParameters != null)
        foreach (PropertyInfo prop in outputParameters.GetType().GetProperties())
            dynamicParameters.Add(prop.Name, 
                prop.GetValue(outputParameters),
                GetDbType(prop.PropertyType),
                ParameterDirection.Output);

    connection.Execute(storedProcedure, dynamicParameters,
        commandType: CommandType.StoredProcedure);

    if (outputParameters != null)
        foreach (PropertyInfo prop in outputParameters.GetType().GetProperties())
            // 1st try
            prop.SetValue(outputParameters, dynamicParameters.Get<prop.PropertyType>($"@{prop.Name}"));
            // 2nd try
            prop.SetValue(outputParameters, dynamicParameters.GetType().GetProperty(prop.Name));
}

1st try
is not working because I can't pass prop.PropertyType as a generic type, because C# is expecting a hardcoded type, not a variable type.

2nd try
is not working because dynamicParameters is not populated when I call connection.Execute(...

3rd try
would be for this method to return dynamicParameters variable so that the user of this function can get the result with this syntax dynamicParameters.Get<int>("some_output_parameter");, with any type, not just int, but that's a no go as I would have to add using Dapper; to all my service classes, and that's closely coupling dapper with my services, which beats the purpose of having IDataAccess.

It's not the job of this class to expose all stored procedures, but to expose a generic way of calling them. It's job of the service classes to know and call stored procedures that are specific for that service.

My question is

How do I get all output parameters from stored procedure that's been called with dapper, generically?

dapper c#
1 Answers

Dapper now provides the DynamicParameters.Output function, which allows you to map a single Expression<Func<T, object>> to an output parameter.

So your function can accept an array of property or field expressions for the output object, which you can then map to the DynamicParameters object.

private DynamicParameters GetDynamicParameters<TInput, TOutput> (
    TInput inputObject,
    TOutput outputObject,
    params Expression<Func<TOutput, object>>[] outputExpressions
    )
{
    var dp = new DynamicParameters(inputObject);
    foreach (var expr in outputExpressions)
        dp.Output(outputObject, expr);

    return dp;
}

public void CallProcedure<TInput, TOutput> (
    string storedProcedure,
    TInput inputObject,
    TOutput outputObject,
    string connectionId,
    params Expression<Func<TOutput, object>>[] outputExpressions
    )
{
    using var connection =
        new SqlConnection(_config.GetConnectionString(connectionId ??  "Default"));

    var dynamicParameters = GetDynamicParameters<TInput, TOutput>(inputObject, outputObject, outputExpressions);

    connection.Execute(storedProcedure, dynamicParameters,
        commandType: CommandType.StoredProcedure);
}

public async Task CallProcedureAsync<TInput, TOutput> (
    string storedProcedure,
    TInput inputObject,
    TOutput outputObject,
    string connectionId = "Default",
    params Expression<Func<TOutput, object>>[] outputExpressions
    )
{
    using var connection =
        new SqlConnection(_config.GetConnectionString(connectionId ??  "Default"));

    var dynamicParameters = GetDynamicParameters<TInput, TOutput>(inputObject, outputObject, outputExpressions);

    await connection.ExecuteAsync(storedProcedure, dynamicParameters,
        commandType: CommandType.StoredProcedure);
}
user_148689970
Related