Query方法也是一个扩展方法,可以被IDbConnection对象调用,可以执行查询语句,并且映射到结果。
可以映射的类型:
参数名 | 参数说明 |
sql | 数据库语句 |
param | 查询参数 |
transaction | 所使用的事务 |
buffered | 是否缓存 |
commandTimeout | 执行超时时间 |
commandType | 语句类型 |
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);
}
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);
}
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);
}