E-Commerce Real-Time Application Development using ASP.NET Core Web API 

E-Commerce Real-Time Application Development using ASP.NET Core Web API

In this article, I will develop a complete Real-Time E-Commerce Application Development using the ASP.NET Core Web API Project. We will start by discussing how to design the database in SQL Server, create the Models and DTOs to hold the data, Create the Repositories where we will implement the data access code, and create Controllers to expose the endpoints to the clients. This is a very in-depth article, so please read the article with patience till the end. At the end of this article, you will understand the following:

E-Commerce Real-Time Application using ASP.NET Core Web API

We will start by using ADO.NET Core with T-SQL Statement to perform the database Operations. Then, I will show you how to create stored procedures and use them using ADO.NET Core. Finally, we will see how to use EF Core for Database Operations. When required, we will also implement Transactions in our code. For better performance and to handle multiple client requests, we will also use asynchronous programming, which will not block the threads whenever we interact with the database.

Creating an E-Commerce Application using ASP.NET Core Web API

Let us start by Creating an E-Commerce application using ASP.NET Core Web API and performing database operations using ADO.NET Core with T-SQL statements. This involves several steps, from designing the database, creating an ASP.NET Core Web API, and efficiently handling database operations.

Database Design For E-Commerce Application:

In our E-Commerce Application, we will use the following database tables.

  • Products Table: Stores information about products like ID, name, price, and quantity.
  • Customers Table: Contains customer details such as ID, name, and email.
  • Orders Table: Tracks each order, linking to the customer via a foreign key.
  • OrderItems Table: Details each item in an order, linking to the orders and products tables.
  • Payments Table: Records payment transactions, including the payment method and amount linked to orders.

First, create a new database named ECommerce. Switch to the ECommerce database and create the tables by executing the following SQL Scripts on the SQL Server database.

CREATE DATABASE ECommerce;
GO

USE ECommerce;
GO

-- Customer Table
CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(255) NOT NULL,
    Email NVARCHAR(255) NOT NULL,
    Address NVARCHAR(500) NOT NULL,
    IsDeleted BIT NOT NULL DEFAULT 0,
    CONSTRAINT UQ_Customers_Email UNIQUE (Email)
);
GO

-- Product Table
CREATE TABLE Products (
    ProductId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    Quantity INT NOT NULL,
    Description NVARCHAR(MAX),
    IsDeleted BIT NOT NULL DEFAULT 0,
    CONSTRAINT UQ_Products_Name UNIQUE (Name)
);
GO

-- Orders Table
CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    TotalAmount DECIMAL(10, 2) NOT NULL,
    Status NVARCHAR(50) NOT NULL DEFAULT 'Pending',
    OrderDate DATETIME NOT NULL,
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);
CREATE INDEX IDX_CustomerId ON Orders (CustomerId);
GO

-- Payment Table
CREATE TABLE Payments (
    PaymentId INT PRIMARY KEY IDENTITY(1,1),
    OrderId INT NOT NULL,
    Amount DECIMAL(10, 2) NOT NULL,
    Status NVARCHAR(50) NOT NULL DEFAULT 'Pending',
    PaymentType NVARCHAR(50) NOT NULL,
    PaymentDate DATETIME NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES Orders(OrderId)
);
GO

-- OrderItems Table
CREATE TABLE OrderItems (
    OrderItemId INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity INT NOT NULL,
    PriceAtOrder DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES Orders(OrderId),
    FOREIGN KEY (ProductId) REFERENCES Products(ProductId)
);
CREATE INDEX IDX_OrderId ON OrderItems (OrderId);
GO

Understanding the above SQL Script:

Customers Table

It Stores information about customers. The meaning of the Columns is as follows:

  • CustomerId: The primary key that uniquely identifies each customer. It is auto-incremented.
  • Name: Stores the customer’s name.
  • Email: Stores the customer’s email address. It is unique across the table.
  • Address: Stores the customer’s address.
  • IsDeleted: A flag indicating whether the customer is considered deleted (logical delete rather than physical delete).

Relationships: Acts as the parent table for Orders, where CustomerId is referenced.

Products Table

It Contains details about products being sold. The meaning of the Columns of this table is as follows:

  • ProductId: The primary key uniquely identifies each product. It is auto-incremented.
  • Name: Product name, which is unique.
  • Price: The selling price of the product.
  • Quantity: The quantity of the product available in stock.
  • Description: A detailed description of the product.
  • IsDeleted: Indicates whether the product is considered deleted.

Relationships: Acts as the parent table for OrderItems, where ProductId is referenced.

Orders Table

It Stores records of customer orders. The meaning of the Columns of this table is as follows:

  • OrderId: The primary key uniquely identifies each order. It is auto-incremented.
  • CustomerId: Foreign key links to the Customers table, indicating which customer placed the order.
  • TotalAmount: The total monetary amount of the order.
  • Status: The current status of the order (e.g., Pending, Failed, Processing, Shipped, and Delivered).
  • OrderDate: The date and time the order was placed.

Relationships: Parent table for Payments and OrderItems.

Payments Table

This table Records details of payments made for orders. The meaning of the Columns of this table is as follows:

  • PaymentId: The primary key uniquely identifies each payment. It is auto-incremented.
  • OrderId: Foreign key referencing the Orders table, linking each payment to a specific order.
  • Amount: The amount of money paid.
  • Status: The payment status (e.g., Pending, Completed, Failed, or Refunded).
  • PaymentType: The payment method (e.g., CC, DC, COD).
  • PaymentDate: The date and time the payment was made.

Relationships: Links each payment to a specific order, establishing a many-to-one relationship with Orders.

OrderItems Table

It links products to orders, detailing which products are included in each order and in what quantity. The meaning of the Columns of this table is as follows:

  • OrderItemId: The primary key uniquely identifies each order item entry. It is auto-incremented.
  • OrderId: Foreign key that references the Orders table.
  • ProductId: Foreign key that references the Products table.
  • Quantity: The quantity of the product ordered.
  • PriceAtOrder: The PriceAtOrder field stores the product’s actual price at the time the order is made.

Relationships: Links to Orders and Products, establishing a many-to-one relationship with both (an order can contain multiple products, and a product can be in multiple orders).

Database Relationships Overview
  • Customers and Orders: One-to-many (one customer can have multiple orders).
  • Orders and Payments: One-to-one (one order can have one payment).
  • Orders and OrderItems: One-to-many (one order can contain multiple products).
  • Products and OrderItems: Many-to-many (products can appear in many orders, and orders can contain many products, mediated through the OrderItems table).
Inserting Dummy Data:

We need some dummy and master data to test the functionalities. So, please execute the following Script to insert some dummy data into the database tables that we just created:

USE ECommerce;
GO

-- Inserting dummy data into Customers table
INSERT INTO Customers (Name, Email, Address)
VALUES 
('Pranaya', 'Pranaya@example.com', 'Some Street, Some City, Some State, 12345'),
('Priyanka', 'Priyanka@example.com', 'Other Street, Other City, Other State, 67890'),
('Ramesh', 'Ramesh@example.com', 'Another Street, Another City, Anothe rState, 13579');

-- Inserting dummy data into Products table
INSERT INTO Products (Name, Price, Quantity, Description)
VALUES 
('Laptop', 1200.00, 10, 'High-performance laptop suitable for gaming and professional work'),
('Smartphone', 800.00, 15, 'Latest model smartphone with high-resolution camera'),
('Headphones', 150.00, 30, 'Noise-cancelling headphones with over 20 hours of battery life');

-- Inserting dummy data into Orders table
INSERT INTO Orders (CustomerId, TotalAmount, OrderDate)
VALUES 
(1, 2400.00, GETDATE()),   -- Order Id = 1
(2, 800.00, GETDATE()),     -- Order Id = 2
(1, 300.00, GETDATE());     -- Order Id = 3

