C# Dapper
Introduction
Dapper is a simple, lightweight micro-ORM (Object-Relational Mapper) for C# that extends the IDbConnection interface. It was developed by the team at Stack Overflow to address their performance needs while maintaining a clean, simple API. Dapper is often called a "micro-ORM" because it provides just the essential features needed to map SQL query results to .NET objects, without the complexity and overhead of full-featured ORMs like Entity Framework.
In this tutorial, we'll explore Dapper and learn how to use it for database operations in C# applications.
Why Choose Dapper?
Before diving into Dapper's functionality, let's understand why you might choose it:
- Performance: Dapper is one of the fastest ORMs available for .NET, with performance close to raw ADO.NET.
- Simplicity: The API is straightforward and easy to learn.
- SQL Control: You write your own SQL queries, giving you complete control over database operations.
- Lightweight: Minimal abstraction means minimal overhead.
- Production-Proven: Used by high-traffic sites like Stack Overflow.
Getting Started with Dapper
Installation
To start using Dapper, you need to install it via NuGet:
bash
Install-Package Dapper
Or using the .NET CLI:
bash
dotnet add package Dapper
Setting Up
To use Dapper, you first need to include the necessary namespaces:
csharp
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;
For our examples, we'll use a simple Product class:
csharp
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public int Stock { get; set; }
}
Basic CRUD Operations with Dapper
Let's explore how to perform CRUD (Create, Read, Update, Delete) operations with Dapper.
Establishing a Connection
First, we need to create a database connection:
csharp
string connectionString = "Server=localhost;Database=StoreDB;Trusted_Connection=True;";
using (IDbConnection connection = new SqlConnection(connectionString))
{
// Dapper operations will go here
}
Reading Data (SELECT)
Querying a Single Object
csharp
public Product GetProduct(int id)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT Id, Name, Price, Stock FROM Products WHERE Id = @Id";
return connection.QueryFirstOrDefault<Product>(sql, new { Id = id });
}
}
Example usage and output:
csharp
var product = GetProduct(1);
Console.WriteLine($"Product: {product.Name}, Price: ${product.Price}");
// Output (example):
// Product: Laptop, Price: $999.99
Querying Multiple Objects
csharp
public IEnumerable<Product> GetAllProducts()
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT Id, Name, Price, Stock FROM Products";
return connection.Query<Product>(sql);
}
}
Example usage:
csharp
var products = GetAllProducts();
foreach (var product in products)
{
Console.WriteLine($"Product ID: {product.Id}, Name: {product.Name}, Price: ${product.Price}");
}
// Output (example):
// Product ID: 1, Name: Laptop, Price: $999.99
// Product ID: 2, Name: Mouse, Price: $24.99
// Product ID: 3, Name: Keyboard, Price: $49.99
Creating Data (INSERT)
csharp
public int AddProduct(Product product)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
string sql = @"
INSERT INTO Products (Name, Price, Stock)
VALUES (@Name, @Price, @Stock);
SELECT CAST(SCOPE_IDENTITY() as int)";
return connection.QuerySingle<int>(sql, product);
}
}
Example usage:
csharp
var newProduct = new Product
{
Name = "Monitor",
Price = 299.99m,
Stock = 10
};
int newId = AddProduct(newProduct);
Console.WriteLine($"New product added with ID: {newId}");
// Output (example):
// New product added with ID: 4
Updating Data (UPDATE)
csharp
public bool UpdateProduct(Product product)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
string sql = @"
UPDATE Products
SET Name = @Name,
Price = @Price,
Stock = @Stock
WHERE Id = @Id";
int rowsAffected = connection.Execute(sql, product);
return rowsAffected > 0;
}
}
Example usage:
csharp
var product = GetProduct(1);
product.Price = 899.99m;
product.Stock = 15;
bool updated = UpdateProduct(product);
Console.WriteLine($"Product updated: {updated}");
// Output:
// Product updated: True
Deleting Data (DELETE)
csharp
public bool DeleteProduct(int id)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
string sql = "DELETE FROM Products WHERE Id = @Id";
int rowsAffected = connection.Execute(sql, new { Id = id });
return rowsAffected > 0;
}
}
Example usage:
csharp
bool deleted = DeleteProduct(3);
Console.WriteLine($"Product deleted: {deleted}");
// Output:
// Product deleted: True
Advanced Dapper Features
Multiple Resultsets
You can execute a single query that returns multiple result sets:
csharp
public (IEnumerable<Product> Products, IEnumerable<Category> Categories) GetProductsAndCategories()
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
string sql = @"
SELECT Id, Name, Price, Stock FROM Products;
SELECT Id, Name FROM Categories;";
using (var multi = connection.QueryMultiple(sql))
{
var products = multi.Read<Product>().ToList();
var categories = multi.Read<Category>().ToList();
return (products, categories);
}
}
}
Stored Procedures
Dapper works great with stored procedures:
csharp
public IEnumerable<Product> GetProductsByCategory(int categoryId)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
return connection.Query<Product>(
"GetProductsByCategory",
new { CategoryId = categoryId },
commandType: CommandType.StoredProcedure
);
}
}
Dynamic Parameters
For more complex parameter scenarios:
csharp
public IEnumerable<Product> SearchProducts(string name, decimal? maxPrice)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
var parameters = new DynamicParameters();
parameters.Add("@Name", name + "%");
string sql = "SELECT * FROM Products WHERE Name LIKE @Name";
if (maxPrice.HasValue)
{
parameters.Add("@MaxPrice", maxPrice.Value);
sql += " AND Price <= @MaxPrice";
}
return connection.Query<Product>(sql, parameters);
}
}
Transactions
Dapper supports transactions seamlessly:
csharp
public void TransferStock(int sourceProductId, int targetProductId, int amount)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
// Decrease stock from source product
connection.Execute(
"UPDATE Products SET Stock = Stock - @Amount WHERE Id = @Id",
new { Amount = amount, Id = sourceProductId },
transaction
);
// Increase stock in target product
connection.Execute(
"UPDATE Products SET Stock = Stock + @Amount WHERE Id = @Id",
new { Amount = amount, Id = targetProductId },
transaction
);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
One-to-Many Relationships
You can handle one-to-many relationships efficiently:
csharp
public IEnumerable<Order> GetOrdersWithDetails()
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
string sql = @"
SELECT o.*, od.*
FROM Orders o
JOIN OrderDetails od ON o.Id = od.OrderId";
var orderDictionary = new Dictionary<int, Order>();
connection.Query<Order, OrderDetail, Order>(
sql,
(order, detail) => {
if (!orderDictionary.TryGetValue(order.Id, out var existingOrder))
{
existingOrder = order;
existingOrder.Details = new List<OrderDetail>();
orderDictionary.Add(order.Id, existingOrder);
}
existingOrder.Details.Add(detail);
return existingOrder;
},
splitOn: "Id"
);
return orderDictionary.Values;
}
}
Practical Example: Building a Product Inventory System
Let's put together what we've learned to build a simple product inventory system:
csharp
public class InventoryService
{
private readonly string connectionString;
public InventoryService(string connString)
{
connectionString = connString;
}
public IEnumerable<Product> GetLowStockProducts(int threshold)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
return connection.Query<Product>(
"SELECT * FROM Products WHERE Stock < @Threshold",
new { Threshold = threshold }
);
}
}
public bool UpdateStock(int productId, int newStockLevel)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
int rowsAffected = connection.Execute(
"UPDATE Products SET Stock = @Stock WHERE Id = @Id",
new { Stock = newStockLevel, Id = productId }
);
return rowsAffected > 0;
}
}
public bool ProcessSale(int productId, int quantity)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
// Check if enough stock is available
var currentStock = connection.QueryFirstOrDefault<int>(
"SELECT Stock FROM Products WHERE Id = @Id",
new { Id = productId },
transaction
);
if (currentStock < quantity)
{
transaction.Rollback();
return false;
}
// Update stock
connection.Execute(
"UPDATE Products SET Stock = Stock - @Quantity WHERE Id = @Id",
new { Quantity = quantity, Id = productId },
transaction
);
// Record the sale (assumes Sales table exists)
connection.Execute(
"INSERT INTO Sales (ProductId, Quantity, Date) VALUES (@ProductId, @Quantity, @Date)",
new { ProductId = productId, Quantity = quantity, Date = DateTime.Now },
transaction
);
transaction.Commit();
return true;
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
public ProductSalesReport GetProductSalesReport(int productId)
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
// Get the product
var product = connection.QueryFirstOrDefault<Product>(
"SELECT * FROM Products WHERE Id = @Id",
new { Id = productId }
);
if (product == null)
return null;
// Get sales data
var sales = connection.Query<Sale>(
"SELECT * FROM Sales WHERE ProductId = @ProductId",
new { ProductId = productId }
).ToList();
return new ProductSalesReport
{
Product = product,
TotalSold = sales.Sum(s => s.Quantity),
SalesList = sales
};
}
}
}
// Supporting classes
public class Sale
{
public int Id { get; set; }
public int ProductId { get; set; }
public int Quantity { get; set; }
public DateTime Date { get; set; }
}
public class ProductSalesReport
{
public Product Product { get; set; }
public int TotalSold { get; set; }
public List<Sale> SalesList { get; set; }
}
Example usage:
csharp
var inventoryService = new InventoryService(connectionString);
// Check for products with low stock (less than 5 units)
var lowStockProducts = inventoryService.GetLowStockProducts(5);
foreach (var product in lowStockProducts)
{
Console.WriteLine($"Low stock alert: {product.Name} - only {product.Stock} remaining");
}
// Process a sale
bool saleCompleted = inventoryService.ProcessSale(1, 2);
if (saleCompleted)
Console.WriteLine("Sale processed successfully");
else
Console.WriteLine("Sale failed - insufficient stock");
// Get sales report for a product
var report = inventoryService.GetProductSalesReport(1);
Console.WriteLine($"Sales report for {report.Product.Name}:");
Console.WriteLine($"Total units sold: {report.TotalSold}");
Console.WriteLine("Recent sales:");
foreach (var sale in report.SalesList.OrderByDescending(s => s.Date).Take(5))
{
Console.WriteLine($" {sale.Date}: {sale.Quantity} units");
}
Best Practices with Dapper
- Use parameters for all user inputs to avoid SQL injection attacks.
- Dispose connections properly using the
usingstatement. - Keep your SQL clean and formatted for readability.
- Use transactions for operations that require atomic execution.
- Consider cache for frequently accessed data to reduce database calls.
- Watch out for n+1 query problems by using proper JOIN operations.
- Use async methods for better scalability in ASP.NET applications.
Summary
Dapper is a powerful, lightweight micro-ORM that provides a perfect balance between the raw performance of ADO.NET and the convenience of an ORM. It excels in scenarios where you need full control over your SQL while still wanting the convenience of automatic object mapping.
Key takeaways:
- Dapper is extremely fast while maintaining a simple API.
- It works with raw SQL, giving you complete control over database operations.
- It handles mapping between SQL results and C# objects automatically.
- It supports all major database operations (CRUD) with minimal code.
- Advanced features like multiple resultsets, stored procedures, and transactions are well supported.
Additional Resources
Exercises
- Create a simple console application that uses Dapper to connect to a SQLite database and perform CRUD operations on a
Bookentity. - Extend the inventory system example to include categories for products and implement methods to get products by category.
- Implement a method that uses Dapper's multi-mapping feature to retrieve customers with their orders in a single query.
- Create a data repository pattern implementation using Dapper for a domain of your choice (e.g., blog, e-commerce, library).
- Write code to handle more complex queries with paging and sorting using Dapper's features.
By completing these exercises, you'll gain solid hands-on experience with Dapper and be well on your way to building efficient data access layers in your C# applications.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)