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