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(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( 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; var invoices = connection.Query( sql, (invoice, invoiceItem) => { Invoice invoiceEntry; if (!invoiceDictionary.TryGetValue(invoice.InvoiceID, out invoiceEntry)) { invoiceEntry = invoice; invoiceEntry.Items = new List; 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; using (var reader = connection.ExecuteReader(sql)) { var storeInvoiceParser = reader.GetRowParser; var webInvoiceParser = reader.GetRowParser; 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);}