A Comprehensive Guide to Using Dapper in C#

Introduction

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.

Prerequisites

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");
    connection.Open();
   

   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
{
    [Column("BookTitle")]
    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())
{
    try
    {
        // Dapper commands here

        transaction.Commit();
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        // 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."

Conclusion

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.