public IEnumerable <Employee> GetEmployees()
{
using (var r = CreateTextCommand("SELECT * FROM Employees").ExecuteReader())
while (r?.Read() ?? false)
{
yield return(OledbOrm.CreateEmployee(r));
}
}
public IEnumerable <Shipper> GetShippers()
{
using (var r = CreateTextCommand("SELECT * FROM Shippers").ExecuteReader())
while (r?.Read() ?? false)
{
yield return(OledbOrm.CreateShipper(r));
}
}
public IEnumerable <Category> GetCategories()
{
using (var r = CreateTextCommand("SELECT * FROM Categories").ExecuteReader())
while (r?.Read() ?? false)
{
yield return(OledbOrm.CreateCategory(r));
}
}
/// <summary>
/// Get Category Sales by building a dynamic SQL via SqlBinder. The *real* meat of this method is in the .Sql file.
/// </summary>
public IEnumerable <CategorySale> GetCategorySales(int[] categoryIds = null, DateTime?fromDate = null, DateTime?toDate = null)
{
var query = new DbQuery(_connection, GetSqlBinderScript("CategorySales.sql"));
query.SetCondition("categoryIds", categoryIds);
query.SetConditionRange("shippingDates", fromDate, toDate);
using (var r = query.CreateCommand().ExecuteReader())
while (r.Read())
{
yield return(OledbOrm.CreateCategorySale(r));
}
TraceQuery("Category Sales", query);
}
/// <summary>
/// Get Orders by building a dynamic SQL via SqlBinder. The *real* meat of this method is in the .Sql file.
/// </summary>
public IEnumerable <Order> GetOrders(int?orderId = null,
int[] productIds = null,
string[] customerIds = null,
int[] employeeIds = null,
int[] shipperIds = null,
DateTime?orderDateFrom = null, DateTime?orderDateTo = null,
DateTime?reqDateFrom = null, DateTime?reqDateTo = null,
DateTime?shipDateFrom = null, DateTime?shipDateTo = null,
decimal?freightFrom = null, decimal?freightTo = null,
string shipCity = null,
string shipCountry = null)
{
var query = new DbQuery(_connection, GetSqlBinderScript("Orders.sql"));
if (orderId.HasValue)
{
query.SetCondition("orderId", orderId);
}
else
{
query.SetCondition("productIds", productIds);
query.SetCondition("customerIds", customerIds);
query.SetCondition("employeeIds", employeeIds);
query.SetCondition("shipperIds", shipperIds);
query.SetConditionRange("freight", freightFrom, freightTo);
query.SetConditionRange("orderDate", orderDateFrom, orderDateTo);
query.SetConditionRange("reqDate", reqDateFrom, reqDateTo);
query.SetConditionRange("shipDate", shipDateFrom, shipDateTo);
query.SetCondition("shipCity", shipCity, ignoreIfNull: true);
query.SetCondition("shipCountry", shipCountry, ignoreIfNull: true);
}
using (var r = query.CreateCommand().ExecuteReader())
while (r.Read())
{
yield return(OledbOrm.CreateOrder(r));
}
TraceQuery("Orders", query);
}
/// <summary>
/// Get Products by building a dynamic SQL via SqlBinder. The *real* meat of this method is in the .Sql file.
/// </summary>
public IEnumerable <Product> GetProducts(decimal?productId = null,
string productName = null,
int[] supplierIds = null,
int[] categoryIds = null,
decimal?unitPriceFrom = null,
decimal?unitPriceTo = null,
bool?isDiscontinued = null,
bool priceGreaterThanAvg = false)
{
var query = new DbQuery(_connection, GetSqlBinderScript("Products.sql"));
if (productId != null)
{
query.SetCondition("productId", productId);
}
else
{
query.SetCondition("productName", productName, StringOperator.Contains);
query.SetCondition("supplierIds", supplierIds);
query.SetCondition("categoryIds", categoryIds);
query.SetConditionRange("unitPrice", unitPriceFrom, unitPriceTo);
query.SetCondition("isDiscontinued", isDiscontinued, ignoreIfNull: true);
if (priceGreaterThanAvg)
{
query.DefineVariable("priceGreaterThanAvg", "> (SELECT AVG(UnitPrice) From Products)");
}
}
using (var r = query.CreateCommand().ExecuteReader())
while (r.Read())
{
yield return(OledbOrm.CreateProduct(r));
}
TraceQuery("Products", query);
}
