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

文章图片

示例 - 强类型查询（最常用）直接执行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-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); }