-- Inserting dummy data into OrderItems table
INSERT INTO OrderItems (OrderId, ProductId, Quantity, PriceAtOrder)
VALUES 
(1, 1, 2, 1200.00),    -- 2 laptops ordered by Customer 1
(2, 2, 1, 800.00),      -- 1 smartphone ordered by Customer 2
(3, 3, 2, 150.00);      -- 2 headphones ordered by Customer 1
GO

-- Inserting dummy data into Payments table
INSERT INTO Payments (OrderId, Amount, PaymentType, PaymentDate)
VALUES 
(1, 2400.00, 'CC', GETDATE()),   -- CC => Credit Card
(2, 800.00, 'DC', GETDATE()),     -- DC => Debit Card
(3, 300.00, 'COD', GETDATE());  -- COD => Cash On Delivery

Create a New ASP.NET Core Web API Application:

Open Visual Studio and create a new ASP.NET Core Web API Project named ECommerceAPI.

Creating Data Models

Models are the classes that will hold the business data. Add a folder called Models to your project. In this folder, we will create all the Models required for our application. In our application, we will create the corresponding model for each database table.

Customer.cs

Create a class file named Customer.cs within the Data folder and copy and paste the following code. This will be our Customer model, which will hold the Customer data.

namespace ECommerceAPI.Models
{
    public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public bool IsDeleted { get; set; }
    }
}
Product.cs

Create a class file named Product.cs within the Data folder and copy and paste the following code. This will be our Product model, which will hold the Product data.

namespace ECommerceAPI.Models
{
    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public int Quantity { get; set; }
        public string Description { get; set; }
        public bool IsDeleted { get; set; }
    }
}
Order.cs

Create a class file named Order.cs within the Data folder and copy and paste the following code. This will be our Order model, which will hold the Order information.

namespace ECommerceAPI.Models
{
    public class Order
    {
        public int OrderId { get; set; }
        public int CustomerId { get; set; }
        public decimal TotalAmount { get; set; }
        public string Status { get; set; } = "Pending";
        public DateTime OrderDate { get; set; }
    }
}
OrderItem.cs

Create a class file named OrderItem.cs within the Data folder and copy and paste the following code. This will be our OrderItem model, which will hold the Order Items of a particular order.

namespace ECommerceAPI.Models
{
    public class OrderItem
    {
        public int OrderItemId { get; set; }
        public int OrderId { get; set; }
        public int ProductId { get; set; }
        public int Quantity { get; set; }
        public decimal PriceAtOrder { get; set; }
    }
}
Payment.cs

Create a class file named Payment.cs within the Data folder and copy and paste the following code. This will be our Payment model, which will hold an order’s Payment information.

namespace ECommerceAPI.Models
{
    public class Payment
    {
        public int PaymentId { get; set; }
        public int OrderId { get; set; }
        public decimal Amount { get; set; }
        public string Status { get; set; } = "Pending";
        public string PaymentType { get; set; }
        public DateTime PaymentDate { get; set; }
    }
}
Setting Up the Database Connection

Before we create the repositories to manage the database CRUD Operations, we need a way to manage our database connection. You can typically manage your database connection string in your appsettings.json file. So, first, modify the appsettings.json file as follows:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ECommerce;Trusted_Connection=True;TrustServerCertificate=True;"
  },
  "AllowedHosts": "*"
}
Add Necessary NuGet Package for ADO.NET Core

To use ADO.NET Core to connect with SQL Server Database, you need to add Microsoft.Data.SqlClient package to your project. You can install this package using NuGet Package Manager or Package Manager Console. You can install Microsoft.Data.SqlClient package by running the following command in the Package Manager Console:

Install-Package Microsoft.Data.SqlClient

Once the connection string is defined within the appsettings.json file and once we add the required ADO.NET Core Package, we need to create a simple service for creating and opening a database connection.

So, what we are going to do is create a folder called Data, and inside that Data folder, we are going to create all the database-related classes and repositories. So, add a folder named Data to the project root directory. Then create a class file named SqlConnectionFactory.cs within the Data folder, and then copy and paste the following code:

using Microsoft.Data.SqlClient;
namespace ECommerceAPI.Data
{
    public class SqlConnectionFactory 
    {
        private readonly IConfiguration _configuration;

        public SqlConnectionFactory(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public SqlConnection CreateConnection()
            => new SqlConnection(_configuration.GetConnectionString("DefaultConnection"));
    }
}

Registering the SqlConnectionFactory Service:

Next, we need to register this service to our Program.cs class file as follows:

builder.Services.AddTransient<SqlConnectionFactory>();

Creating DTOs Required for our E-Commerce Application:

DTO stands for Data Transfer Objects. We cannot expose our Models to the Client. This is because we don’t want to expose all our model data to the client, and we don’t expect the client to send all the model data while creating and updating a resource. For this purpose, we are going to use DTOs with the required properties only, which are used only for Data Transformations.

So, create a folder called DTO where we will create all our DTOs. The DTOs are specifically used for creating and updating new entities by excluding unnecessary properties. This customization ensures that the DTO only carries the information needed for the operations, simplifying the data handling.

Creating Customer DTO:

Let us create the DTO required for the Customer. So, create a class file named CustomerDTO.cs within the DTO folder and copy and paste the following code. We will use this class when we create a new customer and update an existing customer. This class contains only the properties required for the creation and update operations. 

using System.ComponentModel.DataAnnotations;

namespace ECommerceAPI.DTO
{
    public class CustomerDTO
    {
        public int CustomerId { get; set; } // Used only for updates, not for inserts
        [Required]
        public string Name { get; set; }
        [Required]
        [EmailAddress]
        public string Email { get; set; }
        public string Address { get; set; }
    }
}
CustomerResponseDTO

Next, create a class file named CustomerResponseDTO.cs within the DTO folder and copy and paste the following code. We will use this class as the return type of the action method that creates a new customer. Once the customer is created, we will return that new customer ID using this DTO.

namespace ECommerceAPI.DTO
{
    public class CustomerResponseDTO
    {
        public int CustomerId { get; set; }
    }
}
Creating Repository Classes

The Repository classes will contain the business and data access logic for our application. You can separate the business logic and data access logic into different layers, but for simplicity, we are going to merge both business and data access logic with the repository classes.

Creating a Repository for Customer

Create a class file named CustomerRepository.cs within the Data folder and copy and paste the following code. This class contains the business and data access logic to perform customer database operations, such as fetching all customer details, fetching customers by ID, and creating, updating, and deleting customers.

using ECommerceAPI.DTO;
using ECommerceAPI.Models;
using Microsoft.Data.SqlClient;

namespace ECommerceAPI.Data
{
    public class CustomerRepository
    {
        private readonly SqlConnectionFactory _connectionFactory;

        public CustomerRepository(SqlConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        //Method to return All Customer
        public async Task<List<Customer>> GetAllCustomersAsync()
        {
            var customers = new List<Customer>();
            var query = "SELECT CustomerId, Name, Email, Address FROM Customers WHERE IsDeleted = 0";

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            customers.Add(new Customer
                            {
                                CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Email = reader.GetString(reader.GetOrdinal("Email")),
                                Address = reader.GetString(reader.GetOrdinal("Address")),
                                IsDeleted = false
                            });
                        }
                    }
                }
            }

