c# - Dapper.Net - QueryMultiple 与返回多个结果的存储过程一起使用
问题描述
我是 Dapper.Net 的新手,正在尝试编写自己的微 ORM,但经过一些审查后觉得 Dapper 可以满足我的要求。
我已经非常接近做到这一点,但仍然没有。我认为我当前的实现不如原生 Dapper 快。请协助并谢谢!
波科斯
public class CustomerCollections
{
public decimal CifNo { get; set; }
public string StatusCode { get; set; }
public decimal TotalPastDue { get; set; }
public int PaymentsReturned { get; set; }
public int DaysPastDue { get; set; }
public List<CollectionPayment> CollectionPayments { get; set; }
}
public class CollectionPayment
{
public decimal CifNo { get; set; }
public decimal AcctRefNo { get; set; }
public decimal PaymentReferenceNumber { get; set; }
public decimal PaymentAmount { get; set; }
public DateTime DueDate { get; set; }
public DateTime NsfDate { get; set; }
public CollectionPaymentDetail PaymentDetail { get; set; }
}
public class CollectionPaymentDetail
{
public decimal PaymentReferenceNumber { get; set; }
public string PaymentMethodType { get; set; }
public decimal PaymentAmount { get; set; }
public decimal InterestAmount { get; set; }
public decimal PrincipalAmount { get; set; }
public DateTime DueDate { get; set; }
public DateTime NsfDate { get; set; }
public string ReturnCode { get; set; }
public string AgentUser { get; set; }
}
这是我的存储过程
ALTER PROCEDURE ULPS.sp_GetCollections
(
@cifno INT
)
AS
BEGIN
SET NOCOUNT ON;
--Past Due Header Info
SELECT l.cifno AS Cifno
--, l.acctrefno
, c.status_code AS StatusCode
, CASE
WHEN LOWER(c.status_code) = 'past due' THEN SUM(l.total_past_due_balance)
ELSE 0
END
AS TotalPastDue
, SUM(p.num_payments_returned) AS PaymentsReturned
, CAST(SUM(d.days_past_due) AS INT) AS DaysPastDue
FROM dbo.loanacct l
OUTER APPLY (
SELECT h.acctrefno, COUNT(tc.transaction_description) AS num_payments_returned FROM dbo.loanacct_trans_history h
INNER JOIN dbo.loan_transaction_codes tc
ON tc.transaction_code = h.transaction_code AND LOWER(tc.transaction_description) = 'nsf fee'
GROUP BY h.acctrefno
) AS p
LEFT JOIN dbo.loanacct_statuses s
ON s.acctrefno = l.acctrefno
INNER JOIN dbo.loan_status_codes c
ON c.status_code_no = s.status_code_no
OUTER APPLY (
SELECT cifno, acctrefno, days_past_due, open_date FROM dbo.loanacct
) AS d
WHERE p.acctrefno = l.acctrefno
AND (LOWER(c.status_code) = 'write-off' OR LOWER(c.status_code) = 'past due')
AND l.status_code_no = 0 --ACTIVE ONLY
AND l.cifno = @cifno
AND d.acctrefno = l.acctrefno
AND d.cifno = @cifno
GROUP BY l.cifno
, c.status_code
--Past Due Loan List
CREATE TABLE #PastDueLoanList (
Cifno NUMERIC(10,0)
, AcctRefNo NUMERIC(10,0)
, PaymentReferenceNumber NUMERIC(9,0)
, PaymentAmount NUMERIC(12,2)
, DueDate DATETIME
, NsfDate DATETIME
)
INSERT INTO #PastDueLoanList
SELECT l.cifno AS Cifno
, h.acctrefno AS AcctRefNo
, h.payment_reference_no AS PaymentReferenceNumber
, SUM(h.payment_amount) AS PaymentAmount
, h.date_due AS DueDate
, h.nsf_date AS NsfDate
FROM dbo.loanacct l
INNER JOIN dbo.loanacct_payment_history h
ON h.acctrefno = l.acctrefno and h.nsf_flag = 1 and h.payment_number <> 0
WHERE l.cifno = @cifno
GROUP BY l.cifno
, h.acctrefno
, h.payment_reference_no
, h.transaction_reference_no
, h.payment_number
, h.date_due
, h.nsf_date
SELECT * FROM #PastDueLoanList
--Past Due Payment Details
SELECT h.acctrefno AS AcctRefNo
, h.payment_reference_no AS PaymentReferenceNumber
, m.payment_method_code AS PaymentMethodType
, SUM(CASE WHEN h.transaction_code = 0 THEN payment_amount ELSE 0 END) AS PaymentAmount
, SUM(CASE WHEN h.transaction_code = 206 THEN payment_amount ELSE 0 END) AS InterestAmount
, SUM(CASE WHEN h.transaction_code = 204 THEN payment_amount ELSE 0 END) AS PrincipalAmount
, MAX(h.date_due) AS DueDate
, MAX(h.nsf_date) AS NsfDate
, 'n/a' AS ReturnCode
, 'unknown, user' AS AgentUser
FROM dbo.loanacct l (NOLOCK)
INNER JOIN #PastDueLoanList tmp
ON tmp.AcctRefNo = l.acctrefno
INNER JOIN dbo.loanacct_payment_history h (NOLOCK)
ON h.acctrefno = l.acctrefno and h.nsf_flag = 1
INNER JOIN dbo.loan_payment_method m (NOLOCK)
ON m.payment_method_no = h.payment_method_no
GROUP BY h.acctrefno
, h.payment_reference_no
, m.payment_method_code
END
GO
这有效,但不是 Dapper 的方式。
public async Task<CustomerCollections> GetCustomerCollections(decimal cifno)
{
var collections = new CustomerCollections();
using (var multi = await DbContext.NativeContext().QueryMultipleAsync("ULPS.sp_GetCollections", new { cifno }, commandType: CommandType.StoredProcedure))
{
collections = await multi.ReadFirstOrDefaultAsync<CustomerCollections>();
collections.CollectionPayments = multi.Read<CollectionPayment>().ToList();
var details = multi.Read<CollectionPaymentDetail>().ToList();
collections.CollectionPayments.ForEach(c =>
{
details.ForEach(d =>
{
if (c.PaymentReferenceNumber == d.PaymentReferenceNumber)
c.PaymentDetail = d;
});
});
}
return collections;
}
解决方案
我会将结果集作为 JSON 返回(如果您使用的是支持它的 SQL Server 版本,例如 SQL Server 2016 或更高版本或 Azure SQL),然后使用“自定义类型处理”功能将 JSON 反序列化为您的复杂对象:
https://medium.com/dapper-net/custom-type-handling-4b447b97c620
推荐阅读
- php - 限制不同登录页面的失败登录尝试
- java - 如何使用 kotlin 在内部存储中创建文件夹和文件
- c# - C#如何以函数式风格实现多态性?
- java - 是否有可能在具有 Completable 未来的 Java 中为 IO 密集型服务获得高吞吐量(4-5 TPS),或者 NodeJs 是更好的选择
- html - 自动调整高度
- 适合内容
- pine-script - 当价格与指数移动平均线相距特定百分比距离时,如何打开/关闭交易?(松树)
- r - dplyr 和 tibbletime。对象不属于“til_time”类。时间聚合的一般方法
- javascript - 如何在禁用的输入框中禁用用户选择
- python - 通过将 URL 从其他 *.py 文件传递给 Scrapy 从 url 中获取数据
- reactjs - React Router Dom Redirect 需要刷新页面才能工作