Dapper with .NET: A Beginner-Friendly Guide

Dapper is a lightweight Object-Relational Mapper (ORM) for .NET. It helps you communicate with databases using SQL while reducing the amount of repetitive code you need to write.

In this beginner-friendly guide, we'll learn what Dapper is, why developers use it, how to install it, and how to perform common database operations with simple examples.

What Is Dapper?

Dapper is a lightweight library for .NET that makes working with relational databases easier.

Normally, when working with a database, you need to:

• Open a database connection.
• Write a SQL query.
• Execute the query.
• Read the returned data.
• Convert the database rows into C# objects.

Dapper helps simplify these steps.

For example, suppose your database has a Products table:

Products
--------------------------------
Id        Name          Price
1         Laptop        75000
2         Mouse         1200
3         Keyboard     2500

You might have a corresponding C# class:

// Represents one product from the database.
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

With Dapper, you can execute a SQL query and have the returned rows automatically mapped to Product objects.

Dapper vs Entity Framework Core

If you are new to .NET, you may wonder why Dapper is needed when Entity Framework Core already exists.

Both are useful, but they approach database access differently.

A simple way to remember it is:

Dapper gives you more control over SQL, while EF Core gives you more database abstraction.

Dapper is particularly attractive when you already know SQL and want a simple way to map query results to C# objects.

Installing Dapper

Let's assume you're creating an ASP.NET Core application.

You can install Dapper using the .NET CLI:

# Install the Dapper NuGet package.
dotnet add package Dapper

If you're using SQL Server, you'll also need a SQL Server database provider such as Microsoft.Data.SqlClient:

# Install the SQL Server client library.
dotnet add package Microsoft.Data.SqlClient

After installation, you can import Dapper in your C# code:

// Provides Dapper extension methods such as QueryAsync and ExecuteAsync.
using Dapper;

// Provides SqlConnection for connecting to SQL Server.
using Microsoft.Data.SqlClient;

Creating a Database Connection

Before Dapper can execute SQL, your application needs a database connection.

For example:

// Connection string used to connect to SQL Server.
string connectionString =  "Server=localhost;Database=ShopDb;Trusted_Connection=True;TrustServerCertificate=True";

// Create a connection to the database.
using var connection = new SqlConnection(connectionString);

// Open the database connection.
await connection.OpenAsync();

The connection string contains information about your SQL Server and database.

For a real application, don't hard-code passwords or connection strings containing secrets in your source code. Store them in configuration or a secure secret-management system.

Reading Data with Dapper

Let's start with the most common operation: retrieving data.

Suppose we have this SQL table:

-- Create a Products table.
CREATE TABLE Products
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(18, 2) NOT NULL
);

We can retrieve all products with Dapper:

// SQL query that retrieves all products.
string sql = "SELECT Id, Name, Price FROM Products";

// Execute the query and map each row to a Product object.
IEnumerable<Product> products =
    await connection.QueryAsync<Product>(sql);

// Display each product.
foreach (Product product in products)
{
    Console.WriteLine($"{product.Name} - {product.Price}");
}

The important method here is:

QueryAsync<Product>()

Dapper looks at the columns returned by SQL and maps them to matching properties in the Product class.

For example:

Database column       C# property
----------------------------------
Id                    Id
Name                  Name
Price                 Price

This automatic mapping is one of the things that makes Dapper convenient.

Getting a Single Record

Sometimes you don't want all records. You might want one product by its ID.

// SQL query that retrieves a product by its ID.
string sql = """
    SELECT Id, Name, Price
    FROM Products
    WHERE Id = @Id
    """;

// Values supplied to the SQL query.
var parameters = new
{
    Id = 1
};

// Execute the query and return one Product object.
Product? product =
    await connection.QuerySingleOrDefaultAsync<Product>(
        sql,
        parameters
    );

// Check whether a product was found.
if (product != null)
{
    Console.WriteLine(product.Name);
}

Notice the @Id parameter:

WHERE Id = @Id

and this object:

var parameters = new
{
    Id = 1
};

Dapper sends the value as a parameter instead of directly concatenating it into the SQL string.

This is important because you should use parameterized queries rather than building SQL with user input.

Why Parameters Matter

Avoid code like this:

// DON'T build SQL by concatenating user input.
string sql = "SELECT * FROM Products WHERE Name = '" + productName + "'";

This approach can create SQL injection vulnerabilities.

Instead, use a parameter:

// Use a parameter instead of inserting user input into SQL.
string sql = """
    SELECT Id, Name, Price
    FROM Products
    WHERE Name = @Name
    """;

