Using Dapper in ASP.NET Core Web API

In this article, we will be using Dapper in ASP NET Core Web API. I will create an ASP.NET Core WEB API, and we will be using SQL Stored procedure on this project. This tutorial will help us to understand how Dapper works and how to implement them.

What is Dapper?

Up to 80% OFF Web Hosting Plans +
FREE Domain + FREE SSL

Hostinger

Dapper is a micro ORM. It is a simple object mapper framework that helps map the native query output to a model class. This framework is built by the StackOverflow team and released as open-source. This tool is available on NuGet Packages

Before we start, Please make sure to have installed the following

  • The latest version of Visual Studio
  • Alternatively, you can also use the Visual Studio Code.
  • SQL Server

If you already have installed all of that , then let’s start.

I. Create and Setup a new ASP.NET Core Web API

  1. First, create your ASP.NET Core Web API. To do that just follow the steps below.
  • Select File > New > Project.
  • Select ASP.NET Core Web Application. Name the project DpCoreAPI to have the same namespace as my project. Click OK.
  • Select API and then uncheck Configure for HTTPS.
  • Lastly, Click on Create.
New Core Web API - Dapper in ASP NET Core Web API

2. Now we need to install required packages for this project. I have listed it all below.

  • Dapper
  • Microsoft.EntityFrameworkCore.SqlServer.Design

Use NuGet Package Manager to install this package. If this is your first time using this, you can visit this article.

NuGet Packages

3. After installation of all the necessary packages that we need. Add your connection string configuration inside appsettings.json.

Web API Appsettings.json
"ConnectionStrings": {
     "default": "Data Source=DESKTOP-4DU98BI\SQLEXPRESS01;Initial Catalog=DBTest;User ID=freecode;Password=freecodespot;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
   }

II. Create a dummy Database table

Since we need a database for this tutorial, below is the table structure that I created.

DBTest Table structure

As you can observe in the image above, I also created three stored procedures. That is sp_AddMember, sp_DeleteMember and sp_UpdateMember.

sp_AddMember

Add new member from members table
USE [DBTest]
 GO
 ALTER PROCEDURE [dbo].[sp_AddMember]
 @id int, @Name Nvarchar(50) , @Contact nvarchar(50), @Address Nvarchar(50), @retVal int output
 AS
 BEGIN
     SET NOCOUNT ON;
     Insert into members([Name],Contact,[Address],RegDate) VALUES(@Name,@Contact,@Address,GETDATE())
     if @@ROWCOUNT > 0
     BEGIN
     SET @retVal = 200
     END
     ELSE
     BEGIN
     SET @retVal = 500
     END
 END

sp_DeleteMember

Delete member from members table
USE [DBTest]
 GO
 ALTER PROCEDURE [dbo].[sp_DeleteMember]
 @id int, @retVal int output
 AS
 BEGIN
     SET NOCOUNT ON;
     Delete members where Id = @id
 if @@ROWCOUNT > 0 BEGIN SET @retVal = 200 END ELSE BEGIN SET @retVal = 500 END
 END

sp_UpdateMember

Update record in the members table
USE [DBTest]
 GO
 ALTER PROCEDURE [dbo].[sp_UpdateMember]
 @id int, @Name Nvarchar(50) , @Contact nvarchar(50), @Address Nvarchar(50), @retVal int output
 AS
 BEGIN
     SET NOCOUNT ON;
     UPDATE members SET [Name] = @Name, [email protected], [Address] = @Address where Id = @id
 if @@ROWCOUNT > 0 BEGIN SET @retVal = 200 END ELSE BEGIN SET @retVal = 500 END
 END

III. Create Repositories

This part handles the main functions of our application this is where we can see Dappers in action.

Services repositories
  1. Create a Service folder inside your projects. Create an interface class; you can name it IDapperRepository. Place the code snippet below.
T execute_sp(string query, DynamicParameters sp_params, CommandType commandType = CommandType.StoredProcedure);
         List GetAll(string query, DynamicParameters sp_params, CommandType commandType = CommandType.StoredProcedure);

This is how my IDapperRepository full code look like.

using Dapper;
 using System;
 using System.Collections.Generic;
 using System.Data;
 using System.Linq;
 using System.Threading.Tasks;
 namespace DPCoreApi.Services
 {
     public interface IDapperRepository
     {
         T execute_sp<T>(string query, DynamicParameters sp_params, CommandType commandType = CommandType.StoredProcedure);
         List<T> GetAll<T>(string query, DynamicParameters sp_params, CommandType commandType = CommandType.StoredProcedure);
 }
 }

2. Now create the implementation class for IDapperRepository. In my case, I named it DapperRepository. This repository is where we will place the method for our crud operation.

using Dapper;
 using Microsoft.Extensions.Configuration;
 using System;
 using System.Collections.Generic;
 using System.Data;
 using System.Data.SqlClient;
 using System.Linq;
 using System.Threading.Tasks;
 namespace DPCoreApi.Services
 {
     public class DapperRepository : IDapperRepository
     {
         private readonly IConfiguration _configuration;
     public DapperRepository(IConfiguration configuration)
     {
         _configuration = configuration;
     }
     public List<T> GetAll<T>(string query, DynamicParameters sp_params, CommandType commandType = CommandType.StoredProcedure)
     {
         using IDbConnection db = new SqlConnection(_configuration.GetConnectionString("default"));
         return db.Query<T>(query, sp_params, commandType: commandType).ToList();
     }
     public T execute_sp<T>(string query, DynamicParameters sp_params, CommandType commandType = CommandType.StoredProcedure)
     {
         T result;
         using (IDbConnection dbConnection = new SqlConnection(_configuration.GetConnectionString("default"))) {
             if (dbConnection.State == ConnectionState.Closed)
                 dbConnection.Open();
             using var transaction = dbConnection.BeginTransaction();
             try
             {
                 dbConnection.Query<T>(query, sp_params, commandType: commandType, transaction: transaction);
                 result = sp_params.Get<T>("retVal"); //get output parameter value
                 transaction.Commit();
             }
             catch (Exception ex)
             {
                 transaction.Rollback();
                 throw ex;
             }
         };
             return result;
      }
   }
 }

