//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
}
}
//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);
}
//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();
}
}
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);
}