// Dapper safely sends the value as a SQL parameter.
var parameters = new
{
    Name = productName
};

// Execute the parameterized query.
var products = await connection.QueryAsync<Product>(
    sql,
    parameters
);

For beginners, a good rule is:

Never concatenate untrusted user input directly into SQL.

Inserting Data with Dapper

Now let's add a new product.

// SQL command that inserts a new product.
string sql = """
    INSERT INTO Products (Name, Price)
    VALUES (@Name, @Price)
    """;

// Values that will be inserted into the database.
var parameters = new
{
    Name = "Wireless Mouse",
    Price = 1500m
};

// Execute the INSERT command.
int rowsAffected =
    await connection.ExecuteAsync(sql, parameters);

// Display how many rows were inserted.
Console.WriteLine($"Rows inserted: {rowsAffected}");

For commands such as INSERT, UPDATE, and DELETE, Dapper commonly uses:

ExecuteAsync()

The method returns the number of affected rows.

Updating Data

Suppose we want to change a product's price.

// SQL command that updates the product price.
string sql = """
    UPDATE Products
    SET Price = @Price
    WHERE Id = @Id
    """;

// Values used by the SQL parameters.
var parameters = new
{
    Id = 1,
    Price = 70000m
};

// Execute the UPDATE command.
int rowsAffected =
    await connection.ExecuteAsync(sql, parameters);

// Display the number of updated rows.
Console.WriteLine($"Rows updated: {rowsAffected}");

Again, Dapper handles the parameter values for us.

Deleting Data

Deleting a record is similar:

// SQL command that deletes a product.
string sql = """
    DELETE FROM Products
    WHERE Id = @Id
    """;

// ID of the product that should be deleted.
var parameters = new
{
    Id = 1
};

// Execute the DELETE command.
int rowsAffected =
    await connection.ExecuteAsync(sql, parameters);

// Display the number of deleted rows.
Console.WriteLine($"Rows deleted: {rowsAffected}");

Be careful with DELETE statements. Always make sure your WHERE condition is correct.

Getting the Newly Created ID

A common requirement is to insert a record and immediately get its generated ID.

For SQL Server, you can use OUTPUT INSERTED.Id.

// SQL command that inserts the product
// and returns the newly generated ID.
string sql = """
    INSERT INTO Products (Name, Price)
    OUTPUT INSERTED.Id
    VALUES (@Name, @Price)
    """;

// Values for the new product.
var parameters = new
{
    Name = "Mechanical Keyboard",
    Price = 4500m
};

// Execute the INSERT and retrieve the generated ID.
int newProductId =
    await connection.ExecuteScalarAsync<int>(
        sql,
        parameters
    );

// Display the generated ID.
Console.WriteLine($"New product ID: {newProductId}");

Here we use:

ExecuteScalarAsync<int>()

because we expect a single value from the database.

Creating a Simple Repository

As your application grows, you shouldn't put all your SQL queries directly inside controllers.

A common approach is to create a repository or data-access class.

For example:

public class ProductRepository
{
    // Stores the database connection string.
    private readonly string _connectionString;

    // Receive the connection string through the constructor.
    public ProductRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        // SQL query used to retrieve all products.
        string sql = """
            SELECT Id, Name, Price
            FROM Products
            """;

        // Create the database connection.
        using var connection =
            new SqlConnection(_connectionString);

        // Execute the query and map rows to Product objects.
        return await connection.QueryAsync<Product>(sql);
    }
}

Now your application can simply call:

// Create the repository.
var repository = new ProductRepository(connectionString);

// Retrieve all products.
var products = await repository.GetAllAsync();

// Display the products.
foreach (var product in products)
{
    Console.WriteLine(product.Name);
}

This keeps your database-related code in one place.

Using Dapper in ASP.NET Core

In an ASP.NET Core application, you will usually store your connection string in appsettings.json.

For example:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=ShopDb;Trusted_Connection=True;TrustServerCertificate=True"
  }
}

Then you can register your database connection in Program.cs.

One simple approach is:

// Register the SQL Server connection string with dependency injection.
builder.Services.AddScoped<SqlConnection>(serviceProvider =>
{
    // Read the connection string from application configuration.
    string connectionString =
        builder.Configuration.GetConnectionString("DefaultConnection")!;

    // Create and return a SQL Server connection.
    return new SqlConnection(connectionString);
});

Your repository can then receive SqlConnection through dependency injection:

public class ProductRepository
{
    // Database connection supplied by dependency injection.
    private readonly SqlConnection _connection;

    // Receive the connection through the constructor.
    public ProductRepository(SqlConnection connection)
    {
        _connection = connection;
    }

    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        // SQL query that retrieves all products.
        string sql = """
            SELECT Id, Name, Price
            FROM Products
            """;

