= Advanced Application Development

== Transactions

==== Registering a new consumer

{{{#!div style="text-align: justify; width: 100%;"
This transaction is used during consumer registration to keep the creation of the account consistent across multiple tables. If any part of the registration fails, all database changes are rolled back, preventing partially created consumer accounts.
}}}

{{{
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Register(RegisterViewModel model)
{
    if (!ModelState.IsValid)
        return View(model);

    if (_context.Users.Any(x => x.Username == model.Username))
    {
        ModelState.AddModelError(
            "Username",
            "Username is already taken.");

        return View(model);
    }

    if (_context.Users.Any(x => x.Email == model.Email))
    {
        ModelState.AddModelError(
            "Email",
            "Email is already registered.");

        return View(model);
    }

    using var transaction =
        _context.Database.BeginTransaction();

    try
    {
        var user = new User
        {
            Email = model.Email,
            Username = model.Username,
            DateCreated = DateTime.Today,
            ShippingAddress = model.ShippingAddress,
            TelephoneNumber = model.TelephoneNumber
        };

        user.Password =
            _passwordHasher.HashPassword(
                user,
                model.Password);

        _context.Users.Add(user);
        _context.SaveChanges();

        var consumer = new Consumer
        {
            UserId = user.UserId,
            PointsCollected = 0
        };

        _context.Consumers.Add(consumer);
        _context.SaveChanges();

        transaction.Commit();

        return RedirectToAction(nameof(Login));
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}
}}}

==== Adding a product to order

{{{#!div style="text-align: justify; width: 100%;"
This transaction is used when adding a product to an order because the operation may require creating a new pending order and adding or updating its associated order item. If any step fails, all changes are rolled back, preventing incomplete orders or inconsistent cart data.
}}}

{{{
[HttpGet]
public IActionResult AddProduct(long id)
{
    var userId = GetUserId();

    if (userId == null)
        return RedirectToAction("Login", "Account");

    var product = _context.Products
        .Include(p => p.Release)
        .FirstOrDefault(p => p.ProductId == id);

    if (product == null)
        return NotFound();

    if (product.Stock <= 0)
    {
        TempData["Error"] =
            "This product is currently out of stock.";

        return RedirectToAction(
            "Details",
            "Release",
            new { id = product.ReleaseId });
    }

    using var transaction =
        _context.Database.BeginTransaction();

    try
    {
        var order = _context.Orders
            .Include(o => o.OrderProducts)
            .FirstOrDefault(o =>
                o.UserId == userId.Value &&
                o.Status == OrderStatusType.PENDING);

        if (order == null)
        {
            order = new Order
            {
                UserId = userId.Value,
                PaymentMethod = PaymentMethodType.CARD,
                PurchaseDate = DateTime.Today,
                PointsEarned = 0,
                PointsUsed = null,
                Status = OrderStatusType.PENDING
            };

            _context.Orders.Add(order);
            _context.SaveChanges();
        }

        var existingItem = order.OrderProducts
            .FirstOrDefault(x =>
                x.ProductId == product.ProductId);

        if (existingItem != null)
        {
            existingItem.Quantity++;
        }
        else
        {
            var orderProduct = new OrderProduct
            {
                OrderId = order.OrderId,
                ProductId = product.ProductId,
                PriceAtPurchase = product.Price,
                Quantity = 1
            };

            _context.OrderProducts.Add(orderProduct);
        }

        _context.SaveChanges();

        transaction.Commit();

        return RedirectToAction(nameof(Cart));
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}
}}}

==== Adding a product to a wishlist

{{{#!div style="text-align: justify; width: 100%;"
This transaction is used when adding a product to a wishlist because the operation may first require creating a wishlist and then adding the selected product to it. If any step fails, all changes are rolled back, preventing incomplete or inconsistent wishlist data.
}}}

{{{
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Add(long productId)
{
    var sessionUserId =
        HttpContext.Session.GetInt32("UserId");

    if (!sessionUserId.HasValue)
    {
        return RedirectToAction(
            "Login",
            "Account");
    }

    var userId =
        (long)sessionUserId.Value;


    var product =
        _context.Products
            .FirstOrDefault(p =>
                p.ProductId == productId);

    if (product == null)
        return NotFound();


    using var transaction =
        _context.Database.BeginTransaction();

    try
    {
        var wishlist =
            _context.Wishlists
                .FirstOrDefault(w =>
                    w.UserId == userId);

        
        if (wishlist == null)
        {
            wishlist = new Wishlist
            {
                WishlistId =
                    GetNextWishlistId(),

                UserId =
                    userId
            };


            _context.Wishlists.Add(
                wishlist);

            _context.SaveChanges();
        }

        
        var alreadyExists =
            _context.WishlistProducts
                .Any(wp =>
                    wp.WishlistId ==
                    wishlist.WishlistId &&
                    wp.ProductId ==
                    productId);


        if (!alreadyExists)
        {
            var wishlistProduct =
                new WishlistProduct
                {
                    WishlistId =
                        wishlist.WishlistId,

                    ProductId =
                        productId
                };


            _context.WishlistProducts.Add(
                wishlistProduct);

            _context.SaveChanges();
        }


        transaction.Commit();


        TempData["Success"] =
            "Product added to your wishlist.";


        return RedirectToAction(
            nameof(Index));
    }
    catch
    {
        transaction.Rollback();

        throw;
    }
}
}}}

== Pooling

{{{#!div style="text-align: justify; width: 100%;"
Since the backend of our application is developed using ASP.NET Core with Entity Framework Core and PostgreSQL, database connections are managed through Npgsql. 

In our project, we use the following dependency:

{{{
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.8" />
}}}

The data source is created in Program.cs using NpgsqlDataSourceBuilder:

{{{
var dataSourceBuilder = new NpgsqlDataSourceBuilder(
    builder.Configuration.GetConnectionString("KernelRecords"));

var dataSource = dataSourceBuilder.Build();

builder.Services.AddDbContext<KernelRecordsContext>(options =>
    options.UseNpgsql(dataSource));
}}}

Npgsql has built-in connection pooling, and pooling is enabled by default. This means that the application does not need to establish a completely new physical connection to the PostgreSQL database every time a database operation is performed.

When the application needs access to the database, Npgsql takes an available connection from the connection pool. After the operation is finished, the connection is returned to the pool and can be reused by another request.

In our project, we do not manually change the pooling configuration, so the default Npgsql settings are used. Some of the important default values are:

{{{
Pooling                  = true
Minimum Pool Size        = 0
Maximum Pool Size        = 100
Connection Idle Lifetime = 300 seconds
Timeout                  = 15 seconds
}}}

If needed, these values can be changed through the connection string.

Connection pooling improves application performance and reduces resource usage because existing database connections can be reused instead of opening a new connection for every request.
}}}