//public IQueryable<View_OrderDetails> FindviewodById(string uid)
//{
// var viewordersd = db.View_OrderDetails.Where(c => c.Users_id == uid);
// return viewordersd;
//}
public void RemoveOrdersDetails(OrderDetails ordersd)
{
db.OrderDetails.Remove(ordersd);
db.SaveChanges();
}
public OrderDetails GetOrdersDetailsById(int?id)
{
OrderDetails ordersd = db.OrderDetails.Find(id);
return(ordersd);
}
/// <summary>
/// Детали заказа
/// </summary>
/// <param name="OrderID"></param>
public bool OrderDetails(int OrderID)
{
try
{
var order = new List <Orders>();
var products = new List <Products>();
var orderdetails = new List <OrderDetails>();
using (var connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(
"SELECT o.OrderID, o.CustomerID, o.EmployeeID, " +
"o.OrderDate, o.RequiredDate, o.ShippedDate, " +
"o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, " +
" o.ShipCity, o.ShipRegion, o.ShipPostalCode, " +
" o.ShipCountry, p.ProductID, p.ProductName, " +
"od.Quantity, od.UnitPrice, od.Discount FROM " +
"Northwind.Orders AS o INNER JOIN " +
"Northwind.[Order Details] AS od ON " +
"o.OrderID = od.OrderID INNER JOIN " +
"Northwind.Products AS p ON od.ProductID=p.ProductID" +
" WHERE o.OrderID = @OrderID", connection);
command.Parameters.AddWithValue("@OrderID", OrderID);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var o = new Orders();
var p = new Products();
var od = new OrderDetails();
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetName(i) + " = ");
if (reader.GetValue(i).ToString() == "")
{
Console.WriteLine("null;");
}
else
{
Console.WriteLine(reader.GetValue(i) + "; ");
}
if (reader.GetName(i).ToString() == "OrderID")
{
o.OrderID = Convert.ToInt32(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "CustomerID") &&
(reader.GetValue(i).ToString() != ""))
{
o.CustomerID = Convert.ToString(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "EmployeeID") &&
(reader.GetValue(i).ToString() != ""))
{
o.EmployeeID = Convert.ToInt32(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "OrderDate") &&
(reader.GetValue(i).ToString() != ""))
{
o.OrderDate = Convert.ToDateTime(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "RequiredDate") &&
(reader.GetValue(i).ToString() != ""))
{
o.RequiredDate = Convert.ToDateTime(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ShippedDate") &&
(reader.GetValue(i).ToString() != ""))
{
o.ShippedDate = Convert.ToDateTime(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ShipVia") &&
(reader.GetValue(i).ToString() != ""))
{
o.ShipVia = Convert.ToInt32(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "Freight") &&
(reader.GetValue(i).ToString() != ""))
{
o.Freight = Convert.ToDouble(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ShipName") &&
(reader.GetValue(i).ToString() != ""))
{
o.ShipName = Convert.ToString(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ShipAddress") &&
(reader.GetValue(i).ToString() != ""))
{
o.ShipAddress = Convert.ToString(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ShipCity") &&
(reader.GetValue(i).ToString() != ""))
{
o.ShipCity = Convert.ToString(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ShipRegion") &&
(reader.GetValue(i).ToString() != ""))
{
o.ShipRegion = Convert.ToString(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ShipPostalCode") &&
(reader.GetValue(i).ToString() != ""))
{
o.ShipPostalCode = Convert.ToString(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ShipCountry") &&
(reader.GetValue(i).ToString() != ""))
{
o.ShipCountry = Convert.ToString(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ProductID") &&
(reader.GetValue(i).ToString() != ""))
{
p.ProductID = Convert.ToInt32(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "ProductName") &&
(reader.GetValue(i).ToString() != ""))
{
p.ProductName = Convert.ToString(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "Quantity") &&
(reader.GetValue(i).ToString() != ""))
{
od.Quantity = Convert.ToInt32(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "UnitPrice") &&
(reader.GetValue(i).ToString() != ""))
{
od.UnitPrice = Convert.ToDouble(reader.GetValue(i));
}
if ((reader.GetName(i).ToString() == "Discount") &&
(reader.GetValue(i).ToString() != ""))
{
od.Discount = Convert.ToDouble(reader.GetValue(i));
}
}
Console.WriteLine();
order.Add(o);
products.Add(p);
orderdetails.Add(od);
}
if (((order.Count != 0) && (products.Count != 0) &&
(orderdetails.Count != 0)) || (reader.HasRows == false))
{
return(true);
}
else
{
return(false);
}
}
}
catch (Exception)
{
Console.Write("Произошла ошибка, перепроверьте вводимые данные");
return(false);
}
}
/// <summary>
/// Детали заказа
/// </summary>
/// <param name="OrderID"></param>
public IEnumerable <OrderDetails> ShowOrderDetails(int OrderID)
{
if (CheckOrderID(OrderID) == false)
{
throw new ArgumentException("Incorrect value", "OrderID");
}
var order = new List <OrderDetails>();
using (var connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(
"SELECT o.OrderID, o.CustomerID, o.EmployeeID, " +
"o.OrderDate, o.RequiredDate, o.ShippedDate, " +
"o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, " +
" o.ShipCity, o.ShipRegion, o.ShipPostalCode, " +
" o.ShipCountry, p.ProductID, p.ProductName, " +
"od.Quantity, od.UnitPrice, od.Discount FROM " +
"Northwind.Orders AS o INNER JOIN " +
"Northwind.[Order Details] AS od ON " +
"o.OrderID = od.OrderID INNER JOIN " +
"Northwind.Products AS p ON od.ProductID=p.ProductID" +
" WHERE o.OrderID = @OrderID", connection);
command.Parameters.AddWithValue("@OrderID", OrderID);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var o = new OrderDetails();
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.EmployeeID = Convert.ToInt32(reader.GetValue(2));
}
if (reader.GetValue(3).ToString() != "")
{
o.OrderDate = Convert.ToDateTime(reader.GetValue(3));
}
if (reader.GetValue(4).ToString() != "")
{
o.RequiredDate = Convert.ToDateTime(reader.GetValue(4));
}
if (reader.GetValue(5).ToString() != "")
{
o.ShippedDate = Convert.ToDateTime(reader.GetValue(5));
}
if (reader.GetValue(6).ToString() != "")
{
o.ShipVia = Convert.ToInt32(reader.GetValue(6));
}
if (reader.GetValue(7).ToString() != "")
{
o.Freight = Convert.ToDouble(reader.GetValue(7));
}
if (reader.GetValue(8).ToString() != "")
{
o.ShipName = Convert.ToString(reader.GetValue(8));
}
if (reader.GetValue(9).ToString() != "")
{
o.ShipAddress = Convert.ToString(reader.GetValue(9));
}
if (reader.GetValue(10).ToString() != "")
{
o.ShipCity = Convert.ToString(reader.GetValue(10));
}
if (reader.GetValue(11).ToString() != "")
{
o.ShipRegion = Convert.ToString(reader.GetValue(11));
}
if (reader.GetValue(12).ToString() != "")
{
o.ShipPostalCode = Convert.ToString(reader.GetValue(12));
}
if (reader.GetValue(13).ToString() != "")
{
o.ShipCountry = Convert.ToString(reader.GetValue(13));
}
if (reader.GetValue(14).ToString() != "")
{
o.ProductID = Convert.ToInt32(reader.GetValue(14));
}
if (reader.GetValue(15).ToString() != "")
{
o.ProductName = Convert.ToString(reader.GetValue(15));
}
if (reader.GetValue(16).ToString() != "")
{
o.Quantity = Convert.ToInt32(reader.GetValue(16));
}
if (reader.GetValue(17).ToString() != "")
{
o.UnitPrice = Convert.ToDouble(reader.GetValue(17));
}
if (reader.GetValue(18).ToString() != "")
{
o.Discount = Convert.ToDouble(reader.GetValue(18));
}
order.Add(o);
}
return(order);
}
}
/// <summary>
/// Обновление информации о заказе
/// </summary>
/// <param name="order"></param>
public bool AddOrder(OrderDetails order)
{
using (var connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(
"UPDATE Northwind.Orders SET CustomerID = @CustomerID," +
" EmployeeID = @EmployeeID, OrderDate = @OrderDate," +
" RequiredDate = @OrderDate, ShippedDate = @ShippedDate," +
" ShipVia = @ShipVia, Freight = @Freight, ShipName = @ShipName," +
" ShipAddress = @ShipAddress, ShipCity = @ShipCity," +
" ShipRegion = @ShipRegion, ShipPostalCode = @ShipPostalCode," +
"ShipCountry = @ShipCountry WHERE OrderID = @OrderID", connection);
command.Parameters.Add("@OrderID",
SqlDbType.Int).Value = order.OrderID;
command.Parameters.Add("@CustomerID",
SqlDbType.NChar, 5).Value = order.CustomerID;
command.Parameters.AddWithValue("@EmployeeID",
order.EmployeeID);
if ((order.OrderDate == DateTime.MinValue) ||
(order.OrderDate.ToString() == ""))
{
command.Parameters.AddWithValue("@OrderDate",
SqlDbType.DateTime).Value = DBNull.Value;
}
else
{
command.Parameters.AddWithValue("@OrderDate",
order.OrderDate);
}
if ((order.RequiredDate == DateTime.MinValue) ||
(order.OrderDate.ToString() == ""))
{
command.Parameters.AddWithValue("@RequiredDate",
SqlDbType.DateTime).Value = DBNull.Value;
}
else
{
command.Parameters.AddWithValue("@RequiredDate",
order.RequiredDate);
}
if ((order.ShippedDate == DateTime.MinValue) ||
(order.OrderDate.ToString() == ""))
{
command.Parameters.AddWithValue("@ShippedDate",
SqlDbType.DateTime).Value = DBNull.Value;
}
else
{
command.Parameters.AddWithValue("@ShippedDate",
order.ShippedDate);
}
command.Parameters.AddWithValue("@ShipVia", order.ShipVia);
command.Parameters.AddWithValue("@Freight", order.Freight);
command.Parameters.Add("@ShipName", SqlDbType.NVarChar,
40).Value = order.ShipName;
command.Parameters.Add("@ShipAddress", SqlDbType.NVarChar,
60).Value = order.ShipAddress;
command.Parameters.Add("@ShipCity", SqlDbType.NVarChar,
15).Value = order.ShipCity;
command.Parameters.Add("@ShipRegion", SqlDbType.NVarChar,
15).Value = order.ShipRegion;
command.Parameters.Add("@ShipPostalCode",
SqlDbType.NVarChar, 10).Value = order.ShipPostalCode;
command.Parameters.Add("@ShipCountry", SqlDbType.NVarChar,
15).Value = order.ShipCountry;
connection.Open();
return(command.ExecuteNonQuery() == 1);
}
}
/// <summary>
/// Детали заказа
/// </summary>
/// <param name="OrderID"></param>
public OrderDetails OrderDetails(int OrderID)
{
if (CheckOrderID(OrderID) == false)
{
throw new ArgumentException("Incorrect value", "OrderID");
}
using (var connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(
"SELECT o.OrderID, o.CustomerID, o.EmployeeID, " +
"o.OrderDate, o.RequiredDate, o.ShippedDate, " +
"o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, " +
" o.ShipCity, o.ShipRegion, o.ShipPostalCode, " +
" o.ShipCountry FROM Northwind.Orders AS o " +
" WHERE o.OrderID = @OrderID", connection);
command.Parameters.AddWithValue("@OrderID", OrderID);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
OrderDetails o = new OrderDetails();
while (reader.Read())
{
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.EmployeeID = Convert.ToInt32(reader.GetValue(2));
}
if (reader.GetValue(3).ToString() != "")
{
o.OrderDate = Convert.ToDateTime(reader.GetValue(3));
}
if (reader.GetValue(4).ToString() != "")
{
o.RequiredDate = Convert.ToDateTime(reader.GetValue(4));
}
if (reader.GetValue(5).ToString() != "")
{
o.ShippedDate = Convert.ToDateTime(reader.GetValue(5));
}
if (reader.GetValue(6).ToString() != "")
{
o.ShipVia = Convert.ToInt32(reader.GetValue(6));
}
if (reader.GetValue(7).ToString() != "")
{
o.Freight = Convert.ToDouble(reader.GetValue(7));
}
if (reader.GetValue(8).ToString() != "")
{
o.ShipName = Convert.ToString(reader.GetValue(8));
}
if (reader.GetValue(9).ToString() != "")
{
o.ShipAddress = Convert.ToString(reader.GetValue(9));
}
if (reader.GetValue(10).ToString() != "")
{
o.ShipCity = Convert.ToString(reader.GetValue(10));
}
if (reader.GetValue(11).ToString() != "")
{
o.ShipRegion = Convert.ToString(reader.GetValue(11));
}
if (reader.GetValue(12).ToString() != "")
{
o.ShipPostalCode = Convert.ToString(reader.GetValue(12));
}
if (reader.GetValue(13).ToString() != "")
{
o.ShipCountry = Convert.ToString(reader.GetValue(13));
}
break;
}
return(o);
}
}