A Comprehensive Guide to Using Dapper in C#


Dapper is a lightweight and efficient Object-Relational Mapping (ORM) library for C#. Unlike other ORMs that can be complex and require significant configuration, Dapper is designed to be simple and straightforward. It provides a convenient way to map database queries to C# objects and is particularly well-suited for scenarios where performance is crucial. In this article, we'll explore how to use Dapper in C# to interact with a database.


Before diving into Dapper, make sure you have the following prerequisites:

  1. Visual Studio or any C# development environment.

  2. .NET Core or .NET Framework.

  3. A database (e.g., SQL Server, MySQL, PostgreSQL) with a connection string.

Getting Started with Dapper

  1. Install Dapper:

    You can install Dapper using NuGet Package Manager or the .NET CLI with the following command:

     dotnet add package Dapper
  2. Create a Connection:

    Before using Dapper, establish a database connection using the appropriate ADO.NET provider. For example, if you're using SQL Server:

     using System.Data;
     using System.Data.SqlClient;
     var connection = new SqlConnection("YourConnectionString");

    Replace "YourConnectionString" with your actual database connection string.

Basic CRUD Operations with Dapper

Now, let's dive into some basic operations with Dapper:

1. Query Data

Dapper provides a simple way to retrieve data from the database and map it to C# objects. Here's an example of querying data:

var books = connection.Query<Book>("SELECT * FROM Books").ToList();

In this example, we're querying all records from a "Books" table and mapping them to a list of Book objects.

2. Execute Commands

Dapper also allows you to execute SQL commands (e.g., INSERT, UPDATE, DELETE) easily:

var newBook = new Book { Title = "New Book", Author = "John Doe" };
var insertSql = "INSERT INTO Books (Title, Author) VALUES (@Title, @Author)";

connection.Execute(insertSql, newBook);

Here, we're inserting a new book into the "Books" table.

3. Parameterized Queries

Dapper supports parameterized queries, which help prevent SQL injection:

var authorName = "J.K. Rowling";
var books = connection.Query<Book>("SELECT * FROM Books WHERE Author = @Author", new { Author = authorName }).ToList();

In this example, @Author is a named parameter, and we pass a parameterized object containing the Author property.

4. Mapping Results

Dapper automatically maps query results to C# objects based on naming conventions. For example, a column named "Title" in the database table will be mapped to a property named Title in your C# class.

You can also specify custom mappings using the ColumnAttribute or the Map method.

public class Book
    public string Title { get; set; }

    public string Author { get; set; }

var books = connection.Query<Book>("SELECT BookTitle AS Title, Author FROM Books").ToList();

5. Transactions

Dapper works seamlessly with ADO.NET transactions. You can wrap multiple Dapper commands in a transaction scope:

using (var transaction = connection.BeginTransaction())
        // Dapper commands here

    catch (Exception ex)
        // Handle exceptions

6. Stored Procedures

Dapper supports calling stored procedures:

var parameters = new DynamicParameters();
parameters.Add("@Author", "J.K. Rowling", DbType.String);

var books = connection.Query<Book>("GetBooksByAuthor", parameters, commandType: CommandType.StoredProcedure).ToList();

In this example, we call a stored procedure named "GetBooksByAuthor."


Dapper is a powerful and efficient ORM library for C# that simplifies database operations without sacrificing performance. It's an excellent choice for projects where simplicity and control over SQL queries are paramount. Whether you're building a small application or a large-scale system, Dapper can help you interact with your database in a clean and efficient manner.