SQL Server Dapper Call stored procedure and map result to class

vngu2lb8 于 2023-10-15 发布在其他

关注(0)|答案(3)|浏览(102)

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?

sql-server

来源：https://stackoverflow.com/questions/31333937/dapper-call-stored-procedure-and-map-result-to-class

3条答案

按热度按时间

tvmytwxo1#

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

赞(0）回复(0）举报 2023-10-15

zaqlnxep2#

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.

赞(0）回复(0）举报 2023-10-15

m528fe3b3#

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,
    };
}

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Dapper Call stored procedure and map result to class

3条答案

相关问题

热门标签

最新问答