예제 #1
0
 public IEnumerable <Employee> GetEmployees()
 {
     using (var r = CreateTextCommand("SELECT * FROM Employees").ExecuteReader())
         while (r?.Read() ?? false)
         {
             yield return(OledbOrm.CreateEmployee(r));
         }
 }
예제 #2
0
 public IEnumerable <Shipper> GetShippers()
 {
     using (var r = CreateTextCommand("SELECT * FROM Shippers").ExecuteReader())
         while (r?.Read() ?? false)
         {
             yield return(OledbOrm.CreateShipper(r));
         }
 }
예제 #3
0
 public IEnumerable <Category> GetCategories()
 {
     using (var r = CreateTextCommand("SELECT * FROM Categories").ExecuteReader())
         while (r?.Read() ?? false)
         {
             yield return(OledbOrm.CreateCategory(r));
         }
 }
예제 #4
0
        /// <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);
        }
예제 #5
0
        /// <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);
        }
예제 #6
0
        /// <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);
        }