Dapper是什么？
Dpper是一款.Net平台简单（Simple）的对象映射库，并且Dapper拥有着“微型ORM之王”的称号。就速度而言与手写ADO.NET SqlDateReader相同。ORM是Object Relational Mapper的缩写，代表着数据库与编程语言之间的映射。
Dapper的基本特性是通过扩展IDbConnection 接口，并为其提供查询数据库的方法。
Dapper是如何工作的？
将大象装冰箱总共分三步，Dapper也是如此。
- 创建IDBConnection对象（例如：SqlConnection）
- 编写用于CRUD的Sql语句
- 将Sql语句当作参数纯如执行操作的方法
安装
通过NuGet安装：https://www.nuget.org/packages/Dapper
PM> Install-Package Dapper
方法表
Dapper会用多个方法去扩展IDBConnection接口
- Execute
- Query
- QueryFirst
- QueryFirstOrDefault
- QuerySingle
- QuerySingleOrDefault
- QueryMultiple
string sqlInvoices = "SELECT * FROM Invoice;"; string sqlInvoice = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; string sp = "EXEC Invoice_Insert"; using (var connection = My.ConnectionFactory()) { var invoices = connection.Query<Invoice>(sqlInvoices).ToList(); var invoice = connection.QueryFirstOrDefault(sqlInvoice, new {InvoiceID = 1}); var affectedRows = connection.Execute(sp, new { Param1 = "Single_Insert_1" }, commandType: CommandType.StoredProcedure); }
参数表
有多种不同的方式给“查询（例如：select）”和“执行（例如：delete、update、insert）”方法传递参数
- Anonymous
- Dynamic
- List
- String
// 匿名类对象（相对比较常用） var affectedRows = connection.Execute(sql, new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"}, commandType: CommandType.StoredProcedure); // Dynamic DynamicParameters parameter = new DynamicParameters(); parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input); parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input); parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); connection.Execute(sql, new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"}, commandType: CommandType.StoredProcedure); // List connection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList(); // String connection.Query<Invoice>(sql, new {Code = new DbString {Value = "Invoice_1", IsFixedLength = false, Length = 9, IsAnsi = true}}).ToList();
结果集
查询方法的返回值可以映射成多种类型
- Anonymous
- Strongly Typed
- Multi-Mapping
- Multi-Result
- Multi-Type
string sql = "SELECT * FROM Invoice;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var anonymousList = connection.Query(sql).ToList(); var invoices = connection.Query<Invoice>(sql).ToList(); }
Utilities
- Async
- Buffered
- Transaction
- Stored Procedure
// Async connection.QueryAsync<Invoice>(sql) // Buffered connection.Query<Invoice>(sql, buffered: false) // Transaction using (var transaction = connection.BeginTransaction()) { var affectedRows = connection.Execute(sql, new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"}, commandType: CommandType.StoredProcedure, transaction: transaction); transaction.Commit(); } // Stored Procedure var affectedRows = connection.Execute(sql, new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"}, commandType: CommandType.StoredProcedure);
执行非查询语句
描述
你可以从任意实现IDbConnection的类对象中调用Dapper的扩展方法“Execute”。它能够执行一条命令（Command）一次或者多次，并返回受影响的行数。这个方法通常用来执行：
- Stored Procedure
- INSERT statement
- UPDATE statement
- DELETE statement
参数
下面表格中显示了Execute方法的不同参数
| 名称 | 描述 |
|---|---|
| sql | 要执行的sql语句文本 |
| param | command的参数 |
| transaction | 事务 |
| commandTimeout | command超时时间 |
| commandType | command类型 |
示例：执行存储过程
单次
执行存储过程一次
string sql = "EXEC Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure);
My.Result.Show(affectedRows);
}
多次
执行存储过程多次，数组中的每条数据都执行一次
string sql = "EXEC Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql,
new[]
{
new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_1"},
new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_2"}, new {Kind = InvoiceKind.StoreInvoice, Code = "Many_Insert_3"} }, commandType: CommandType.StoredProcedure ); My.Result.Show(affectedRows); }
示例：执行插入
单次
执行插入语句一次
string sql = "INSERT INTO Invoice (Code) Values (@Code);";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql, new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"});
My.Result.Show(affectedRows);
}
多次
执行多次，数组中的每条数据都执行一次
string sql = "INSERT INTO Invoice (Code) Values (@Code);";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql,
new[]
{
new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_1"},
new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_2"}, new {Kind = InvoiceKind.StoreInvoice, Code = "Many_Insert_3"} } ); My.Result.Show(affectedRows); }
示例：执行Update
单次
执行Update语句一次
string sql = "UPDATE Invoice SET Code = @Code WHERE InvoiceID = @InvoiceID";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql, new {InvoiceID = 1, Code = "Single_Update_1"});
My.Result.Show(affectedRows);
}
多次
执行多次，数组中的每条数据都执行一次
string sql = "UPDATE Invoice SET Code = @Code WHERE InvoiceID = @InvoiceID";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql,
new[]
{
new {InvoiceID = 1, Code = "Many_Update_1"},
new {InvoiceID = 2, Code = "Many_Update_2"}, new {InvoiceID = 3, Code = "Many_Update_3"} }); My.Result.Show(affectedRows); }
示例：执行Delete
单次
执行Delete语句一次
string sql = "DELETE FROM Invoice WHERE InvoiceID = @InvoiceID";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql, new {InvoiceID = 1});
My.Result.Show(affectedRows);
}
多次
执行多次，数组中的每条数据都执行一次
string sql = "DELETE FROM Invoice WHERE InvoiceID = @InvoiceID";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql,
new[]
{
new {InvoiceID = 1},
new {InvoiceID = 2}, new {InvoiceID = 3} }); }
Dapper Query查询
介绍
查询方法（Query）是IDbConnection的扩展方法，它可以用来执行查询（select）并映射结果到C#实体（Model、Entity）类
查询结果可以映射成如下类型：
- Anonymous 匿名类型
- Strongly Typed 强类型
- Multi-Mapping (One to One) 多映射 一对一
- Multi-Mapping (One to Many) 多映射 一对多
- Multi-Type 多类型
参数
下面表格中显示了Query方法的不同参数
| 名称 | 描述 |
|---|---|
| sql | 要执行的sql语句文本 |
| param | command的参数 |
| transaction | 事务 |
| buffered | True to buffer readeing the results of the query (default = true). 翻译不来。。。 |
| commandTimeout | command超时时间 |
| commandType | command类型
|
示例 - 匿名查询
Raw SQL query can be executed using Query method and map the result to a dynamic list.
直接执行SQL语句字符串，然后将结果映射成 dynamic类型的List中
string sql = "SELECT * FROM Invoice;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = connection.Query(sql).ToList();
My.Result.Show(invoices);
}
示例 - 强类型查询（最常用）
直接执行SQL语句字符串，然后将结果映射成强类型类型的List中
string sql = "SELECT * FROM Invoice;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = connection.Query<Invoice>(sql).ToList();
My.Result.Show(invoices);
}
示例 - 多映射查询 (One to One)
Raw SQL query can be executed using Query method and map the result to a strongly typed list with a one to one relation.（没太理解）
直接执行SQL语句字符串，然后将结果用一对一的关系映射成强类型类型的List中
string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
sql,
(invoice, invoiceDetail) =>
{
invoice.InvoiceDetail = invoiceDetail;
return invoice; }, splitOn: "InvoiceID") .Distinct() .ToList(); My.Result.Show(invoices); }
示例 - 查询多映射 (One to Many)
Raw SQL query can be executed using Query method and map the result to a strongly typed list with a one to many relations.
直接执行SQL语句字符串，然后将结果用一对多的关系映射成强类型类型的List中
string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceItem AS B ON A.InvoiceID = B.InvoiceID;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoiceDictionary = new Dictionary<int, Invoice>();
var invoices = connection.Query<Invoice, InvoiceItem, Invoice>(
sql,
(invoice, invoiceItem) =>
{
Invoice invoiceEntry;
if (!invoiceDictionary.TryGetValue(invoice.InvoiceID, out invoiceEntry)) { invoiceEntry = invoice; invoiceEntry.Items = new List<InvoiceItem>(); invoiceDictionary.Add(invoiceEntry.InvoiceID, invoiceEntry); } invoiceEntry.Items.Add(invoiceItem); return invoiceEntry; }, splitOn: "InvoiceID") .Distinct() .ToList(); My.Result.Show(invoices); }
示例 - Query Multi-Type
Raw SQL query can be executed using Query method and map the result to a list of different types.
string sql = "SELECT * FROM Invoice;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = new List<Invoice>();
using (var reader = connection.ExecuteReader(sql))
{
var storeInvoiceParser = reader.GetRowParser<StoreInvoice>();
var webInvoiceParser = reader.GetRowParser<WebInvoice>(); while (reader.Read()) { Invoice invoice; switch ((InvoiceKind) reader.GetInt32(reader.GetOrdinal("Kind"))) { case InvoiceKind.StoreInvoice: invoice = storeInvoiceParser(reader); break; case InvoiceKind.WebInvoice: invoice = webInvoiceParser(reader); break; default: throw new Exception(ExceptionMessage.GeneralException); } invoices.Add(invoice); } } My.Result.Show(invoices); }
参考文章：
