Dapper Call stored procedure and map result to class

Question

I have a T-SQL stored procedure:

CREATE PROCEDURE [dbo].[GetRequestTest] 
        @RequestId UNIQUEIDENTIFIER
AS
BEGIN
    SELECT 
        Request.Amount,
        Request.Checksum 
    FROM 
        Request 
    WHERE
        RequestId = @RequestId
END

C# mapping class:

public class CustomTest : Itest
{
    public decimal Amount {get;set;}
    public string Checksum { get; set; }
}

I'm calling trying to invoke stored procedure by using Dapper:

public void Load(CustomTest obj, Guid RequestId)
{
    using (var con = base.GetClosedConnection())
    {
        con.Open();

        var p = new DynamicParameters();
        p.Add("@RequestId", dbType: DbType.Guid, direction: ParameterDirection.Input);               

        var result = con.ExecuteReader("[dbo].[GetRequestTest]", param: p, commandType: CommandType.StoredProcedure);

        while (result.Read())
             obj.Amount = (decimal)result["Amount"];
    }            
}

But result is null

I tried to call to put SQL statement from stored procedure directly into C# code - and it works fine, but it doesn't work with stored procedure.

Any ideas - how to make it work?

Shirlshirlee

Answer 1

You call wrong method:

public void Load(CustomTest obj, Guid RequestId)
{
    using (var con = base.GetClosedConnection())
    {
        con.Open();                

        //result is list of CustomTest
        var result = db.Query<CustomTest>("GetRequestTest", new {RequestId},
                         commandType: CommandType.StoredProcedure);
    }            
}

How to use dapper: https://github.com/StackExchange/dapper-dot-net

Socle

Answer 2

using (var con = base.GetClosedConnection())
{
    var result = conn.Query<CustomTest>("exec [dbo].[GetRequestTest] @id", new {Id = RequestId});
}

Column names stored procedure or query returns should be same as CustomTest`s property names (e.g. Amount, Checksum). As result you will receive IEnumerable filled with appropriate data.

Swords

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run. – Softa

@MaximilianPeters, thank you for comment. Answer updated. – Swords

Answer 3

If you are like me, you came here from Google because you need to call a stored procedure and you can't change that stored procedure, you just need to map the return results to a normal looking POCO.

Query and QuerySingle/First all return dynamic. So you can just manually map like this most of the time:

using(var connection = new SqlConnection(connectionString))
{
    //Set up DynamicParameters object to pass parameters  
    DynamicParameters parameters = new DynamicParameters();   
    parameters.Add("id", 1);  
    
    //Execute stored procedure and map the returned result to a Customer object  
    var res = conn.QuerySingleOrDefault("GetCustomerById", parameters, commandType: CommandType.StoredProcedure);

    var customer = new Customer()
    {
        MyProperty = res.my_prop,
    };
}

Franza