/// <summary>
/// Метод создания заказа
/// </summary>
/// <param name="order"></param>
public bool CreateOrder(Orders order)
{
if (CheckCustomerID(order.CustomerID) == false)
throw new ArgumentException("Incorrect value", "CustomerID");
using (var connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(
"INSERT INTO Northwind.Orders (CustomerID," +
"OrderDate) values (@CustomerID, @OrderDate)", connection);
command.Parameters.Add("@CustomerID",
SqlDbType.NChar, 5).Value = order.CustomerID;
if (order.OrderDate == DateTime.MinValue)
command.Parameters.AddWithValue("@OrderDate",
SqlDbType.DateTime).Value = DBNull.Value;
else
command.Parameters.AddWithValue("@OrderDate",
order.OrderDate);
connection.Open();
return command.ExecuteNonQuery() == 1;
}
}
public IHttpActionResult PutOrders(int id, Orders orders)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
if (id != orders.OrderID)
{
return BadRequest();
}
db.Entry(orders).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!OrdersExists(id))
{
return NotFound();
}
else
{
throw;
}
}
return StatusCode(HttpStatusCode.NoContent);
}
public IHttpActionResult PostOrders(Orders orders)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
db.Orders.Add(orders);
db.SaveChanges();
return CreatedAtRoute("DefaultApi", new { id = orders.OrderID }, orders);
}
/// <summary>
/// Вывод таблицы заказов
/// </summary>
/// <param name=""></param>
public IEnumerable<Orders> ShowOrders()
{
var order = new List<Orders>();
using (var connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(
"SELECT TOP(30) total.OrderID, o.CustomerID, c.ContactName, o.OrderDate," +
" o.RequiredDate, o.ShippedDate, total.Total" +
" FROM Northwind.Customers as c INNER JOIN Northwind.Orders as o" +
" ON c.CustomerID = o.CustomerID INNER JOIN " +
"(SELECT o.OrderID, SUM((UnitPrice * (1 - Discount)) * Quantity) as Total" +
" FROM Northwind.Orders as o FULL OUTER JOIN Northwind.[Order Details] as od" +
" ON o.OrderID = od.OrderID GROUP BY o.OrderID) as total" +
" ON total.OrderID = o.OrderID ORDER BY o.OrderDate DESC", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var o = new Orders();
o.OrderID = Convert.ToInt32(reader.GetValue(0));
if (reader.GetValue(1).ToString() != "")
o.CustomerID = Convert.ToString(reader.GetValue(1));
if (reader.GetValue(2).ToString() != "")
o.ContactName = Convert.ToString(reader.GetValue(2));
if (reader.GetValue(3).ToString() != "")
o.OrderDate = Convert.ToDateTime(reader.GetValue(3));
if ((reader.GetValue(5).ToString() == DateTime.MinValue.ToString())
|| (reader.GetValue(5).ToString() == ""))
{
o.OrderStatus = OrderTypes.Underway;
}
else
{
o.OrderStatus = OrderTypes.Done;
}
if ((o.OrderDate == DateTime.MinValue) || (o.OrderDate.ToString() == ""))
{
o.OrderStatus = OrderTypes.NotShipped;
}
if (reader.GetValue(6).ToString() != "")
o.Total = Convert.ToDouble(reader.GetValue(6));
Console.WriteLine();
order.Add(o);
}
return order;
}
}