Dapper-ORM操作手册¶

1. Dapper 介绍¶

Dapper是.NET的简单对象映射器，在速度方面拥有微型ORM之王的称号，几乎与使用原始 ADO.NET 数据读取器一样快。ORM是一个对象关系映射器，负责数据库和编程语言之间的映射。

Dapper 通过提供有用的扩展方法来查询数据库来扩展 IDbConnection。

Dapper 是一个简易的微型ORM, 实现了IDbConnection的扩展方法, 为开发者提供了更多的操作方法, 但是开发者还需要根据数据库编写 SQL 语句, 因此 Dapper 不需要做数据库提供程序的适配。神通数据库原生支持 Dapper 框架，您可用 Dapper 与神通数据库提供程序配合使用。

2. Dapper 是如何工作的？¶

这是一个三步过程。

创建一个 IDbConnection 对象。
编写查询以执行 CRUD 操作。
在 Execute 方法中将查询作为参数传递。

3. 依赖安装¶

Dapper 通过 NuGet 安装：https://www.nuget.org/packages/Dapper

Oscar Provider 通过 NuGet 安装：https://www.nuget.org/packages/Oscar.Data.SqlClient

$ dotnet add package Dapper
$ dotnet add package Oscar.Data.SqlClient

4. 快速开始DEMO¶

创建控制台应用

$ dotnet new console -n test
$ cd test

安装依赖

$ dotnet add package Dapper
$ dotnet add package Oscar.Data.SqlClient

在文件 Program.cs

using System.Data.OscarClient;
using Dapper;

using(var connect = new OscarConnection("Server=10.1.1.66;Port=2003;UserId=SYSDBA;  Password=szoscar55;Database=OSRDB;"))
{
    connect.Open();
    var date = connect.Query("select now()");
    foreach(var item in date)
        Console.WriteLine(item);
}

运行

$ dotnet run

输出:

{DapperRow, NOW = '2022/4/13 18:34:13 +08:00'}

5. Dapper 方法¶

Dapper将使用多种方法扩展您的 IDbConnection 接口:

Execute
Query
QueryFirst
QueryFirstOrDefault
QuerySingle
QuerySingleOrDefault
QueryMultiple

示例

string sqlOrderDetails = "SELECT TOP 5 * FROM OrderDetails;";
string sqlOrderDetail = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";
string sqlCustomerInsert = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

using (var connection = new OscarConnection("Server=10.1.1.66;Port=2003;UserId=SYSDBA;  Password=szoscar55;Database=OSRDB;"))
{
    var orderDetails = connection.Query<OrderDetail>(sqlOrderDetails).ToList();
    var orderDetail = connection.QueryFirstOrDefault<OrderDetail>(sqlOrderDetail, new   {OrderDetailID = 1});
    var affectedRows = connection.Execute(sqlCustomerInsert,  new {CustomerName = "Mark"});

    Console.WriteLine(orderDetails.Count);
    Console.WriteLine(affectedRows);

    FiddleHelper.WriteTable(orderDetails);
    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}

6. Dapper 参数¶

执行和查询方法可以通过多种不同的方式使用参数:

Anonymous
Dynamic
List
String

示例

// Anonymous
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,
    parameter,
    commandType: CommandType.StoredProcedure);

int rowCount = parameter.Get<int>("@RowCount");

// 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();

7. Dapper 结果¶

查询方法返回的结果可以映射到多种类型:

Anonymous
Strongly Typed
Multi-Mapping
Multi-Result
Multi-Type

示例

string sqlOrderDetails = "SELECT TOP 10 * FROM OrderDetails;";

using (var connection = new OscarConnection("Server=10.1.1.66;Port=2003;UserId=SYSDBA;  Password=szoscar55;Database=OSRDB;"))
{
    var anonymousList = connection.Query(sqlOrderDetails).ToList();
    var orderDetails = connection.Query<OrderDetail>(sqlOrderDetails).ToList();

    Console.WriteLine(anonymousList.Count);
    Console.WriteLine(orderDetails.Count);

    FiddleHelper.WriteTable(orderDetails);

    FiddleHelper.WriteTable(connection.Query(sqlOrderDetails).FirstOrDefault());
}

8. Dapper 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);