Data Access with Dapper: A Lightweight ORM for .NET Apps

Introduction

In this blog article, we'll cover how to efficiently access data using Dapper, a lightweight ORM for .NET applications. We'll discuss its key features and compare it with Entity Framework along the way.

Data access is a crucial aspect of any application. The selection of the best tool for the job will impact performance, maintainability, and development ease. Dapper is a lightweight Object Relational Mapper (ORM) for .NET that will compete with standard ORMs like Entity Framework. This article will introduce Dapper, compare it with Entity Framework, and demonstrate real-world examples with performance benchmarks.

For those interested in learning more about .NET development, check out our .NET Development blogs. Stay updated with the latest insights and best practices!

Introduction to Dapper

Dapper is a micro-ORM developed by the team at Stack Exchange. Unlike full-fledged ORMs like Entity Framework, Dapper focuses on being simple and performant. It does this by providing a straightforward API for executing SQL queries and mapping results to strongly-typed objects.

Key Features of Dapper

Lightweight and Fast: Dapper is designed to be minimalistic and efficient, with minimal overhead.
Simple API: The API is intuitive and easy to use, allowing developers to execute SQL queries directly and map results to objects.
Flexibility: It allows you to write raw SQL queries, giving you full control over your database interactions.
Extension Methods: Dapper extends the IDbConnection interface, making it easy to integrate with existing ADO.NET code.

To get started with Dapper, you need to install the Dapper package from NuGet. You can do this using the Package Manager Console:

Install-Package Dapper

Comparing Dapper with Entity Framework

Entity Framework (EF) is a popular ORM for .NET that provides a high level of abstraction over database interactions. It uses a model-first or code-first approach to generate database schemas and manage data access.

Key Differences:

Performance: Dapper is significantly faster than Entity Framework because it generates minimal overhead. EF, on the other hand, offers rich features but at the cost of performance.
Complexity: EF provides a higher level of abstraction and includes features like change tracking, lazy loading, and navigation properties. Dapper is more lightweight and requires you to write SQL queries manually.
Flexibility: Dapper offers more control and flexibility as it allows direct SQL execution. EF abstracts much of the SQL away, which can be a limitation in some scenarios.
Learning Curve: EF has a steeper learning curve due to its rich feature set. Dapper is easier to learn and use for developers familiar with SQL.

Real-world Examples

Let's explore some real-world examples to see how Dapper can be used for common data access tasks.

Example 1: Basic CRUD Operations

First, we need to set up a database connection. Assume we have a Product table in our database.

CREATE TABLE Product (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Price DECIMAL(18, 2)
);

Now, let's perform basic CRUD operations using Dapper.

Create:

using System.Data.SqlClient;
using Dapper;

string connectionString = "your_connection_string";
using (var connection = new SqlConnection(connectionString))
{
    string insertQuery = "INSERT INTO Product (Name, Price) VALUES (@Name, @Price)";
    var result = connection.Execute(insertQuery, new { Name = "Laptop", Price = 999.99m });
    Console.WriteLine($"{result} row(s) inserted.");
}

Read:

using (var connection = new SqlConnection(connectionString))
{
    string selectQuery = "SELECT * FROM Product WHERE Id = @Id";
    var product = connection.QuerySingleOrDefault<Product>(selectQuery, new { Id = 1 });
    Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}

Update:

using (var connection = new SqlConnection(connectionString))
{
    string updateQuery = "UPDATE Product SET Price = @Price WHERE Id = @Id";
    var result = connection.Execute(updateQuery, new { Id = 1, Price = 1099.99m });
    Console.WriteLine($"{result} row(s) updated.");
}

Delete:

using (var connection = new SqlConnection(connectionString))
{
    string deleteQuery = "DELETE FROM Product WHERE Id = @Id";
    var result = connection.Execute(deleteQuery, new { Id = 1 });
    Console.WriteLine($"{result} row(s) deleted.");
}

Example 2: Using Stored Procedures

Dapper also supports executing stored procedures.

CREATE PROCEDURE GetProductById
    @Id INT
AS
BEGIN
    SELECT * FROM Product WHERE Id = @Id
END

using (var connection = new SqlConnection(connectionString))
{
    var product = connection.QuerySingleOrDefault<Product>(
        "GetProductById",
        new { Id = 1 },
        commandType: CommandType.StoredProcedure);
    Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}

Example 3: Mapping Complex Types

Dapper can map complex types and relationships.

CREATE TABLE Category (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100)
);

ALTER TABLE Product ADD CategoryId INT;
ALTER TABLE Product ADD CONSTRAINT FK_Product_Category FOREIGN KEY (CategoryId) REFERENCES Category(Id);

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public Category Category { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

using (var connection = new SqlConnection(connectionString))
{
    string sql = @"
        SELECT p.*, c.* 
        FROM Product p 
        INNER JOIN Category c ON p.CategoryId = c.Id
        WHERE p.Id = @Id";

    var product = connection.Query<Product, Category, Product>(
        sql,
        (product, category) => {
            product.Category = category;
            return product;
        },
        new { Id = 1 },
        splitOn: "Id").FirstOrDefault();

    Console.WriteLine($"Product: {product.Name}, Price: {product.Price}, Category: {product.Category.Name}");
}

Performance Benchmarks

To illustrate the performance differences, let's compare Dapper and Entity Framework in terms of query execution time. Below are some benchmark results (in milliseconds) for retrieving 1000 records from a Product table.

The benchmark results show that Dapper performs significantly better than Entity Framework for this specific use case. While EF offers more features, Dapper's performance advantage can be crucial for high-load applications.

ORM	Query Execution Time (ms)
Dapper	15
Entity Framework	45

Conclusion

Dapper is an excellent choice for developers who need a lightweight, fast and flexible ORM for .NET applications. Its simplicity and performance make it suitable for many data access situations, whenever you want complete control over SQL queries. Though Entity Framework offers additional features, Dapper is more efficient and simpler to use compared to a lot of programs.

Adding Dapper to your .NET projects will help you optimize data access, improve performance, and maintain flexibility in your database interactions. Whether you are developing a small application or a large enterprise system, Dapper enables you to develop a maintainable data access code.