            return customers;
        }

        //Method to Find a Customer By Id
        public async Task<Customer?> GetCustomerByIdAsync(int customerId)
        {
            var query = "SELECT CustomerId, Name, Email, Address FROM Customers WHERE CustomerId = @CustomerId AND IsDeleted = 0";
            Customer? customer = null;

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@CustomerId", customerId);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            customer = new Customer
                            {
                                CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Email = reader.GetString(reader.GetOrdinal("Email")),
                                Address = reader.GetString(reader.GetOrdinal("Address")),
                                IsDeleted = false
                            };
                        }
                    }
                }
            }

            return customer;
        }

        // Method to Add a New Customer and return the created Customer ID
        public async Task<int> InsertCustomerAsync(CustomerDTO customer)
        {
            var query = @"INSERT INTO Customers (Name, Email, Address, IsDeleted) 
                        VALUES (@Name, @Email, @Address, 0);
                        SELECT CAST(SCOPE_IDENTITY() as int);";

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Name", customer.Name);
                    command.Parameters.AddWithValue("@Email", customer.Email);
                    command.Parameters.AddWithValue("@Address", customer.Address);

                    // ExecuteScalar is used here to return the first column of the first row in the result set
                    int customerId = (int)await command.ExecuteScalarAsync();
                    return customerId;
                }
            }
        }

        //Method to Update an Existing Customer
        public async Task UpdateCustomerAsync(CustomerDTO customer)
        {
            var query = "UPDATE Customers SET Name = @Name, Email = @Email, Address = @Address WHERE CustomerId = @CustomerId";

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@CustomerId", customer.CustomerId);
                    command.Parameters.AddWithValue("@Name", customer.Name);
                    command.Parameters.AddWithValue("@Email", customer.Email);
                    command.Parameters.AddWithValue("@Address", customer.Address);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        //Method to Delete an Existing Customer
        public async Task DeleteCustomerAsync(int customerId)
        {
            var query = "UPDATE Customers SET IsDeleted = 1 WHERE CustomerId = @CustomerId";

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@CustomerId", customerId);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }
    }
}
Creating Product DTOs:

Let us create the DTO required for the Product. So, create a class file named ProductDTO.cs within the DTO folder and copy and paste the following code. We will use this DTO when we create a new product or update an existing product. This class contains only the properties required to create and update products. 

using System.ComponentModel.DataAnnotations;

namespace ECommerceAPI.DTO
{
    public class ProductDTO
    {
        public int ProductId { get; set; }  // Used only for updates, not for inserts
        [Required]
        public string Name { get; set; }
        [Required]
        public decimal Price { get; set; }
        [Required]
        public int Quantity { get; set; }
        public string Description { get; set; }
    }
}
ProductResponseDTO

Next, create a class file named ProductResponseDTO.cs within the DTO folder and copy and paste the following code. We will use this DTO as the return type of the action method that creates a new Product. Once the Product is created, we will return that newly created Product ID to the client using this DTO from our action method.

namespace ECommerceAPI.DTO
{
    public class ProductResponseDTO
    {
        public int ProductId { get; set; }
    }
}

Creating a Repository for Product

Create a class file named ProductRepository.cs within the Data folder and copy and paste the following code. This class contains the business logic and data access logic to perform database operations on the Product database, such as fetching all products, fetching product details by ID, creating, updating, and deleting a product, etc.

using ECommerceAPI.DTO;
using ECommerceAPI.Models;
using Microsoft.Data.SqlClient;

namespace ECommerceAPI.Data
{
    public class ProductRepository
    {
        private readonly SqlConnectionFactory _connectionFactory;

        public ProductRepository(SqlConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<List<Product>> GetAllProductsAsync()
        {
            var products = new List<Product>();
            var query = "SELECT ProductId, Name, Price, Quantity, Description, IsDeleted FROM Products WHERE IsDeleted = 0";

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            products.Add(new Product
                            {
                                ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Price = reader.GetDecimal(reader.GetOrdinal("Price")),
                                Quantity = reader.GetInt32(reader.GetOrdinal("Quantity")),
                                Description = reader.GetString(reader.GetOrdinal("Description")),
                                IsDeleted = reader.GetBoolean(reader.GetOrdinal("IsDeleted"))
                            });
                        }
                    }
                }
            }

            return products;
        }

        public async Task<Product?> GetProductByIdAsync(int productId)
        {
            var query = "SELECT ProductId, Name, Price, Quantity, Description, IsDeleted FROM Products WHERE ProductId = @ProductId AND IsDeleted = 0";
            Product? product = null;

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@ProductId", productId);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            product = new Product
                            {
                                ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Price = reader.GetDecimal(reader.GetOrdinal("Price")),
                                Quantity = reader.GetInt32(reader.GetOrdinal("Quantity")),
                                Description = reader.GetString(reader.GetOrdinal("Description")),
                                IsDeleted = reader.GetBoolean(reader.GetOrdinal("IsDeleted"))
                            };
                        }
                    }
                }
            }

            return product;
        }

        public async Task<int> InsertProductAsync(ProductDTO product)
        {
            var query = @"INSERT INTO Products (Name, Price, Quantity, Description, IsDeleted) 
                        VALUES (@Name, @Price, @Quantity, @Description, 0);
                        SELECT CAST(SCOPE_IDENTITY() as int);";

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Name", product.Name);
                    command.Parameters.AddWithValue("@Price", product.Price);
                    command.Parameters.AddWithValue("@Quantity", product.Quantity);
                    command.Parameters.AddWithValue("@Description", product.Description ?? (object)DBNull.Value);

                    // ExecuteScalar is used here to return the first column of the first row in the result set
                    int productId = (int)await command.ExecuteScalarAsync();
                    return productId;
                }
            }
        }

        public async Task UpdateProductAsync(ProductDTO product)
        {
            var query = "UPDATE Products SET Name = @Name, Price = @Price, Quantity = @Quantity, Description = @Description WHERE ProductId = @ProductId";

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@ProductId", product.ProductId);
                    command.Parameters.AddWithValue("@Name", product.Name);
                    command.Parameters.AddWithValue("@Price", product.Price);
                    command.Parameters.AddWithValue("@Quantity", product.Quantity);
                    command.Parameters.AddWithValue("@Description", product.Description ?? (object)DBNull.Value);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task DeleteProductAsync(int productId)
        {
            var query = "UPDATE Products SET IsDeleted = 1 WHERE ProductId = @ProductId";

            using (var connection = _connectionFactory.CreateConnection())
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@ProductId", productId);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }
    }
}
Creating Order DTOs:

Let us create the DTOs required for the Order.

OrderItemDetailsDTO.cs

Create a class file named OrderItemDetailsDTO.cs within the DTO folder and copy and paste the following code. We will use this DTO within the OrderDTO, which will contain the Product and the Quantity of the Order.

using System.ComponentModel.DataAnnotations;

namespace ECommerceAPI.DTO
{
    public class OrderItemDetailsDTO
    {
        [Required]
        public int ProductId { get; set; }
        [Required]
        public int Quantity { get; set; }
    }
}

OrderDTO.cs

Create a class file named OrderDTO.cs within the DTO folder and copy and paste the following code. We will use this class when we are going to create a new order. This class contains only the properties that are required to create a new order.

using System.ComponentModel.DataAnnotations;

namespace ECommerceAPI.DTO
{
    public class OrderDTO
    {
        [Required]
        public int CustomerId { get; set; }
        [Required]
        public List<OrderItemDetailsDTO> Items { get; set; }
    }
}
OrderStatusDTO.cs

Create a class file named OrderStatusDTO.cs within the DTO folder and copy and paste the following code. We will use this class to update the status of order. This class contains only the properties required for status update operations.

using System.ComponentModel.DataAnnotations;

namespace ECommerceAPI.DTO
{
    public class OrderStatusDTO
    {
        [Required]
        public int OrderId { get; set; }
        [Required]
        public string Status { get; set; }
    }
}
OrderStatusResponseDTO.cs

Create a class file named OrderStatusResponseDTO.cs within the DTO folder and then copy and paste the following code. We will use this class as the return type of the action method that updates the order status.

namespace ECommerceAPI.DTO
{
    public class OrderStatusResponseDTO
    {
        public int OrderId { get; set; }
        public string Status { get; set; }
        public bool IsUpdated { get; set; }
        public string Message { get; set; }
    }
}
ConfirmOrderResponseDTO.cs

Create a class file named ConfirmOrderResponseDTO.cs within the DTO folder and then copy and paste the following code. We will use this class as the return type of the action method that confirms the order.

namespace ECommerceAPI.DTO
{
    public class ConfirmOrderResponseDTO
    {
        public int OrderId { get; set; }
        public bool IsConfirmed { get; set; }
        public string Message { get; set; }
    }
}
CreateOrderResponseDTO

Create a class file named CreateOrderResponseDTO.cs within the DTO folder and then copy and paste the following code. We will use this class as the return type of the action method that creates a new order. Once the order is created, we will return that new Order ID and the status to the client using this DTO.

namespace ECommerceAPI.DTO
{
    public class CreateOrderResponseDTO
    {
        public int OrderId { get; set; }
        public string Status { get; set; }
        public string Message { get; set; }
        public bool IsCreated { get; set; }
    }
}
Creating a Repository for Order

Create a class file named OrderRepository.cs within the Data folder and copy and paste the following code. This class contains the business and data access logic to perform the database operations on the Order, such as fetching creating a new order, confirming an order, fetching the order details by ID, updating the order status, etc.

using ECommerceAPI.DTO;
using ECommerceAPI.Models;
using Microsoft.Data.SqlClient;
namespace ECommerceAPI.Data
{
public class OrderRepository
{
private readonly SqlConnectionFactory _connectionFactory;
public OrderRepository(SqlConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
}
// Method to fetch orders based on Status
public async Task<List<Order>> GetAllOrdersAsync(string Status)
{
var orders = new List<Order>();
var query = "SELECT OrderId, CustomerId, TotalAmount, Status, OrderDate FROM Orders WHERE Status = @Status";
using (var connection = _connectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Status", Status);
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var order = new Order
{
OrderId = reader.GetInt32(reader.GetOrdinal("OrderId")),
CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
TotalAmount = reader.GetDecimal(reader.GetOrdinal("TotalAmount")),
Status = reader.GetString(reader.GetOrdinal("Status")),
OrderDate = reader.GetDateTime(reader.GetOrdinal("OrderDate"))
};
orders.Add(order);
}
}
}
}
return orders;
}
//Create the Order with Pending State
public async Task<CreateOrderResponseDTO> CreateOrderAsync(OrderDTO orderDto)
{
// Queries to fetch product details and to insert order and order items
var productQuery = "SELECT ProductId, Price, Quantity FROM Products WHERE ProductId = @ProductId AND IsDeleted = 0";
var orderQuery = "INSERT INTO Orders (CustomerId, TotalAmount, Status, OrderDate) OUTPUT INSERTED.OrderId VALUES (@CustomerId, @TotalAmount, @Status, @OrderDate)";
var itemQuery = "INSERT INTO OrderItems (OrderId, ProductId, Quantity, PriceAtOrder) VALUES (@OrderId, @ProductId, @Quantity, @PriceAtOrder)";
decimal totalAmount = 0m;
List<OrderItem> validatedItems = new List<OrderItem>();
CreateOrderResponseDTO createOrderResponseDTO = new CreateOrderResponseDTO();
using (var connection = _connectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
try
{
foreach (OrderItemDetailsDTO item in orderDto.Items)
{
using (var productCommand = new SqlCommand(productQuery, connection, transaction))
{
productCommand.Parameters.AddWithValue("@ProductId", item.ProductId);
using (var reader = await productCommand.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
int stockQuantity = reader.GetInt32(reader.GetOrdinal("Quantity"));
decimal price = reader.GetDecimal(reader.GetOrdinal("Price"));
if (stockQuantity >= item.Quantity)
{
totalAmount += price * item.Quantity;
validatedItems.Add(new OrderItem
{
ProductId = item.ProductId,
Quantity = item.Quantity,
PriceAtOrder = price  // Using the price from the database
});
}
else
{
// Handle the case where there isn't enough stock
createOrderResponseDTO.Message = $"Insufficient Stock for Product ID {item.ProductId}";
createOrderResponseDTO.IsCreated = false;
return createOrderResponseDTO;
}
}
else
{
// Handle the case for Invalid Product Id
createOrderResponseDTO.Message = $"Product Not Found for Product ID {item.ProductId}";
createOrderResponseDTO.IsCreated = false;
return createOrderResponseDTO;
}
reader.Close(); // Ensure the reader is closed before next iteration
}
}
}
// Proceed with creating the order if all items are validated
using (var orderCommand = new SqlCommand(orderQuery, connection, transaction))
{
orderCommand.Parameters.AddWithValue("@CustomerId", orderDto.CustomerId);
orderCommand.Parameters.AddWithValue("@TotalAmount", totalAmount);
orderCommand.Parameters.AddWithValue("@Status", "Pending");
orderCommand.Parameters.AddWithValue("@OrderDate", DateTime.Now);
var orderId = (int)await orderCommand.ExecuteScalarAsync();
// Insert all validated items
foreach (var validatedItem in validatedItems)
{
using (var itemCommand = new SqlCommand(itemQuery, connection, transaction))
{
itemCommand.Parameters.AddWithValue("@OrderId", orderId);
itemCommand.Parameters.AddWithValue("@ProductId", validatedItem.ProductId);
itemCommand.Parameters.AddWithValue("@Quantity", validatedItem.Quantity);
itemCommand.Parameters.AddWithValue("@PriceAtOrder", validatedItem.PriceAtOrder);
await itemCommand.ExecuteNonQueryAsync();
}
}
transaction.Commit();
createOrderResponseDTO.Status = "Pending";
createOrderResponseDTO.IsCreated = true;
createOrderResponseDTO.OrderId = orderId;
createOrderResponseDTO.Message = "Order Created Successfully";
return createOrderResponseDTO;
}
}
catch (Exception)
{
transaction.Rollback();
throw;  // Re-throw to handle the exception further up the call stack
}
}
}
}
public async Task<ConfirmOrderResponseDTO> ConfirmOrderAsync(int orderId)
{
// Queries to fetch order and payment details
var orderDetailsQuery = "SELECT TotalAmount FROM Orders WHERE OrderId = @OrderId";
var paymentDetailsQuery = "SELECT Amount, Status FROM Payments WHERE OrderId = @OrderId";
var updateOrderStatusQuery = "UPDATE Orders SET Status = 'Confirmed' WHERE OrderId = @OrderId";
var getOrderItemsQuery = "SELECT ProductId, Quantity FROM OrderItems WHERE OrderId = @OrderId";
var updateProductQuery = "UPDATE Products SET Quantity = Quantity - @Quantity WHERE ProductId = @ProductId";
ConfirmOrderResponseDTO confirmOrderResponseDTO = new ConfirmOrderResponseDTO()
{
OrderId = orderId,
};
using (var connection = _connectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
try
{
decimal orderAmount = 0m;
decimal paymentAmount = 0m;
string paymentStatus = string.Empty;
// Retrieve order amount
using (var orderCommand = new SqlCommand(orderDetailsQuery, connection, transaction))
{
orderCommand.Parameters.AddWithValue("@OrderId", orderId);
using (var reader = await orderCommand.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
orderAmount = reader.GetDecimal(reader.GetOrdinal("TotalAmount"));
}
reader.Close();
}
}
// Retrieve payment details
using (var paymentCommand = new SqlCommand(paymentDetailsQuery, connection, transaction))
{
paymentCommand.Parameters.AddWithValue("@OrderId", orderId);
using (var reader = await paymentCommand.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
paymentAmount = reader.GetDecimal(reader.GetOrdinal("Amount"));
paymentStatus = reader.GetString(reader.GetOrdinal("Status"));
}
reader.Close();
}
}
// Check if payment is complete and matches the order total
if (paymentStatus == "Completed" && paymentAmount == orderAmount)
{
// Update product quantities
using (var itemCommand = new SqlCommand(getOrderItemsQuery, connection, transaction))
{
itemCommand.Parameters.AddWithValue("@OrderId", orderId);
using (var reader = await itemCommand.ExecuteReaderAsync())
{
while (reader.Read())
{
int productId = reader.GetInt32(reader.GetOrdinal("ProductId"));
int quantity = reader.GetInt32(reader.GetOrdinal("Quantity"));
using (var updateProductCommand = new SqlCommand(updateProductQuery, connection, transaction))
{
updateProductCommand.Parameters.AddWithValue("@ProductId", productId);
updateProductCommand.Parameters.AddWithValue("@Quantity", quantity);
await updateProductCommand.ExecuteNonQueryAsync();
}
}
reader.Close();
}
}
// Update order status to 'Confirmed'
using (var statusCommand = new SqlCommand(updateOrderStatusQuery, connection, transaction))
{
statusCommand.Parameters.AddWithValue("@OrderId", orderId);
await statusCommand.ExecuteNonQueryAsync();
}
transaction.Commit();
confirmOrderResponseDTO.IsConfirmed = true;
confirmOrderResponseDTO.Message = "Order Confirmed Successfully";
return confirmOrderResponseDTO;
}
else
{
transaction.Rollback();
confirmOrderResponseDTO.IsConfirmed = false;
confirmOrderResponseDTO.Message = "Cannot Confirm Order: Payment is either incomplete or does not match the order total.";
return confirmOrderResponseDTO;
}
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception("Error Confirming Order: " + ex.Message);
}
}
}
}
// Update the order status with conditions
// An order cannot move directly from "Pending" to "Delivered".
// An order can only be set to "Cancelled" if it is currently "Pending".
// An order can be marked as "Processing" only if it's currently "Confirmed"
public async Task<OrderStatusResponseDTO> UpdateOrderStatusAsync(int orderId, string newStatus)
{
OrderStatusResponseDTO orderStatusDTO = new OrderStatusResponseDTO()
{
OrderId = orderId
};
using (var connection = _connectionFactory.CreateConnection())
{
await connection.OpenAsync();
try
{
// Fetch the current status of the order
var currentStatusQuery = "SELECT Status FROM Orders WHERE OrderId = @OrderId";
string currentStatus;
using (var statusCommand = new SqlCommand(currentStatusQuery, connection))
{
statusCommand.Parameters.AddWithValue("@OrderId", orderId);
var result = await statusCommand.ExecuteScalarAsync();
if (result == null)
{
orderStatusDTO.Message = "Order not found.";
orderStatusDTO.IsUpdated = false;
return orderStatusDTO;
}
currentStatus = result.ToString();
}
// Check if the status transition is valid
if (!IsValidStatusTransition(currentStatus, newStatus))
{
orderStatusDTO.Message = $"Invalid status transition from {currentStatus} to {newStatus}.";
orderStatusDTO.IsUpdated = false;
return orderStatusDTO;
}
// Update the status if valid
var updateStatusQuery = "UPDATE Orders SET Status = @NewStatus WHERE OrderId = @OrderId";
using (var updateCommand = new SqlCommand(updateStatusQuery, connection))
{
updateCommand.Parameters.AddWithValue("@OrderId", orderId);
updateCommand.Parameters.AddWithValue("@NewStatus", newStatus);
int rowsAffected = await updateCommand.ExecuteNonQueryAsync();
if (rowsAffected > 0)
{
orderStatusDTO.Message = $"Order status updated to {newStatus}";
orderStatusDTO.Status = newStatus;
orderStatusDTO.IsUpdated = true;
}
else
{
orderStatusDTO.IsUpdated = false;
orderStatusDTO.Message = $"No order found with ID {orderId}";
}
}
return orderStatusDTO;
}
catch (Exception ex)
{
throw new Exception("Error updating order status: " + ex.Message, ex);
}
}
}
private bool IsValidStatusTransition(string currentStatus, string newStatus)
{
// Define valid status transitions
switch (currentStatus)
{
case "Pending":
return newStatus == "Processing" || newStatus == "Cancelled";
case "Confirmed":
return newStatus == "Processing";
case "Processing":
return newStatus == "Delivered";
case "Delivered":
// Delivered orders should not transition to any other status
return false;
case "Cancelled":
// Cancelled orders should not transition to any other status
return false;
default:
return false;
}
}
//Get the Order Details by Id
public async Task<Order?> GetOrderDetailsAsync(int orderId)
{
var query = "SELECT OrderId, CustomerId, TotalAmount, Status, OrderDate FROM Orders WHERE OrderId = @OrderId";
using (var connection = _connectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@OrderId", orderId);
using (var reader = await command.ExecuteReaderAsync())
{
if (!reader.Read()) return null;
return new Order
{
OrderId = reader.GetInt32(reader.GetOrdinal("OrderId")),
CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
TotalAmount = reader.GetDecimal(reader.GetOrdinal("TotalAmount")),
Status = reader.GetString(reader.GetOrdinal("Status")),
OrderDate = reader.GetDateTime(reader.GetOrdinal("OrderDate"))
};
}
}
}
}
}
}

