Dapper ORM实现高效SQL CRUD操作指南

1. Dapper与原生SQL CRUD操作概述在.NET生态系统中Dapper作为一款轻量级ORM工具以其卓越的性能和简洁的API设计赢得了开发者的青睐。与Entity Framework等全功能ORM不同Dapper采用微ORM的架构理念它不尝试抽象掉SQL本身而是提供高效的对象映射机制让开发者可以完全掌控SQL语句的编写。原生SQL CRUDCreate, Read, Update, Delete是数据库操作的基础Dapper通过扩展方法将这些操作变得异常简单。以下是Dapper的核心优势近乎原生ADO.NET的性能表现极低的学习曲线和简洁的API设计完整的SQL控制权灵活的参数化查询支持强大的多映射能力2. 环境准备与基础配置2.1 项目初始化与依赖安装首先创建一个新的.NET Core控制台应用程序并添加必要的NuGet包dotnet new console -n DapperDemo cd DapperDemo dotnet add package Dapper dotnet add package Microsoft.Data.SqlClient对于不同的数据库类型可以选择对应的ADO.NET提供程序SQL Server: Microsoft.Data.SqlClientMySQL: MySql.DataPostgreSQL: NpgsqlSQLite: Microsoft.Data.Sqlite2.2 数据库连接配置在appsettings.json中配置数据库连接字符串{ ConnectionStrings: { DefaultConnection: Server(localdb)\\mssqllocaldb;DatabaseDapperDemoDB;Trusted_ConnectionTrue; } }创建数据库连接辅助类using System.Data; using Microsoft.Data.SqlClient; using Microsoft.Extensions.Configuration; public class DbContext { private readonly IConfiguration _config; public DbContext(IConfiguration config) { _config config; } public IDbConnection CreateConnection() new SqlConnection(_config.GetConnectionString(DefaultConnection)); }3. 基础CRUD操作实现3.1 实体类定义首先定义我们的领域模型以Product类为例public class Product { public int Id { get; set; } public string Name { get; set; } public decimal Price { get; set; } public int Stock { get; set; } public DateTime CreatedDate { get; set; } DateTime.UtcNow; }3.2 创建(Create)操作使用Dapper执行INSERT操作public class ProductRepository { private readonly DbContext _dbContext; public ProductRepository(DbContext dbContext) { _dbContext dbContext; } public async Taskint CreateAsync(Product product) { using var connection _dbContext.CreateConnection(); var sql INSERT INTO Products (Name, Price, Stock, CreatedDate) VALUES (Name, Price, Stock, CreatedDate); SELECT CAST(SCOPE_IDENTITY() as int); return await connection.ExecuteScalarAsyncint(sql, product); } }关键点说明ExecuteScalarAsync用于获取插入后生成的ID参数通过匿名对象或实体类本身传递SQL中的参数名必须与对象属性名匹配3.3 查询(Read)操作实现各种查询场景// 获取单个实体 public async TaskProduct GetByIdAsync(int id) { using var connection _dbContext.CreateConnection(); var sql SELECT * FROM Products WHERE Id Id; return await connection.QueryFirstOrDefaultAsyncProduct(sql, new { Id id }); } // 获取列表 public async TaskIEnumerableProduct GetAllAsync() { using var connection _dbContext.CreateConnection(); return await connection.QueryAsyncProduct(SELECT * FROM Products ORDER BY Id); } // 带条件的查询 public async TaskIEnumerableProduct GetByPriceRangeAsync(decimal minPrice, decimal maxPrice) { using var connection _dbContext.CreateConnection(); var sql SELECT * FROM Products WHERE Price BETWEEN MinPrice AND MaxPrice; return await connection.QueryAsyncProduct(sql, new { MinPrice minPrice, MaxPrice maxPrice }); }3.4 更新(Update)操作实现数据更新public async Taskbool UpdateAsync(Product product) { using var connection _dbContext.CreateConnection(); var sql UPDATE Products SET Name Name, Price Price, Stock Stock WHERE Id Id; var affectedRows await connection.ExecuteAsync(sql, product); return affectedRows 0; }3.5 删除(Delete)操作实现数据删除public async Taskbool DeleteAsync(int id) { using var connection _dbContext.CreateConnection(); var sql DELETE FROM Products WHERE Id Id; var affectedRows await connection.ExecuteAsync(sql, new { Id id }); return affectedRows 0; }4. 高级查询技巧4.1 多表关联查询Dapper处理复杂查询同样得心应手。假设我们有一个Order实体public class Order { public int Id { get; set; } public DateTime OrderDate { get; set; } public decimal TotalAmount { get; set; } public ListOrderItem Items { get; set; } } public class OrderItem { public int Id { get; set; } public int OrderId { get; set; } public int ProductId { get; set; } public int Quantity { get; set; } public decimal UnitPrice { get; set; } public Product Product { get; set; } }实现多表查询public async TaskOrder GetOrderWithItemsAsync(int orderId) { using var connection _dbContext.CreateConnection(); var sql SELECT o.*, oi.*, p.* FROM Orders o LEFT JOIN OrderItems oi ON o.Id oi.OrderId LEFT JOIN Products p ON oi.ProductId p.Id WHERE o.Id OrderId; var orderDictionary new Dictionaryint, Order(); var result await connection.QueryAsyncOrder, OrderItem, Product, Order( sql, (order, orderItem, product) { if (!orderDictionary.TryGetValue(order.Id, out var existingOrder)) { existingOrder order; existingOrder.Items new ListOrderItem(); orderDictionary.Add(existingOrder.Id, existingOrder); } if (orderItem ! null) { orderItem.Product product; existingOrder.Items.Add(orderItem); } return existingOrder; }, new { OrderId orderId }, splitOn: Id,Id); return orderDictionary.Values.FirstOrDefault(); }4.2 存储过程调用Dapper完美支持存储过程public async TaskIEnumerableProduct GetTopSellingProductsAsync(int count) { using var connection _dbContext.CreateConnection(); return await connection.QueryAsyncProduct( sp_GetTopSellingProducts, new { Count count }, commandType: CommandType.StoredProcedure); }4.3 批量操作使用Dapper的批量操作提高性能public async Taskint BulkInsertProductsAsync(IEnumerableProduct products) { using var connection _dbContext.CreateConnection(); var sql INSERT INTO Products (Name, Price, Stock, CreatedDate) VALUES (Name, Price, Stock, CreatedDate); return await connection.ExecuteAsync(sql, products); }5. 性能优化与最佳实践5.1 连接管理策略正确的连接管理对性能至关重要// 错误做法 - 不要这样使用 public IEnumerableProduct GetProducts() { using var connection new SqlConnection(_connectionString); return connection.QueryProduct(SELECT * FROM Products); } // 正确做法 - 在单个方法调用中完成连接生命周期 public async TaskIEnumerableProduct GetProductsAsync() { await using var connection _dbContext.CreateConnection(); await connection.OpenAsync(); return await connection.QueryAsyncProduct(SELECT * FROM Products); }5.2 参数化查询始终使用参数化查询防止SQL注入// 不安全 - 字符串拼接 var unsafeSql $SELECT * FROM Products WHERE Name {name}; // 安全 - 参数化查询 var safeSql SELECT * FROM Products WHERE Name Name; var products await connection.QueryAsyncProduct(safeSql, new { Name name });5.3 异步操作充分利用Dapper的异步APIpublic async TaskProduct GetProductAsync(int id) { await using var connection _dbContext.CreateConnection(); await connection.OpenAsync(); return await connection.QueryFirstOrDefaultAsyncProduct( SELECT * FROM Products WHERE Id Id, new { Id id }); }5.4 事务处理实现原子性操作public async Taskbool ProcessOrderAsync(Order order) { await using var connection _dbContext.CreateConnection(); await connection.OpenAsync(); using var transaction await connection.BeginTransactionAsync(); try { // 插入订单 var orderSql INSERT INTO Orders (OrderDate, TotalAmount) VALUES (OrderDate, TotalAmount); SELECT CAST(SCOPE_IDENTITY() as int); var orderId await connection.ExecuteScalarAsyncint( orderSql, order, transaction: transaction); // 插入订单项 foreach (var item in order.Items) { item.OrderId orderId; var itemSql INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice) VALUES (OrderId, ProductId, Quantity, UnitPrice); await connection.ExecuteAsync( itemSql, item, transaction: transaction); } await transaction.CommitAsync(); return true; } catch { await transaction.RollbackAsync(); throw; } }6. 常见问题与解决方案6.1 列名与属性名不匹配当数据库列名与C#属性名不一致时有几种解决方案使用SQL别名var sql SELECT ProductID AS Id, ProductName AS Name FROM Products;自定义映射public class ProductMap : SqlMapper.ITypeMap { // 实现接口方法... } // 注册自定义映射 SqlMapper.SetTypeMap(typeof(Product), new ProductMap());使用特性标注public class Product { [Column(ProductID)] public int Id { get; set; } [Column(ProductName)] public string Name { get; set; } }6.2 处理NULL值Dapper默认会将数据库NULL映射为C#的null对于值类型public class Product { public int? Rating { get; set; } // 可空类型处理数据库NULL }6.3 大数据集处理对于大型数据集使用缓冲查询// 默认缓冲适合小数据集 var products await connection.QueryAsyncProduct(SELECT * FROM Products); // 非缓冲查询适合大数据集 var unbufferedProducts await connection.QueryUnbufferedAsyncProduct( SELECT * FROM Products);6.4 动态结果处理当返回的列不固定时使用dynamic类型var results await connection.QueryAsync( SELECT Id, Name FROM Products WHERE Price Price, new { Price 50 }); foreach (dynamic product in results) { Console.WriteLine(${product.Id}: {product.Name}); }7. 实际应用案例7.1 分页查询实现实现高效的分页查询public async Task(IEnumerableProduct Data, int TotalCount) GetPagedProductsAsync( int pageNumber, int pageSize, string searchTerm null) { await using var connection _dbContext.CreateConnection(); var whereClause string.IsNullOrEmpty(searchTerm) ? string.Empty : WHERE Name LIKE SearchTerm; var sql $ SELECT * FROM Products {whereClause} ORDER BY Id OFFSET Offset ROWS FETCH NEXT PageSize ROWS ONLY; SELECT COUNT(*) FROM Products {whereClause}; using var multi await connection.QueryMultipleAsync(sql, new { SearchTerm $%{searchTerm}%, Offset (pageNumber - 1) * pageSize, PageSize pageSize }); var products await multi.ReadAsyncProduct(); var totalCount await multi.ReadSingleAsyncint(); return (products, totalCount); }7.2 复杂报表查询处理复杂的报表数据public async TaskSalesReport GetSalesReportAsync(DateTime startDate, DateTime endDate) { await using var connection _dbContext.CreateConnection(); var sql SELECT p.Id, p.Name, SUM(oi.Quantity) AS TotalSold, SUM(oi.Quantity * oi.UnitPrice) AS TotalRevenue FROM Products p JOIN OrderItems oi ON p.Id oi.ProductId JOIN Orders o ON oi.OrderId o.Id WHERE o.OrderDate BETWEEN StartDate AND EndDate GROUP BY p.Id, p.Name ORDER BY TotalRevenue DESC; var reportItems await connection.QueryAsyncSalesReportItem(sql, new { StartDate startDate, EndDate endDate }); var summarySql SELECT COUNT(DISTINCT o.Id) AS TotalOrders, SUM(oi.Quantity * oi.UnitPrice) AS GrandTotal FROM Orders o JOIN OrderItems oi ON o.Id oi.OrderId WHERE o.OrderDate BETWEEN StartDate AND EndDate; var summary await connection.QuerySingleAsyncSalesSummary(summarySql, new { StartDate startDate, EndDate endDate }); return new SalesReport { Items reportItems, Summary summary, StartDate startDate, EndDate endDate }; }7.3 使用Dapper Contrib简化CRUD对于简单的CRUD操作可以使用Dapper.Contrib// 安装Dapper.Contrib dotnet add package Dapper.Contrib // 使用特性标注实体 [Table(Products)] public class Product { [Key] public int Id { get; set; } public string Name { get; set; } public decimal Price { get; set; } } // 简化CRUD操作 public async TaskProduct GetProductAsync(int id) { await using var connection _dbContext.CreateConnection(); return await connection.GetAsyncProduct(id); } public async Taskint InsertProductAsync(Product product) { await using var connection _dbContext.CreateConnection(); return await connection.InsertAsync(product); } public async Taskbool UpdateProductAsync(Product product) { await using var connection _dbContext.CreateConnection(); return await connection.UpdateAsync(product); } public async Taskbool DeleteProductAsync(int id) { await using var connection _dbContext.CreateConnection(); return await connection.DeleteAsync(new Product { Id id }); }