public FullOrderInformation GetOrderInformation(int orderID)
{
var info = new FullOrderInformation();
using (var connection = this.factory.CreateConnection())
{
connection.ConnectionString = this.connectionString;
var command = (SqlCommand)connection.CreateCommand();
command.CommandText = "SELECT * FROM Northwind.dbo.Orders as O " +
"FULL JOIN Northwind.dbo.[Order Details] as OD " +
"ON O.OrderID = OD.OrderID " +
"FULL JOIN Northwind.dbo.Products as P " +
"ON OD.ProductID = P.ProductID " +
"WHERE O.OrderID = @orderID; ";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@orderID", orderID);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var tmpProduct = new Product();
var tmpOrderDetails = new OrderDetails();
info.Order.OrderID = reader["OrderID"] as int?;
info.Order.CustomerID = reader["CustomerID"] as string;
info.Order.EmployeeID = reader["EmployeeID"] as int?;
info.Order.OrderDate = reader["OrderDate"] as DateTime?;
info.Order.RequiredDate = reader["RequiredDate"] as DateTime?;
info.Order.ShippedDate = reader["ShippedDate"] as DateTime?;
info.Order.ShipVia = reader["ShipVia"] as int?;
info.Order.Freight = reader["Freight"] as decimal?;
info.Order.ShipName = reader["ShipName"] as string;
info.Order.ShipAddress = reader["ShipAddress"] as string;
info.Order.ShipCity = reader["ShipCity"] as string;
info.Order.ShipRegion = reader["ShipRegion"] as string;
info.Order.ShipPostalCode = reader["ShipPostalCode"] as string;
info.Order.ShipCountry = reader["ShipCountry"] as string;
tmpOrderDetails.OrderID = (int)reader["OrderID"];
tmpOrderDetails.ProductID = reader["ProductID"] as int?;
tmpOrderDetails.ProductName = reader["ProductName"] as string;
tmpOrderDetails.UnitPrice = reader["UnitPrice"] as decimal?;
tmpOrderDetails.Quantity = reader["Quantity"] as short?;
tmpOrderDetails.Discount = reader["Discount"] as float?;
tmpProduct.ProductID = reader["ProductID"] as int?;
tmpProduct.ProductName = reader["ProductName"] as string;
tmpProduct.UnitPrice = reader["UnitPrice"] as double?;
info.Products.Add(tmpProduct);
info.OrderDetails.Add(tmpOrderDetails);
}
}
}
return(info);
}
public List <FullOrderInformation> GetOrderInformation(int orderID)
{
var info = new List <FullOrderInformation>();
using (var connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
var command = (SqlCommand)connection.CreateCommand();
command.CommandText = "SELECT * FROM Northwind.dbo.Orders as O " +
"JOIN Northwind.dbo.[Order Details] as OD " +
"ON O.OrderID = OD.OrderID " +
"JOIN Northwind.dbo.Products as P " +
"ON OD.ProductID = P.ProductID " +
"WHERE O.OrderID = @orderID; ";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@orderID", orderID);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var tmpOrder = new Order();
var tmpProduct = new Product();
var tmpOrderDetails = new OrderDetails();
tmpOrder.OrderID = reader["OrderID"] as int?;
tmpOrder.CustomerID = reader["CustomerID"] as string;
tmpOrder.EmployeeID = reader["EmployeeID"] as int?;
tmpOrder.OrderDate = reader["OrderDate"] as DateTime?;
tmpOrder.ShippedDate = reader["ShippedDate"] as DateTime?;
tmpOrder.ShipAddress = reader["ShipAddress"] as string;
tmpProduct.ProductID = reader["ProductID"] as int?;
tmpProduct.ProductName = reader["ProductName"] as string;
tmpProduct.UnitPrice = reader["UnitPrice"] as double?;
tmpOrderDetails.Quantity = reader["Quantity"] as int?;
tmpOrderDetails.UnitPrice = reader["UnitPrice"] as double?;
tmpOrderDetails.Discount = reader["Discount"] as double?;
var tmpInformation = new FullOrderInformation();
tmpInformation.Order = tmpOrder;
tmpInformation.Product = tmpProduct;
tmpInformation.OrderDetails = tmpOrderDetails;
info.Add(tmpInformation);
}
}
}
return(info);
}
