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