Efficient API Development Database Operations in ASP.NET Core Web Using Dapper and Stored Procedures

Diving into ASP.NET Core with a dapper sounds like a great way to work with databases more straightforwardly. Dapper is a micro ORM (Object-Relational Mapping) that helps in handling database operations while keeping the SQL queries simple and efficient.

To get started with using Dapper in an ASP.NET Core application.

Step 1. Create a New ASP.NET Core Project

You can create a new ASP.NET Core project using Visual Studio or by using the .NET CLI with the command.

dotnet new web -n APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure

cd APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure

Step 2. Install the Dapper Package

Add the Dapper package to your project using the .NET CLI.

dotnet add package Dapper

Step 3. Set Up Database Connection

In your appsettings.json add your database connection string.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "YourDatabaseConnectionString"
  },
  "AllowedHosts": "*"
}

Step 4. Create a Model

Define a model class that represents the table structure in your database.

namespace APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Model
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string Category { get; set; }
        public string CategoryName { get; set; }
        public string CategoryDescription { get; set; }
        public string CategoryCategory { get; set; }
        public string CategoryCategoryName { get; set; }
    }
}

Dapper Db Connection

using System.Data;

namespace APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.ServiceContracts
{
    public interface IDapperDbConnection
    {
        public IDbConnection CreateConnection();
    }
}

Dapper Db Connection Class

using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.ServiceContracts;
using Microsoft.Data.SqlClient;
using System.Data;

namespace APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.DapperDbConnection
{
    public class DapperDbConnection: IDapperDbConnection
    {
        public readonly string _connectionString;

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

        public IDbConnection CreateConnection()
        {
            return new SqlConnection(_connectionString);
        }

    }
}

Product Repository Interface

using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Model;

namespace APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.ServiceContracts
{
    public interface IProductRepository
    {
        Task<IEnumerable<Product>> GetAllProductsAsync();
        Task<Product> GetProductByIdAsync(int id);
        Task<int> CreateProductAsync(Product Product);
        Task<bool> UpdateProductAsync(Product Product);
        Task<bool> DeleteProductAsync(int id);
    }
}

Create SQL Stored Procedures

GetAllProducts Store Procedure

CREATE PROCEDURE StpGetAllProducts
AS
BEGIN
    SELECT * FROM Products;
END

StpGetProductById Store Procedure

CREATE PROCEDURE StpGetProductById
    @Id INT
AS
BEGIN
    SELECT * FROM Products WHERE Id = @Id;
END

StpAddProduct Store Procedure

CREATE PROCEDURE StpAddProduct
    @Name NVARCHAR(100), 
AS
BEGIN
    INSERT INTO Products (Name)
    VALUES (@Name);
    SELECT SCOPE_IDENTITY();
END

StpUpdateProduct Store Procedure

CREATE PROCEDURE StpUpdateProduct
    @Id INT,
    @Name NVARCHAR(100)
AS
BEGIN
    UPDATE Products
    SET Name = @Name 
    WHERE Id = @Id;
END

StpDeleteProduct Store Procedure

CREATE PROCEDURE StpDeleteProduct
    @Id INT
AS
BEGIN
    DELETE FROM Products WHERE Id = @Id;
END

Product Repository Implementation

using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Model;
using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.ServiceContracts;
using Dapper;
using System.Data;

namespace APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Repository
{
    public class ProductRepository : IProductRepository
    {
        private readonly IDapperDbConnection _dapperDbConnection;

        public ProductRepository(IDapperDbConnection dapperDbConnection)
        {
            _dapperDbConnection = dapperDbConnection;
        }

        public async Task<IEnumerable<Product>> GetAllProductsAsync()
        {
            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                return await db.QueryAsync<Product>("StpGetAllProducts", commandType: CommandType.StoredProcedure);
            }
        }

        public async Task<Product> GetProductByIdAsync(int id)
        {
            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                var parameters = new { Id = id };
                return await db.QueryFirstOrDefaultAsync<Product>("StpGetProductById", parameters, commandType: CommandType.StoredProcedure);
            }
        }

        public async Task<int> CreateProductAsync(Product product)
        {
            if(product == null)
            {
                throw new ArgumentNullException(nameof(product));
            }

            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                return await db.ExecuteScalarAsync<int>("StpAddProduct",
                    new
                    {
                        product.Name,
                        // Other parameters
                    },
                    commandType: CommandType.StoredProcedure);
            }
        }

        public async Task<bool> UpdateProductAsync(Product product)
        {
            if(product == null)
            {
                throw new ArgumentNullException(nameof(product));
            }

            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                int rowsAffected = await db.ExecuteAsync("StpUpdateProduct",
                    new
                    {
                        product.Id,
                        product.Name,
                        // Other parameters
                    },
                    commandType: CommandType.StoredProcedure);

                return rowsAffected > 0;
            }
        }

        public async Task<bool> DeleteProductAsync(int id)
        {
            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                int rowsAffected = await db.ExecuteAsync("StpDeleteProduct",
                    new { Id = id },
                    commandType: CommandType.StoredProcedure);

                return rowsAffected > 0;
            }
        }
    }
}

