Post
Ask Question

Stored Procedure with Dapper in .NET Core API

Introduction

If you're diving into .NET Core development and want to leverage the simplicity and efficiency of Dapper for working with databases, understanding how to use Dapper with stored procedures is a valuable skill. In this article, we'll explore the basics of integrating Dapper with stored procedures in a .NET Core application.

To use Dapper with stored procedures in .NET Core, you'll typically follow these steps:

Why Dapper?

Dapper is a lightweight, high-performance micro-ORM (Object-Relational Mapping) framework for .NET. Unlike full-fledged ORMs like Entity Framework, Dapper focuses on simplicity and performance, allowing you to execute raw SQL queries efficiently while still mapping the results to your .NET objects. This makes Dapper an excellent choice for scenarios where you need fine-grained control over your SQL queries or want to work closely with stored procedures.

Getting Started

To begin, ensure you have Dapper installed in your .NET Core project. You can do this easily using NuGet.

Install Dapper: First, you need to install the Dapper package into your .NET Core project. You can do this via NuGet Package Manager Console or NuGet Package Manager UI in Visual Studio, or via the dot net CLI.

Option 1. Install the Dapper, Microsoft.Data.SqlClient Library through NuGet Package Manager

  1. Go to Project > Manage NuGet Packages.
  2. On the NuGet Package Manager page, select nuget.org as the Package source.
  3. Under the Browse tab, search for Dapper, then choose Dapper from the list and select Install.
  4. If prompted to verify the installation, click OK.
    Dapper

Option 2. Installing the Dapper Library through the Visual Studio Command Line

  1. In Visual Studio, navigate to Tools > NuGet Package Manager > Package Manager Console.
  2. Enter the following command in the Package Manager Console tab. 
    Install-Package Dapper

Prerequisites

  1. Visual Studio is the latest version with the ASP.NET and web development workload.
  2. .NET SDK latest version (.NET 8.0)
  3. SQL SERVER latest

Creating database table

Launch SQL Server, select Database, and New Database.

CREATE TABLE [dbo].[t_categories](
	[f_uid] [uniqueidentifier] PRIMARY KEY NOT NULL,
	[f_iid] [int] IDENTITY(1,1) NOT NULL,
	[f_category_name] [varchar](255) NOT NULL,
	[f_create_date] [datetime] NULL,
	[f_update_date] [datetime] NULL,
	[f_delete_date] [datetime] NULL
)

Creating Stored Procedure

---------Get All Stored Prodecedure-------

Create procedure GetAllCategories
AS
BEGIN
 SELECT[f_uid]
      ,[f_iid]
      ,[f_category_name]
      ,[f_create_date]
      ,[f_update_date]
      ,[f_delete_date]
 FROM
       [t_categories]
END

---------Find by Id Stored Prodecedure-------

Create procedure GetCategoryById
@f_uid uniqueidentifier
AS
BEGIN
 SELECT[f_uid]
      ,[f_iid]
      ,[f_category_name]
      ,[f_create_date]
      ,[f_update_date]
      ,[f_delete_date]
 FROM
       [t_categories]
 WHERE
	 [f_uid]=@f_uid
END

---------Add Stored Prodecedure-------

Create procedure AddNewCategoty
@f_uid uniqueidentifie
@f_category_name varchar,
@f_create_date datetime
AS
BEGIN
  INSERT INTO
  t_categories
 (f_uid,
  f_category_name,
  f_create_date)
values(@f_uid,
  @f_category_name,
  @f_create_date)
END

---------Update Stored Prodecedure-------

Create Procedure UpdateCategory
	@f_uid uniqueidentifier,
	@f_category_name varchar(255),
	@f_update_date datetime
AS
BEGIN
	UPDATE [dbo].[t_categories]
	SET
		[f_category_name]=@f_category_name,
		[f_update_date]=@f_update_date
	WHERE
        [f_uid]=@f_uid
END

---------Delete Stored Prodecedure-------

Create Procedure DeleteCategory
@f_uid uniqueidentifier,
@f_delete_date datetime
AS
BEGIN
 UPDATE [dbo].[t_categories]
    SET
    [f_delete_date]=@f_delete_date
WHERE
    [f_uid]=@f_uid
END

Creating a New Project in Visual Studio

  1. Launch Visual Studio and select Create a New Project.
    Create a new project

     

Creating a Core Web API

  1. Start Visual Studio and choose Create a new project.
  2. In the Create a new project dialog, select ASP.NET Core Web API
  3. Click Next.
    new API Create

Configuring Your New Project

  1. Enter the project name as BikeStore.
  2. Choose a location for your project.
  3. Specify the solution name.
  4. Click on Next.
  5. Select Framework 8.0.
  6. Click Create.
    Additional information

Additional information

  1. In the Additional information dialog.
    • Select .NET 8.0 (Long Term Support).
    • Verify that Do not use top-level statements is unchecked.
  2. Select Create.
    Additional information

Adding a Data Model Class

A model is an object that represents the data in your application. ASP.NET Web API can automatically serialize your model to JSON, XML, or some other format and then write the serialized data into the body of the HTTP response message. As long as a client can read the serialization format, it can deserialize the object. Most clients can parse either XML or JSON.

In Solution Explorer, right-click the Models folder. From the context menu, select Add, then select Class

Name the class "BaseModel". Add the following properties to the BaseModel class.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.Models
{
    public class BaseModel
    {
        public Guid f_uid { get; set; }
        [Display(Name = "ID")]
        public int f_iid { get; set; }
        [Display(Name = "Create Date")]
        public DateTime f_create_date { get; set; }
        [Display(Name = "Update Date")]
        public DateTime f_update_date { get; set; }
        [Display(Name = "Delete Date")]
        public DateTime f_delete_date { get; set; }
    }
}

In Solution Explorer, right-click the Models folder. From the context menu, select Add, then select Class

Name the class "BrandModel". Add the following properties to the BrandModel class.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.Models
{
    public class CategoriesModel:BaseModel
    {
        [Display(Name ="Category Name")]
        public string f_category_name { get; set; }
    }
}

Adding interfaces

  1. In Solution Explorer, right-click Add New Folder.
  2. Rename the folder to Interfaces.
  3. Right-click on the renamed folder and Add the interface name IGenericRepository.cs

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.

Run the application

Run the application

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!


Similar Articles