示例#1
0
        /// <summary>
        /// Return records from Orders, Order Details, Products for OrderID = orderId param
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public SummaryOrderData GetOrderInfo(int orderId)
        {
            var orderInfo = new SummaryOrderData();

            orderInfo.Order    = new Orders();
            orderInfo.Deatails = new OrderDetails();
            orderInfo.Product  = new Products();

            try
            {
                using (var connection = this.providerFactory.CreateConnection())
                {
                    connection.ConnectionString = this.connectionString;
                    connection.Open();

                    SqlCommand commandSelOrder = (SqlCommand)connection.CreateCommand();
                    commandSelOrder.CommandText = "SELECT * FROM [dbo].[Orders] WHERE OrderID = @id";
                    commandSelOrder.Parameters.AddWithValue("@id", orderId);

                    using (IDataReader reader = commandSelOrder.ExecuteReader())
                    {
                        reader.Read();

                        orderInfo.Order.OrderID        = (int)reader[OrdersFields.OrderID];
                        orderInfo.Order.CustomerID     = reader[OrdersFields.CustomerID] as string;
                        orderInfo.Order.EmployeeID     = reader[OrdersFields.EmployeeID] as int?;
                        orderInfo.Order.OrderDate      = reader[OrdersFields.OrderDate] as DateTime?;
                        orderInfo.Order.RequiredDate   = reader[OrdersFields.RequiredDate] as DateTime?;
                        orderInfo.Order.ShippedDate    = reader[OrdersFields.ShippedDate] as DateTime?;
                        orderInfo.Order.ShipVia        = reader[OrdersFields.ShipVia] as int?;
                        orderInfo.Order.Freight        = reader[OrdersFields.Freight] as decimal?;
                        orderInfo.Order.ShipName       = reader[OrdersFields.ShipName] as string;
                        orderInfo.Order.ShipAddress    = reader[OrdersFields.ShipAddress] as string;
                        orderInfo.Order.ShipCity       = reader[OrdersFields.ShipCity] as string;
                        orderInfo.Order.ShipRegion     = reader[OrdersFields.ShipRegion] as string;
                        orderInfo.Order.ShipPostalCode = reader[OrdersFields.ShipPostalCode] as string;
                        orderInfo.Order.ShipCountry    = reader[OrdersFields.ShipCountry] as string;
                    }

                    var commandSelOrderDet = (SqlCommand)connection.CreateCommand();
                    commandSelOrderDet.Parameters.AddWithValue("@id", orderId);
                    commandSelOrderDet.CommandText = "SELECT * FROM [dbo].[Order Details] WHERE OrderID = @id";

                    using (IDataReader reader = commandSelOrderDet.ExecuteReader())
                    {
                        reader.Read();

                        orderInfo.Deatails.OrderID   = orderId;
                        orderInfo.Deatails.ProductID = (int)reader[OrderDetailsFields.ProductID];
                        orderInfo.Deatails.UnitPrice = (decimal)reader[OrderDetailsFields.UnitPrice];
                        orderInfo.Deatails.Quantity  = Convert.ToInt32(reader[OrderDetailsFields.Quantity]);
                        orderInfo.Deatails.Discount  = Convert.ToDouble(reader[OrderDetailsFields.Discount]);
                    }

                    var commandSelProduct = (SqlCommand)connection.CreateCommand();
                    commandSelProduct.CommandText = "SELECT * FROM [dbo].[Products] WHERE ProductID = @prodId";
                    commandSelProduct.Parameters.AddWithValue("@prodId", orderInfo.Deatails.ProductID);

                    using (IDataReader reader = commandSelProduct.ExecuteReader())
                    {
                        reader.Read();

                        orderInfo.Product.ProductID       = (int)reader[ProductsFields.ProductID];
                        orderInfo.Product.ProductName     = (string)reader[ProductsFields.ProductName];
                        orderInfo.Product.SupplierID      = reader[ProductsFields.SupplierID] as int?;
                        orderInfo.Product.CategoryID      = reader[ProductsFields.CategoryID] as int?;
                        orderInfo.Product.QuantityPerUnit = (string)reader[ProductsFields.QuantityPerUnit];
                        orderInfo.Product.UnitPrice       = reader[ProductsFields.UnitPrice] as decimal?;
                        orderInfo.Product.UnitsInStock    = reader[ProductsFields.UnitsInStock] as int?;
                        orderInfo.Product.UnitsOnOrder    = reader[ProductsFields.UnitsOnOrder] as int?;
                        orderInfo.Product.ReorderLevel    = reader[ProductsFields.ReorderLevel] is System.DBNull ? null : (short?)(reader["ReorderLevel"]);
                        orderInfo.Product.Discontinued    = (bool)reader[ProductsFields.Discontinued];
                    }
                }

                return(orderInfo);
            }
            catch (SqlException exc)
            {
                // ...
                return(null);
            }
            catch (InvalidCastException exc)
            {
                // Discontinued
                return(null);
            }
            catch (Exception exc)
            {
                this.UnresolvedExceptions.Add(exc);
                return(null);
            }
        }
