/// <summary> /// The GetOrderIDs() method retrieves all the OrderID values from the Orders table. /// </summary> /// <returns>A list of all the OrderIDs</returns> public static List <int> GetOrderIDs() { // Local variables List <int> ordIDs = new List <int>(); // Empty list for storing the order ID values // Open a connection to the database SqlConnection con = NorthwindDB.GetConnection(); // Define the SQL statement for retrieving the OrderIDs string selectStatement = "SELECT OrderID " + "FROM Orders " + "ORDER BY OrderID"; // Create the SqlCommand object SqlCommand cmd = new SqlCommand(selectStatement, con); // Try executing the query. If successful, loop through the results and populate // the list orderIDs. try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // Loop through all the results { ordIDs.Add((int)reader["OrderID"]); } } // If unsuccessful, throw the exception catch (SqlException ex) // This is the only type of exception that can result { throw ex; } // In either case, close the connection finally { con.Close(); } return(ordIDs); }
/// <summary> /// The UpdateShippedDate() method updates the ShippedDate in the Orders table, /// for the order specified by the ord parameter. /// </summary> /// <param name="ord">The order to be updated</param> /// <param name="newShippedDate">The new ShippedDate for the order</param> /// <returns>true if successful, otherwise false</returns> public static bool UpdateShippedDate(Order ord, DateTime?newShippedDate) { // The new ShippedDate will always be either a valid date or null, as it is obtained from // a DateTimePicker on the main form, so invalid dates cannot be entered. if (newShippedDate.HasValue) { // If the new ShippedDate is not null, it must be on or after the OrderDate, // and on or before the RequiredDate, if these dates are non-null. // Only the date portion of the DateTime value needs to be considered, and not // the time. "DateTime?" values must be typecase to "DateTime" before using .Date. if ((!ord.OrderDate.HasValue || (((DateTime)newShippedDate).Date >= ((DateTime)ord.OrderDate).Date)) && (!ord.RequiredDate.HasValue || (((DateTime)newShippedDate).Date <= ((DateTime)ord.RequiredDate).Date))) { // Open the database connection SqlConnection con = NorthwindDB.GetConnection(); // Create the SQL update statement and SqlCommand object. // // Concurrency: use "last-in" concurrency here; i.e., this application is // allowed to update the ShippedDate even if the record has been updated by // another user since the data was read. It is very unlikely that different // users would try to update the ShippedDate to different values simultaneously. string updateStatement = "UPDATE Orders " + "SET ShippedDate = @NewShippedDate " + "WHERE OrderID = @OrderID"; SqlCommand cmd = new SqlCommand(updateStatement, con); cmd.Parameters.AddWithValue("@NewShippedDate", ((DateTime)newShippedDate).ToString("yyyy-MM-dd")); cmd.Parameters.AddWithValue("@OrderID", ord.OrderID); // Try executing the query. try { con.Open(); int count = cmd.ExecuteNonQuery(); // Returns the number of rows updated (will be either 0 or 1 here) if (count > 0) { return(true); } else { return(false); } } // If unsuccessful, throw the exception catch (SqlException ex) { throw ex; } // In either case, close the database connection finally { con.Close(); } } else { // The new ShippedDate is invalid. Throw an exception to indicate this. throw new Exception("The Shipped Date must be no earlier than the Order Date, and " + "no later than the Required Date."); } } else { // The new ShippedDate is null, and will be updated to NULL in the database. // This could happen if the ShippedDate was entered incorrectly and nees to // be reset. // Open a connection to the database SqlConnection con = NorthwindDB.GetConnection(); // Create the SQL update statement and SqlCommand object. // As above, use "last-in" concurrency. string updateStatement = "UPDATE Orders " + "SET ShippedDate = NULL " + "WHERE OrderID = @OrderID"; // Create the SqlCommand object SqlCommand cmd = new SqlCommand(updateStatement, con); cmd.Parameters.AddWithValue("@OrderID", ord.OrderID); // Try executing the query. try { con.Open(); int count = cmd.ExecuteNonQuery(); // Returns the number of rows updated (will be either 0 or 1 here) if (count > 0) { return(true); } else { return(false); } } // If unsuccessful, throw the exception catch (SqlException ex) { throw ex; } // In either case, close the database connection finally { con.Close(); } } }