How to map multiple records from a single SP with Dapper-dot-net
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.
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 tab开发者_运维百科le #Posts");
}
EDIT
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.
Each call to .Read<T>()
relates to a successive results grid.
精彩评论