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