首页 类库正文

Dapper实用教程

小编 2026-07-12 阅读:411 评论:0
Dapper是什么？　　Dpper是一款.Net平台简单（Simple）的对象映射库，并且Dap...

Dapper是什么？

　　Dpper是一款.Net平台简单（Simple）的对象映射库，并且Dapper拥有着“微型ORM之王”的称号。就速度而言与手写ADO.NET SqlDateReader相同。ORM是Object Relational Mapper的缩写，代表着数据库与编程语言之间的映射。

　　Dapper的基本特性是通过扩展IDbConnection 接口，并为其提供查询数据库的方法。

Dapper是如何工作的？

　　将大象装冰箱总共分三步，Dapper也是如此。

  1. 创建IDBConnection对象（例如：SqlConnection）
  2. 编写用于CRUD的Sql语句
  3. 将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);// DynamicDynamicParameters 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);// Listconnection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList();// Stringconnection.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
// Asyncconnection.QueryAsync<Invoice>(sql)// Bufferedconnection.Query<Invoice>(sql, buffered: false)// Transactionusing (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 Procedurevar 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);}

Dapper实用教程

多次

执行存储过程多次，数组中的每条数据都执行一次

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);}

Dapper实用教程

 

 

示例 - 强类型查询（最常用）

直接执行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);}

Dapper实用教程

示例 - 多映射查询 (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);}

Dapper实用教程

示例 - 查询多映射 (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);}

Dapper实用教程

示例 - 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);}

Dapper实用教程

 

参考文章：

dapper toturial  http://dapper-tutorial.net/dapper

http://dapper-tutorial.net/query

版权声明

本文仅代表作者观点，不代表百度立场。
本文系作者授权百度百家发表，未经许可，不得转载。

上一篇：c#中new的三个作用 下一篇：预处理
相关阅读
热门文章

  • Sequential Monte Carlo Methods (SMC) 序列蒙特卡洛/粒子滤波/Bootstrap Filtering

    Sequential Monte Carlo Methods (SMC) 序列蒙特卡洛/粒子滤波/Bootstrap Filtering
    Problem Statement 我们考虑一个具有马尔可夫性质、非线性、非高斯的状态空间模型(State Space Model)：对于一个时间序列上的观测结果{yt,t∈N}\\{ y_t , t \\in N \\}{yt​,t∈N}，我们认为每个观测结果yty_tyt​的生成依赖于一个无法直接观察的隐变量xt∈{xt,t∈N}x_t \\in \\{x_t , t \\in N \\}xt​∈{xt​,t∈N}，即：p(...

  • 机房智能化温湿度解决方式之POE供电以太网温湿度传感器

    机房智能化温湿度解决方式之POE供电以太网温湿度传感器
    机房智能化温湿度解决方式之POE供电以太网温湿度传感器 北京盈创力和电子科技有限公司 智能型TCP网口温湿度记录仪 北京IP网络温湿度记录仪厂家，北京盈创力和 北京智能型TCP网口温湿度记录仪IP网络温湿度记录仪是一种新型的基于TCP/IP协议双绞线以太网标准温湿度采集模块，利用它可以实现现场温度值、相对湿度值的采集，同时利用其自身的RJ45通信接口可以方便地和机房监控主机或交换机集线器进行联网。 工作于-40℃～85℃工业级带...

  • Hive 系统函数及示例

    Hive 系统函数及示例
    查看所有系统函数 show functions; 函数分类 内置函数【系统函数】 数学函数： floor、round、ceil、cos、log2等 字符串函数： length、reverse、trim、lower、get_json_object、repeat等 收集函数： size 转换函数： cast 日期函数： year、month、datediff、date、date_add等 条件函数： coalesce、case…w...

  • CSRF的原理和防范措施

    CSRF的原理和防范措施
    a)攻击原理：i.用户C访问正常网站A时进行登录，浏览器保存A的cookieii.用户C再访问攻击网站B，网站B上有某个隐藏的链接或者图片标签会自动请求网站A的URL地址,例如表单提交，传指定的参数iii.而攻击网站B在访问网站A的时候，浏览器会自动带上网站A的cookieiv.所以网站A在接收到请求之后可判断当前用户是登录状态，所以...

  • HTTP状态保持的原理

    HTTP状态保持的原理
    a)在用户登录之后，浏览器返回响应的时候会在响应中添加上cookieb)浏览器接收到cookie之后会自动保存c)当用户再次请求同一服务器中的其他网页的时候，浏览器会自动带上之前保存的cookied)服务接收到请求之后可以请 request 对象中取到cookie 判断当前用户是否登录　　Http是无状态的，就是连接时数据互通，关闭后...
最近发表
标签列表

Powered By Z-BlogPHP  Theme By 老白编程