Advanced Application Development

Transactions

Registering a new consumer

[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

[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

[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

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.