Creating Payment DTOs:

Let us create a Payment DTO. This DTO is specifically used to make the payment. This customization ensures that the DTO only carries the information needed for our operations, simplifying the data handling.

PaymentDTO.cs

Create a class file named PaymentDTO.cs within the DTO folder and then copy and paste the following code. We will use this DTO when we make the payment. This class only contains the properties required to make the payment. 

using System.ComponentModel.DataAnnotations;
namespace ECommerceAPI.DTO
{
public class PaymentDTO
{
[Required]
public int OrderId { get; set; }
[Required]
public decimal Amount { get; set; }
[Required]
public string PaymentType { get; set; }
}
}
PaymentResponseDTO.cs

Create a class file named PaymentResponseDTO.cs within the DTO folder and then copy and paste the following code. We will use this DTO class as the return type of the action method that handles the payment creation.

namespace ECommerceAPI.DTO
{
public class PaymentResponseDTO
{
public int PaymentId { get; set; }
public string Status { get; set; }
public string Message { get; set; }
public bool IsCreated { get; set; }
}
}
PaymentStatusDTO.cs

Create a class file named PaymentStatusDTO.cs within the DTO folder and copy and paste the following code. We will use this DTO to update the payment status. This class contains only the properties required for the Payment Status Update operations. 

using System.ComponentModel.DataAnnotations;
namespace ECommerceAPI.DTO
{
public class PaymentStatusDTO
{
[Required]
public int PaymentId { get; set; }
[Required]
public string Status { get; set; }
}
}
UpdatePaymentResponseDTO.cs

Create a class file named UpdaePaymentResponseDTO.cs within the DTO folder and then copy and paste the following code. We will use this DTO class as the action method’s return type that handles the Payment Status Update Operation.

namespace ECommerceAPI.DTO
{
public class UpdatePaymentResponseDTO
{
public int PaymentId { get; set; }
public string CurrentStatus { get; set; }
public string UpdatedStatus { get; set; }
public string Message { get; set; }
public bool IsUpdated { get; set; }
}
}
Creating a Repository for Payment

Create a class file named PaymentRepository.cs within the Data folder and copy and paste the following code. This class contains the business and data access logic to perform payment-related database operations, such as Making the Payment, Fetching the Payment Details, Updating the Payment Status, etc.

