Dapper在C#中应用详解

摘要：Dapper是一个轻量级的ORM(Object Relational Mapper)工具，专为.NET框架设计，不过个人还是喜欢SQLSugar。它提供了一种高速的，在一定程度上接近手写SQL语句性能的数据库访问方法。它因其简单性和灵活性而广受欢迎，非常适合需

Dapper是一个轻量级的ORM(Object Relational Mapper)工具，专为.NET框架设计，不过个人还是喜欢SQLSugar。它提供了一种高速的，在一定程度上接近手写SQL语句性能的数据库访问方法。它因其简单性和灵活性而广受欢迎，非常适合需要对性能有高要求但又不希望处理复杂的ORM框架，比如Entity Framework的场景。

Dapper是一个单文件库，集成进项目非常简单，不会像其他大型ORM框架那样增加额外的负担。

Dapper直接使用ADO.NET，即其底层仅是对ADO.NET的封装，这保证了非常高的执行效率，几乎与直接使用raw ADO.NET相当。

Dapper的API设计简洁易懂，而且它只做必要的工作，绝不过度抽象化。开发者能够在需要时直接编写Sql查询，获取完整的功能控制。

由于直接编写SQL语句，Dapper能直接利用数据库特性例如存储过程、复杂查询等，可维护性高，也能与其他ORM框架并行使用。

在开始使用Dapper之前，首先需要确保你的项目中有Dapper这个库。可以使用NuGet包管理器来安装：

Install-PackAge Dapper

using System.Data.SqlClient;// Connection stringstring connectionString = "Server=localhost;Database=testdb;User Id=sa;Password=123;";// 创建 SQL 连接using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open;// 这里可以进行数据库操作}

Dapper最常用的操作之一是查询数据库。这包括简单的SELECT语句以及复杂的多表JOIN。

using Dapper;using System.Collections.Generic;// 定义数据模型public class Person{public int Id { get; set; }public string Name { get; set; }public int Age { get; set; }}// 查询数据string sqlQuery = "SELECT * FROM Person";using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open;IEnumerable people = connection.Query(sqlQuery);foreach (var person in people){Console.WriteLine($"ID: {person.Id}, Name: {person.Name}, Age: {person.Age}");}}

你还可以使用Dapper进行插入操作：

string insertQuery = "INSERT INTO Person (Name, Age) VALUES (@Name, @Age)";using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open;var affectedRows = connection.Execute(insertQuery, new { Name = "John Doe", Age = 30 });Console.WriteLine($"Rows inserted: {affectedRows}");}

更新数据库记录同样简单：

string updateQuery = "UPDATE Person SET Age = @Age WHERE Name = @Name";using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open;var affectedRows = connection.Execute(updateQuery, new { Name = "John Doe", Age = 31 });Console.WriteLine($"Rows updated: {affectedRows}");}

删除操作

删除操作也非常直观：

string deleteQuery = "DELETE FROM Person WHERE Name = @Name";using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open;var affectedRows = connection.Execute(deleteQuery, new { Name = "John Doe" });Console.WriteLine($"Rows deleted: {affectedRows}");}多结果集查询CREATE PROCEDURE GetPersonAndOrders@PersonId INTASBEGINSELECT Id, NameFROM PersonWHERE Id = @PersonIdSELECT Id, PersonId, AmountFROM [Order]WHERE PersonId = @PersonIdEND

有时候，我们可能需要从一个查询中获取多个结果集，Dapper可以轻松处理这种情况。

using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open;var personIdParameter = new { PersonId = 1 }; // 假设要查询的 PersonId 为 1using (var multi = connection.QueryMultiple("GetPersonAndOrders", personIdParameter, commandType: CommandType.StoredProcedure)){var person = multi.Read.FirstOrDefault;var orders = multi.Read.ToList;if (person != null){Console.WriteLine($"Person: {person.Name}");foreach (var order in orders){Console.WriteLine($"Order ID: {order.OrderId}, Amount: {order.Amount}");}}else{Console.WriteLine("No person found with the given ID.");}}}

存储过程CREATE PROCEDURE GetPersonById@Id INTASBEGINSELECT Id, Name, AgeFROM PersonWHERE Id = @Id;END

Dapper直接支持调用存储过程，这使得它在使用数据库的内置功能时非常方便。

string storedProcedure = "GetPersonById";var parameters = new { Id = 1 }; // 假设要查询的人员 ID 为1using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open;try{var person = connection.QueryFirstOrDefault(storedProcedure, parameters, commandType: CommandType.StoredProcedure);if (person != null){Console.WriteLine($"Person: {person.Name}, Age: {person.Age}");}else{Console.WriteLine("No person found with the given ID.");}}catch (SqlException ex){Console.WriteLine($"SQL Error: {ex.Message}");}catch (Exception ex){Console.WriteLine($"An error occurred: {ex.Message}");}}

事务支持

Dapper支持数据库事务，可以在多个操作中维护完整性和一致性。

using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open;using (var transaction = connection.BeginTransaction){try{string insertPersonQuery = "INSERT INTO Person (Name, Age) VALUES (@Name, @Age)";string insertOrderQuery = "INSERT INTO [Order] (PersonId, Amount) VALUES (@PersonId, @Amount)";connection.Execute(insertPersonQuery, new { Name = "Jane Doe", Age = 27 }, transaction);connection.Execute(insertOrderQuery, new { PersonId = 2, Amount = 200 }, transaction);transaction.Commit;Console.WriteLine("Transaction committed successfully.");}catch (Exception ex){transaction.Rollback;Console.WriteLine($"Transaction rolled back: {ex.Message}");}}}映射一对多关系public class Person{public int Id { get; set; }public string Name { get; set; }public int Age { get; set; }public List Orders { get; set; }public Person{Orders = new List;}}

Dapper允许我们在一次查询中映射父/子关系，非常适用于表之间有关系的情况。

string sql = @"SELECT * FROM Person pLEFT JOIN [Order] o ON p.Id = o.PersonIdWHERE p.Id = @Id";using (SqlConnection connection = new SqlConnection(connectionString)){var lookup = new Dictionary;connection.Query(sql, (person, order) =>{if (!lookup.TryGetValue(person.Id, out var foundPerson)){foundPerson = person;foundPerson.Orders = new List;lookup.Add(foundPerson.Id, foundPerson);}if (order != null){foundPerson.Orders.Add(order);}return foundPerson;}, new { Id = 1 });var result = lookup.Values.FirstOrDefault;Console.WriteLine($"Person: {result.Name}");foreach (var order in result.Orders){Console.WriteLine($"Order ID: {order.OrderId}, Amount: {order.Amount}");}}