Dapper官方教程翻译3:Dapper方法之Query(转)

韩善
2023-12-01

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

Query方法描述

 

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

可以映射的类型:

 

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.  
    }

 

转载于:https://www.cnblogs.com/LiZhongZhongY/p/10991643.html

 类似资料: