// Updates the ShippedDate field of the Order table, return success or failure bool
public static bool UpdateShippedDate(string ShippedDate, int orderID)
{
SqlConnection con = NorthwndDb.GetConnection();
string updateStatement = "UPDATE Orders " +
"SET ShippedDate = @ShippedDate " +
"WHERE OrderID = @OrderID";
SqlCommand updateCommand = new SqlCommand(updateStatement, con);
updateCommand.Parameters.AddWithValue("@ShippedDate", ShippedDate ?? Convert.DBNull);
updateCommand.Parameters.AddWithValue("@OrderID", orderID);
try {
con.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
} catch (SqlException ex) {
throw ex;
} finally {
con.Close();
}
}
// select query returns order details for a particular OrderID and returns a list
public static List <OrderDetails> GetOrderDetailsList(int orderID)
{
List <OrderDetails> ODList = new List <OrderDetails>();
OrderDetails od;
SqlConnection con = NorthwndDb.GetConnection();
string selectStatement = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
"FROM [Order Details] " +
"WHERE OrderID = @OrderID";
SqlCommand selectCommand = new SqlCommand(selectStatement, con);
selectCommand.Parameters.AddWithValue("@OrderID", orderID);
try {
con.Open();
SqlDataReader ordReader = selectCommand.ExecuteReader();
while (ordReader.Read())
{
od = new OrderDetails();
od.OrderID = (int)ordReader["OrderID"];
od.ProductID = (int)ordReader["ProductID"];
od.UnitPrice = (decimal)ordReader["UnitPrice"];
od.Quantity = Convert.ToInt32(ordReader["Quantity"]);
od.Discount = Convert.ToDecimal(ordReader["Discount"]);
ODList.Add(od);
}
} catch (SqlException ex) {
throw ex;
} finally {
con.Close();
}
return(ODList);
}
} // end GetOrderListByCustomerID()
// get a list of orders by dateType ... @dateType must be OrderDate, RequiredDate, or ShippedDate
public static List <Order> GetOrderListByDateRange(string dateType, string fromDate, string toDate)
{
List <Order> orderList = new List <Order>();
Order ord;
SqlConnection con = NorthwndDb.GetConnection();
string selectStatement = "SELECT OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate " +
"FROM Orders " +
"WHERE " + dateType + " BETWEEN @fromDate AND @toDate";
SqlCommand selectCommand = new SqlCommand(selectStatement, con);
selectCommand.Parameters.AddWithValue("@fromDate", fromDate);
selectCommand.Parameters.AddWithValue("@toDate", toDate);
try {
con.Open();
SqlDataReader ordReader = selectCommand.ExecuteReader();
while (ordReader.Read())
{
ord = new Order();
ord.OrderID = (int)ordReader["OrderID"];
ord.CustomerID = ordReader["CustomerID"].ToString();
if (ordReader["OrderDate"] != DBNull.Value)
{
ord.OrderDate = (DateTime)ordReader["OrderDate"];
}
else
{
ord.OrderDate = null;
}
if (ordReader["RequiredDate"] != DBNull.Value)
{
ord.RequiredDate = (DateTime)ordReader["RequiredDate"];
}
else
{
ord.RequiredDate = null;
}
if (ordReader["ShippedDate"] != DBNull.Value)
{
ord.ShippedDate = Convert.ToDateTime(ordReader["ShippedDate"].ToString());
}
else
{
ord.ShippedDate = null;
}
orderList.Add(ord); // add order to list
}
} catch (SqlException ex) {
throw ex;
} finally {
con.Close();
}
return(orderList);
}
// get order by orderID, return list of records
public static List <Order> GetOrderByOrderID(string orderID)
{
List <Order> orderList = new List <Order>();
Order ord;
SqlConnection con = NorthwndDb.GetConnection();
string selectStatement = "SELECT OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate " +
"FROM Orders " +
"WHERE OrderID LIKE @OrderID";
SqlCommand selectCommand = new SqlCommand(selectStatement, con);
selectCommand.Parameters.AddWithValue("@OrderID", orderID + "%"); //use SQL wildcard to match partial string, sql injection handled at form level
try {
con.Open();
SqlDataReader ordReader = selectCommand.ExecuteReader();
while (ordReader.Read())
{
ord = new Order();
ord.OrderID = (int)ordReader["OrderID"];
ord.CustomerID = ordReader["CustomerID"].ToString();
if (ordReader["OrderDate"] != DBNull.Value) // handle null values
{
ord.OrderDate = (DateTime)ordReader["OrderDate"];
}
else
{
ord.OrderDate = null;
}
if (ordReader["RequiredDate"] != DBNull.Value)
{
ord.RequiredDate = (DateTime)ordReader["RequiredDate"];
}
else
{
ord.RequiredDate = null;
}
if (ordReader["ShippedDate"] != DBNull.Value)
{
ord.ShippedDate = Convert.ToDateTime(ordReader["ShippedDate"].ToString());
}
else
{
ord.ShippedDate = null;
}
orderList.Add(ord); // add order to list
}
} catch (SqlException ex) {
throw ex;
} finally {
con.Close();
}
return(orderList);
}
