Dapper官方教程翻译3:Dapper方法之Query(转)
Dapper官方教程翻译3:Dapper方法之Query
Query方法描述
Query方法也是一个扩展方法,可以被IDbConnection对象调用,可以执行查询语句,并且映射到结果。
可以映射的类型:
- Anonymous(匿名类型 var)
- Strongly Typed(强类型)
- Multi-Mapping (One to One)(一对一映射)
- Multi-Mapping (One to Many)(一对多映射)
- Multi-Type(多类型)
Query方法可使用的参数
参数名 | 参数说明 |
sql | 数据库语句 |
param | 查询参数 |
transaction | 所使用的事务 |
buffered | 是否缓存 |
commandTimeout | 执行超时时间 |
commandType | 语句类型 |
示例:Query执行匿名类型
-
string sql = "SELECT TOP 10 * FROM OrderDetails";
-
-
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
-
{
-
//使用var进行类型推断
-
var orderDetail = connection.Query(sql).FirstOrDefault();
-
-
FiddleHelper.WriteTable(orderDetail);
-
}
示例:Query执行强类型查询
-
string sql = "SELECT TOP 10 * FROM OrderDetails";
-
-
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
-
{
-
var orderDetails = connection.Query<OrderDetail>(sql).ToList();
-
-
Console.WriteLine(orderDetails.Count);
-
-
FiddleHelper.WriteTable(orderDetails);
-
}
示例:执行一对一关系查询
-
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();
-
}
示例:执行一对多关系查询
-
string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";
-
-
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
-
{
-
var orderDictionary = new Dictionary<int, Order>();
-
-
-
var list = connection.Query<Order, OrderDetail, Order>(
-
sql,
-
(order, orderDetail) =>
-
{
-
Order orderEntry;
-
-
if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))
-
{
-
orderEntry = order;
-
orderEntry.OrderDetails = new List<OrderDetail>();
-
orderDictionary.Add(orderEntry.OrderID, orderEntry);
-
}
-
-
orderEntry.OrderDetails.Add(orderDetail);
-
return orderEntry;
-
},
-
splitOn: "OrderID")
-
.Distinct()
-
.ToList();
-
-
Console.WriteLine(list.Count);
-
-
FiddleHelper.WriteTable(list);
-
FiddleHelper.WriteTable(list.First().OrderDetails);
-
}
示例:Query执行多类型
-
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);
-
}