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

Real-Time vs Persistent Notifications: Choosing Between Observer Pattern and Databases

Leveraging the Observer Design Pattern : 10 Real-World Examples in Action

Observer Design Pattern: Create a Real-Time Stock Market Notification System in C#

Boost Your Productivity - Setting Up and Customizing Windows Terminal with Git Bash and Oh My Posh