Overview

Dapper is a micro ORM (Object-Relational Mapper) that provides a simple way to interact with databases in .NET applications. Unlike traditional ORMs that can be heavy and complex, Dapper is lightweight and fast, offering developers fine-grained control over SQL queries while still allowing for the convenience of mapping results to objects. This makes it a compelling choice for ASP.NET Core developers who want to optimize performance and maintainability.

The primary problem Dapper addresses is the overhead and complexity of using full-fledged ORMs like Entity Framework. In scenarios where performance is critical, such as high-traffic web applications or services that require rapid data access, Dapper's speed and minimalistic design come into play. Real-world use cases include enterprise applications where direct SQL execution and mapping to objects is necessary, or applications with complex querying needs that require the flexibility of raw SQL.

Prerequisites

• C#: Basic understanding of C# programming language.
• ASP.NET Core: Familiarity with creating and running ASP.NET Core applications.
• SQL Database: Knowledge of SQL and a working understanding of relational databases.
• Dapper NuGet Package: Ability to install and manage NuGet packages in a .NET project.

Setting Up Dapper in an ASP.NET Core Project

To start using Dapper in your ASP.NET Core application, you first need to install the Dapper library via NuGet. This can be done through the Package Manager Console or by editing your project file directly. Installing Dapper allows you to leverage its features for efficient data access.

Install-Package Dapper

This command fetches the Dapper library and adds it to your project. After installation, you can begin to utilize Dapper's methods for querying databases. The next step involves setting up a database connection.

using System.Data.SqlClient;

public class DatabaseConnection
{
    private readonly string _connectionString;

    public DatabaseConnection(string connectionString)
    {
        _connectionString = connectionString;
    }

    public SqlConnection GetConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

This code defines a DatabaseConnection class that encapsulates the logic for creating a SQL connection. The constructor takes a connection string, which is essential for establishing a connection to your SQL Server database. The GetConnection method returns a new instance of SqlConnection, using the connection string stored in the private field.

Configuring Connection Strings

Connection strings are crucial for accessing your database. In an ASP.NET Core application, you typically store the connection string in the appsettings.json file.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;"
  }
}

Here, replace your_server, your_db, your_user, and your_password with the actual values for your database. This allows for easy configuration and management of connection strings.

Executing Queries with Dapper

Once Dapper is set up, the next step is to execute queries. Dapper simplifies the process of executing SQL commands and mapping the results to C# objects. The Query and Execute methods are the primary ways to interact with the database.

using Dapper;
using System.Collections.Generic;
using System.Data;

public class Repository
{
    private readonly DatabaseConnection _dbConnection;

    public Repository(DatabaseConnection dbConnection)
    {
        _dbConnection = dbConnection;
    }

    public IEnumerable<User> GetAllUsers()
    {
        using (IDbConnection db = _dbConnection.GetConnection())
        {
            db.Open();
            return db.Query<User>("SELECT * FROM Users");
        }
    }
}

This Repository class demonstrates how to fetch data from a Users table. The GetAllUsers method opens a connection, executes a SQL query, and maps the results to a list of User objects. The use of IDbConnection allows for better abstraction and testability.

Mapping Results to Objects

Dapper automatically maps the results of queries to C# objects based on property names. For example, if the Users table has columns named Id, Name, and Email, the User class should have matching properties.

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

By ensuring that your object properties match the column names, Dapper can automatically populate instances of the User class with data returned from the query.

Inserting Data with Dapper

In addition to reading data, Dapper allows for easy insertion of records into the database. The Execute method is used for commands that do not return a result set, such as INSERT statements.

public void AddUser(User user)
{
    using (IDbConnection db = _dbConnection.GetConnection())
    {
        db.Open();
        var sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
        db.Execute(sql, user);
    }
}

The AddUser method takes a User object and inserts it into the Users table. The SQL statement uses parameters (denoted by the @ symbol) to prevent SQL injection attacks. Dapper automatically maps the properties of the User object to the parameters in the SQL query.

Handling Transactions

Dapper supports transactions, which are essential for ensuring data integrity when performing multiple related operations. You can create a transaction by using the IDbTransaction interface.

