public int CreateNewOrder(Order order)
{
string queryString = @"insert into Northwind.Orders(CustomerID, EmployeeID, OrderDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipPostalCode, ShipCountry) OUTPUT Inserted.OrderID values (@CustomerID, @EmployeeID, @OrderDate, @ShipVia, @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipPostalCode, @ShipCountry);";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(queryString, connection))
{
command.CommandText = queryString;
command.Parameters.Add(new SqlParameter("@CustomerID", order.CustomerID));
command.Parameters.Add(new SqlParameter("@EmployeeID", order.EmployeeID));
command.Parameters.Add(new SqlParameter("@OrderDate", order.OrderDate));
command.Parameters.Add(new SqlParameter("@ShipVia", order.ShipVia));
command.Parameters.Add(new SqlParameter("@Freight", order.Freight));
command.Parameters.Add(new SqlParameter("@ShipName", order.ShipName));
command.Parameters.Add(new SqlParameter("@ShipAddress", order.ShipAddress));
command.Parameters.Add(new SqlParameter("@ShipCity", order.ShipCity));
command.Parameters.Add(new SqlParameter("@ShipPostalCode", order.ShipPostalCode));
command.Parameters.Add(new SqlParameter("@ShipCountry", order.ShipCountry));
order.OrderID = (int)command.ExecuteScalar();
}
}
return order.OrderID;
}
public List<Order> GetAllOrders()
{
var result = new List<Order>();
string queryString = "select * from Northwind.Orders;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
var order = new Order();
order.OrderID = Convert.ToInt32(reader["OrderID"]);
order.CustomerID = Convert.ToString(reader["CustomerID"]);
order.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
order.OrderDate = reader.IsDBNull(reader.GetOrdinal("OrderDate")) ? (DateTime?)null : Convert.ToDateTime(reader["OrderDate"]);
order.RequiredDate = reader.IsDBNull(reader.GetOrdinal("RequiredDate")) ? (DateTime?)null : Convert.ToDateTime(reader["RequiredDate"]);
order.ShippedDate = reader.IsDBNull(reader.GetOrdinal("ShippedDate")) ? (DateTime?)null : Convert.ToDateTime(reader["ShippedDate"]);
order.ShipVia = Convert.ToInt32(reader["ShipVia"]);
order.Freight = Convert.ToDecimal(reader["Freight"]);
order.ShipName = Convert.ToString(reader["ShipName"]);
order.ShipAddress = Convert.ToString(reader["ShipAddress"]);
order.ShipCity = Convert.ToString(reader["ShipCity"]);
order.ShipRegion = Convert.ToString(reader["ShipRegion"]);
order.ShipPostalCode = Convert.ToString(reader["ShipPostalCode"]);
order.ShipCountry = Convert.ToString(reader["ShipCountry"]);
result.Add(order);
}
}
finally
{
reader.Close();
}
}
return result;
}
public Order GetOrderDetails(int id)
{
var order = new Order();
string queryString = "select * from Northwind.Orders where OrderID = @OrderID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.Add(new SqlParameter("@OrderID", id));
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
order.OrderID = Convert.ToInt32(reader["OrderID"]);
order.CustomerID = Convert.ToString(reader["CustomerID"]);
order.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
order.OrderDate = reader.IsDBNull(reader.GetOrdinal("OrderDate")) ? (DateTime?)null : Convert.ToDateTime(reader["OrderDate"]);
order.RequiredDate = reader.IsDBNull(reader.GetOrdinal("RequiredDate")) ? (DateTime?)null : Convert.ToDateTime(reader["RequiredDate"]);
order.ShippedDate = reader.IsDBNull(reader.GetOrdinal("ShippedDate")) ? (DateTime?)null : Convert.ToDateTime(reader["ShippedDate"]);
order.ShipVia = Convert.ToInt32(reader["ShipVia"]);
order.Freight = Convert.ToDecimal(reader["Freight"]);
order.ShipName = Convert.ToString(reader["ShipName"]);
order.ShipAddress = Convert.ToString(reader["ShipAddress"]);
order.ShipCity = Convert.ToString(reader["ShipCity"]);
order.ShipRegion = Convert.ToString(reader["ShipRegion"]);
order.ShipPostalCode = Convert.ToString(reader["ShipPostalCode"]);
order.ShipCountry = Convert.ToString(reader["ShipCountry"]);
}
}
finally
{
reader.Close();
}
string queryString1 = "select * from Northwind.[Order Details Extended] where OrderID = @OrderID;";
SqlCommand command1 = new SqlCommand(queryString1, connection);
command1.Parameters.Add(new SqlParameter("@OrderID", id));
SqlDataReader reader1 = command1.ExecuteReader();
try
{
while (reader1.Read())
{
var orderdetail = new OrderDetail();
orderdetail.OrderID = Convert.ToInt32(reader1["OrderID"]);
orderdetail.ProductID = Convert.ToInt32(reader1["ProductID"]);
orderdetail.ProductName = Convert.ToString(reader1["ProductName"]);
orderdetail.UnitPrice = Convert.ToDecimal(reader1["UnitPrice"]);
orderdetail.Quantity = Convert.ToInt32(reader1["Quantity"]);
orderdetail.Discount = Convert.ToSingle(reader1["Discount"]);
orderdetail.ExtendedPrice = Convert.ToDecimal(reader1["ExtendedPrice"]);
order.OrderDetails.Add(orderdetail);
}
}
finally
{
reader1.Close();
}
}
return order;
}