Stored Procedure with Dapper in .NET Core API

IGenericRepository.cs

using BikeStores.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.BusinessLogic.Interfaces
{
    public interface IGenericRepository<T> where T : class
    {
        Task<IEnumerable<T>> Get();
        Task<T> Find(Guid uid);
        Task<T> Add(T model);
        Task<T> Update(T model);
        Task<int> Remove(T model);
    }
}

IUnitOfWork.cs

using BikeStores.BusinessLogic.Repositories;
using BikeStores.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.BusinessLogic.Interfaces
{
    public interface IUnitOfWork
    {
       ICategories Categories { get; }
    }
}

UnitOfWork.cs

using BikeStores.BusinessLogic.Interfaces;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.BusinessLogic.Repositories
{
    public class UnitOfWork : IUnitOfWork
    {
        public ICategories Categories { get; set; }

        public UnitOfWork(ICategories Categories)
        {
            this.Categories = Categories;
        }
    }
}

ICategories.cs

using BikeStores.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.BusinessLogic.Interfaces
{
    public interface ICategories:IGenericRepository<CategoriesModel>
    {
    }
}

CategoriesRepository.cs

using BikeStores.BusinessLogic.Interfaces;
using BikeStores.Models;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Reflection.Metadata;

namespace BikeStores.BusinessLogic.Repositories
{
    public class CategoriesRepository : ICategories
    {
        private readonly IConfiguration _configuration;
        private readonly SqlConnection _connection;
        public CategoriesRepository(IConfiguration configuration)
        {
            _configuration = configuration;
            _connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));
        }
        public async Task<IEnumerable<CategoriesModel>> Get()
        {
            return await _connection.QueryAsync<CategoriesModel>("GetAllCategories", commandType: CommandType.StoredProcedure);
        }
        public async Task<CategoriesModel> Find(Guid uid)
        {
            var parmeters = new DynamicParameters();
            parmeters.Add("@f_uid",uid);
            return await _connection.QueryFirstOrDefaultAsync<CategoriesModel>("GetCategoryById", parmeters,commandType: CommandType.StoredProcedure);
        }
        public async Task<CategoriesModel> Add(CategoriesModel model)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@f_uid", Guid.NewGuid());
            parameters.Add("@f_category_name", model.f_category_name);
            parameters.Add("",model.f_create_date=DateTime.Now);
            await _connection.ExecuteAsync("AddNewCategoty",parameters,commandType:CommandType.StoredProcedure);
            return model;
        }
        public async Task<CategoriesModel> Update(CategoriesModel model)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@f_category_name", model.f_category_name);
            parameters.Add("@f_update_date", model.f_update_date= DateTime.Now);

            await _connection.ExecuteAsync("UpdateCategory", parameters,commandType:CommandType.StoredProcedure);
            return model;
        }
        public async Task<int> Remove(CategoriesModel model)
        {
            var parameters=new DynamicParameters();
            parameters.Add("@f_uid", model.f_uid);
            parameters.Add("f_delete_date", model.f_delete_date=DateTime.Now);
            return await _connection.ExecuteAsync("DeleteCategory",parameters,commandType:CommandType.StoredProcedure);
        }
    }
}

Adding a Controller

In Web API, a controller is an object that handles HTTP requests. We'll add a controller that can return either a list of brands or a single brand specified by ID.

1. In Solution Explorer, right-click the Controllers folder. Select Add and then select Controller.
2. In the Add Scaffold dialog, select Web API Controller - Empty. Click Add.
3. In the Add Controller dialog, name the controller "BrandsController". Click Add.
4. The scaffolding creates a file named BrandsController.cs in the Controllers folder.

If this file is not open already, double-click the file to open it. Replace the code in this file with the following.

using BikeStores.BusinessLogic.Interfaces;
using BikeStores.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace BikeStores.Api.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class CategoriesController : ControllerBase
    {
        private readonly IUnitOfWork unitOfWork;
        public CategoriesController(IUnitOfWork unitOfWork)
        {
            this.unitOfWork = unitOfWork;
        }

        [HttpGet]
        public async Task<IEnumerable<CategoriesModel>> GetCatigories()
        {
            var catigories = await unitOfWork.Categories.Get();
            return catigories;
        }

        [HttpGet("{id}")]
        public async Task<IActionResult>GetCategory(Guid id)
        {
            if (id == Guid.Empty)
            {
                return BadRequest();
            }

            var category = await unitOfWork.Categories.Find(id);

            if (category == null)
            {
                return NotFound();
            }

            return Ok(category);
        }
        [HttpPost]
        public async Task<IActionResult> PostCategory(CategoriesModel model)
        {
            await unitOfWork.Categories.Add(model);
            return Ok();
        }

        [HttpPut]
        public async Task<IActionResult> PutCategory(CategoriesModel model)
        {
            if (model == null || model.f_uid == Guid.Empty)
            {
                return BadRequest();
            }

            var category = await unitOfWork.Categories.Find(model.f_uid);

            if (category == null)
            {
                return NotFound();
            }

            await unitOfWork.Categories.Update(category);
            return Ok();
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteCategory(Guid id)
        {
            if (id == Guid.Empty)
            {
                return BadRequest();
            }

            var category = await unitOfWork.Categories.Find(id);

            if (category == null)
            {
                return NotFound();
            }

            await unitOfWork.Categories.Remove(category);
            return Ok();
        }
    }
}

Add Connection String

In Solution Explorer, select a file appsettings.json. Double-click on this file to open the "Add" connection string, as shown below.

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=FARHAN1086\\SQLEXPRESS;Initial Catalog=BikeStore-Dev-DB;Integrated Security=True;MultipleActiveResultSets=True;Encrypt=False;Trust Server Certificate=False;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Add repository services in the Program.cs

builder.Services.AddTransient<ICategories, CategoriesRepository>();
builder.Services.AddTransient<IUnitOfWork, UnitOfWork>();

Run the Application

Select Ctrl+F5 to run the app without the debugger. Visual Studio runs the ASP.NET app and opens the default browser.

Conclusion

By leveraging Dapper with stored procedures in your .NET Core application, you can achieve a balance of performance and simplicity when working with databases. Whether you're querying data, performing data manipulation operations, or executing complex business logic stored in your database, Dapper provides the tools you need to get the job done efficiently.

In the following sections, we'll delve into each step in more detail, providing code examples and best practices along the way. Let's get started on our journey to mastering Dapper and stored procedures in .NET Core!