public void AddUsers(IEnumerable<User> users)
{
    using (IDbConnection db = _dbConnection.GetConnection())
    {
        db.Open();
        using (var transaction = db.BeginTransaction())
        {
            try
            {
                foreach (var user in users)
                {
                    db.Execute("INSERT INTO Users (Name, Email) VALUES (@Name, @Email)", user, transaction);
                }
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

In this example, the AddUsers method inserts multiple users within a single transaction. If any insertion fails, the transaction is rolled back, ensuring that the database remains consistent.

Edge Cases & Gotchas

When using Dapper, developers may encounter certain edge cases and pitfalls. One common issue is forgetting to open the database connection before executing queries, which can lead to runtime exceptions.

// Incorrect: Missing db.Open()
public IEnumerable<User> GetAllUsersIncorrect()
{
    using (IDbConnection db = _dbConnection.GetConnection())
    {
        return db.Query<User>("SELECT * FROM Users"); // Throws exception
    }
}

The code above will throw an exception because the connection is not opened before executing the query. Always ensure you call db.Open() before executing any commands.

Parameter Handling

Another gotcha is related to parameter handling. Using incorrect parameter names or mismatched types can lead to runtime errors or unexpected behavior.

// Incorrect: Mismatched parameter names
public void AddUserIncorrect(User user)
{
    var sql = "INSERT INTO Users (Name, Email) VALUES (@UserName, @UserEmail)"; // Wrong parameter names
    db.Execute(sql, user);
}

In this case, Dapper won't map the properties correctly because the parameter names in the SQL query do not match the property names of the User object. Always verify that parameter names match the object properties.

Performance & Best Practices

While Dapper is inherently fast, there are several best practices to further enhance performance. One effective technique is to use asynchronous methods, which can improve responsiveness in web applications.

public async Task<IEnumerable<User>> GetAllUsersAsync()
{
    using (IDbConnection db = _dbConnection.GetConnection())
    {
        await db.OpenAsync();
        return await db.QueryAsync<User>("SELECT * FROM Users");
    }
}

This asynchronous version of GetAllUsers opens the connection and executes the query without blocking the main thread, which is particularly beneficial for ASP.NET Core applications that handle multiple requests concurrently.

Batching Commands

Another performance optimization is batching commands. Dapper allows you to execute multiple commands in a single database round trip, which can significantly reduce latency.

public void AddUsersBatch(IEnumerable<User> users)
{
    using (IDbConnection db = _dbConnection.GetConnection())
    {
        db.Open();
        var sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
        db.Execute(sql, users);
    }
}

By passing a collection of users to the Execute method, Dapper can batch the insert operations, reducing the number of database round trips and enhancing performance.

Real-World Scenario: Building a Simple User Management API

To demonstrate the concepts learned, let’s build a simple User Management API using ASP.NET Core and Dapper. This API will allow users to create, read, update, and delete user records.

// Startup.cs
public void ConfigureServices(IServiceCollection services)
{
    services.AddScoped<DatabaseConnection>();
    services.AddScoped<Repository>();
    services.AddControllers();
}

// UsersController.cs
[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly Repository _repository;

    public UsersController(Repository repository)
    {
        _repository = repository;
    }

    [HttpGet]
    public ActionResult<IEnumerable<User>> GetUsers()
    {
        return Ok(_repository.GetAllUsers());
    }

    [HttpPost]
    public IActionResult CreateUser([FromBody] User user)
    {
        _repository.AddUser(user);
        return CreatedAtAction(nameof(GetUsers), new { id = user.Id }, user);
    }
}

This example demonstrates how to configure services and create a controller to handle user-related API requests. The UsersController includes methods for retrieving all users and adding a new user.

Testing the API

To test the API, you can use tools like Postman or curl. Sending a GET request to /api/users should return a list of users, while sending a POST request with a user object will add a new user to the database.

Conclusion

• Dapper is a lightweight ORM that provides fast and flexible data access.
• Setting up Dapper in an ASP.NET Core application is straightforward with NuGet.
• Understanding how to execute queries, handle transactions, and manage connections is crucial for effective database operations.
• Be aware of edge cases and best practices to avoid common pitfalls and enhance performance.
• Real-world applications can benefit significantly from using Dapper for managing data access.