-
-
Notifications
You must be signed in to change notification settings - Fork 10
2.2. Read(command, params) connection extensions
Takes command with optional parameters and builds an enumeration generator for your database command results.
That means that actual database operation will not start until enumeration also starts, typically by foreach statement (or for example some of the LINQ statements such as ToList, ToArray, Count, Sum, etc).
This approach avoids unnecessary iteration over the result sets.
Read extension also takes one or more type parameters - to determine the type of generated results.
Type parameters can be either:
- Basic (built-in) types (
int,string, etc) - Class type -
class - Record type
record - Tuples, and named tuple type -
(int, string, DateTime, bool),(int Field1, string Field2), etc
foreach(var (orderId, productId, quantity) in connection.Read<int, int, int>("SELECT TOP 10 OrderId, ProductId, Quantity FROM OrderDetails"))
{
Console.WriteLine($"order={orderId}, product={productId} with quantity {quantity}");
}Read extension maps first basic type parameter int to first and only database result.
Important notes:
Besides using only one type parameter to map a single value - Read extension can have up to 12 basic type parameters.
public class OrderDetail
{
public int OrderDetailID { get; set; }
public int OrderID { get; set; }
public int ProductID { get; set; }
public int Quantity { get; set; }
}
//...
var sql = "SELECT TOP 10 * FROM OrderDetails";
var orderDetail = connection.Read<OrderDetail>(sql);
foreach (var d in orderDetails)
{
Console.WriteLine($"{d.OrderDetailID}, {d.OrderID}, {d.ProductID} {d.Quantity}");
}This example will map database rows to instances of the class OrderDetail.
Important: Mapping logic will match fields by name.
Besides using only one type parameter to map a single class instance - Read extension can have up to 12 type parameters.
public record OrderDetail(int OrderDetailID, int OrderID, int ProductID, int Quantity);
//...
var sql = "SELECT TOP 10 * FROM OrderDetails";
var orderDetail = connection.Read<OrderDetail>(sql);
foreach (var d in orderDetails)
{
Console.WriteLine($"{d.OrderDetailID}, {d.OrderID}, {d.ProductID} {d.Quantity}");
}This example will map database rows to record instances OrderDetail.
Important: Mapping logic will match fields by name.
Besides using only one type parameter to map a single record instance - Read extension can have up to 12 type parameters.
var sql = "SELECT TOP 10 * FROM OrderDetails";
var orderDetails = connection.Read<(int OrderDetailID, int OrderID, int ProductID, int Quantity)>(sql);
foreach (var d in orderDetails)
{
Console.WriteLine($"{d.OrderDetailID}, {d.OrderID}, {d.ProductID} {d.Quantity}");
}This example will map database rows to named tuple instances (int OrderDetailID, int OrderID, int ProductID, int Quantity).
Important: Mapping logic will match fields by name.
Besides using only one type parameter to map a single tuple instance - Read extension can have up to 12 type parameters.
var name = connection.Read<string>("SELECT CustomerName FROM Customers where CustomerID = @p1;", 1).Single();
Console.WriteLine(name); // Alfreds Futterkisteconnection.Read<OrderDetail>("SELECT TOP 10 * FROM OrderDetails where ProductID = @productId", ("productId", 51));connection.Read<OrderDetail>("SELECT TOP 10 * FROM OrderDetails where ProductID = @productId", ("productId", 51, DbType.Int32));connection.Read<OrderDetail>("SELECT TOP 10 * FROM OrderDetails where ProductID = @productId", ("productId", 51, SqlDbType.Int));