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?
  1. Creates an IDbConnection Object.
  2. Write a query to perform CRUD Operations
  3. 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.
  1. using Dapper;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. using System.Data.Common;
  6. using System.Linq;
  7. using System.Threading.Tasks;
  9. namespace Dapper_ORM.Services
  10. {
  11. public interface IDapper : IDisposable
  12. {
  13. DbConnection GetDbconnection();
  14. T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
  15. List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
  16. int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
  17. T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
  18. T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
  19. }
  20. }
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
  1. using Dapper;
  2. using Microsoft.Extensions.Configuration;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Data;
  6. using System.Data.Common;
  7. using System.Data.SqlClient;
  8. using System.Linq;
  9. using System.Threading.Tasks;
  11. namespace Dapper_ORM.Services
  12. {
  13. public class Dapperr : IDapper
  14. {
  15. private readonly IConfiguration _config;
  16. private string Connectionstring = "DefaultConnection";
  18. public Dapperr(IConfiguration config)
  19. {
  20. _config = config;
  21. }
  22. public void Dispose()
  23. {
  25. }
  27. public int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
  28. {
  29. throw new NotImplementedException();
  30. }
  32. public T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.Text)
  33. {
  34. using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
  35. return db.Query<T>(sp, parms, commandType: commandType).FirstOrDefault();
  36. }
  38. public List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
  39. {
  40. using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
  41. return db.Query<T>(sp, parms, commandType: commandType).ToList();
  42. }
  44. public DbConnection GetDbconnection()
  45. {
  46. return new SqlConnection(_config.GetConnectionString(Connectionstring));
  47. }
  49. public T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
  50. {
  51. T result;
  52. using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
  53. try
  54. {
  55. if (db.State == ConnectionState.Closed)
  56. db.Open();
  58. using var tran = db.BeginTransaction();
  59. try
  60. {
  61. result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
  62. tran.Commit();
  63. }
  64. catch (Exception ex)
  65. {
  66. tran.Rollback();
  67. throw ex;
  68. }
  69. }
  70. catch (Exception ex)
  71. {
  72. throw ex;
  73. }
  74. finally
  75. {
  76. if (db.State == ConnectionState.Open)
  77. db.Close();
  78. }
  80. return result;
  81. }
  83. public T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
  84. {
  85. T result;
  86. using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
  87. try
  88. {
  89. if (db.State == ConnectionState.Closed)
  90. db.Open();
  92. using var tran = db.BeginTransaction();
  93. try
  94. {
  95. result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
  96. tran.Commit();
  97. }
  98. catch (Exception ex)
  99. {
  100. tran.Rollback();
  101. throw ex;
  102. }
  103. }
  104. catch (Exception ex)
  105. {
  106. throw ex;
  107. }
  108. finally
  109. {
  110. if (db.State == ConnectionState.Open)
  111. db.Close();
  112. }
  114. return result;
  115. }
  116. }
  117. }
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.
  1. using Microsoft.EntityFrameworkCore;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using System.Threading.Tasks;
  7. namespace Dapper_ORM.DataContext
  8. {
  9. public class AppContext : DbContext
  10. {
  11. public AppContext() { }
  12. public AppContext(DbContextOptions<AppContext> options) : base(options) { }
  13. }
  14. }
Add the Connection String in the appsettings.json File:
  1. {
  2. "Logging": {
  3. "LogLevel": {
  4. "Default": "Information",
  5. "Microsoft": "Warning",
  6. "Microsoft.Hosting.Lifetime": "Information"
  7. }
  8. },
  9. "AllowedHosts": "*",
  10. "ConnectionStrings": {
  11. "DefaultConnection": "YOUR CONNECTION STRING"
  12. }
  13. }
