Using Dapper In ASP.NET Core Web API

Introduction

First of all, let's have a clear look at Dapper and how it will be useful in our Core API. I think most of us know what Dapper is, but this article is for those who don't know about Dapper.

Dapper

Dapper is a simple Object Mapper and is nothing but Object-relational mapping (ORM) and is responsible for mapping between database and programming language and also it owns the title of King of Micro ORM in terms of speed. It is virtually as fast as using a raw ADO.NET data reader and also Entity Framework.

How Does Dapper Work?

Creates an IDbConnection Object.
Write a query to perform CRUD Operations
Passes a Query as Parameter in the Execute Method.

Performance

Dapper is the Second Fastest ORM when compared with all Object-relational mappings.

Step 1

Create an ASP.NET Core project

Click on Next Button.

Step 2

Add a Project Name and Solution name to save the project to whichever location you want.

Click on Create Button.

Step 3

Choose the Appropriate version of API

Click on Create Button a sample project with basic setup will be created. Now let's dive into our project.

Create an empty API Controller with any name (Home)

Now Create the Services folder and add one Interface(IDapper.cs) and one Class(Dapperr.cs) to it.

Now add the ASP.NET Core Libraries to set up the database and also Dapper library into our project from the Nuget Package Manager.

Add the below code in IDapper.cs interface to where to perform the Crud Operations in our project.

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
namespace Dapper_ORM.Services
{
public interface IDapper : IDisposable
{
DbConnection GetDbconnection();
T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
}
}

Add the code in Dapperr.cs File where the actual method implementation takes place in it for each and every method which we already declared in Interface

using Dapper;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
namespace Dapper_ORM.Services
{
public class Dapperr : IDapper
{
private readonly IConfiguration _config;
private string Connectionstring = "DefaultConnection";
public Dapperr(IConfiguration config)
{
_config = config;
}
public void Dispose()
{
}
public int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
throw new NotImplementedException();
}
public T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.Text)
{
using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
return db.Query<T>(sp, parms, commandType: commandType).FirstOrDefault();
}
public List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
return db.Query<T>(sp, parms, commandType: commandType).ToList();
}
public DbConnection GetDbconnection()
{
return new SqlConnection(_config.GetConnectionString(Connectionstring));
}
public T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
T result;
using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
try
{
if (db.State == ConnectionState.Closed)
db.Open();
using var tran = db.BeginTransaction();
try
{
result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (db.State == ConnectionState.Open)
db.Close();
}
return result;
}
public T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
T result;
using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
try
{
if (db.State == ConnectionState.Closed)
db.Open();
using var tran = db.BeginTransaction();
try
{
result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (db.State == ConnectionState.Open)
db.Close();
}
return result;
}
}
}

Create a DataContext Folder and Add AppContext Class in it.

Add the Code in AppContext.cs file to connect with the DbContext and also to make a connection with Database.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace Dapper_ORM.DataContext
{
public class AppContext : DbContext
{
public AppContext() { }
public AppContext(DbContextOptions<AppContext> options) : base(options) { }
}
}

Add the Connection String in the appsettings.json File:

{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DefaultConnection": "YOUR CONNECTION STRING"
}
}

Make the Connection setup in the Startup.cs file.

Startup.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;
using Dapper_ORM.Services;
namespace Dapper_ORM
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
services.AddDbContext<DataContext.AppContext>(options =>
options.UseSqlServer(
Configuration.GetConnectionString("DefaultConnection")));
//Register dapper in scope
services.AddScoped<IDapper, Dapperr>();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}

Add the Parameters.cs File which acts as an object mapping with our existing SQL Database.

Parameters.cs File

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace Dapper_ORM.Models
{
public class Parameters
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
}

Create a table in SQL DB to access the table data using Dapper from this Core API, so I have created a table name with dummy in database.

Adding the CRUD Methods in Home Controller.

