Exemplo n.º 1
0
 //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();
 }
Exemplo n.º 2
0
        public OrderDetails GetOrdersDetailsById(int?id)
        {
            OrderDetails ordersd = db.OrderDetails.Find(id);

            return(ordersd);
        }
Exemplo n.º 3
0
        /// <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);
            }
        }
Exemplo n.º 4
0
        /// <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);
            }
        }
Exemplo n.º 5
0
        /// <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);
            }
        }
Exemplo n.º 6
0
        /// <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);
            }
        }