Recently I got a chance to work with dapper using .net standard 2.0 So i thought to write my learning with you in this small blog post.
Firstly we will know what is the dapper ?
Dapper is micro ORM(Object Relational Mapper) framework which helps to map database directly with C# object. It is developed by StackOverflow team and released as open source.
Advantages of Dapper
1. It is a high-performance data access system
2. It is database independent
3. Same code work with all relational database.
4. Fewer lines of code.
5. Easy Handling of SQL Query.
6. Easy Handling of Stored Procedure.
7. Dapper also allows fetching multiple data based on multiple inputs.
8. Get generic result for simple or complex data type.
9. ease of use
10. It provides support for both static and dynamic object binding using transactions, stored
procedures, or bulk inserts of data.
In this post I have also used .net standard 2.0, so we have to know what is the advantage of this
Advantages of .net standard 2.0
>> Same code can be shared on all type of .net application. This will be portable. This means you can write and can use in applications that run on multiple platforms. Their purpose is to share code between applications.
Now we will see how to implement in asp.net core web api
In this demo I m going to design the simple demo web api as given below simple architecture
Step 1: Now we will Create the blank empty web api project like this
Step 2: Right click on solution explorer and create the EmpManagement.Entities layers using .net standard 2.0 like this
Step 3: Follow the same steps and create the EmpManagement.Repository
Step 4: Follow the same steps and create the EmpManagement.Repository.Interfaces
Step 5: Create the table and sp in database like this
USE [EmpManagement]
GO
/****** Object: StoredProcedure [dbo].[AddEmp] Script Date: 12/12/2019 10:14:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddEmp]
@EmpName nvarchar(50),
@EmpAddress nvarchar(50),
@EmailId nvarchar(50),
@MobileNum nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
insert into tblEmp(EmpName, EmpAddress, EmailId, MobileNum)
values(@EmpName, @EmpAddress, @EmailId, @MobileNum)
END
GO
/****** Object: StoredProcedure [dbo].[DeleteEmp] Script Date: 12/12/2019 10:14:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteEmp]
@Id int
AS
BEGIN
SET NOCOUNT ON;
DELETE from tblEmp where Id=@Id
END
GO
/****** Object: StoredProcedure [dbo].[GetAllEmps] Script Date: 12/12/2019 10:14:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAllEmps]
AS
BEGIN
SET NOCOUNT ON;
SELECT * from TBLEMP
END
GO
/****** Object: StoredProcedure [dbo].[GetEmpById] Script Date: 12/12/2019 10:14:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetEmpById]
@Id int
AS
BEGIN
SET NOCOUNT ON;
SELECT * from TBLEMP where Id=@Id
END
GO
/****** Object: StoredProcedure [dbo].[UpdateEmp] Script Date: 12/12/2019 10:14:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateEmp]
@Id int,
@EmpName nvarchar(50),
@EmpAddress nvarchar(50),
@EmailId nvarchar(50),
@MobileNum nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE tblEmp SET EmpName=@EmpName,EmpAddress=@EmpAddress,EmailId=@EmailId,MobileNum=@MobileNum where Id=@Id
END
GO
/****** Object: Table [dbo].[tblEmp] Script Date: 12/12/2019 10:14:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEmp](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpAddress] [nvarchar](50) NULL,
[EmailId] [nvarchar](50) NULL,
[MobileNum] [nvarchar](50) NULL,
CONSTRAINT [PK_tblEmp] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Step 6: Go to EmpManagement.Entities layer and Create the Emp Entities class like this
using System;
namespace DataManagement.Entities
{
public class Emp
{
public int Id { get; set; }
public string EmpName { get; set; }
public string EmpAddress { get; set; }
public string EmailId { get; set; }
public string MobileNum { get; set; }
}
}
Step 7: Go to EmpManagement.Repository.Interfaces layer and Create the generic interface like this
using System.Collections.Generic;
namespace DataManagement.Repository.Interfaces
{
public interface IEmpRepository<T> where T : class
{
IEnumerable<T> GetAllEmp();
T GetEmpById(int id);
void AddEmp(T entity);
void DeleteEmp(int id);
void UpdateEmp(T entity);
}
}
Step 8: Create the BaseRepository.cs file in EmpManagement.Repository layer like this
using System;
using System.Data;
using System.Data.SqlClient;
namespace DataManagement.Repository
{
public class BaseRepository : IDisposable
{
protected IDbConnection con;
public BaseRepository()
{
string connectionString = "Data Source=localhost;Initial Catalog=EmpManagement;Integrated Security=True";
con = new SqlConnection(connectionString);
}
public void Dispose()
{
throw new NotImplementedException();
}
}
}
Step 9: Create the BaseRepository.cs file in EmpManagement.Repository layer like this
using Dapper;
using DataManagement.Entities;
using DataManagement.Repository;
using DataManagement.Repository.Interfaces;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace EmpManagement.Repository
{
public class EmpRepository<T> : BaseRepository, IEmpRepository<Emp>
{
public void AddEmp(Emp objEmp)
{
try
{
DynamicParameters parameters = new DynamicParameters();
con.Open();
parameters.Add("EmpName", objEmp.EmpName);
parameters.Add("EmpAddress", objEmp.EmpAddress);
parameters.Add("EmailId", objEmp.EmailId);
parameters.Add("MobileNum", objEmp.MobileNum);
SqlMapper.Execute(con, "AddEmp", param: parameters, commandType: CommandType.StoredProcedure);
//For implementing commandtimeout and transaction
// SqlMapper.Execute(con, "AddEmp", param: parameters, transaction:null, commandTimeout: 100, commandType: CommandType.StoredProcedure);
// Other approach to excecute Storeprocedure in dapper
// con.Execute("AddEmp", parameters, null, null, commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw ex;
}
}
public void DeleteEmp(int Id)
{
try
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("Id", Id);
SqlMapper.Execute(con, "DeleteEmp", param: parameters, commandType: CommandType.StoredProcedure);
}
catch (Exception)
{
throw;
}
}
public IEnumerable<Emp> GetAllEmp()
{
try
{
return SqlMapper.Query<Emp>(con, "GetAllEmps", commandType: CommandType.StoredProcedure);
}
catch (Exception)
{
throw;
}
}
public Emp GetEmpById(int Id)
{
try
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("Id", Id);
return SqlMapper.Query<Emp>(con, "GetEmpById", parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception)
{
throw;
}
}
public void UpdateEmp(Emp objEmp)
{
try
{
DynamicParameters parameters = new DynamicParameters();
con.Open();
parameters.Add("EmpName", objEmp.EmpName);
parameters.Add("EmpAddress", objEmp.EmpAddress);
parameters.Add("EmailId", objEmp.EmailId);
parameters.Add("MobileNum", objEmp.MobileNum);
parameters.Add("Id", objEmp.Id);
SqlMapper.Execute(con, "UpdateEmp", param: parameters, commandType: CommandType.StoredProcedure);
}
catch (Exception)
{
throw;
}
}
}
}
Step 10 :Create the Emp Controller in WebApi Layer as given below
using DataManagement.Entities;
using DataManagement.Repository.Interfaces;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
namespace Dapper_Demo.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class EmpController : ControllerBase
{
IEmpRepository<Emp> _empRepository;
public EmpController(IEmpRepository<Emp> empRepository)
{
_empRepository = empRepository;
}
// GET: api/Emp
[HttpGet]
public IEnumerable<Emp> Get()
{
return _empRepository.GetAllEmp();
}
// GET: api/Emp/5
[HttpGet("{id}", Name = "Get")]
public Emp Get(int id)
{
return _empRepository.GetEmpById(id);
}
// POST: api/Emp
[HttpPost]
public void Post([FromBody] Emp emp)
{
_empRepository.AddEmp(emp);
}
// PUT: api/Emp/5
[HttpPut("{id}")]
public void Put(int id, [FromBody] Emp emp)
{
_empRepository.UpdateEmp(emp);
}
// DELETE: api/ApiWithActions/5
[HttpDelete("{id}")]
public void Delete(int id)
{
_empRepository.DeleteEmp(id);
}
}
}
Step 11: Register the interface in Startup.cs file like this
Step 12: Now run the application you will see the output like this
Summary:
In this post we learnt that how to create the web api core standalone layer using dapper and .net standard 2.0.
You can download the working code from this github repo
Chandradev's Blog 