星空雅梦

Dapper官方教程翻译3：Dapper方法之Query

2018年12月20日 16:51:44 Day_and_Night_2017 阅读数：271

 

Query方法描述

Query方法也是一个扩展方法，可以被IDbConnection对象调用，可以执行查询语句，并且映射到结果。

可以映射的类型：

• Anonymous（匿名类型 var）
• Strongly Typed（强类型）
• Multi-Mapping (One to One)（一对一映射）
• Multi-Mapping (One to Many)（一对多映射）
• Multi-Type（多类型）

Query方法可使用的参数

Query方法参数说明

参数名	参数说明
sql	数据库语句
param	查询参数
transaction	所使用的事务
buffered	是否缓存
commandTimeout	执行超时时间
commandType	语句类型

示例：Query执行匿名类型

1.  
   string sql = "SELECT TOP 10 * FROM OrderDetails";
2.  
    
3.  
   using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
4.  
   {
5.  
   //使用var进行类型推断
6.  
   var orderDetail = connection.Query(sql).FirstOrDefault();
7.  
    
8.  
   FiddleHelper.WriteTable(orderDetail);
9.  
   }

示例：Query执行强类型查询

1.  
   string sql = "SELECT TOP 10 * FROM OrderDetails";
2.  
    
3.  
   using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
4.  
   {
5.  
   var orderDetails = connection.Query<OrderDetail>(sql).ToList();
6.  
    
7.  
   Console.WriteLine(orderDetails.Count);
8.  
    
9.  
   FiddleHelper.WriteTable(orderDetails);
10.  
    }

示例：执行一对一关系查询

1.  
   string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;";
2.  
    
3.  
   using (var connection = My.ConnectionFactory())
4.  
   {
5.  
   connection.Open();
6.  
    
7.  
   var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
8.  
   sql,
9.  
   (invoice, invoiceDetail) =>
10.  
    {
11.  
    invoice.InvoiceDetail = invoiceDetail;
12.  
    return invoice;
13.  
    },
14.  
    splitOn: "InvoiceID")
15.  
    .Distinct()
16.  
    .ToList();
17.  
    }

示例：执行一对多关系查询

1.  
   string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";
2.  
    
3.  
   using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
4.  
   {
5.  
   var orderDictionary = new Dictionary<int, Order>();
6.  
    
7.  
    
8.  
   var list = connection.Query<Order, OrderDetail, Order>(
9.  
   sql,
10.  
    (order, orderDetail) =>
11.  
    {
12.  
    Order orderEntry;
13.  
     
14.  
    if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))
15.  
    {
16.  
    orderEntry = order;
17.  
    orderEntry.OrderDetails = new List<OrderDetail>();
18.  
    orderDictionary.Add(orderEntry.OrderID, orderEntry);
19.  
    }
20.  
     
21.  
    orderEntry.OrderDetails.Add(orderDetail);
22.  
    return orderEntry;
23.  
    },
24.  
    splitOn: "OrderID")
25.  
    .Distinct()
26.  
    .ToList();
27.  
     
28.  
    Console.WriteLine(list.Count);
29.  
     
30.  
    FiddleHelper.WriteTable(list);
31.  
    FiddleHelper.WriteTable(list.First().OrderDetails);
32.  
    }

示例：Query执行多类型

1.  
   string sql = "SELECT * FROM Invoice;";
2.  
    
3.  
   using (var connection = My.ConnectionFactory())
4.  
   {
5.  
   connection.Open();
6.  
    
7.  
   var invoices = new List<Invoice>();
8.  
    
9.  
   using (var reader = connection.ExecuteReader(sql))
10.  
    {
11.  
    var storeInvoiceParser = reader.GetRowParser<StoreInvoice>();
12.  
    var webInvoiceParser = reader.GetRowParser<WebInvoice>();
13.  
     
14.  
    while (reader.Read())
15.  
    {
16.  
    Invoice invoice;
17.  
     
18.  
    switch ((InvoiceKind) reader.GetInt32(reader.GetOrdinal("Kind")))
19.  
    {
20.  
    case InvoiceKind.StoreInvoice:
21.  
    invoice = storeInvoiceParser(reader);
22.  
    break;
23.  
    case InvoiceKind.WebInvoice:
24.  
    invoice = webInvoiceParser(reader);
25.  
    break;
26.  
    default:
27.  
    throw new Exception(ExceptionMessage.GeneralException);
28.  
    }
29.  
     
30.  
    invoices.Add(invoice);
31.  
    }
32.  
    }
33.  
     
34.  
    My.Result.Show(invoices);
35.  
    }