using ECommerceAPI.Data;
using ECommerceAPI.DTO;
using ECommerceAPI.Models;
using Microsoft.Data.SqlClient;
public class PaymentRepository
{
private readonly SqlConnectionFactory _connectionFactory;
public PaymentRepository(SqlConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
}
public async Task<PaymentResponseDTO> MakePaymentAsync(PaymentDTO paymentDto)
{
var orderValidationQuery = "SELECT TotalAmount FROM Orders WHERE OrderId = @OrderId AND Status = 'Pending'";
var insertPaymentQuery = "INSERT INTO Payments (OrderId, Amount, Status, PaymentType, PaymentDate) OUTPUT INSERTED.PaymentId VALUES (@OrderId, @Amount, 'Pending', @PaymentType, @PaymentDate)";
var updatePaymentStatusQuery = "UPDATE Payments SET Status = @Status WHERE PaymentId = @PaymentId";
PaymentResponseDTO paymentResponseDTO = new PaymentResponseDTO();
using (var connection = _connectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
try
{
// Validate order amount and status
decimal orderAmount = 0m;
using (var validationCommand = new SqlCommand(orderValidationQuery, connection, transaction))
{
validationCommand.Parameters.AddWithValue("@OrderId", paymentDto.OrderId);
var result = await validationCommand.ExecuteScalarAsync();
if (result == null)
{
paymentResponseDTO.Message = "Order either does not exist or is not in a pending state.";
return paymentResponseDTO;
}
orderAmount = (decimal)result;
}
if (orderAmount != paymentDto.Amount)
{
paymentResponseDTO.Message = "Payment amount does not match the order total.";
return paymentResponseDTO;
}
// Insert initial payment record with 'Pending' status
int paymentId;
using (var insertCommand = new SqlCommand(insertPaymentQuery, connection, transaction))
{
insertCommand.Parameters.AddWithValue("@OrderId", paymentDto.OrderId);
insertCommand.Parameters.AddWithValue("@Amount", paymentDto.Amount);
insertCommand.Parameters.AddWithValue("@PaymentType", paymentDto.PaymentType);
insertCommand.Parameters.AddWithValue("@PaymentDate", DateTime.Now);
paymentId = (int)await insertCommand.ExecuteScalarAsync();
}
// Simulate interaction with a payment gateway
string paymentStatus = SimulatePaymentGatewayInteraction(paymentDto);
// Update the payment status after receiving the gateway response
using (var updateCommand = new SqlCommand(updatePaymentStatusQuery, connection, transaction))
{
updateCommand.Parameters.AddWithValue("@Status", paymentStatus);
updateCommand.Parameters.AddWithValue("@PaymentId", paymentId);
await updateCommand.ExecuteNonQueryAsync();
paymentResponseDTO.IsCreated = true;
paymentResponseDTO.Status = paymentStatus;
paymentResponseDTO.PaymentId = paymentId;
paymentResponseDTO.Message = $"Payment Processed with Status {paymentStatus}";
}
transaction.Commit();
return paymentResponseDTO;
}
catch (Exception)
{
transaction.Rollback();
throw; // Re-throw to handle the exception further up the call stack
}
}
}
}
private string SimulatePaymentGatewayInteraction(PaymentDTO paymentDto)
{
// Simulate different responses based on the payment type or other logic
switch (paymentDto.PaymentType)
{
case "COD":
return "Completed";  // COD is usually confirmed immediately if used
case "CC":
return "Completed";  // Assuming credit card payments are processed immediately
case "DC":
return "Failed";     // Simulating a failure for demonstration purposes
default:
return "Completed";  // Default to completed for simplicity in this example
}
}
public async Task<UpdatePaymentResponseDTO> UpdatePaymentStatusAsync(int paymentId, string newStatus)
{
// Queries to fetch related order and current payment details
var paymentDetailsQuery = "SELECT p.OrderId, p.Amount, p.Status, o.Status AS OrderStatus FROM Payments p INNER JOIN Orders o ON p.OrderId = o.OrderId WHERE p.PaymentId = @PaymentId";
var updatePaymentStatusQuery = "UPDATE Payments SET Status = @Status WHERE PaymentId = @PaymentId";
UpdatePaymentResponseDTO updatePaymentResponseDTO = new UpdatePaymentResponseDTO()
{
PaymentId = paymentId
};
using (var connection = _connectionFactory.CreateConnection())
{
await connection.OpenAsync();
int orderId;
decimal paymentAmount;
string currentPaymentStatus, orderStatus;
// Retrieve current payment and order status
using (var command = new SqlCommand(paymentDetailsQuery, connection))
{
command.Parameters.AddWithValue("@PaymentId", paymentId);
using (var reader = await command.ExecuteReaderAsync())
{
if (!reader.Read())
{
throw new Exception("Payment record not found.");
}
orderId = reader.GetInt32(reader.GetOrdinal("OrderId"));
paymentAmount = reader.GetDecimal(reader.GetOrdinal("Amount"));
currentPaymentStatus = reader.GetString(reader.GetOrdinal("Status"));
orderStatus = reader.GetString(reader.GetOrdinal("OrderStatus"));
//Also set the CurrentStatus in updatePaymentResponseDTO
updatePaymentResponseDTO.CurrentStatus = currentPaymentStatus;
}
}
// Validate the new status change
if (!IsValidStatusTransition(currentPaymentStatus, newStatus, orderStatus))
{
updatePaymentResponseDTO.IsUpdated = false;
updatePaymentResponseDTO.Message = $"Invalid status transition from {currentPaymentStatus} to {newStatus} for order status {orderStatus}.";
return updatePaymentResponseDTO;
}
// Update the payment status
using (var updateCommand = new SqlCommand(updatePaymentStatusQuery, connection))
{
updateCommand.Parameters.AddWithValue("@PaymentId", paymentId);
updateCommand.Parameters.AddWithValue("@Status", newStatus);
await updateCommand.ExecuteNonQueryAsync();
updatePaymentResponseDTO.IsUpdated = true;
updatePaymentResponseDTO.UpdatedStatus = newStatus;
updatePaymentResponseDTO.Message = $"Payment Status Updated from {currentPaymentStatus} to {newStatus}";
return updatePaymentResponseDTO;
}
}
}
public async Task<Payment?> GetPaymentDetailsAsync(int paymentId)
{
var query = "SELECT PaymentId, OrderId, Amount, Status, PaymentType, PaymentDate FROM Payments WHERE PaymentId = @PaymentId";
Payment? payment = null;
using (var connection = _connectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@PaymentId", paymentId);
using (var reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
payment = new Payment
{
PaymentId = reader.GetInt32(reader.GetOrdinal("PaymentId")),
OrderId = reader.GetInt32(reader.GetOrdinal("OrderId")),
Amount = reader.GetDecimal(reader.GetOrdinal("Amount")),
Status = reader.GetString(reader.GetOrdinal("Status")),
PaymentType = reader.GetString(reader.GetOrdinal("PaymentType")),
PaymentDate = reader.GetDateTime(reader.GetOrdinal("PaymentDate"))
};
}
}
}
}
return payment;
}
private bool IsValidStatusTransition(string currentStatus, string newStatus, string orderStatus)
{
// Completed payments cannot be modified unless it's a refund for a returned order.
if (currentStatus == "Completed" && newStatus != "Refund")
{
return false;
}
// Only pending payments can be cancelled.
if (currentStatus == "Pending" && newStatus == "Cancelled")
{
return true;
}
// Refunds should only be processed for returned orders.
if (currentStatus == "Completed" && newStatus == "Refund" && orderStatus != "Returned")
{
return false;
}
// Payments should only be marked as failed if they are not completed or cancelled.
if (newStatus == "Failed" && (currentStatus == "Completed" || currentStatus == "Cancelled"))
{
return false;
}
// Assuming 'Pending' payments become 'Completed' when the order is shipped or services are rendered.
if (currentStatus == "Pending" && newStatus == "Completed" && (orderStatus == "Shipped" || orderStatus == "Confirmed"))
{
return true;
}
// Handle other generic cases or add more specific business rule checks
return true;
}
}
Registering the Repositories:

