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 the 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 a 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
{
DbConnectionGetDbconnection();
TGet<T>(string sp, DynamicParametersparms,
CommandTypecommandType=CommandType.StoredProcedure);
List<T> GetAll<T>(string sp, DynamicParametersparms,
CommandTypecommandType=CommandType.StoredProcedure);
int Execute(string sp, DynamicParametersparms,
CommandTypecommandType=CommandType.StoredProcedure);
TInsert<T>(string sp, DynamicParametersparms,
CommandTypecommandType=CommandType.StoredProcedure);
TUpdate<T>(string sp, DynamicParametersparms,
CommandTypecommandType=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(IConfigurationconfig)
{
_config=config;
}
public void Dispose()
{
}
public int Execute(string sp,
DynamicParametersparms,
CommandTypecommandType=CommandType.StoredProcedure)
{
throw new NotImplementedException();
}
public TGet<T>(string sp, DynamicParametersparms,
CommandTypecommandType=CommandType.Text)
{
usingIDbConnectiondb=new SqlConnection
(_config.GetConnectionString(Connectionstring));
return db.Query<T>(sp,
parms,
commandType: commandType).FirstOrDefault();
}
public List<T> GetAll<T>(string sp, DynamicParametersparms,
CommandTypecommandType=CommandType.StoredProcedure)
{
usingIDbConnectiondb = new SqlConnection
(_config.GetConnectionString(Connectionstring));
return db.Query<T>(sp,
parms,
commandType: commandType).ToList();
}
public DbConnectionGetDbconnection()
{
return new SqlConnection
(_config.GetConnectionString(Connectionstring)
);
}
public TInsert<T>(string sp, DynamicParametersparms,
CommandTypecommandType=CommandType.StoredProcedure)
{
Tresult;
usingIDbConnectiondb = 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 (Exceptionex)
{
tran.Rollback();
throw ex;
}
}
catch (Exceptionex)
{
throw ex;
}
finally
{
if (db.State==ConnectionState.Open)
db.Close();
}
return result;
}
public TUpdate<T>(string sp, DynamicParametersparms,
CommandTypecommandType=CommandType.StoredProcedure)
{
Tresult;
usingIDbConnectiondb= 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 (Exceptionex)
{
tran.Rollback();
throw ex;
}
}
catch (Exceptionex)
{
throw ex;
}
finally
{
if (db.State==ConnectionState.Open)
db.Close(); 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 the 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(IConfigurationconfiguration)
{
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(IServiceCollectionservices)
{
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(IApplicationBuilderapp,
IWebHostEnvironmentenv)
{
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
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 the 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(IDapperdapper)
{
_dapper=dapper;
}
[HttpPost(nameof(Create))]
public async Task<int> Create(Parametersdata)
{
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 asyn cTask<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(intnum)
{
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(Parametersdata)
{
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:
Keep Learning Keep Reading!
Source: Medium
The Tech platform