IV. Create model properties

Now, we need a Model to class to Map output data from the Dapper query. This model represents our table properties. I place my member model inside the Models folder.

Dapper model properties
public int Id { get; set; }
public string Name { get; set; }
public string Contact { get; set; }
public string Address { get; set; }

V. Create an API Controller

Now, all we need to do is to create an API Controller to output the repository method that we created. To do that, add a new API Controller.

  1. Right click on the controller folder. Select add then choose Controller.
Add new API Controller for crud operation

2. A new window will popup choose an Empty API Controller. See the image below.

Empty ASP NET Core Web API

3. Name your API controller as CRUDController. Copy and paste the code below inside your controller. This snippet contains the CRUD operation.

using System;
 using System.Collections.Generic;
 using System.Data;
 using System.Linq;
 using System.Threading.Tasks;
 using Dapper;
 using DPCoreApi.Models;
 using DPCoreApi.Services;
 using Microsoft.AspNetCore.Http;
 using Microsoft.AspNetCore.Mvc;
 namespace DPCoreApi.Controllers
 {
     [Route("api/[controller]")]
     [ApiController]
     public class CrudController : ControllerBase
     {
         private readonly IDapperRepository _repository;
     public CrudController(IDapperRepository repository)
     {
         _repository = repository;
     }
     [HttpPost(nameof(Create))]
     public async Task<int> Create(member data)
     {
         var dp_params = new DynamicParameters();
         dp_params.Add("Id", data.Id, DbType.Int32);
         dp_params.Add("Name", data.Name, DbType.String);
         dp_params.Add("Address", data.Address, DbType.String);
         dp_params.Add("Contact", data.Contact, DbType.String);
         dp_params.Add("retVal", DbType.String,direction:ParameterDirection.Output);
         var result = await Task.FromResult(_repository.execute_sp<int>("[dbo].[sp_AddMember]"
             ,dp_params,
             commandType: CommandType.StoredProcedure));
         return result;
     }
     [HttpGet(nameof(GetMembers))]
     public async Task<List<member>> GetMembers()
     {
         var result = await Task.FromResult(_repository.GetAll<member>($"Select * from [members]", null, commandType: CommandType.Text));
         return result;
     }
     [HttpPost(nameof(Update))]
     public async Task<int> Update(member data)
     {
         var dp_params = new DynamicParameters();
         dp_params.Add("Id", data.Id, DbType.Int32);
         dp_params.Add("Name", data.Name, DbType.String);
         dp_params.Add("Address", data.Address, DbType.String);
         dp_params.Add("Contact", data.Contact, DbType.String);
         dp_params.Add("retVal", DbType.String, direction: ParameterDirection.Output);
         var result = await Task.FromResult(_repository.execute_sp<int>("[dbo].[sp_UpdateMember]"
             , dp_params,
             commandType: CommandType.StoredProcedure));
         return result;
     }
     [HttpDelete(nameof(Delete))]
     public async Task<int> Delete(int Id)
     {
         var dp_params = new DynamicParameters();
         dp_params.Add("Id", Id, DbType.Int32);
         dp_params.Add("retVal", DbType.String, direction: ParameterDirection.Output);
         var result = await Task.FromResult(_repository.execute_sp<int>("[dbo].[sp_DeleteMember]"
             , dp_params,
             commandType: CommandType.StoredProcedure));
         return result;
     }
 }
 }

By this time we are ready to test our application and see if this web application works.

Enable CORS

Before we proceed with testing here’s bonus round if you are planning to deploy an API to a Server and use it in one of your many Application, You may encounter a CORS Exception. To get rid of this we can add policy to our API application inside your Startup.cs and inside ConfigureService() method by using the following lines of code.

            services.AddCors(option => option.AddPolicy("APIPolicy", builder => {
                builder.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod();
            }));

And then, use this CORS policy inside the Configure method.

app.UseCors("APIPolicy");

VI. Test your ASP.NET Core Web API

Before you proceed with your testing, please ensure that the connection string you set inside the appsettings.json is correct and is pointing to the right database. We will use a postman application on this testing.

Once you run the application, below are the URL’s to access the method via the postman application. I have also included images of the postman requests.

UrlMethod
https://localhost:44302/api/CRUD/GetMembersGET
https://localhost:44302/api/CRUD/CreatePOST
https://localhost:44302/api/CRUD/Delete?id=3DELETE
https://localhost:44302/api/CRUD/UpdatePOST

Create

GetMembers

Get All Available records on members table

Update

Update record by Id

Delete

Delete existing record by Id

You can download the source code for this tutorial in my GitHub Account @coderbugzz.

Summary

In this tutorial, we have learned how to use Dapper in ASP NET Core Web API. We demonstrate how we can install dapper on a project using the NuGet package and execute a full crud operation. Hopefully that this simple Dapper tutorial will help you with your future projects, keep Coding!