Next, we need to register the repositories with the dependency injection container. Please add the following code to the Program.cs class file.

// Registering the Repositories
builder.Services.AddScoped<CustomerRepository>();
builder.Services.AddScoped<ProductRepository>();
builder.Services.AddScoped<OrderRepository>();
builder.Services.AddScoped<PaymentRepository>();

Creating a Custom Response Format in ASP.NET Core Web API:

Creating a Custom Response Format in ASP.NET Core Web API that is uniform across all types of responses, including successful operations and errors, is an excellent way to ensure that the API behaves consistently and predictably. This approach enhances the API’s client integration capabilities and improves its maintainability. So, create a class file named APIResponse.cs within the Models folder and then copy and paste the following code into it.

using System.Net; // Make sure to include this for HttpStatusCode
namespace ECommerceAPI.Models
{
public class APIResponse<T>
{
public bool Success { get; set; }
public HttpStatusCode StatusCode { get; set; }
public string Message { get; set; }
public T Data { get; set; }
public object Error { get; set; }  // Flexible enough to include any error details
// Constructor for a Successful Response
public APIResponse(T data, string message = "", HttpStatusCode statusCode = HttpStatusCode.OK)
{
Success = true;
StatusCode = statusCode;
Message = message;
Data = data;
Error = null;
}
// Constructor for an Error Response
public APIResponse(HttpStatusCode statusCode, string message, object error = null)
{
Success = false;
StatusCode = statusCode;
Message = message;
Data = default(T);
Error = error;
}
}
}

Creating API Controllers:

Now, we will create the Controllers, which will use the repositories to perform the operations. We are going to create different controllers for each service. That means we will create four controllers for handling the Customer, Product, Order, and Payment services. So, let us proceed with implementing these controllers.

Creating Customer Controller:

Create an API Empty Controller named CustomerController within the Controllers folder and then copy and paste the following code. Here, we have defined the methods to handle the Customer related operations using the Customer Repository:

