Dapper

If a single SQL command returns multiple result grids (e.g., multiple SELECT statements), use QueryMultiple to process them sequentially using a GridReader.

var sql = "select * from Customers where Id = @id; select * from Orders where CustomerId = @id;";

using (var multi = connection.QueryMultiple(sql, new { id = selectedId }))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
}

By default, Dapper is buffered, meaning it executes the SQL and loads the entire result set into memory before returning. This is usually best for performance and minimizing database locks.

If you are executing extremely large queries and want to minimize the memory footprint of your application, set buffered: false in the Query method to stream rows one by one.

Dapper and Dapper Plus are related but distinct products:

• Dapper: A high-performance micro-ORM for .NET that provides simple access to the ADO.NET API. It is open-source and freely available.
• Dapper Plus: A separate commercial tool developed by ZZZ Projects. It extends Dapper's capabilities by offering advanced features such as bulk operations.

Note that Dapper Plus is a major sponsor of the Dapper project, but this sponsorship does not change the ownership, license, or operation of the core Dapper libraries.

For Dapper.Rainbow to function correctly, every table in your database must have a primary key column named exactly Id.

CREATE TABLE Users (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

Multi-mapping allows you to map a single row from a SQL JOIN result into multiple nested objects. You provide a mapping function that defines how the objects relate.

To use it, specify the types in the Query method signature as <T1, T2, ..., TReturn>. Dapper splits the row into these objects based on the Id column (or a custom splitOn parameter).

// Maps a Post and its associated User from a single JOIN query
var sql = "select * from #Posts p left join #Users u on u.Id = p.OwnerId";

var data = connection.Query<Post, User, Post>(sql, (post, user) => 
{
    post.Owner = user;
    return post;
});

Dapper.SqlBuilder is a simple SQL formatter for .NET. You can install the stable version via NuGet to add helper methods for generating SQL templates with dynamic parameters.

dotnet add package Dapper.SqlBuilder

As of version 2.0.4, Dapper removed the hard dependency on System.Data.SqlClient. This allows consumers to choose between System.Data.SqlClient or Microsoft.Data.SqlClient.

Note for existing users: If your project previously relied on Dapper to provide the SQL client, you may need to explicitly add System.Data.SqlClient or Microsoft.Data.SqlClient as a <PackageReference> in your project file to ensure your code builds and runs correctly.

Once your Database context is initialized with an open connection, you can perform CRUD operations on your tables using the Table<T> properties.

// Setup
var db = new MyDatabase { Connection = connection };

// Insert
var newUser = new User { Name = "John Doe", Email = "john.doe@example.com" };
var insertedUser = db.Users.Insert(newUser);

// Select
var user = db.Users.Get(id); // Single user by ID

// Update
var userToUpdate = db.Users.Get(id);
userToUpdate.Email = "new.email@example.com";
db.Users.Update(userToUpdate);

// Delete
db.Users.Delete(id);

To use the Rainbow CRUD patterns, you must define a database context class that inherits from Database<T>, where T is your context class. Within this class, define properties for each table using the Table<TEntity> type.

using Dapper;
using System.Data;

public class MyDatabase : Database<MyDatabase>
{
    public Table<User> Users { get; set; }
}

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

Dapper is available as a NuGet package. You can install the core library or various extension packages depending on your needs.

Core Packages:

• Dapper: The core library.
• Dapper.EntityFramework: Extension handlers for EntityFramework.
• Dapper.EntityFramework.StrongName: Strong-named extension handlers for EntityFramework.
• Dapper.Rainbow: Micro-ORM built on Dapper providing CRUD helpers.
• Dapper.SqlBuilder: Component for building SQL queries dynamically and composably.
• Dapper.StrongName: Strong-named version of the core library.