This tutorial series guides you through integrating and utilizing Dapper, a powerful yet simple Object-Relational Mapper (ORM), within your ASP.NET Core applications. We'll explore its efficiency, ease of use, and how it can streamline your database interactions.
Dapper is a micro-ORM developed by Stack Overflow. It extends IDbConnection and provides a fast, lightweight way to execute SQL queries and map the results to your .NET objects. Unlike full ORMs like Entity Framework, Dapper focuses on performance and simplicity, giving developers more control over their SQL statements.
Why use Dapper?
To begin, you need to install the Dapper NuGet package into your ASP.NET Core project.
dotnet add package Dapper
Next, you'll need to establish a database connection. This is typically done in your Startup.cs or Program.cs file (depending on your ASP.NET Core version).
For demonstration purposes, let's assume you're using SQL Server. You'll need a connection string, which is commonly stored in appsettings.json.
{
"ConnectionStrings": {
"DefaultConnection": "Server=your_server_name;Database=your_database_name;User Id=your_user;Password=your_password;"
}
}
In your Startup.cs (or Program.cs), you can register your IDbConnection:
using System.Data;
using Microsoft.Data.SqlClient; // Or System.Data.SqlClient for .NET Framework
// ... inside ConfigureServices or Program.cs
// Using dependency injection for DbConnection
services.AddScoped<IDbConnection>(provider =>
{
var configuration = provider.GetRequiredService<IConfiguration>();
var connectionString = configuration.GetConnectionString("DefaultConnection");
return new SqlConnection(connectionString);
});
Note: Using AddScoped is common for web applications to ensure a new connection is created per request. For console apps or other scenarios, AddSingleton or AddTransient might be more appropriate.
Dapper provides several extension methods on IDbConnection to execute SQL commands. The most common are Query and Execute.
Let's assume you have a simple Product model:
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
To fetch a list of products:
using Dapper;
using System.Collections.Generic;
using System.Data;
// Inject IDbConnection into your service or controller
private readonly IDbConnection _dbConnection;
public ProductRepository(IDbConnection dbConnection)
{
_dbConnection = dbConnection;
}
public async Task<IEnumerable<Product>> GetAllProductsAsync()
{
string sql = "SELECT Id, Name, Price FROM Products";
return await _dbConnection.QueryAsync<Product>(sql);
}
Dapper automatically maps the columns from your SQL query to the properties of your Product class based on their names.
To fetch a single product by its ID:
public async Task<Product> GetProductByIdAsync(int id)
{
string sql = "SELECT Id, Name, Price FROM Products WHERE Id = @ProductId";
return await _dbConnection.QuerySingleOrDefaultAsync<Product>(sql, new { ProductId = id });
}
Using named parameters (like @ProductId) is a good practice for security (preventing SQL injection) and clarity.
The ExecuteAsync method is used for commands that don't return a result set, such as INSERT, UPDATE, and DELETE statements. It returns the number of rows affected.
public async Task<int> AddProductAsync(Product product)
{
string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT CAST(SCOPE_IDENTITY() as int);";
// If you need the ID of the inserted row, you can use QuerySingleAsync
return await _dbConnection.ExecuteAsync(sql, product);
}
public async Task<int> UpdateProductAsync(Product product)
{
string sql = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id";
return await _dbConnection.ExecuteAsync(sql, product);
}
public async Task<int> DeleteProductAsync(int id)
{
string sql = "DELETE FROM Products WHERE Id = @Id";
return await _dbConnection.ExecuteAsync(sql, new { Id = id });
}
When inserting, using SELECT CAST(SCOPE_IDENTITY() as int); at the end of the INSERT statement allows you to retrieve the newly generated ID using QuerySingleAsync<int>.
Dapper supports dynamic parameters, which are useful when the number or names of parameters are not known at compile time.
public async Task<IEnumerable<Product>> GetProductsByPriceRange(decimal minPrice, decimal maxPrice)
{
string sql = "SELECT Id, Name, Price FROM Products WHERE Price BETWEEN @Min AND @Max";
// Dapper automatically handles mapping from anonymous object to SQL parameters
return await _dbConnection.QueryAsync<Product>(sql, new { Min = minPrice, Max = maxPrice });
}
Sometimes, you might want to map a single SQL row to multiple .NET objects, or combine data from different tables into one object.
public class ProductWithCategory
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public string CategoryName { get; set; }
}
// Assuming a JOIN query
public async Task<IEnumerable<ProductWithCategory>> GetProductsWithCategoryAsync()
{
string sql = @"
SELECT P.Id, P.Name, P.Price, C.Name AS CategoryName
FROM Products P
JOIN Categories C ON P.CategoryId = C.Id";
// Dapper automatically maps columns to properties based on name
// If column names differ from property names, use AS in SQL or Dapper's type handlers
return await _dbConnection.QueryAsync<ProductWithCategory>(sql);
}
For more complex scenarios where one row needs to map to multiple objects, or you need to split a row into multiple objects, Dapper's TypeHandler or manual mapping with Map<> is used.
Dapper can handle queries that return multiple distinct result sets (e.g., using SQL Server's ; separator).
public class ProductsAndCount
{
public IEnumerable<Product> Products { get; set; }
public int TotalCount { get; set; }
}
public async Task<ProductsAndCount> GetProductsAndCountAsync()
{
string sql = "SELECT Id, Name, Price FROM Products; SELECT COUNT(*) FROM Products;";
using (var multi = await _dbConnection.QueryMultipleAsync(sql))
{
var products = await multi.ReadAsync<Product>();
var count = await multi.ReadSingleAsync<int>();
return new ProductsAndCount { Products = products, TotalCount = count };
}
}
Executing stored procedures is as straightforward:
public async Task<IEnumerable<Product>> GetProductsByCategoryProc(int categoryId)
{
return await _dbConnection.QueryAsync<Product>(
"spGetProductsByCategory", // Stored procedure name
new { CategoryId = categoryId },
commandType: CommandType.StoredProcedure // Specify command type
);
}
_dbConnection.BeginTransaction() to start a transaction and Commit() or Rollback() as needed.SqlMapper.SetTypeMap() to define custom mappings or explore third-party extensions like Dapper.SimpleCRUD for common CRUD operations.
Dapper offers a powerful, performant, and flexible way to handle data access in ASP.NET Core applications. Its simplicity makes it ideal for developers who want to write efficient SQL and maintain fine-grained control over their database interactions. By following the patterns and best practices outlined in this tutorial, you can effectively integrate Dapper into your projects.
Continue exploring the official Dapper documentation for more advanced features and customization options.