using Microsoft.AspNetCore.Mvc;
using ECommerceAPI.Data;
using ECommerceAPI.DTO;
using ECommerceAPI.Models;
using System.Net;
namespace ECommerceAPI.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class CustomerController : ControllerBase
{
private readonly CustomerRepository _customerRepository;
public CustomerController(CustomerRepository customerRepository)
{
_customerRepository = customerRepository;
}
// GET: api/customer
[HttpGet]
public async Task<APIResponse<List<Customer>>> GetAllCustomers()
{
try
{
var customers = await _customerRepository.GetAllCustomersAsync();
return new APIResponse<List<Customer>>(customers, "Retrieved all customers successfully.");
}
catch (Exception ex)
{
return new APIResponse<List<Customer>>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// GET: api/customer/5
[HttpGet("{id}")]
public async Task<APIResponse<Customer>> GetCustomerById(int id)
{
try
{
var customer = await _customerRepository.GetCustomerByIdAsync(id);
if (customer == null)
{
return new APIResponse<Customer>(HttpStatusCode.NotFound, "Customer not found.");
}
return new APIResponse<Customer>(customer, "Customer retrieved successfully.");
}
catch (Exception ex)
{
return new APIResponse<Customer>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// POST: api/customer
[HttpPost]
public async Task<APIResponse<CustomerResponseDTO>> CreateCustomer([FromBody] CustomerDTO customerDto)
{
if (!ModelState.IsValid)
{
return new APIResponse<CustomerResponseDTO>(HttpStatusCode.BadRequest, "Invalid data", ModelState);
}
try
{
var customerId = await _customerRepository.InsertCustomerAsync(customerDto);
var responseDTO = new CustomerResponseDTO { CustomerId = customerId };
return new APIResponse<CustomerResponseDTO>(responseDTO, "Customer Created Successfully.");
}
catch (Exception ex)
{
return new APIResponse<CustomerResponseDTO>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// PUT: api/customer/5
[HttpPut("{id}")]
public async Task<APIResponse<bool>> UpdateCustomer(int id, [FromBody] CustomerDTO customerDto)
{
if (!ModelState.IsValid)
{
return new APIResponse<bool>(HttpStatusCode.BadRequest, "Invalid data", ModelState);
}
if (id != customerDto.CustomerId)
{
return new APIResponse<bool>(HttpStatusCode.BadRequest, "Mismatched Customer ID");
}
try
{
await _customerRepository.UpdateCustomerAsync(customerDto);
return new APIResponse<bool>(true, "Customer Updated Successfully.");
}
catch (Exception ex)
{
return new APIResponse<bool>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// DELETE: api/customer/5
[HttpDelete("{id}")]
public async Task<APIResponse<bool>> DeleteCustomer(int id)
{
try
{
var customer = await _customerRepository.GetCustomerByIdAsync(id);
if (customer == null)
{
return new APIResponse<bool>(HttpStatusCode.NotFound, "Customer not found.");
}
await _customerRepository.DeleteCustomerAsync(id);
return new APIResponse<bool>(true, "Customer deleted successfully.");
}
catch (Exception ex)
{
return new APIResponse<bool>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
}
}
Creating Product Controller:

Create an API Empty Controller named ProductController within the Controllers folder and then copy and paste the following code. Here, we have defined the methods to handle the Product related operations using the Product Repository:

using Microsoft.AspNetCore.Mvc;
using ECommerceAPI.Data;
using ECommerceAPI.DTO;
using ECommerceAPI.Models;
using System.Net;
namespace ECommerceAPI.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class ProductController : ControllerBase
{
private readonly ProductRepository _productRepository;
public ProductController(ProductRepository productRepository)
{
_productRepository = productRepository;
}
// GET: api/product
[HttpGet]
public async Task<APIResponse<List<Product>>> GetAllProducts()
{
try
{
var products = await _productRepository.GetAllProductsAsync();
return new APIResponse<List<Product>>(products, "Retrieved all products successfully.");
}
catch (Exception ex)
{
return new APIResponse<List<Product>>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// GET: api/product/5
[HttpGet("{id}")]
public async Task<APIResponse<Product>> GetProductById(int id)
{
try
{
var product = await _productRepository.GetProductByIdAsync(id);
if (product == null)
{
return new APIResponse<Product>(HttpStatusCode.NotFound, "Product not found.");
}
return new APIResponse<Product>(product, "Product retrieved successfully.");
}
catch (Exception ex)
{
return new APIResponse<Product>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// POST: api/product
[HttpPost]
public async Task<APIResponse<ProductResponseDTO>> CreateProduct([FromBody] ProductDTO product)
{
if (!ModelState.IsValid)
{
return new APIResponse<ProductResponseDTO>(HttpStatusCode.BadRequest, "Invalid data", ModelState);
}
try
{
var productId = await _productRepository.InsertProductAsync(product);
var responseDTO = new ProductResponseDTO { ProductId = productId };
return new APIResponse<ProductResponseDTO>(responseDTO, "Product created successfully.");
}
catch (Exception ex)
{
return new APIResponse<ProductResponseDTO>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// PUT: api/product/5
[HttpPut("{id}")]
public async Task<APIResponse<bool>> UpdateProduct(int id, [FromBody] ProductDTO product)
{
if (!ModelState.IsValid)
{
return new APIResponse<bool>(HttpStatusCode.BadRequest, "Invalid data", ModelState);
}
if (id != product.ProductId)
{
return new APIResponse<bool>(HttpStatusCode.BadRequest, "Mismatched product ID");
}
try
{
await _productRepository.UpdateProductAsync(product);
return new APIResponse<bool>(true, "Product updated successfully.");
}
catch (Exception ex)
{
return new APIResponse<bool>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// DELETE: api/product/5
[HttpDelete("{id}")]
public async Task<APIResponse<bool>> DeleteProduct(int id)
{
try
{
var product = await _productRepository.GetProductByIdAsync(id);
if (product == null)
{
return new APIResponse<bool>(HttpStatusCode.NotFound, "Product not found.");
}
await _productRepository.DeleteProductAsync(id);
return new APIResponse<bool>(true, "Product deleted successfully.");
}
catch (Exception ex)
{
return new APIResponse<bool>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
}
}
Creating Order Controller:

Create an API Empty Controller named OrderController within the Controllers folder and then copy and paste the following code. Here, we have defined the methods to handle the Order related operations using the Order Repository:

using Microsoft.AspNetCore.Mvc;
using ECommerceAPI.Data;
using ECommerceAPI.DTO;
using ECommerceAPI.Models;
using System.Net;
namespace ECommerceAPI.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class OrderController : ControllerBase
{
private readonly OrderRepository _orderRepository;
public OrderController(OrderRepository orderRepository)
{
_orderRepository = orderRepository;
}
// GET: api/order
[HttpGet]
public async Task<ActionResult<APIResponse<List<Order>>>> GetAllOrders(string Status = "Pending")
{
try
{
var orders = await _orderRepository.GetAllOrdersAsync(Status);
return Ok(new APIResponse<List<Order>>(orders, "Retrieved all orders successfully."));
}
catch (Exception ex)
{
return StatusCode(500, new APIResponse<List<Order>>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message));
}
}
// POST: api/order
[HttpPost]
public async Task<APIResponse<CreateOrderResponseDTO>> CreateOrder([FromBody] OrderDTO orderDto)
{
if (!ModelState.IsValid)
{
return new APIResponse<CreateOrderResponseDTO>(HttpStatusCode.BadRequest, "Invalid data", ModelState);
}
try
{
var response = await _orderRepository.CreateOrderAsync(orderDto);
return new APIResponse<CreateOrderResponseDTO>(response, response.Message);
}
catch (Exception ex)
{
return new APIResponse<CreateOrderResponseDTO>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// GET: api/order/5
[HttpGet("{id}")]
public async Task<APIResponse<Order>> GetOrderById(int id)
{
try
{
var order = await _orderRepository.GetOrderDetailsAsync(id);
if (order == null)
{
return new APIResponse<Order>(HttpStatusCode.NotFound, "Order not found.");
}
return new APIResponse<Order>(order, "Order retrieved successfully.");
}
catch (Exception ex)
{
return new APIResponse<Order>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// PUT: api/order/5/status
[HttpPut("{id}/status")]
public async Task<APIResponse<OrderStatusResponseDTO>> UpdateOrderStatus(int id, [FromBody] OrderStatusDTO status)
{
if (!ModelState.IsValid)
{
return new APIResponse<OrderStatusResponseDTO>(HttpStatusCode.BadRequest, "Invalid data", ModelState);
}
if (id != status.OrderId)
{
return new APIResponse<OrderStatusResponseDTO>(HttpStatusCode.BadRequest, "Mismatched Order ID");
}
try
{
var response = await _orderRepository.UpdateOrderStatusAsync(id, status.Status);
return new APIResponse<OrderStatusResponseDTO>(response, response.Message);
}
catch (Exception ex)
{
return new APIResponse<OrderStatusResponseDTO>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// PUT: api/order/5/confirm
[HttpPut("{id}/confirm")]
public async Task<APIResponse<ConfirmOrderResponseDTO>> ConfirmOrder(int id)
{
try
{
var response = await _orderRepository.ConfirmOrderAsync(id);
return new APIResponse<ConfirmOrderResponseDTO>(response, response.Message);
}
catch (Exception ex)
{
return new APIResponse<ConfirmOrderResponseDTO>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
}
}
Creating Payment Controller:

Create an API Empty Controller named PaymentController within the Controllers folder and then copy and paste the following code. Here, we have defined the methods to handle the Payment related operations using the Payment Repository:

using Microsoft.AspNetCore.Mvc;
using ECommerceAPI.DTO;
using ECommerceAPI.Models;
using System.Net;
namespace ECommerceAPI.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class PaymentController : ControllerBase
{
private readonly PaymentRepository _paymentRepository;
public PaymentController(PaymentRepository paymentRepository)
{
_paymentRepository = paymentRepository;
}
// POST: api/payment/makepayment
[HttpPost("MakePayment")]
public async Task<APIResponse<PaymentResponseDTO>> MakePayment([FromBody] PaymentDTO paymentDto)
{
if (!ModelState.IsValid)
{
return new APIResponse<PaymentResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data", ModelState);
}
try
{
var response = await _paymentRepository.MakePaymentAsync(paymentDto);
return new APIResponse<PaymentResponseDTO>(response, response.Message);
}
catch (Exception ex)
{
return new APIResponse<PaymentResponseDTO>(HttpStatusCode.InternalServerError, "Internal Server Error: " + ex.Message);
}
}
// GET: api/payment/paymentdetails/5
[HttpGet("PaymentDetails/{id}")]
public async Task<APIResponse<Payment>> GetPaymentDetails(int id)
{
try
{
var payment = await _paymentRepository.GetPaymentDetailsAsync(id);
if (payment == null)
{
return new APIResponse<Payment>(HttpStatusCode.NotFound, $"Payment with ID {id} not found.");
}
return new APIResponse<Payment>(payment, "Payment retrieved successfully.");
}
catch (Exception ex)
{
return new APIResponse<Payment>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
// PUT: api/payment/updatepaymentstatus/5
[HttpPut("UpdatePaymentStatus/{id}")]
public async Task<APIResponse<UpdatePaymentResponseDTO>> UpdatePaymentStatus(int id, [FromBody] PaymentStatusDTO paymentStatusDTO)
{
if (!ModelState.IsValid)
{
return new APIResponse<UpdatePaymentResponseDTO>(HttpStatusCode.BadRequest, "Invalid data", ModelState);
}
if (id != paymentStatusDTO.PaymentId)
{
return new APIResponse<UpdatePaymentResponseDTO>(HttpStatusCode.BadRequest, "Mismatched Payment ID");
}
try
{
var response = await _paymentRepository.UpdatePaymentStatusAsync(id, paymentStatusDTO.Status);
return new APIResponse<UpdatePaymentResponseDTO>(response, response.Message);
}
catch (Exception ex)
{
return new APIResponse<UpdatePaymentResponseDTO>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
}
}
}
}
Globalization Invariant Mode set to FALSE.

Globalization Invariant Mode is a feature that is typically used in .NET applications to reduce the deployment size by not including culture-specific data. This mode can be especially useful in environments where resources are constrained or where deployment size is a critical factor, such as Docker containers or microservices. When this mode is enabled, the application treats all cultures like the invariant culture (en-US-POSIX-like behavior). So, open the Project Properties file and set the InvariantGlobalization to false as follows:

<InvariantGlobalization>false</InvariantGlobalization>

Modifying JSON Serialization Settings:

In ASP.NET Core Web API, the default behavior for JSON serialization involves converting property names to camel cases. This is due to the default JSON options set by the framework to align with JavaScript and JSON standards. However, if you want to keep the property names exactly as they are defined in your C# models (typically PascalCase), you can customize the JSON serialization settings. So, modify the AddController Service as follows with the Program.cs class file:

builder.Services.AddControllers().AddJsonOptions(options =>
{
options.JsonSerializerOptions.PropertyNamingPolicy = null;
});

That’s it. Now, run the application and test the API endpoints, and it should work as expected. I hope you enjoy this article on how to develop a Real-Time E-Commerce Application Development using ASP.NET Core Web API. Please give your valuable feedback in the comment section.

Leave a Reply

Your email address will not be published. Required fields are marked *