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); }