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方法的不同參數

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

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

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

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

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！