gistfile1.txt

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;

namespace Edenred.WhatIsMyAddress.Controllers
{
    [ApiController]
    [Route("/")]
    public class GeneralController : ControllerBase
    {
        [HttpPost]
        public async void SettleDebt(string input)
        {
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conn01"].ConnectionString);
            connection.Open();

            var data = JsonConvert.DeserializeObject<dynamic>(input);
            try
            {
                if (data.ClientID == null || int.Parse(data.ClientID) <= 0)
                {
                    throw new Exception("Invalid Client ID");
                }
                if (data.DebtID == null || int.Parse(data.DebtID) <= 0)
                {
                    throw new Exception("Invalid Client ID");
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Invalid Client ID or Debt ID");
            }
            var clientId = int.Parse(data.ClientID);
            var debtId = int.Parse(data.DebtID);

            var command1 = new SqlCommand("SELECT * FROM clients WHERE id = " + data.ClientID.ToString(), connection);
            var clienReader = command1.ExecuteReader();
            var installmentsList = new List<int>();
            try
            {
                while (clienReader.Read())
                {
                    var command2 = new SqlCommand("SELECT * FROM installments WHERE paymentDate is null AND debtId = " + data.DebtID.ToString() + " AND clientId = " + clienReader["id"].ToString() + " ORDER BY DueDate", connection);
                    var installmentReader = command2.ExecuteReader();
                    while (clienReader.Read())
                    {
                        installmentsList.Add(installmentReader.GetInt32(0));
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Could not find the installments to be clared");
            }

            try
            {
                foreach (var item in installmentsList)
                {
                    var tran = connection.BeginTransaction();
                    var command2 = new SqlCommand("UPDATE installments SET paymentDate = getdate(), paymentMethod = " + data.PaymentMethod + " WHERE id = " + item, connection);
                    command2.ExecuteScalar();
                    tran.Commit();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Could not update installments successfully");
            }
            finally
            {
                connection.Close();
            }
        }
    }
}