        // Execute the query using Dapper.
        return await _connection.QueryAsync<Product>(sql);
    }
}

For larger applications, you may choose a connection factory or another database abstraction rather than registering a single connection instance. The important idea is to keep connection management clean and predictable.

Dapper and Stored Procedures

Dapper can also execute stored procedures.

Suppose SQL Server contains a stored procedure:

-- Create a stored procedure that retrieves a product by ID.
CREATE PROCEDURE GetProductById
    @Id INT
AS
BEGIN
    -- Return the requested product.
    SELECT Id, Name, Price
    FROM Products
    WHERE Id = @Id;
END;

You can call it from C#:

// Parameters required by the stored procedure.
var parameters = new
{
    Id = 1
};

// Execute the stored procedure and map the result to Product.
Product? product =
    await connection.QuerySingleOrDefaultAsync<Product>(
        "GetProductById",
        parameters,
        commandType: CommandType.StoredProcedure
    );

Don't forget:

// Provides CommandType.StoredProcedure.
using System.Data;

Transactions with Dapper

Sometimes you need multiple database operations to succeed together.

Imagine transferring money between two accounts. You don't want the first update to succeed while the second update fails.

That's where a transaction can help.

A simplified example:

// Create a database transaction.
using var transaction =
    connection.BeginTransaction();

try
{
    // Remove money from the first account.
    await connection.ExecuteAsync(
        """
        UPDATE Accounts
        SET Balance = Balance - @Amount
        WHERE Id = @FromAccount
        """,
        new
        {
            Amount = 100,
            FromAccount = 1
        },
        transaction
    );

    // Add money to the second account.
    await connection.ExecuteAsync(
        """
        UPDATE Accounts
        SET Balance = Balance + @Amount
        WHERE Id = @ToAccount
        """,
        new
        {
            Amount = 100,
            ToAccount = 2
        },
        transaction
    );

    // Make both changes permanent.
    transaction.Commit();
}
catch
{
    // Undo all changes if something goes wrong.
    transaction.Rollback();

    // Re-throw the error so the application can handle it.
    throw;
}

The key idea is:

Either all operations succeed, or the transaction rolls them back.

Important Dapper Methods to Remember

As a beginner, you don't need to memorize every Dapper method.

Start with these:

Once you're comfortable with these methods, most basic Dapper operations become straightforward.

Advantages of Dapper

Dapper has several advantages.

1. Simple

Dapper has a relatively small API, so beginners can learn the basics quickly.

2. SQL control

You write the SQL yourself, giving you direct control over queries.

3. Lightweight

Dapper doesn't try to provide the large feature set of a full ORM.

4. Object mapping

Dapper can automatically map query results to C# objects.

5. Good for existing databases

If you already have a database with established SQL queries or stored procedures, Dapper can be a convenient choice.

Things to Be Careful About

Dapper is simple, but it doesn't remove the need to understand SQL.

You still need to think about:

• SQL injection
• indexes
• query performance
• database relationships
• transactions
• connection management
• error handling
• database constraints
• pagination for large result sets

Also remember that Dapper doesn't automatically provide all the features you may expect from Entity Framework Core, such as change tracking and migrations.

Dapper: A Simple Mental Model

If you're struggling to understand Dapper, remember this basic flow:

C# Application
      |
      v
   Dapper
      |
      v
 SQL Query
      |
      v
 SQL Server
      |
      v
 Database Rows
      |
      v
 Dapper maps rows
      |
      v
 C# Objects

For example:

// SQL query.
string sql = "SELECT Id, Name, Price FROM Products";

// Dapper executes the SQL and maps the rows to Product objects.
var products = await connection.QueryAsync<Product>(sql);

That's essentially what Dapper is helping you accomplish.

Final Thoughts

Dapper is a great library to learn if you want to understand how C# applications communicate with relational databases while retaining direct control over SQL.

For a beginner, start with these concepts:

1. Create a database connection.
2. Write a SELECT query.
3. Use QueryAsync<T>() to retrieve objects.
4. Use parameters instead of concatenating user input.
5. Use ExecuteAsync() for INSERT, UPDATE, and DELETE.
6. Learn transactions when your application performs multiple related changes.
7. As your application grows, organize database code into repositories or another suitable data-access layer.

Once you understand these fundamentals, you can gradually move on to joins, pagination, stored procedures, transactions, multiple result sets, and more advanced Dapper patterns.

The most important thing to remember: Dapper doesn't replace SQL—it makes using SQL from your .NET application much easier.