示例#2
0
        public void GetOrderInfoTest()
        {
            int id;

            using (var connection = this.providerFactory.CreateConnection())
            {
                connection.ConnectionString = this.connectionString;
                connection.Open();

                SqlCommand command = (SqlCommand)connection.CreateCommand();
                command.CommandText = "SELECT TOP 1 OrderID FROM [dbo].[Orders] ORDER BY OrderID";
                id = (int)command.ExecuteScalar();

                command.CommandText = "SELECT * FROM [dbo].[Orders] WHERE OrderID = @id";
                command.Parameters.AddWithValue("@id", id);
                var orderInfo = new SummaryOrderData();
                orderInfo.Order    = new Orders();
                orderInfo.Deatails = new OrderDetails();
                orderInfo.Product  = new Products();

                using (IDataReader reader = command.ExecuteReader())
                {
                    reader.Read();

                    orderInfo.Order.OrderID        = (int)reader["OrderID"];
                    orderInfo.Order.CustomerID     = reader["CustomerID"] as string;
                    orderInfo.Order.EmployeeID     = reader["EmployeeID"] as int?;
                    orderInfo.Order.OrderDate      = reader["OrderDate"] as DateTime?;
                    orderInfo.Order.RequiredDate   = reader["RequiredDate"] as DateTime?;
                    orderInfo.Order.ShippedDate    = reader["ShippedDate"] as DateTime?;
                    orderInfo.Order.ShipVia        = reader["ShipVia"] as int?;
                    orderInfo.Order.Freight        = reader["Freight"] as decimal?;
                    orderInfo.Order.ShipName       = reader["ShipName"] as string;
                    orderInfo.Order.ShipAddress    = reader["ShipAddress"] as string;
                    orderInfo.Order.ShipCity       = reader["ShipCity"] as string;
                    orderInfo.Order.ShipRegion     = reader["ShipRegion"] as string;
                    orderInfo.Order.ShipPostalCode = reader["ShipPostalCode"] as string;
                    orderInfo.Order.ShipCountry    = reader["ShipCountry"] as string;
                }

                var temp1 = this.repository.GetOrderInfo(id);

                Assert.AreEqual(temp1.Order.OrderID, orderInfo.Order.OrderID);

                command.CommandText = "SELECT * FROM [dbo].[Order Details] WHERE OrderID = @id";

                using (IDataReader reader = command.ExecuteReader())
                {
                    reader.Read();

                    orderInfo.Deatails.OrderID   = id;
                    orderInfo.Deatails.ProductID = (int)reader["ProductID"];
                    orderInfo.Deatails.UnitPrice = (decimal)reader["UnitPrice"];
                    orderInfo.Deatails.Quantity  = Convert.ToInt32(reader["Quantity"]);
                    orderInfo.Deatails.Discount  = Convert.ToDouble(reader["Discount"]);
                }

                Assert.AreEqual(temp1.Deatails.OrderID, orderInfo.Deatails.OrderID);

                command.Parameters.RemoveAt(0);
                command.CommandText = "SELECT * FROM[dbo].[Products] WHERE ProductID = @prodId";
                command.Parameters.AddWithValue("@prodId", orderInfo.Deatails.ProductID);

                using (IDataReader reader = command.ExecuteReader())
                {
                    reader.Read();

                    orderInfo.Product.ProductID       = (int)reader["ProductID"];
                    orderInfo.Product.ProductName     = (string)reader["ProductName"];
                    orderInfo.Product.SupplierID      = reader["SupplierID"] as int?;
                    orderInfo.Product.CategoryID      = reader["CategoryID"] as int?;
                    orderInfo.Product.QuantityPerUnit = (string)reader["QuantityPerUnit"];
                    orderInfo.Product.UnitPrice       = reader["UnitPrice"] as decimal?;
                    orderInfo.Product.UnitsInStock    = reader["UnitsInStock"] as int?;
                    orderInfo.Product.UnitsOnOrder    = reader["UnitsOnOrder"] as int?;
                    orderInfo.Product.ReorderLevel    = reader["ReorderLevel"] is System.DBNull ? null : (short?)(reader["ReorderLevel"]);
                    orderInfo.Product.Discontinued    = (bool)reader["Discontinued"];
                }

                Assert.AreEqual(temp1.Product.ProductID, orderInfo.Product.ProductID);
            }
        }