Dapper入门教程——Dapper Query查询

欠伸展肢体,吟咏心自愉。这篇文章主要讲述Dapper入门教程——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入门教程——Dapper Query查询

文章图片

 
 
示例 - 强类型查询(最常用)直接执行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入门教程——Dapper Query查询

文章图片

示例 - 多映射查询 (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入门教程——Dapper Query查询

文章图片

示例 - 查询多映射 (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入门教程——Dapper Query查询

文章图片

示例 - Query Multi-TypeRaw 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 Query查询

文章图片

 
  参考文章:
【Dapper入门教程——Dapper Query查询】http://dapper-tutorial.net/query

    推荐阅读