Make the Connection setup in the Startup.cs file.
Startup.cs
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Threading.Tasks;
  5. using Microsoft.AspNetCore.Builder;
  6. using Microsoft.AspNetCore.Hosting;
  7. using Microsoft.AspNetCore.HttpsPolicy;
  8. using Microsoft.AspNetCore.Mvc;
  9. using Microsoft.Extensions.Configuration;
  10. using Microsoft.Extensions.DependencyInjection;
  11. using Microsoft.Extensions.Hosting;
  12. using Microsoft.Extensions.Logging;
  13. using Microsoft.EntityFrameworkCore;
  14. using Dapper_ORM.Services;
  16. namespace Dapper_ORM
  17. {
  18. public class Startup
  19. {
  20. public Startup(IConfiguration configuration)
  21. {
  22. Configuration = configuration;
  23. }
  25. public IConfiguration Configuration { get; }
  27. // This method gets called by the runtime. Use this method to add services to the container.
  28. public void ConfigureServices(IServiceCollection services)
  29. {
  30. services.AddControllers();
  31. services.AddDbContext<DataContext.AppContext>(options =>
  32. options.UseSqlServer(
  33. Configuration.GetConnectionString("DefaultConnection")));
  34. //Register dapper in scope
  35. services.AddScoped<IDapper, Dapperr>();
  36. }
  38. // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
  39. public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
  40. {
  41. if (env.IsDevelopment())
  42. {
  43. app.UseDeveloperExceptionPage();
  44. }
  46. app.UseHttpsRedirection();
  48. app.UseRouting();
  50. app.UseAuthorization();
  52. app.UseEndpoints(endpoints =>
  53. {
  54. endpoints.MapControllers();
  55. });
  56. }
  57. }
  58. }
Add the Parameters.cs File which acts as an object mapping with our existing SQL Database.
Parameters.cs File
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Threading.Tasks;
  6. namespace Dapper_ORM.Models
  7. {
  8. public class Parameters
  9. {
  10. public int Id { get; set; }
  11. public string Name { get; set; }
  12. public int Age { get; set; }
  13. }
  14. }
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
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Linq;
  5. using System.Threading.Tasks;
  6. using Dapper;
  7. using Dapper_ORM.Models;
  8. using Dapper_ORM.Services;
  9. using Microsoft.AspNetCore.Http;
  10. using Microsoft.AspNetCore.Mvc;
  12. namespace Dapper_ORM.Controllers
  13. {
  14. [Route("api/[controller]")]
  15. [ApiController]
  16. public class HomeController : ControllerBase
  17. {
  18. private readonly IDapper _dapper;
  19. public HomeController(IDapper dapper)
  20. {
  21. _dapper = dapper;
  22. }
  23. [HttpPost(nameof(Create))]
  24. public async Task<int> Create(Parameters data)
  25. {
  26. var dbparams = new DynamicParameters();
  27. dbparams.Add("Id", data.Id, DbType.Int32);
  28. var result = await Task.FromResult(_dapper.Insert<int>("[dbo].[SP_Add_Article]"
  29. , dbparams,
  30. commandType: CommandType.StoredProcedure));
  31. return result;
  32. }
  33. [HttpGet(nameof(GetById))]
  34. public async Task<Parameters> GetById(int Id)
  35. {
  36. var result = await Task.FromResult(_dapper.Get<Parameters>($"Select * from [Dummy] where Id = {Id}", null, commandType: CommandType.Text));
  37. return result;
  38. }
  39. [HttpDelete(nameof(Delete))]
  40. public async Task<int> Delete(int Id)
  41. {
  42. var result = await Task.FromResult(_dapper.Execute($"Delete [Dummy] Where Id = {Id}", null, commandType: CommandType.Text));
  43. return result;
  44. }
  45. [HttpGet(nameof(Count))]
  46. public Task<int> Count(int num)
  47. {
  48. var totalcount = Task.FromResult(_dapper.Get<int>($"select COUNT(*) from [Dummy] WHERE Age like '%{num}%'", null,
  49. commandType: CommandType.Text));
  50. return totalcount;
  51. }
  52. [HttpPatch(nameof(Update))]
  53. public Task<int> Update(Parameters data)
  54. {
  55. var dbPara = new DynamicParameters();
  56. dbPara.Add("Id", data.Id);
  57. dbPara.Add("Name", data.Name, DbType.String);
  59. var updateArticle = Task.FromResult(_dapper.Update<int>("[dbo].[SP_Update_Article]",
  60. dbPara,
  61. commandType: CommandType.StoredProcedure));
  62. return updateArticle;
  63. }
  64. }
  65. }
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 ....!