HomeController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Dapper_ORM.Models;
using Dapper_ORM.Services;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
namespace Dapper_ORM.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class HomeController : ControllerBase
{
private readonly IDapper _dapper;
public HomeController(IDapper dapper)
{
_dapper = dapper;
}
[HttpPost(nameof(Create))]
public async Task<int> Create(Parameters data)
{
var dbparams = new DynamicParameters();
dbparams.Add("Id", data.Id, DbType.Int32);
var result = await Task.FromResult(_dapper.Insert<int>("[dbo].[SP_Add_Article]"
, dbparams,
commandType: CommandType.StoredProcedure));
return result;
}
[HttpGet(nameof(GetById))]
public async Task<Parameters> GetById(int Id)
{
var result = await Task.FromResult(_dapper.Get<Parameters>($"Select * from [Dummy] where Id = {Id}", null, commandType: CommandType.Text));
return result;
}
[HttpDelete(nameof(Delete))]
public async Task<int> Delete(int Id)
{
var result = await Task.FromResult(_dapper.Execute($"Delete [Dummy] Where Id = {Id}", null, commandType: CommandType.Text));
return result;
}
[HttpGet(nameof(Count))]
public Task<int> Count(int num)
{
var totalcount = Task.FromResult(_dapper.Get<int>($"select COUNT(*) from [Dummy] WHERE Age like '%{num}%'", null,
commandType: CommandType.Text));
return totalcount;
}
[HttpPatch(nameof(Update))]
public Task<int> Update(Parameters data)
{
var dbPara = new DynamicParameters();
dbPara.Add("Id", data.Id);
dbPara.Add("Name", data.Name, DbType.String);
var updateArticle = Task.FromResult(_dapper.Update<int>("[dbo].[SP_Update_Article]",
dbPara,
commandType: CommandType.StoredProcedure));
return updateArticle;
}
}
}

Now we can run the application and call the respective methods to fetch the data or to add the data to the existing database using Dapper.

Output

Github Link

I hope this article helps you.

Keep learning ....!

18 Comments

Join the conversation! Your thoughts help the community grow.

Aseman Za4y ago
Why you use dapper and EF both of them?why you need EF?
Alpesh Viradiya4y ago
Hello Jay, thank you for the article. I am fresher. I need the same article in the MVC web application.
Shenbagapandiyan P5y ago
Jay Krishna Reddy I Couldn't understand the need for AppContext. Please explain why we have registered here?
fausto luis5y ago
If I'm wrong, correct me, please. Why didn't you use a generic asynchronous repository? Why Task.FromResult, it would be enough to return await in the controller's methods. By the way, why the use of entity framework? To communicate with the database? Seriously? Another thing, why "finally" to close the connection? Isn't the "using (...) instruction supposed to have that effect? Other than that, I consider the article well written and educational.
Hamid Khan5y ago
How we can add SP in Dapper
Hamid Khan5y ago
Nice explanation
Rakesh Pathak5y ago
Return await dbContext.ExecuteAsync<Response>("highlights_sp_update", param: parameters, commandTimeout: 180, commandType: CommandType.StoredProcedure); I am getting error "Error CS1061 'IDbConnection' does not contain a definition for 'ExecuteAsync' and no accessible extension method 'ExecuteAsync' accepting a first argument of type 'IDbConnection' could be found (are you missing a using directive or an assembly reference?) "
mikepowertech mikepowertech5y ago
Thank you for the project. At the same time there is error: localhost page . And I checked my connection string is OK. Could it be Stored Procedures issue? Like here [dbo].[SP_Add_Article]" ? Hope to get more information on how to proper run your project, thank you!
Steven Rieger5y ago
@Jay, thank you for the article. I pulled the repo from git and updated the connection string. When I run it, I get the localhost page can not be found. Any thoughts as to why?
- Jay Krishna Reddy5y ago
  Make sure to have proper connection string and build properties.
Shivaji Kakad6y ago
@Jay, This article is really informative. I have just one concern here that instead of Registering dapper in Scoped, shouldn't we do it as Singleton? if not then please share your thoughts.
- Gerard Jaryczewski5y ago
  Https://dapper-tutorial.net/knowledge-base/42937942/dapper-with--net-core---injected-sqlconnection-lifetime-scope#answer-0
Muhammad Bilal6y ago
Unable to connect with DB Configuration returning null please help me regarding that
- Jay Krishna Reddy6y ago
  Please make sure to attach a proper connection string
sreenivasa k6y ago
Thank you Jay Krishna Reddy.
Jay Krishna Reddy6y ago
Thanks @Amit Mohanty
Amit Mohanty6y ago
Nice article.
Sri6y ago
Keep up the good work.