IProduct Service Interface

using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Model;

namespace APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.ServiceContracts
{
    public interface IProductService
    {
        Task<IEnumerable<Product>> GetAllProductsAsync();
        Task<Product> GetProductByIdAsync(int id);
        Task<int> CreateProductAsync(Product Product);
        Task<bool> UpdateProductAsync(Product Product);
        Task<bool> DeleteProductAsync(int id);
    }
}

Product Service Implementation

using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Model;
using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.ServiceContracts;

namespace APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Services
{
    public class ProductService: IProductService
    {
        IProductRepository _productRepository;
        ILogger<ProductService> _logger;
        public ProductService(IProductRepository productRepository, ILogger<ProductService> logger)
        {
            this._productRepository = productRepository;
            _logger = logger;
        }

        public async Task<int> CreateProductAsync(Product Product)
        {
            try
            {
                return await _productRepository.CreateProductAsync(Product);
            }
            catch(Exception ex)
            {
                _logger.Log(LogLevel.Error, ex.Message);    
                throw new Exception();
            }
        }

        public Task<bool> DeleteProductAsync(int id)
        {
            throw new NotImplementedException();
        }

        public async Task<IEnumerable<Product>> GetAllProductsAsync()
        {
            try
            {
                return await _productRepository.GetAllProductsAsync();
            }
            catch(Exception ex)
            {
                _logger.Log(LogLevel.Error, ex.Message);
                throw new Exception();
            }
        }

        public async Task<Product> GetProductByIdAsync(int id)
        {
            try
            {
                return await _productRepository.GetProductByIdAsync(id);
            }
            catch(Exception ex)
            {
                _logger.Log(LogLevel.Error, ex.Message);
                throw new Exception();
            }
        }

        public async Task<bool> UpdateProductAsync(Product Product)
        {
            try
            {
                return await _productRepository.UpdateProductAsync(Product);
            }
            catch(Exception ex)
            {
                _logger.Log(LogLevel.Error, ex.Message);
                throw new Exception();
            }
        }
    }
}

Application DB Context

using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Model;
using Microsoft.EntityFrameworkCore;

namespace APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.DatabaseContext
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
        {

        }

        public DbSet<Product> Products { get; set; }
    }
}

Startup.cs and Dependency Injection

using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.DapperDbConnection;
using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.DatabaseContext;
using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Repository;
using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.ServiceContracts;
using APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure.Services;
using Microsoft.EntityFrameworkCore;
using Microsoft.OpenApi.Models;

var builder = WebApplication.CreateBuilder(args);

// Access configuration from the builder
var configuration = builder.Configuration;

// Add services to the container.
// Add Dapper connection
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(configuration.GetConnectionString("DefaultConnection")));

// Add repositories and services
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<IProductService, ProductService>();
builder.Services.AddScoped<IDapperDbConnection, DapperDbConnection>();

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(c =>
{
    c.SwaggerDoc("v1", new OpenApiInfo { Title = "API Devlopment Using Dapper and Stored Procedure with AspNET Core Web API", Version = "v1" });
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if(app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI(c =>
    {
        c.SwaggerEndpoint("/swagger/v1/swagger.json", "API Devlopment Using Dapper and Stored Procedure with AspNET Core Web API");
        // Configure additional settings for SwaggerUI if needed
    });
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Output

Output image

GitHub Project Link

https://github.com/SardarMudassarAliKhan/APIDevelopmentUsingAspNetCoreWithDapperAndStoredProcedure

Conclusion

Setting up an ASP.NET Core application, whether in .NET 7 or earlier versions, involves configuring services, defining middleware, and structuring your application's components like repositories, services, and controllers.

Here's a summarized approach.

Configuration: Configure services in the Startup class using the `ConfigureServices` method. Register dependencies, such as DbContext, services, repositories, and other components.
Middleware: Use the Configure method in Startup to set up middleware. This includes error handling, routing, authentication, and endpoint mapping.
DbContext and Dapper: If using both Entity Framework Core and Dapper, ensure proper configuration for DbContext and Dapper connections. You can inject DbContext and Dapper connections into your repositories or services.
Services and Repositories: Implement business logic in services and handle data access in repositories. Ensure separation of concerns, where services handle business logic, and repositories interact with the database.
Controllers: Create controllers to define endpoints that handle incoming HTTP requests and utilize services to process data.
Exception Handling and Logging: Implement appropriate exception handling and logging strategies throughout your application to handle errors gracefully and log relevant information.

Remember to stay updated with the latest documentation and best practices, as newer versions might introduce improvements or changes in API usage. Building an efficient and maintainable ASP.NET Core application involves following solid architectural patterns, ensuring proper separation of concerns, and keeping code clean and maintainable.