Home > Software engineering >  Using JSON to write data to multiple tables
Using JSON to write data to multiple tables

Time:03-14

Could you please assist me? I am still a newbie at coding. I have been trying to build an invoice form using Angular 11, and then using JSON to send the data to my web API, and then finally to write the data to a SQL database.

I have 2 classes - Invoice and InvoiceLineItems

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

I have 2 DTO's (1 for each class above)

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

and

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

My controller for Adding the Invoice:

[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);
        }

And for adding the Invoice Line Items:

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

PROBLEM: When I initially designed this, I was going to call an API to add the invoice first, and then return the invoice ID to the front-end. Thereafter I would call the InvoiceLineItems controller to create the Invoice Line Items.

However, in my front-end, I have 1 API call in JSON format as follows:

{
    "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"
        }
    ]
}

Is it possible to have a Controller that can take the 1 JSON call and write to the Invoice table, return the Invoice ID, and then write to the InvoiceLineItems (array of Line Items)?

Thanks again!

CodePudding user response:

You can use ExecuteScalar to get back the ID for the line inserted.

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

CodePudding user response:

Add CustomerInvoiceLineAddDto as a property in CustomerInvoiceAddDto like below-

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

and keep your Angular JSON format as it is.

then in your action method do something like it.

I modified your code assuming you have code something like-

  [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);
    }
  • Related