source: WineTrackerFinal/WineTrackerWebApi/Controllers/WineController.cs

using System.Data;
using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Npgsql;
using WineTrackerWebApi.Helpers;
using WineTrackerWebApi.Models.ErrorHandle;
using WineTrackerWebApi.Models.Wine;

namespace WineTrackerWebApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class WineController : ControllerBase
    {
        private readonly string _connectionString;

        public WineController(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        // GET: api/Wine
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Wine>>> GetWine()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select 
                            w.wine_id as WineId,
                            wt.wine_type_id as WineTypeId,
                            w.wine_name as WineName,
                            wt.wine_type_name as WineTypeName,
                            w.base_price as WineBasePrice,
                        TO_CHAR(w.year_produced, 'DD-MM-YYYY') as WineYearProduced
                    from wine w 
                    join wine_type wt on w.wine_type_id = wt.wine_type_id
                ";
                var wines = await db.QueryAsync<Wine>(query);
                return Ok(wines);
            }
        }

        // GET: api/Wine/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Wine>> GetWine(int id)
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                var parameters = new { id = id };

                const string query = $@"
                    select 
                            w.wine_id as WineId,
                            wt.wine_type_id as WineTypeId,
                            w.wine_name as WineName,
                            wt.wine_type_name as WineTypeName,
                            w.base_price as WineBasePrice,
                            TO_CHAR(w.year_produced, 'YYYY-MM-DD') as WineYearProduced 
                    from wine w 
                    join wine_type wt on w.wine_type_id = wt.wine_type_id
                    where w.wine_id = @id   
                ";
                var wine = await db.QueryFirstAsync<Wine>(query, parameters);
                return Ok(wine);
            }
        }

        // PUT: api/Wine/5
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPut]
        public async Task<IActionResult> PutWine([FromBody] Wine wine)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { 
                            WineName = wine.WineName.Trim(), 
                            WineTypeId = wine.WineTypeId, 
                            WineBasePrice = wine.WineBasePrice, 
                            WineYearProduced = DateTime.Parse(wine.WineYearProduced),
                            WineId = wine.WineId 
                        };

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Wine", parameters.WineName, entityId: parameters.WineId);

                        string query = $@"
                        update wine
                        set 
                            wine_name = @WineName,
                            wine_type_id = @WineTypeId,
                            base_price = @WineBasePrice,
                            year_produced = @WineYearProduced
                        where wine_id = @WineId
                        ";
                        await db.ExecuteAsync(query, parameters, transaction: transaction);

                        transaction.Commit();
                        return Ok();
                    }
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }

        // POST: api/Wine
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<IActionResult> PostWine([FromBody] Wine wine)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { 
                            WineName = wine.WineName.Trim(), 
                            WineTypeId = wine.WineTypeId, 
                            WineBasePrice = wine.WineBasePrice, 
                            WineYearProduced = DateTime.Parse(wine.WineYearProduced)
                        };

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Wine", parameters.WineName);

                        string query = $@"insert into wine (wine_name, wine_type_id, base_price, year_produced) values (@WineName, @WineTypeId, @WineBasePrice, @WineYearProduced)";
                        await db.ExecuteAsync(query, parameters, transaction: transaction);

                        transaction.Commit();
                        return Ok();
                    }
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }

        // DELETE: api/Wine/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteWine(int id)
        {
            try
            {
                //Check if there is a shipment load linked to the wine we are trying to delete, if so return a message and dont let the user delete the wine.
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { id = id };

                        string wineLinkToShipmentLoadQuery = $@"
                            select count(*)
                            from shipment_load sl 
                            where wine_id = @id";

                        var isWineLinkedToShipmentLoad = await db.QueryFirstAsync<int>(wineLinkToShipmentLoadQuery, parameters, transaction: transaction);
                        if (isWineLinkedToShipmentLoad > 0)
                            return BadRequest(new ErrorHandler { Name = "Wine Cannot Be Deleted", Description = "Wine cannot be deleted because it is linked to a shipment load" });

                        string query = $@"delete from wine where wine_Id = @id";
                        await db.ExecuteAsync(query, parameters, transaction: transaction);

                        transaction.Commit();
                        return Ok();
                    }
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }
    }
}