// a method to calculate the order total for a specific order (order ID) from the order details: public static decimal CalOrdTotal(int orderID) { // make an empty total decimal total = 0; // get connected to the database SqlConnection con = NorthwindDB.GetConnection(); // creating the proper sql query to extract data from MS SQL server string SelectQuery = "SELECT SUM(Quantity* UnitPrice *(1 - Discount)) AS Total " + "FROM [Order Details] WHERE OrderID = @OrderID Group by OrderID"; // creating the proper command to run the query SqlCommand cmnd = new SqlCommand(SelectQuery, con); // adding the input parameter for running the command (Order ID) to it cmnd.Parameters.AddWithValue("@OrderID", orderID); // try to run the command try { // opening the connection con.Open(); // creating a sql data reader and run it to update the data in the database SqlDataReader dr = cmnd.ExecuteReader(CommandBehavior.SingleRow); if (dr.Read()) { total = Convert.ToDecimal(dr["Total"]); } dr.Close(); // closing the data reader } catch (Exception ex) { throw ex; } finally { con.Close(); } return(total); }
// a method to update shipping date in the database public static bool UpdateOrder(Order newOrder, Order oldOrder) { bool result = true; // get connected to the database SqlConnection con = NorthwindDB.GetConnection(); // creating the proper sql query to update data in MS SQL server string UpdateQuery = "Update Orders SET ShippedDate = @NewShippedDate " + // to identify record to update "WHERE OrderID = @OldOrderId " + // remaining conditions for optimistic concurrency "AND (ShippedDate = @OldShippedDate OR ShippedDate IS NULL AND @OldShippedDate IS NULL)"; // creating the proper command to run the query SqlCommand cmnd = new SqlCommand(UpdateQuery, con); // adding the input parameter for running the command (Order ID) to it if (newOrder.ShippedDate == null) { cmnd.Parameters.AddWithValue("@NewShippedDate", DBNull.Value); } else { cmnd.Parameters.AddWithValue("@NewShippedDate", newOrder.ShippedDate); } cmnd.Parameters.AddWithValue("@OldOrderId", oldOrder.OrderID); if (oldOrder.ShippedDate == null) { cmnd.Parameters.AddWithValue("@OldShippedDate", DBNull.Value); } else { cmnd.Parameters.AddWithValue("@OldShippedDate", oldOrder.ShippedDate); } // try to run the command try { // opening the connection con.Open(); // running the command to update the data in the database int rowUpdated = cmnd.ExecuteNonQuery(); if (rowUpdated == 0) { result = false; // did not update (another user updated or deleted) } } catch (Exception ex) { throw ex; } finally { con.Close(); } return(result); }
// a method to get a list of orders from the database: public static List <Order> GetOrders() { // make an empty list of orders List <Order> OrderList = new List <Order>(); // make an empty order object Order ord; // get connected to the database SqlConnection con = NorthwindDB.GetConnection(); // creating the proper sql query to extract data from MS SQL server string SelectQuery = "SELECT OrderID, CustomerID, OrderDate, RequiredDate," + "ShippedDate FROM Orders ORDER BY OrderID"; // creating the proper command to run the query SqlCommand cmnd = new SqlCommand(SelectQuery, con); // try to run the command try { // opening the connection con.Open(); // creating a sql data reader and run it to read the data from the database SqlDataReader dr = cmnd.ExecuteReader(); // read line by line as much as there is something to read while (dr.Read()) { // for each line of the returned rows of data from database, //assign the column values to the properties of a new order object ord = new Order(); ord.OrderID = (int)dr["OrderID"]; // *************************************************************************** // these columns can be null in the database // so we need to check if they are null for the current row of data or not. // if they are null, the value for the related property of the object is null. // *************************************************************************** // get the index of the related column in the database int CustomerIDIndex = dr.GetOrdinal("CustomerID"); if (dr.IsDBNull(CustomerIDIndex)) { ord.CustomerID = null; } else { ord.CustomerID = dr["CustomerID"].ToString(); } int OrderDateIndex = dr.GetOrdinal("OrderDate"); if (dr.IsDBNull(OrderDateIndex)) { ord.OrderDate = null; } else { ord.OrderDate = Convert.ToDateTime(dr["OrderDate"]); } int RequiredDateIndex = dr.GetOrdinal("RequiredDate"); if (dr.IsDBNull(RequiredDateIndex)) { ord.RequiredDate = null; } else { ord.RequiredDate = Convert.ToDateTime(dr["RequiredDate"]); } int ShippedDateIndex = dr.GetOrdinal("ShippedDate"); if (dr.IsDBNull(ShippedDateIndex)) { ord.ShippedDate = null; } else { ord.ShippedDate = Convert.ToDateTime(dr["ShippedDate"]); } // *************************************************************************** // adding the new object to the list of objects OrderList.Add(ord); } dr.Close(); // closing the data reader } catch (Exception ex) { throw ex; } finally { con.Close(); } return(OrderList); }
// a method to get an order from the database: public static Order GetOrder(int orderId) { // make an empty order object Order ord = null; // get connected to the database SqlConnection con = NorthwindDB.GetConnection(); // creating the proper sql query to extract data from MS SQL server string SelectQuery = "SELECT OrderID, CustomerID, OrderDate, RequiredDate," + "ShippedDate FROM Orders WHERE OrderID = @OrderId "; // creating the proper command to run the query SqlCommand cmnd = new SqlCommand(SelectQuery, con); // adding the input parameter for running the command (Order ID) to it cmnd.Parameters.AddWithValue("@OrderId", orderId); // try to run the command try { // opening the connection con.Open(); // creating a sql data reader and run it to read the data from the database SqlDataReader dr = cmnd.ExecuteReader(CommandBehavior.SingleRow); // read line by line as much as there is something to read if (dr.Read()) { // assign the column values to the properties of a new order object ord = new Order(); ord.OrderID = (int)dr["OrderID"]; // *************************************************************************** // these columns can be null in the database // so we need to check if they are null for the current row of data or not. // if they are null, the value for the related property of the object is null. // *************************************************************************** // get the index of the related column in the database int CustomerIDIndex = dr.GetOrdinal("CustomerID"); if (dr.IsDBNull(CustomerIDIndex)) { ord.CustomerID = null; } else { ord.CustomerID = dr["CustomerID"].ToString(); } int OrderDateIndex = dr.GetOrdinal("OrderDate"); if (dr.IsDBNull(OrderDateIndex)) { ord.OrderDate = null; } else { ord.OrderDate = Convert.ToDateTime(dr["OrderDate"]); } int RequiredDateIndex = dr.GetOrdinal("RequiredDate"); if (dr.IsDBNull(RequiredDateIndex)) { ord.RequiredDate = null; } else { ord.RequiredDate = Convert.ToDateTime(dr["RequiredDate"]); } int ShippedDateIndex = dr.GetOrdinal("ShippedDate"); if (dr.IsDBNull(ShippedDateIndex)) { ord.ShippedDate = null; } else { ord.ShippedDate = Convert.ToDateTime(dr["ShippedDate"]); } // *************************************************************************** } dr.Close(); // closing the data reader } catch (Exception ex) { throw ex; } finally { con.Close(); } return(ord); }