services.cs

微软的行业应用解决方案实例！非常优秀的Windows Mobile开发案例

                    //Get RouteId from column 8
                    _customer.RouteId = Convert.ToInt32(_obj[8]);

                    //Add hydrated Customer Object to Customer Collection
                    _customers.Add(_customer);
                }
                //Return Customer Collection to UI
                return _customers;
            }
            finally
            {
                //Release Command object resources
                _cmd.Dispose();
            }
        }


        /// <summary>
        /// Retrieve a Collection of Order objects
        /// </summary>
        /// <param name="tomorrow"></param>
        /// <param name="orderState"></param>
        /// <returns></returns>
        public static OrderCollection GetOrders(bool tomorrow, OrderState orderState)
        {
            SqlCeCommand _cmd = null;

            try
            {
                Order _order = null;
                object[] _obj = null;
                //Get int value of OrderState enum
                int _orderState = (int)orderState;

                //Create an OrderCollection to be filled with Order objects
                OrderCollection _orders = new OrderCollection();
                //Create a Command object
                _cmd = new SqlCeCommand();

                //If looking for just tomorrow's orders
                if (tomorrow)
                {
                    //Set DateTime object equal to one day in the future
                    DateTime _tomorrow = DateTime.Today.AddDays(1);

                    //If you're looking for all orders
                    if (_orderState == 6)
                    {
                        //Set the SQL statement
                        _cmd.CommandText = "SELECT Orders.OrderId, Customers.Name, Orders.DeliveryDate, OrderState.Name, Orders.CustomerId " +
                                           "FROM Orders, Customers, OrderState " +
                                           "WHERE Orders.CustomerId = Customers.CustomerId " +
                                           "AND Orders.OrderState = OrderState.OrderStateId " +
                                           "AND Orders.DeliveryDate = @DeliveryDate " +
                                           "AND Orders.OrderState > 0";
                        //Add Paramters plus values
                        _cmd.Parameters.AddWithValue("@DeliveryDate", _tomorrow.ToShortDateString());
                    }
                    //If you're looking for specific orders
                    else
                    {
                        //Set the SQL statement
                        _cmd.CommandText = "SELECT Orders.OrderId, Customers.Name, Orders.DeliveryDate, OrderState.Name, Orders.CustomerId " +
                                           "FROM Orders, Customers, OrderState " +
                                           "WHERE Orders.CustomerId = Customers.CustomerId " +
                                           "AND Orders.OrderState = OrderState.OrderStateId " +
                                           "AND Orders.DeliveryDate = @DeliveryDate " +
                                           "AND Orders.OrderState = @OrderState";
                        //Add Paramters plus values
                        _cmd.Parameters.AddWithValue("@DeliveryDate", _tomorrow.ToShortDateString());
                        _cmd.Parameters.AddWithValue("@OrderState", _orderState);
                    }
                }
                //If you're looking for orders from any date
                else
                {
                    //If you're looking for all orders
                    if (_orderState == 6)
                    {
                        //Set the SQL statement
                        _cmd.CommandText = "SELECT Orders.OrderId, Customers.Name, Orders.DeliveryDate, OrderState.Name, Orders.CustomerId " +
                                           "FROM Orders, Customers, OrderState " +
                                           "WHERE Orders.CustomerId = Customers.CustomerId " +
                                           "AND Orders.OrderState = OrderState.OrderStateId " +
                                           "AND Orders.OrderState > 0";
                    }
                    //If you're looking for specific orders
                    else
                    {
                        //Set the SQL statement
                        _cmd.CommandText = "SELECT Orders.OrderId, Customers.Name, Orders.DeliveryDate, OrderState.Name, Orders.CustomerId " +
                                           "FROM Orders, Customers, OrderState " +
                                           "WHERE Orders.CustomerId = Customers.CustomerId " +
                                           "AND Orders.OrderState = OrderState.OrderStateId " +
                                           "AND Orders.OrderState = @OrderState";
                        //Add Paramters plus values
                        _cmd.Parameters.AddWithValue("@OrderState", _orderState);
                    }
                }

                //Execute query and fill ArrayList with results
                ArrayList _list = Data.Database.ReadValues(_cmd, 0);
             
                //Iterate through the ArrayList
                for (int i = 0; i < _list.Count; i++)
                {
                    //Create an Order object
                    _order = new Order();
                    //Set object array equal to row
                    //of objects found in ArrayList
                    _obj = (object[])_list[i];
                    //Get OrderId from column 0
                    _order.OrderId = Convert.ToInt32(_obj[0]);
                    //Check for null
                    if (_obj[1] != DBNull.Value)
                    {
                        //Get CustomerName from column 1
                        _order.CustomerName = Convert.ToString(_obj[1]).Trim();
                    }
                    //Check for null
                    if (_obj[2] != DBNull.Value)
                    {
                        //Get DeliveryDate from column 2
                        _order.DeliveryDate = Convert.ToDateTime(_obj[2]);
                    }
                    //Check for null
                    if (_obj[3] != DBNull.Value)
                    {
                        //Get OrderState from column 3
                        _order.OrderState = Convert.ToString(_obj[3]).Trim();
                    }
                    //Get CustomerId from column 4
                    _order.CustomerId = Convert.ToInt32(_obj[4]);
                    //Add hydrated Customer Object to Customer Collection
                    _orders.Add(_order);
                }
                //Return Orders Collection to UI
                return _orders;
            }
            finally
            {
                //Release Command object resources
                _cmd.Dispose();
            }
        }


        /// <summary>
        /// Retrieve a Collection of OrderDetails objects
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public static OrderDetailsCollection GetOrderDetails(int orderId)
        {
            SqlCeCommand _cmd = null;

            try
            {
                OrderDetails _orderDetails = null;
                object[] _obj = null;

                //Create an OrderDetailsCollection to be filled with OrderDetails objects
                OrderDetailsCollection _orderDetailsCollection = new OrderDetailsCollection();
                //Create a Command object
                _cmd = new SqlCeCommand();
                //Set the SQL statement
                _cmd.CommandText = "SELECT OrderId, OrderDetailId, InventoryId, Quantity " +
                                   "FROM OrderDetails " +
                                   "WHERE OrderId = @OrderId";
                //Add Paramters plus values
                _cmd.Parameters.AddWithValue("@OrderId", orderId);

                //Execute query and fill ArrayList with results
                ArrayList _list = Data.Database.ReadValues(_cmd, 0);

                //Iterate through the ArrayList
                for (int i = 0; i < _list.Count; i++)
                {
                    //Create new OrderDetails object
                    _orderDetails = new OrderDetails();
                    //Set object array equal to row
                    //of objects found in ArrayList
                    _obj = (object[])_list[i];
                    //Get OrderId from column 0
                    _orderDetails.OrderId = Convert.ToInt32(_obj[0]);
                    //Get OrderDetailsId from column 1
                    _orderDetails.OrderDetailsId = Convert.ToInt32(_obj[1]);
                    //Get InventoryId from column 2
                    _orderDetails.InventoryId = Convert.ToInt32(_obj[2]);
                    //Get Quantity from column 3
                    _orderDetails.Quantity = Convert.ToInt32(_obj[3]);
                    //Add hydrated OrderDetails Object to OrderDetails Collection
                    _orderDetailsCollection.Add(_orderDetails);
                }
                //Return Inventory Collection to UI
                return _orderDetailsCollection;
            }
            finally
            {
                //Release Command object resources
                _cmd.Dispose();
            }
        }


        /// <summary>
        /// Retrieve a Collection of Inventory objects
        /// </summary>
        /// <returns></returns>
        public static InventoryCollection GetInventory()
        {
            SqlCeCommand _cmd = null;

            try
            {
                Inventory _inventory = null;
                object[] _obj = null;

                //Create an InventoryCollection to be filled with Inventory objects
                InventoryCollection _inventoryItems = new InventoryCollection();
                //Create a Command object
                _cmd = new SqlCeCommand();
                //Set the SQL statement
                _cmd.CommandText = "SELECT InventoryId, Name, Picture, Price, InStock, DistributionCenter, Bin FROM Inventory";

                //Execute query and fill ArrayList with results
                ArrayList _list = Data.Database.ReadValues(_cmd, 0);

                //Iterate through the ArrayList
                for (int i = 0; i < _list.Count; i++)
                {
                    //Create new Inventory object
                    _inventory = new Inventory();
                    //Set object array equal to row
                    //of objects found in ArrayList
                    _obj = (object[])_list[i];
                    //Get InventoryId from column 0
                    _inventory.InventoryId = Convert.ToInt32(_obj[0]);
                    //Check for null
                    if (_obj[1] != DBNull.Value)
                    {
                        //Get InventoryName from column 1
                        _inventory.InventoryName = Convert.ToString(_obj[1]).Trim();
                    }
                    //Get Picture from column 2
                    _inventory.Picture = (byte[])_obj[2];
                    //Get Price from column 3
                    _inventory.Price = Convert.ToDecimal(_obj[3]);
                    //Get InStock from column 4
                    _inventory.InStock = Convert.ToInt32(_obj[4]);
                    //Get DistributionCenter from column 5
                    _inventory.DistributionCenter = Convert.ToInt32(_obj[5]);
                    //Get Bin from column 6
                    _inventory.Bin = Convert.ToInt32(_obj[6]);
                    //Add hydrated Inventory Object to Inventory Collection
                    _inventoryItems.Add(_inventory);
                }
                //Return Inventory Collection to UI
                return _inventoryItems;
            }
            finally
            {
                //Release Command object resources
                _cmd.Dispose();
            }
        }


        /// <summary>
        /// Retrieve a Collection of Truck objects
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public static TruckCollection GetTruckToLoad(int orderId)
        {
            SqlCeCommand _cmd = null;

            try
            {
                Truck _truck = null;
                object[] _obj = null;

                //Create a TruckCollection to be filled with Truck objects
                TruckCollection _truckCollection = new TruckCollection();
                //Create a Command object
                _cmd = new SqlCeCommand();
                //Set the SQL statement
                _cmd.CommandText = "SELECT Trucks.TruckId, Trucks.LicensePlateStateProvince, Trucks.LicensePlateNumber, Trucks.RouteId " +
                                   "FROM Orders, Customers, Trucks " +
                                   "WHERE Orders.OrderId = @OrderId " +
                                   "AND Orders.CustomerId = Customers.CustomerId " +
                                   "AND Customers.RouteId = Trucks.RouteId";
                //Add Paramters plus values
                _cmd.Parameters.AddWithValue("@OrderId", orderId);

                //Execute query and fill ArrayList with results
                ArrayList _list = Data.Database.ReadValues(_cmd, 0);

                //Iterate through the ArrayList
                for (int i = 0; i < _list.Count; i++)
                {
                    //Create new Truck object
                    _truck = new Truck();
                    //Set object array equal to row
                    //of objects found in ArrayList
                    _obj = (object[])_list[i];
                    //Get TruckId from column 0
                    _truck.TruckId = Convert.ToInt32(_obj[0]);
                    //Check for null
                    if (_obj[1] != DBNull.Value)
                    {
                        //Get LicenseStateProvince from column 1
                        _truck.LicenseStateProvince = Convert.ToString(_obj[1]).Trim();
                    }
                    //Check for null
                    if (_obj[2] != DBNull.Value)
                    {
                        //Get LicenseNumber from column 2
                        _truck.LicenseNumber = Convert.ToString(_obj[2]).Trim();
                    }
                    //Get RouteId from column 3
                    _truck.RouteId = Convert.ToInt32(_obj[3]);
                    //Add hydrated Truck Object to Truck Collection
                    _truckCollection.Add(_truck);
                }
                //Return Truck Collection to UI
                return _truckCollection;
            }
            finally
            {