使用JSON将数据写入多个表

Question

你能帮我吗？我还是个编码新手。我一直在尝试使用Angular 11构建一个发票表单，然后使用JSON将数据发送到我的web API，最后将数据写入SQL数据库。

我有两个类-发票和发票行项目

public class CustomerInvoice : BaseEntity
{
public string InvoiceNumber { get; set; }
public DateTime DateRaised { get; set; }
public DateTime PaymentDueDate { get; set; }
public int SubTotal { get; set; }
public string DiscountDescription { get; set; }
public int DiscountFactor { get; set; }
public int DiscountAmount { get; set; }
public int TotalDiscount { get; set; }
public int GrandTotal { get; set; }
public FamilyGroup FamilyGroup { get; set; }
public int FamilyGroupId { get; set; }
public List<CustomerInvoiceLine> CustomerInvoiceLines { get; set; }
}

public class CustomerInvoiceLine: BaseEntity
{
public SchoolFee SchoolFee { get; set; }
public int SchoolFeeId { get; set; }
public string Description { get; set; }
public int Quantity { get; set; }
public int Amount { get; set; }
public CustomerInvoice CustomerInvoice { get; set; }
public int CustomerInvoiceId { get; set; }
}

我有2个DTO(以上每类1个(

public class CustomerInvoiceAddDto
{
public int Id { get; set; }
public string InvoiceNumber { get; set; }
public DateTime DateRaised { get; set; }
public DateTime PaymentDueDate { get; set; }
public int SubTotal { get; set; }
public string DiscountDescription { get; set; }
public int DiscountFactor { get; set; }
public int DiscountAmount { get; set; }
public int TotalDiscount { get; set; }
public int GrandTotal { get; set; }
public int FamilyGroupId { get; set; }   
}

和

public class CustomerInvoiceLineAddDto
{
public int SchoolFeeId { get; set; }
public string Description { get; set; }
public int Quantity { get; set; }
public int Amount { get; set; }
public int CustomerInvoiceId { get; set; }
}

我的添加发票控制器：

[HttpPost("addInvoice")]
public async Task<ActionResult> AddInvoice(CustomerInvoiceAddDto invoiceAdd)
{
CustomerInvoice invoice = new CustomerInvoice();
_mapper.Map(invoiceAdd, invoice);
await _unitOfWork.CustomerInvoiceRepository.AddCustomerInvoiceAsync(invoice);
var createdInvoice = await _context.CustomerInvoices
.Where(s => s.FamilyGroupId == invoiceAdd.FamilyGroupId )
.OrderBy(g => g.Id)
.LastOrDefaultAsync();
return Ok(createdInvoice.Id);
}

用于添加发票行项目：

[HttpPost]
public async Task<ActionResult> AddCustomerInvoiceLines(CustomerInvoiceLineAddDto[] invoiceLinesAddDto)
{
var invoiceLineDetails = _mapper.Map<List<CustomerInvoiceLine>>(invoiceLinesAddDto);
await _unitOfWork.CustomerInvoiceLineRepository.AddCustomerInvoiceLinesAsync(invoiceLineDetails);
return Ok();
}

问题：当我最初设计这个时，我将首先调用API添加发票，然后将发票ID返回到前端。此后，我将调用InvoiceLineItems控制器来创建InvoiceLineItem。

然而，在我的前端，我有1个JSON格式的API调用，如下所示：

{
"DateRaised": "2022-03-01T21:32:13.000Z",
"PaymentDueDate": "2022-03-08T21:32:13.000Z",
"FamilyGroupId": 12,
"subTotal": 1500,
"discountFactor": "2",
"discountAmount": 0,
"discountDescription": null,
"totalDiscount": 0,
"grandTotal": 1500,
"invoiceLineItems": [
{
"schoolFeeId": 1,
"quantity": "1",
"unitPrice": "1500"
},
{
"schoolFeeId": 2,
"quantity": "1",
"unitPrice": "3000"
}
]
}

是否可以有一个控制器，它可以接受1 JSON调用并写入Invoice表，返回Invoice ID，然后写入InvoiceLineItems(行项目数组(？

再次感谢！

Answer 1

将CustomerInvoiceLineAddDto添加为CustomerInvoiceAddDto中的属性，如下所示-

public class CustomerInvoiceAddDto
{
public int Id { get; set; }
public string InvoiceNumber { get; set; }
public DateTime DateRaised { get; set; }
public DateTime PaymentDueDate { get; set; }
public int SubTotal { get; set; }
public string DiscountDescription { get; set; }
public int DiscountFactor { get; set; }
public int DiscountAmount { get; set; }
public int TotalDiscount { get; set; }
public int GrandTotal { get; set; }
public int FamilyGroupId { get; set; }   
public List<CustomerInvoiceLineAddDto> CustomerInvoiceLineDto {get;set;}
}

并保持您的Angular JSON格式原样。

然后在你的行动方法中做一些类似的事情。

我修改了你的代码，假设你有类似的代码

[HttpPost("addInvoice")]
public async Task<ActionResult> AddInvoice(CustomerInvoiceAddDto invoiceAdd)
{
CustomerInvoice invoice = new CustomerInvoice();
_mapper.Map(invoiceAdd, invoice);
await _unitOfWork.CustomerInvoiceRepository.AddCustomerInvoiceAsync(invoice);
var createdInvoice = await _context.CustomerInvoices
.Where(s => s.FamilyGroupId == invoiceAdd.FamilyGroupId)
.OrderBy(g => g.Id)
.LastOrDefaultAsync();
var invoiceLineDetails = _mapper.Map<List<CustomerInvoiceLine>>(invoiceAdd.CustomerInvoiceLineDto);
foreach (var line in invoiceLineDetails)
{
line.CustomerInvoiceId = createdInvoice.Id;
}
await _unitOfWork.CustomerInvoiceLineRepository.AddCustomerInvoiceLinesAsync(invoiceLineDetails);
return Ok(createdInvoice.Id);
}

Answer 2

您可以使用ExecuteScalar获取插入行的ID。

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=dotnet-plat-ext-6.0