Dapper Micro-ORM in .NET Core with examples

Today in this article we will see how to use Dapper a very lightweight mini ORM framework.

Dapper provides a simple and easy framework to map your domain model and entities to a traditional relational database like SQL, MYSQL, etc.

Today in this article, we will cover below aspects,

Benefits of Dapper

Below are a few characteristics and benefits of Dapper:

• Dapper provides Object-oriented Types and databases

• Dapper is lightweight and highly performative.

• Provides the option to execute Stored procedures from Code.

• Dapper decouples the Database layer.

• Easy to set up with fewer lines of code.

• Provides options for static/dynamic object binding

• Easy handling of SQL queries.

• Multiple SQL execution query support

Create an ASP.NET Core API

Create an ASP.NET Core API using ASP.NET Core 3.1 or .NET 6 framework.

Add Dapper Nuget package

Please add the Dapper NuGet package,

Using the NuGet Package Manager Console,

PM> Install-Package Dapper

Or

using Nuget Manager

Using Dapper with Database First approach

As we understood, Dapper supports only querying and updating through raw SQL and only works with the Database first approach.

So we shall be using the existing Database where we already have an existing Database called ‘Master‘ and a table name called EmployeeDB.

SQL Database Schema

SQL Connection Configuration

Below is the SQL DB connection configuration defined in appsettings.json.

The connection string could also be stored in secured storage or secured environment variable or Database or any Config Server if available.

We shall be using the Dependency Injection technique using the IConfiguration or IOption interface to read configuration details.

{
  "SQLConnectionSettings": {
  "ConnectionString": 
   "Server=localhost\\SQLEXPRESS;Database=master;Trusted_Connection=True;",
  }
}

Model Class

Below is the EmployeeDb model class created manually representing the database schema table.

public partial class EmployeeDb
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Address { get; set; }
        public string ZipCode { get; set; }
        public string State { get; set; }
        public string Country { get; set; }
        public string Id { get; set; }
    }

Please note that the dapper doesn’t support scaffolding but might support it through its extension if any exists.

You can Scaffolding SQL Database using EFCore Scaffold-DbContext command and reuse those domain model in your Dapper projects.

Example:

Scaffold-DbContext “Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables “EmployeeDB” -ContextDir Context -Context EmployeeContext

Performing CRUD operation using Dapper

Before starting basic CRUD operation, please add the below namespace to your controller,

using Dapper; 

Missing the above statement you will get the below error

 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'Query' and no extension method 'Query' accepting a first argument of type 'System.Data.SqlClient.SqlConnection' could be found 

Dapper Contrib extends the IDbConnection interface with additional CRUD methods like Get and GetAll or Delete and DeleteAll etc.

Please add the below namespace to use extension methods,

using Dapper.Contrib.Extensions;

For simplicity, I have executed the below dapper query within the controller.

However one can use the Repository pattern to extend the support.

Reference : Entity Framework Repository Implementation in ASP.NET Core

Dapper – GET Command example

Below is a sample Dapper – GET Command,

public class EmployeeDetailsController : ControllerBase
    {
        private readonly string _connectionString;
        public EmployeeDetailsController(IConfiguration configuration)
        {
            _connectionString = 
             configuration.GetConnectionString("EmployeeDB");
        }
       
        // GET: api/employee
        [HttpGet]
        public async Task<IActionResult> OnGetAsync()
        {
            var employees = new List<EmployeeDb>();
            using (IDbConnection db = new SqlConnection(_connectionString))
            {
                employees = db.Query<EmployeeDb>("Select * From 
                EmployeeDb").ToList();
            }
            return Ok(employees);
        }
    }

Once executed you shall see the result below,

As discussed above, you can use the above logic within the Repository pattern.

Using Dapper to perform Insert

POC example for Insert Command using Dapper.

      [HttpPost]
        public async Task<ActionResult> Post([FromBody] EmployeeDB empDb)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    using (IDbConnection db = new SqlConnection(_connectionString))
                    {
                        db.Insert(empDb);
                    }
                }
            }
            catch (Exception)
            {
                return StatusCode(StatusCodes.Status500InternalServerError, "Internal Server Error");
            }
            return Ok(Task.CompletedTask);
        }

Update and Delete commands using Dapper

Using the same above code Update or Delete commands can also be designed,

using (IDbConnection db = new SqlConnection(_connectionString))
                    {
                        db.Update(empDb);
                    }

Delete operation

using (IDbConnection db = new SqlConnection(_connectionString))
            {
                db.Delete(new EmployeeDB() { ID = id.ToString() });
            }

If not using any extension method then one can use a generic method Execute to invoke all CRUD operations.

Dapper – CRUD Operation using Execute

One can easily perform all CRUD operations using generic Execute methods as below,

INSERT using Execute,

var employees = new List<EmployeeDB>();
            using (IDbConnection connection = new SqlConnection(_connectionString))
            {
                 var updatedRows = connection.Execute("INSERT INTO [dbo].[EmployeeDb] 
                    (FirstName, LastName) VALUES (@FirstName, @LastName);", employees);
            }

UPDATE using Execute,

using (IDbConnection connection = new SqlConnection(_connectionString))
            {
                var updatedRows = connection.Execute("UPDATE [dbo].[EmployeeDb] SET FirstName = @FirstName, LastName = @LastName WHERE Id = @Id;",
                   new EmployeeDB()
                   {
                       FirstName = "NewFirstName",
                       ID = "13213",
                   });
            }

DELETE using Execute,

using (var connection = new SqlConnection(_connectionString))
            {
                var updatedRows = connection.Execute("DELETE FROM [dbo].[EmployeeDb] WHERE Id = @Id;", new { Id = 10045 });
            }

Disadvantages of Dapper

Apart from multiple benefits, Dapper has a few cons,

• Dapper supports only querying and updating through raw SQL.

• Does not support the scaffolding of model classes to match database tables, you may need to use an extension plugin if supporting.

• Only works with the Database first approach. It doesn’t support code-first development.

With this basic understanding, if interested, you can perform the complex database operation as required.

That’s All, this was very much the basics that we covered today.

Reference:

• Getting Started with EFCore in ASP.NET Core

• Entity Framework Repository Implementation in ASP.NET Core

Summary

Today we understood how to use Dapper using the Database First approach. If you are looking for an alternative to EFCore for whatsoever reason then Dapper proves to be a better option due to its simple, lightweight, and highly performative interface. It lets you perform all basic and complex SQL operations in an object-oriented way providing multiple benefits as discussed above.

---

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

---