Playing with dapper using asp.net core

5 minute read

Introduction
Dapper is mostly used, speedy and high performance ORM tools for Microsoft.NET platform. It is free and open source software. It is very easy to handle sql query using fewer lines of code. Here I will show you CRUD operation using inline query and stored procedure with an asp.net core web api project. So, let’s begin.

Tools and technologies used

Visual Studio 2019
SQL Server 2019
Dapper 2.0.90
ASP.NET Core Web API

Implementation

Step 1: Create database, table and stored procedure.

Create a databse in sql server name - OrderingDB
Run the following script to create table and stored procedure.

USE [OrderingDB]
GO
/****** Object:  Table [dbo].[Customers]    Script Date: 9/16/2021 11:44:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](max) NULL,
	[LastName] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
	[ContactNumber] [nvarchar](max) NULL,
	[Address] [nvarchar](max) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[spGetCustomersByEmail]    Script Date: 9/16/2021 11:44:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC dbo.[spGetCustomersByEmail] @Email = 'mahedee.hasan@gmail.com'
CREATE PROCEDURE [dbo].[spGetCustomersByEmail]
	@Email Varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	SELECT * FROM Customers WHERE Email = @Email
END
GO

Step 2: Create an API project

Create an asp.net web api project name Ordering.API

Step 3: Install nuget packages

Install the following nuget packages in Ordering.API project.

Install-Package Dapper
Install-Package Microsoft.Data.SqlClient

Step 4: Add connection string

Add connection string in appsettings.json appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },

  "ConnectionStrings": {
    "DefaultConnection": "Data Source=localhost;Initial Catalog=OrderingDB;User ID=sa;Password=yourdbpassword"
  },

  "AllowedHosts": "*"
}

Step 5: Create Entity classes

Create BaseEntity and Customer entity class in Entities folder as follows.

BaseEntity.cs

using System;

namespace Ordering.API.Entities
{
    public abstract class BaseEntity
    {
        public Int64 Id { get; set; }
    }
}

Customer.cs

using System;

namespace Ordering.API.Entities
{
    public class Customer : BaseEntity
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string ContactNumber { get; set; }
        public string Address { get; set; }
    }
}

Step 6: Create DbConnector and Repository classes

Create DbConnector class in Repositories folder to connect with database

DbConnector.cs

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;

namespace Ordering.API.Repositories
{
    public class DbConnector
    {
        private readonly IConfiguration _configuration;

        protected DbConnector(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        protected IDbConnection CreateConnection()
        {
            return new SqlConnection(_configuration.GetConnectionString("DefaultConnection"));
        }
    }
}

Create Repository interfaces and concrete class in Repositories folder

IGenericRepository.cs

using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Ordering.API.Repositories
{
    public interface IGenericRepository<T>
    {
        Task<List<T>> GetAllAsync();
        Task<T> GetByIdAsync(Int64 id);
        Task<int> CreateAsync(T entity);
        Task<int> UpdateAsync(T entity);
        Task<int> DeleteAsync(T entity);
    }
}

ICustomerRepository.cs

using Ordering.API.Entities;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Ordering.API.Repositories
{
    public interface ICustomerRepository : IGenericRepository<Customer>
    {
        Task<List<Customer>> GetAllByEmailId(string email);
    }
}

CustomerRepository.cs

using Dapper;
using Microsoft.Extensions.Configuration;
using Ordering.API.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace Ordering.API.Repositories
{
    public class CustomerRepository : DbConnector, ICustomerRepository
    {
        public CustomerRepository(IConfiguration configuration)
            : base(configuration)
        {

        }

        public async Task<int> CreateAsync(Customer entity)
        {
            try
            {
                var query = "INSERT INTO CUSTOMERS (FIRSTNAME, LASTNAME, EMAIL, CONTACTNUMBER, ADDRESS) " +
                    "VALUES (@FIRSTNAME, @LASTNAME, @EMAIL, @CONTACTNUMBER, @ADDRESS)";

                var parameters = new DynamicParameters();
                parameters.Add("FIRSTNAME", entity.FirstName);
                parameters.Add("LASTNAME", entity.LastName);
                parameters.Add("EMAIL", entity.Email);
                parameters.Add("CONTACTNUMBER", entity.ContactNumber);
                parameters.Add("ADDRESS", entity.Address);

                using (var connection = CreateConnection())
                {
                    return (await connection.ExecuteAsync(query, parameters));
                }
            }
            catch(Exception exp)
            {
                throw new Exception(exp.Message, exp);
            }
        }

        public async Task<int> DeleteAsync(Customer entity)
        {
            try
            {
                var query = "DELETE FROM CUSTOMERS WHERE Id = @Id";

                var parameters = new DynamicParameters();
                parameters.Add("Id", entity.Id, DbType.Int64);

                using (var connection = CreateConnection())
                {
                    return (await connection.ExecuteAsync(query, parameters));
                }
            }
            catch(Exception exp)
            {
                throw new Exception(exp.Message, exp);
            }
        }

        public async Task<List<Customer>> GetAllAsync()
        {
            try
            {
                var query = "SELECT * FROM CUSTOMERS";
                using (var connection = CreateConnection())
                {
                    return (await connection.QueryAsync<Customer>(query)).ToList();
                }
            }
            catch(Exception exp)
            {
                throw new Exception(exp.Message, exp);
            }
        }


        public async Task<List<Customer>> GetAllByEmailId(string email)
        {
            try
            {
                var procedure = "spGetCustomersByEmail";
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@Email", email);
                using (var connection = CreateConnection())
                {
                    return (await connection.QueryAsync<Customer>(procedure, parameters, commandType: CommandType.StoredProcedure)).ToList();
                }

            }
            catch(Exception exp)
            {
                throw new Exception(exp.Message, exp);
            }
        }

        public async Task<Customer> GetByIdAsync(Int64 id)
        {
            try
            {
                var query = "SELECT * FROM CUSTOMERS WHERE Id = @Id";
                var parameters = new DynamicParameters();
                parameters.Add("Id", id, DbType.Int32);

                using (var connection = CreateConnection())
                {
                    return (await connection.QueryFirstOrDefaultAsync<Customer>(query, parameters));
                }
            }
            catch(Exception exp)
            {
                throw new Exception(exp.Message, exp);
            }
        }

        public async Task<int> UpdateAsync(Customer entity)
        {
            try
            {
                var query = "UPDATE CUSTOMERS SET FIRSTNAME = @FIRSTNAME, LASTNAME = @LASTNAME, EMAIL = @EMAIL, CONTACTNUMBER = @CONTACTNUMBER, ADDRESS = @ADDRESS WHERE ID = @ID ";
                var parameters = new DynamicParameters();
                parameters.Add("FIRSTNAME", entity.FirstName, DbType.String);
                parameters.Add("LASTNAME", entity.LastName, DbType.String);
                parameters.Add("CONTACTNUMBER", entity.ContactNumber, DbType.String);
                parameters.Add("ADDRESS", entity.Address, DbType.String);
                parameters.Add("EMAIL", entity.Email, DbType.String);
                parameters.Add("ID", entity.Id, DbType.Int64);

                using (var connection = CreateConnection())
                {
                    return (await connection.ExecuteAsync(query, parameters));
                }
            }
            catch(Exception exp)
            {
                throw new Exception(exp.Message, exp);
            }
        }
    }
}

Step 7: Create Service class and interface

Create ICustomerService interface and CustomerService class in Services folder

ICustomerService.cs

using Ordering.API.Entities;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Ordering.API.Services
{
    public interface ICustomerService
    {
        Task<List<Customer>> GetAllCustomer();
        Task<List<Customer>> GetAllAsync();
        Task<Customer> GetByIdAsync(Int64 id);
        Task<List<Customer>> GetAllByEmailId(string email);
        Task<int> CreateAsync(Customer entity);
        Task<int> UpdateAsync(Customer entity);
        Task<int> DeleteAsync(Customer entity);

    }
}

CustomerService.cs

using Ordering.API.Entities;
using Ordering.API.Repositories;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Ordering.API.Services
{
    public class CustomerService : ICustomerService
    {
        private readonly ICustomerRepository _customerRepository;

        public CustomerService(ICustomerRepository customerRepository)
        {
            _customerRepository = customerRepository;
        }

        public async Task<int> CreateAsync(Customer entity)
        {
            return await _customerRepository.CreateAsync(entity);
        }

        public async Task<int> DeleteAsync(Customer entity)
        {
            return await _customerRepository.DeleteAsync(entity);
        }

        public async Task<List<Customer>> GetAllAsync()
        {
            return await _customerRepository.GetAllAsync();
        }

        public async Task<List<Customer>> GetAllByEmailId(string email)
        {
            return await _customerRepository.GetAllByEmailId(email);
        }

        public async Task<List<Customer>> GetAllCustomer()
        {
            return await _customerRepository.GetAllAsync();
        }

        public async Task<Customer> GetByIdAsync(Int64 id)
        {
            return await _customerRepository.GetByIdAsync(id);
        }

        public async Task<int> UpdateAsync(Customer entity)
        {
            return await _customerRepository.UpdateAsync(entity);
        }
    }
}

Step 8: Create controller class

Create CustomerController in controllers folder

CustomerService.cs

using Ordering.API.Entities;
using Ordering.API.Repositories;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Ordering.API.Services
{
    public class CustomerService : ICustomerService
    {
        private readonly ICustomerRepository _customerRepository;

        public CustomerService(ICustomerRepository customerRepository)
        {
            _customerRepository = customerRepository;
        }

        public async Task<int> CreateAsync(Customer entity)
        {
            return await _customerRepository.CreateAsync(entity);
        }

        public async Task<int> DeleteAsync(Customer entity)
        {
            return await _customerRepository.DeleteAsync(entity);
        }

        public async Task<List<Customer>> GetAllAsync()
        {
            return await _customerRepository.GetAllAsync();
        }

        public async Task<List<Customer>> GetAllByEmailId(string email)
        {
            return await _customerRepository.GetAllByEmailId(email);
        }

        public async Task<List<Customer>> GetAllCustomer()
        {
            return await _customerRepository.GetAllAsync();
        }

        public async Task<Customer> GetByIdAsync(Int64 id)
        {
            return await _customerRepository.GetByIdAsync(id);
        }

        public async Task<int> UpdateAsync(Customer entity)
        {
            return await _customerRepository.UpdateAsync(entity);
        }
    }
}

Step 9: Now run the application. You will see the output in swagger. Try CRUD operation using swagger.

Source code

Twitter Facebook LinkedIn

Mahedee Hasan

Playing with dapper using asp.net core

You May Also Enjoy

Utilizing Bulk Operations with Entity Framework and ASP.NET Core

Optimistic Concurrency Management in ASP.NET Core Application Using Raw SQL and ADO.NET

Step-by-Step Guide to Integrating Audit Trail in ASP.NET Core Using ADO.NET and Raw SQL

Optimistic Concurrency Management in ASP.NET Core Application Using Hypermedia