Пример #1
0
        //method to get the total charge for the orderID selected on the datagridview
        public static decimal GetTotalCharge(int orderID)
        {
            SqlConnection con             = NorthWindDB.GetConnection();
            string        selectStatement = "SELECT sum(UnitPrice*Quantity*(1- Discount)) as totalcharge " +
                                            "FROM [Order Details] " +
                                            "WHERE OrderID = @OrderID";
            SqlCommand selectCommand = new SqlCommand(selectStatement, con);

            selectCommand.Parameters.AddWithValue("@OrderID", orderID);
            try
            {
                con.Open();                                           // open database connection
                SqlDataReader reader = selectCommand.ExecuteReader(); // execute query
                if (reader.Read())                                    // read next record while there is one
                {
                    return(Convert.ToDecimal(reader["totalcharge"]));
                }
                else
                {
                    return(0m);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close(); //closes connection
            }
        }
Пример #2
0
        //This is a method which gets the orderID by connecting to the SQL Server
        public static Orders getOrders(int orderID)
        {
            Orders        order       = null;
            SqlConnection con         = NorthWindDB.GetConnection(); //gets connections from NortwindDB class
            string        selectQuery = "SELECT OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate " +
                                        "FROM Orders " +
                                        "WHERE OrderID = @OrderID";
            SqlCommand selectCommand = new SqlCommand(selectQuery, con);

            selectCommand.Parameters.AddWithValue("@OrderID", orderID);
            try
            {
                con.Open();                          //open the connection
                SqlDataReader reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow);
                if (reader.Read())                   //reads data
                {
                    order            = new Orders(); //create a new order object
                    order.OrderID    = (int)reader["OrderID"];
                    order.CustomerID = reader["CustomerID"].ToString();
                    if (DBNull.Value.Equals((DateTime)reader["OrderDate"]))
                    {
                        order.OrderDate = null;
                    }                               //giving a null value
                    else
                    {
                        order.OrderDate = (DateTime)reader["OrderDate"];
                    }
                    if (DBNull.Value.Equals(reader["RequiredDate"]))
                    {
                        order.RequiredDate = null;
                    }
                    else
                    {
                        order.RequiredDate = (DateTime)reader["RequiredDate"];
                    }
                    if (DBNull.Value.Equals(reader["ShippedDate"]))
                    {
                        order.ShippedDate = null;
                    }
                    else
                    {
                        order.ShippedDate = (DateTime)reader["ShippedDate"];
                    }
                    //MessageBox.Show(reader["CustomerID"].ToString());
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();//close the connection
            }

            return(order);
        }
Пример #3
0
        //this is a boolean to update order and is passing 3 arguments
        public static bool UpdateOrder(int OrderID, DateTime oldShippedDate, DateTime?newShippedDate)
        {
            SqlConnection con        = NorthWindDB.GetConnection();
            string        updateStmt = "UPDATE Orders SET " +
                                       "ShippedDate = @newShippedDate " +
                                       "WHERE OrderID = @OrderID " +
                                       "AND ShippedDate = @oldShippedDate";
            //string updateStmt = "UPDATE Orders SET " +
            //                    "ShippedDate = @NewShippedDate " +
            //                    "WHERE ShippedDate = @oldShippedDate is null and @OldShippedDate is null " +
            //                    " AND OrderID = @OrderID";
            SqlCommand updateCommand = new SqlCommand(updateStmt, con);

            //MessageBox.Show(oldShippedDate.ToString() + ":" + newShippedDate.ToString());
            updateCommand.Parameters.AddWithValue("@newShippedDate", newShippedDate);
            updateCommand.Parameters.AddWithValue("@OrderID", OrderID);
            updateCommand.Parameters.AddWithValue("@oldShippedDate", oldShippedDate);
            try
            {
                con.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
Пример #4
0
        public static List <OrderDetails> getOrderDetails(int orderID)//loads up the ordertails to a list and passes using orderID
        {
            List <OrderDetails> od     = new List <OrderDetails>();
            OrderDetails        ordDet = null;
            SqlConnection       con    = NorthWindDB.GetConnection();//connection to the data source
            string     selectQuery     = "SELECT * FROM [Order Details] " + "WHERE OrderID = @OrderID";
            SqlCommand selectCommand   = new SqlCommand(selectQuery, con);

            selectCommand.Parameters.AddWithValue("@OrderID", orderID);
            //Console.WriteLine("Attempting OD fetch...");
            try
            {
                con.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();// System.Data.CommandBehavior.SingleRow);
                while (reader.Read())
                {
                    // Console.WriteLine("Building Row...");
                    ordDet           = new OrderDetails();
                    ordDet.OrderID   = (int)reader["OrderID"];
                    ordDet.ProductID = (int)reader["ProductID"];
                    ordDet.UnitPrice = (decimal)reader["UnitPrice"];
                    ordDet.Quantity  = Convert.ToInt32(reader["Quantity"]);
                    ordDet.Discount  = Convert.ToDecimal(reader["Discount"]);
                    od.Add(ordDet);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            //Console.WriteLine("SetSize: "+od.Count);
            return(od);
        }