Dapper Crud Operation in ASP .NET Core Web API

In this article we will learn Dapper Crud Operation in ASP .NET Core Web API and Test the API using Postman.

What is Dapper?

Dapper is simple object mapper for .NET and it is virtually as fast as using a raw ADO.NET data reader. An ORM is an Object Relational Mapper, Which is responsible for mapping between database and programming language. For that reason it is called King of Micro ORM.

In this article we discuss below points.

• Create Table & Stored procedure
• Create ASP.NET Core Web API
• Installing Dapper ORM
• Using Dapper Async method
• Postman Tool for testing the API

Here in this example we create a Customer Information. And create a CRUD operation using DAPPER API and test in POSTMAN.

➤ Create Table & Stored procedure

 ➥ Customer Table withe below Script.

	CREATE TABLE [dbo].[Customer](
	[CustID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NULL,
	[Age] [int] NULL,
	[Company] [varchar](150) NULL,
	[Email] [varchar](250) NULL,
	[Phone] [varchar](20) NULL,
	[Address] [varchar](500) NULL,
	[CreatedOn] [datetime] NULL,
	CONSTRAINT [PK_Customer_1] PRIMARY KEY CLUSTERED
	(
	[CustID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

view raw Customer.sql hosted with ❤ by GitHub

➥ Stored Procedure to perform the CRUD Operation. Several Action code is use to perform different operation like Insert, Update, Delete and Retrieve etc. 

	CREATE PROCEDURE [dbo].[USP_Customer](
	@ACTION CHAR(1) = NULL,
	@CUSTID INT = NULL,
	@NAME VARCHAR(250) = NULL,
	@AGE INT = NULL,
	@COMPANY VARCHAR(350) = NULL,
	@EMAIL VARCHAR(250) = NULL,
	@PHONE VARCHAR(20) = NULL,
	@ADDRESS VARCHAR(500) = NULL
	)
	AS
	BEGIN
	SET NOCOUNT ON;
	IF(@ACTION='A')
	BEGIN
	SELECT CustID,Name,Age,Company,Email,Phone,Address,CreatedOn From Customer
	END
	ELSE IF(@ACTION='D')
	BEGIN
	DELETE FROM Customer WHERE CustID=@CUSTID
	END
	ELSE IF(@ACTION='E')
	BEGIN
	IF NOT EXISTS(SELECT 1 FROM Customer WHERE CUSTID=@CUSTID)
	BEGIN
	INSERT INTO Customer(
	Name,Age,Company,Email,Phone,[Address]
	)
	VALUES (
	@NAME,@AGE,@COMPANY,@EMAIL,@PHONE,@ADDRESS
	)
	END
	ELSE
	BEGIN
	UPDATE Customer SET
	Name=@Name,Age =@Age,Company=@Company ,
	Email=@Email,Phone=@Phone,Address=@Address
	WHERE CustID=@CustID
	END
	END
	ELSE IF(@ACTION='G')
	BEGIN
	SELECT CustID,Name,Age,Company,Email,Phone,Address,CreatedOn From Customer
	WHERE CustID=@CustID
	END
	END

view raw USP_Customer.sql hosted with ❤ by GitHub

➤ Create a ASP .NET Core API Application

 ➥ Open VS ⟹ File New Project ⟹ ASP .NET Core Web Application ⟹ API as template

Fig-1

➤ Installing Dapper ORM

After creating the ASP .NET Core API application we need to install Dapper through Nuget OR you can install using below command in Nuget package manager console.

Install-Package Dapper -Version 2.0.35

Fig-2

➤ Using Dapper Async method

 ➥ Add the connection string inside appsettings.json file

	"ConnectionStrings": {
	"mySqlConnection": "Server=**********;Database=Customers;User Id=sa;password=*******"
	}

view raw appsettings.json hosted with ❤ by GitHub

 ➥ Add the Model class of Customer.cs inside the Models folder.

	public class Customer
	{
	public int CustID { get; set; }
	public string Name { get; set; }
	public int Age { get; set; }
	public string Company { get; set; }
	public string Email { get; set; }
	public string Phone { get; set; }
	public string Address { get; set; }
	public DateTime CreateOn { get; set; }
	}

view raw Customer.cs hosted with ❤ by GitHub

 ➥ We add a Repository folder and add a Interface ICustomerRepository.cs

	public interface ICustomerRepository
	{
	Task<List<Customer>> GetCustomers();
	Task<Customer> GetCustomerByID(int id);
	Task<Customer> ADDEditCustomer(Customer customer);
	Task<Customer> DeleteCustomer(int id);
	}

view raw ICustomerRepository.cs hosted with ❤ by GitHub

 ➥ Then add the CustomerRepository.cs class, ICustomerRepository.cs implement into it

	using System;
	using System.Collections.Generic;
	using System.Data;
	using System.Data.SqlClient;
	using System.Linq;
	using System.Threading.Tasks;
	using CoreAPIDapper.Models;
	using Dapper;
	using Microsoft.Extensions.Configuration;
	namespace CoreAPIDapper.Repository
	{
	public class CustomerRepository : ICustomerRepository
	{
	private readonly IConfiguration _config;
	public CustomerRepository(IConfiguration config)
	{
	_config = config;
	}
	public IDbConnection connection {
	get {
	return new SqlConnection(_config.GetConnectionString("mySqlConnection"));
	}
	}
	public async Task<List<Customer>> GetCustomers()
	{
	try
	{
	using (IDbConnection con = connection)
	{
	string Query = "USP_Customer";
	con.Open();
	DynamicParameters param = new DynamicParameters();
	param.Add("@ACTION", "A");
	var result = await con.QueryAsync<Customer>(Query, param, commandType: CommandType.StoredProcedure);
	return result.ToList();
	}
	}
	catch (Exception ex)
	{
	throw ex;
	}
	}
	public async Task<Customer> DeleteCustomer(int id)
	{
	try
	{
	using (IDbConnection con = connection)
	{
	string Query = "USP_Customer";
	con.Open();
	DynamicParameters param = new DynamicParameters();
	param.Add("@ACTION", "D");
	param.Add("@CUSTID", id);
	var result = await con.QueryAsync<Customer>(Query, param, commandType: CommandType.StoredProcedure);
	return result.FirstOrDefault();
	}
	}
	catch (Exception ex)
	{
	throw ex;
	}
	}
	public async Task<Customer> ADDEditCustomer(Customer customer)
	{
	try
	{
	using (IDbConnection con = connection)
	{
	string sQuery = "USP_Customer";
	con.Open();
	DynamicParameters param = new DynamicParameters();
	param.Add("@ACTION", "E");
	param.Add("@CUSTID", customer.CustID);
	param.Add("@NAME", customer.Name);
	param.Add("@AGE", customer.Age);
	param.Add("@COMPANY", customer.Company);
	param.Add("@EMAIL", customer.Email);
	param.Add("@PHONE", customer.Phone);
	param.Add("@ADDRESS", customer.Address);
	var result = await con.QueryAsync<Customer>(sQuery, param, commandType: CommandType.StoredProcedure);
	return result.FirstOrDefault();
	}
	}
	catch (Exception ex)
	{
	throw ex;
	}
	}
	public async Task<Customer> GetCustomerByID(int id)
	{
	try
	{
	using (IDbConnection con = connection)
	{
	string sQuery = "USP_Customer";
	con.Open();
	DynamicParameters param = new DynamicParameters();
	param.Add("@ACTION", "G");
	param.Add("@CUSTID", id);
	var result = await con.QueryAsync<Customer>(sQuery, param, commandType: CommandType.StoredProcedure);
	return result.FirstOrDefault();
	}
	}
	catch (Exception ex)
	{
	throw ex;
	}
	}
	}
	}

view raw CustomerRepository.cs hosted with ❤ by GitHub

Code Snippet Explanation
 ⇢ In CustomerRepository we call the stored procedure using Dapper ORM.
 ⇢ using Dapper namespace is use to add the dapper class into it.
 ⇢ using Microsoft.Extensions.Configuration is responsible to inject the interface into controller.
 ⇢ Other details are shared in below image diagram [Fig-3]

Fig-3 Here we use Action Code "A" to fetch data from stored procedure

Like this we did the method ADDEditCustomer(),DeleteCustomer() and GetCustomerByID() in CustomerRepository.cs. In ADDEditCustomer() we manage in procedure level if the customer data is not in the table it insert else it update.

➥ Add the Interface and class into the ConfigureServices inside the startup.cs class. Adding services to this container will make them available for dependency injection.  That means we can inject those services anywhere in our application. 

	public void ConfigureServices(IServiceCollection services)
	{
	services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
	services.AddTransient<ICustomerRepository, CustomerRepository>();
	}

view raw Startup.cs hosted with ❤ by GitHub

➥ Now create a Controller named as "CustomerController" and here inject ICustomerRepository interface like below and here we define the GET,POST, DELETE method to add/edit/delete customer.

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Threading.Tasks;
	using Microsoft.AspNetCore.Mvc;
	using CoreAPIDapper.Repository;
	using CoreAPIDapper.Models;
	namespace CoreAPIDapper.Controllers
	{
	[Route("api/[controller]/[action]")]
	[ApiController]
	public class CustomerController : Controller
	{
	private readonly ICustomerRepository _customerRep;
	public CustomerController(ICustomerRepository customerRepository)
	{
	_customerRep = customerRepository;
	}
	// GET api/values
	[HttpGet]
	public async Task<List<Customer>> GetCustomers()
	{
	return await _customerRep.GetCustomers();
	}
	[HttpGet("{id}")]
	public async Task<ActionResult<Customer>> GetByCustomerByID(int id)
	{
	return await _customerRep.GetCustomerByID(id);
	}
	[HttpPost]
	public async Task<ActionResult<Customer>> ADDEditCustomer([FromBody]Customer customer)
	{
	if (customer == null || !ModelState.IsValid)
	{
	return BadRequest("Invalid State");
	}
	return await _customerRep.ADDEditCustomer(customer);
	}
	[HttpDelete("{id}")]
	public async Task<ActionResult<Customer>> DeleteById(int id)
	{
	return await _customerRep.DeleteCustomer(id);
	}
	}
	}

view raw CustomerController.cs hosted with ❤ by GitHub

Code Snippet Explanation:

⇢ [ApiController] filter attribute is use to specify that the controller is a Web API Controller.
⇢ Here we inject constructor injection of ICustomerRepository.
⇢ GetCustomers() is call to the interface and associate method is called then using Dapper it call to the procedure and give back to us the result.
⇢ GetByCustomerByID() is use to return of one customer into the customers list.
⇢ AddEditCustomer() is used for to Insert and update the Customers.
⇢ DeleteById() is used for to delete particular customer by Id.

➤ Postman Tool for testing the API

Let's open Postman tool to test the API. Run the DapperAPI application mind that we have set the route as  [Route("api/[controller]/[action]")] so our URL is http://localhost:59003/api/controller/action.

 ➥ Add Customer Using Postman

Using the below API method and the parameters you can add the customer information.

Fig-4

You can see the data is inserted into the table as expected.

Fig-5

➥ Edit Customer Using Postman

As we are using same method AddEditCustomer() so just make change in parameter value and check.

Fig-6

After submitting you can see the changed customer information.

Fig-7

 ➥ Retrieve Customer Using Postman

To retrieve the customer we need to call the Get method using Postman like below;
http://localhost:59003/api/Customer/GetCustomers

Fig-8

➥ Retrieve Customer By ID Using Postman

Let's add manually another customer in database so we need to call the Get method by Id using Postman like below;
http://localhost:59003/api/Customer/GetByCustomerByID/3

Fig-9

➥ Delete Customer By ID Using Postman

Like retrieve by customer Id we also delete the data by Customer by ID to follow this method.

http://localhost:59003/api/Customer/DeleteById/3

Fig-10

When you refresh the table data you can find that the Customer ID 3 is deleted.

Fig-11

So far this article we see how to use Dapper in ASP .NET Core Web API and done the CRUD operation.

</> Source Code in Github.com/CoreProgramm/

    Summary

 In this article we discussed Dapper Crud Operation in ASP .NET Core Web API. If have any question related to this topic then give your feedback.