}// Get Order method completed //accessing SQL and changing the shipped date public static bool UpdateOrder(int orderID, DateTime?newOrd) { int count; // how many rows updated using (SqlConnection connection = NorthwindDB.GetConnection()) { string updateStatement = "UPDATE Orders SET " + " ShippedDate = @newShippedDate " + "WHERE orderID = @orderID"; using (SqlCommand cmd = new SqlCommand(updateStatement, connection)) { if (newOrd != null) { cmd.Parameters.AddWithValue("@newShippedDate", newOrd); } else { cmd.Parameters.AddWithValue("@newShippedDate", DBNull.Value); } cmd.Parameters.AddWithValue("@orderID", orderID); connection.Open(); count = cmd.ExecuteNonQuery(); // returns how many rows updated } } return(count > 0); }
//creating a list of objects for order details public static List <OrderDetails> GetOrderDetails(int orderID) { //variables and lists List <OrderDetails> ordDet = new List <OrderDetails>(); OrderDetails singleOrdDet = null; //connection to SQL and submitting required query using (SqlConnection connection = NorthwindDB.GetConnection()) { string query = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " + "FROM [Order Details] " + "WHERE OrderID = @OrderID"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@OrderID", orderID); connection.Open(); using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { //add all items from order details database to object list while (reader.Read()) { singleOrdDet = new OrderDetails(); singleOrdDet.OrderID = (int)reader["OrderID"]; singleOrdDet.ProductID = (int)reader["ProductID"]; singleOrdDet.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]); singleOrdDet.Quantity = Convert.ToInt32(reader["Quantity"]); singleOrdDet.Discount = Convert.ToDecimal(reader["Discount"]); ordDet.Add(singleOrdDet); } } } } return(ordDet); //returns full list of order details objects }// Get Customer method completed
// retrieve a single order data public static Orders GetOrder(int orderID) { //creating the object to store the orders information Orders ord = null; //opening a connection to SQL and inputting a query to access the specific orderIDs information using (SqlConnection connection = NorthwindDB.GetConnection()) { string query = "SELECT OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate " + "FROM Orders " + "WHERE OrderID = @OrderID"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@OrderID", orderID); connection.Open(); using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { //store values into the order object if (reader.Read()) { ord = new Orders(); ord.OrderID = (int)reader["OrderID"]; ord.CustomerID = reader["CustomerID"].ToString(); //taking in account potential null values for dates if (reader["OrderDate"] == DBNull.Value) { ord.OrderDate = null; } else { ord.OrderDate = Convert.ToDateTime(reader["OrderDate"]); } if (reader["RequiredDate"] == DBNull.Value) { ord.RequiredDate = null; } else { ord.RequiredDate = Convert.ToDateTime(reader["RequiredDate"]); } if (reader["ShippedDate"] == DBNull.Value) { ord.ShippedDate = null; } else { ord.ShippedDate = Convert.ToDateTime(reader["ShippedDate"]); } } } } } return(ord); }// Get Order method completed
// gets all order IDs for the combo box public static List <int> GetOrderIDs() { List <int> orderIDs = new List <int>(); // empty list of IDs int id; // for reading using (SqlConnection connection = NorthwindDB.GetConnection()) { string query = "SELECT OrderID FROM Orders"; // any exception not handled here is automatically thrown to the form // where the method was called using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close connection as soon as done with reading while (reader.Read()) { id = (int)reader["OrderID"]; orderIDs.Add(id); } } // command object recycled } // connection object recycled return(orderIDs); }