CRUD Operation With Dapper Using ASP.NET Core 7.0 MVC

Dapper is a micro-ORM (Object Relational Mapping) framework for .NET that is lightweight, fast, and easy to use. It was created by Sam Saffron of Stack Overflow fame and was designed to provide fast data access for microservices and other lightweight applications.

CRUD Operation With Dapper Using ASP.NET Core 7.0 MVC

Dapper is an extension method-based ORM that allows developers to map SQL query results to strongly typed objects. It is designed to be simple and fast and offers a high degree of flexibility, allowing developers to work with raw SQL queries or build queries using the built-in query builder.

Dapper does not offer the full set of features of larger ORM frameworks like Entity Framework, but it is a good choice for developers who need to execute complex queries quickly and efficiently. Because of its simplicity and speed, Dapper is often used in high-performance applications, such as financial trading systems or real-time data processing applications.

Create New Project Using Visual Studio 2022

Installed Required Packages

dotnet add package Microsoft.Data.SqlClient --version 5.1.1
dotnet add package Dapper.Extensions.NetCore --version 4.2.2

Add Database Connection

  "ConnectionStrings": {
    "DefaultConnectionMSSQLNoCred": "Server=DESKTOP-99Q87I2\\MSSQLSERVER2017;Database=DapperCRUD;Trusted_Connection=True;MultipleActiveResultSets=true",
    "connMSSQL": "Server=DESKTOP-99Q87I2\\MSSQLSERVER2017;Database=DapperCRUD;User ID=sa;Password=dev123456;TrustServerCertificate=True;MultipleActiveResultSets=true"
  }

Database Table Script

/****** Object:  Table [dbo].[Branch]    Script Date: 5/8/2023 1:30:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Branch](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](max) NULL,
 [Description] [nvarchar](max) NULL,
 [CreatedDate] [datetime2](7) NOT NULL,
 [UpdatedDate] [datetime2](7) NULL,
 CONSTRAINT [PK_Branch] 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

Create Dapper Context

public interface IDapperContext
    {
        IDbConnection CreateConnection();
    }

public class DapperContext: IDapperContext
    {
        private readonly IConfiguration _iConfiguration;
        private readonly string _connString;
        public DapperContext(IConfiguration iConfiguration)
        {
            _iConfiguration = iConfiguration;
            _connString = _iConfiguration.GetConnectionString("connMSSQL");
        }
        public IDbConnection CreateConnection() => new SqlConnection(_connString);
    }

Create Model

public class BaseEntity
    {
        public Int64 Id { get; set; }
        public DateTime CreatedDate { get; set; }
        public DateTime? UpdatedDate { get; set; }
    }

public class Branch: BaseEntity
    {
        public string Name { get; set; }
        public string Description { get; set; }
    }

Create Model Repository

public interface IBranchRepository
    {
        Task<IEnumerable<Branch>> GetAllAsync();
        Task<Branch> GetByIdAsync(Int64 id);
        Task Create(Branch _Branch);
        Task Update(Branch _Branch);
        Task Delete(Int64 id);
    }

Implement IRepository

public class BranchRepository : IBranchRepository
    {
        private readonly IDapperContext _context;
        public BranchRepository(IDapperContext context)
        {
            _context = context;
        }

        public async Task<IEnumerable<Branch>> GetAllAsync()
        {
            var query = "SELECT * FROM " + typeof(Branch).Name;
            using (var connection = _context.CreateConnection())
            {
                var result = await connection.QueryAsync<Branch>(query);
                return result.ToList();
            }
        }
        public async Task<Branch> GetByIdAsync(Int64 id)
        {
            var query = "SELECT * FROM " + typeof(Branch).Name + " WHERE Id = @Id";

            using (var connection = _context.CreateConnection())
            {
                var result = await connection.QuerySingleOrDefaultAsync<Branch>(query, new { id });
                return result;
            }
        }
        public async Task Create(Branch _Branch)
        {
            var query = "INSERT INTO " + typeof(Branch).Name + " (Name, Description, CreatedDate,UpdatedDate) VALUES (@Name, @Description, @CreatedDate, @UpdatedDate)";
            var parameters = new DynamicParameters();
            parameters.Add("Name", _Branch.Name, DbType.String);
            parameters.Add("Description", _Branch.Description, DbType.String);
            parameters.Add("CreatedDate", _Branch.CreatedDate, DbType.DateTime);
            parameters.Add("UpdatedDate", _Branch.UpdatedDate, DbType.DateTime);
            using (var connection = _context.CreateConnection())
            {
                await connection.ExecuteAsync(query, parameters);
            }
        }
        public async Task Update(Branch _Branch)
        {
            var query = "UPDATE " + typeof(Branch).Name + " SET Name = @Name, Description = @Description, UpdatedDate = @UpdatedDate WHERE Id = @Id";
            var parameters = new DynamicParameters();
            parameters.Add("Id", _Branch.Id, DbType.Int64);
            parameters.Add("Name", _Branch.Name, DbType.String);
            parameters.Add("Description", _Branch.Description, DbType.String);
            parameters.Add("UpdatedDate", _Branch.UpdatedDate, DbType.DateTime);
            using (var connection = _context.CreateConnection())
            {
                await connection.ExecuteAsync(query, parameters);
            }
        }
        public async Task Delete(Int64 id)
        {
            var query = "DELETE FROM " + typeof(Branch).Name + " WHERE Id = @Id";
            using (var connection = _context.CreateConnection())
            {
                await connection.ExecuteAsync(query, new { id });
            }
        }
    }

Inject Services

builder.Services.AddTransient<IDapperContext, DapperContext>();
builder.Services.AddTransient<IBranchRepository, BranchRepository>();

Complete Project

Project Output

Advantage of Dapper ORM

There are several advantages of using Dapper ORM:

1. Performance: Dapper is very fast compared to other ORMs because it uses dynamic SQL generation and lightweight object mapping.
2. Simplicity: Dapper is very easy to use and configure. It has a simple and intuitive API that requires minimal setup and configuration.
3. Flexibility: Dapper provides a lot of flexibility when it comes to mapping database results to objects. It allows you to map results to POCOs, dynamic objects, and even XML.
4. Lightweight: Dapper is a lightweight ORM that has a small footprint and does not add a lot of overhead to your application.
5. Compatibility: Dapper is compatible with many different databases, including SQL Server, MySQL, Oracle, and PostgreSQL.

Overall, Dapper is a great choice for developers who need a lightweight and flexible ORM that can provide fast performance and easy integration with a wide range of databases.

In conclusion, Dapper is a lightweight, simple, and fast ORM that can be used in place of Entity Framework Core for data access in an ASP.NET Core MVC application. In this article, we saw how to create a new ASP.NET Core MVC application, configure Dapper and the database connection, create a model class, and perform CRUD operations on the database using Dapper.

Using Dapper can offer performance benefits due to its lightweight nature and close-to-the-metal approach. However, it also means that there is less abstraction and therefore potentially more code required for CRUD operations compared to Entity Framework Core.

Ultimately, the choice between Dapper and Entity Framework Core depends on the specific requirements of your application, including performance needs, complexity, and the familiarity of the development team with each tool.

👋 My Portfolio

🚀 My Youtube Channel

💻 Github