如何使用Dapper-dot-net从单个SP映射多个记录 [英] How to map multiple records from a single SP with Dapper-dot-net

问题描述

我想在执行单个存储过程将返回50个单独选择的情况下使用Dapper，单个结果集都不会非常宽，最多可能是20或30列。下面的代码来自Dapper测试，我想知道这个示例是否是一个很好的原型。

I'd like to use Dapper in a situation where the execution of a single stored procedure will return 50 multiple separate selects, none of the individual result sets will be very wide, maybe 20 or 30 columns at most. The code below is from the Dapper Tests and I'm wondering if this example is a good prototype to use.

谢谢您，
Stephen

Thank you, Stephen

public void TestMultiMap()
        {
            var createSql = @"
                create table #Users (Id int, Name varchar(20))
                create table #Posts (Id int, OwnerId int, Content varchar(20))

                insert #Users values(99, 'Sam')
                insert #Users values(2, 'I am')

                insert #Posts values(1, 99, 'Sams Post1')
                insert #Posts values(2, 99, 'Sams Post2')
                insert #Posts values(3, null, 'no ones post')";

                connection.Execute(createSql);

            var sql = @"select * from #Posts p 
                      left join #Users u on u.Id = p.OwnerId 
                      Order by p.Id";

            var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post; }).ToList();
            var p = data.First();

            p.Content.IsEqualTo("Sams Post1");
            p.Id.IsEqualTo(1);
            p.Owner.Name.IsEqualTo("Sam");
            p.Owner.Id.IsEqualTo(99);

            data[2].Owner.IsNull();

           connection.Execute("drop table #Users drop table #Posts");
    }

编辑

这是基于Marcs答案的示例。

Here is a sample based on Marcs answer.

        const string sql = @"__sp_GetMISMOLoanInfo";
        using (var multi = _connection.QueryMultiple(sql, new { loannum = "3192381" }, commandType: CommandType.StoredProcedure))
        {
           var address = multi.Read<ADDRESS>().Single();
           var amortizationRule = multi.Read<AMORTIZATION_RULE>().Single();
           var appraiserLicense = multi.Read<APPRAISER_LICENSE>().Single();
           var automatedUnderwriting = multi.Read<AUTOMATED_UNDERWRITING>().Single();
           var avm = multi.Read<AVM>().Single();
           var borrowerDetail = multi.Read<BORROWER_DETAIL>().Single();
        }

推荐答案

这是在家中页面，但测试中应该有类似的内容：

This one is from the home page, but there should be similar in the tests:

var sql = @"...";
using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
} 

参数等正常运行，应映射

Arguments etc work as normal, and should map directly to defined parameter names if CommandType is specified.

每次调用。Read< T>（）与一个连续的结果网格。

Each call to .Read<T>() relates to a successive results grid.

这篇关于如何使用Dapper-dot-net从单个SP映射多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！