//private static string connectionString = "Server=PROG280SERVER.itp.local\\PROG280; Database=nwindsql; user=sa; Password=SQL_2012;";
//private static string connectionString = "Server=PROG280SERVER\\PROG280; Database=nwindsql; user=sa; Password=SQL_2012;";
//private static string connectionString = "server=BrettsPCMasterR;database=NwindSQL;integrated security=true;Timeout=2"; // Bretts home comp
public static List<Order> CustomerOrders(string CusID)
{
List<Order> OrdList = new List<Order>();
sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlDataAdapter da;
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(PROC_CUS_ORDER, sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CustomerID", CusID));
da = new SqlDataAdapter(cmd);
da.FillSchema(dt, SchemaType.Source);
da.Fill(dt);
sqlCon.Close();
// Modify
foreach (DataRow row in dt.Rows)
{
// Start: These will always have a value and will never equal a null.
int OrderID = (int)row["OrderID"];
string CustomerID = (string)row["CustomerID"];
// End.
int? EmployeeID;
DateTime? OrderDate;
DateTime? RequiredDate;
DateTime? ShippedDate;
int? ShipVia;
decimal? Freight;
string ShipName;
string ShipAddress;
string ShipCity;
string ShipRegion;
string ShipPostalCode;
string ShipCountry;
string ShipperName;
if (row["EmployeeID"] == DBNull.Value)
EmployeeID = null;
else
EmployeeID = (int)row["EmployeeID"];
if (row["OrderDate"] == DBNull.Value)
OrderDate = null;
else
OrderDate = (DateTime)row["OrderDate"];
if (row["RequiredDate"] == DBNull.Value)
RequiredDate = null;
else
RequiredDate = (DateTime)row["RequiredDate"];
if (row["ShippedDate"] == DBNull.Value)
ShippedDate = null;
else
ShippedDate = (DateTime)row["ShippedDate"];
if (row["ShipVia"] == DBNull.Value)
ShipVia = null;
else
ShipVia = (int)row["ShipVia"];
if (row["Freight"] == DBNull.Value)
Freight = null;
else
Freight = (decimal)row["Freight"];
if (row["ShipName"] == DBNull.Value)
ShipName = null;
else
ShipName = (string)row["ShipName"];
if (row["ShipAddress"] == DBNull.Value)
ShipAddress = null;
else
ShipAddress = (string)row["ShipAddress"];
if (row["ShipCity"] == DBNull.Value)
ShipCity = null;
else
ShipCity = (string)row["ShipCity"];
if (row["ShipRegion"] == DBNull.Value)
ShipRegion = null;
else
ShipRegion = (string)row["ShipRegion"];
if (row["ShipPostalCode"] == DBNull.Value)
ShipPostalCode = null;
else
ShipPostalCode = (string)row["ShipPostalCode"];
if (row["ShipCountry"] == DBNull.Value)
ShipCountry = null;
else
ShipCountry = (string)row["ShipCountry"];
if (row["ShipName"] == DBNull.Value)
ShipperName = null;
else
ShipperName = (string)row["ShipName"];
Order O = new Order(OrderID);
O.CustomerID = CustomerID;
O.EmployeeID = EmployeeID;
O.OrderDate = OrderDate;
O.RequiredDate = RequiredDate;
O.ShippedDate = ShippedDate;
O.ShipVia = ShipVia;
O.Freight = Freight;
O.ShipName = ShipName;
O.ShipAddress = ShipAddress;
O.ShipCity = ShipCity;
O.ShipRegion = ShipRegion;
O.ShipPostalCode = ShipPostalCode;
O.ShipCountry = ShipCountry;
O.ShipperName = ShipperName;
OrdList.Add(O);
}
return OrdList;
}
public static void UpdateOrder(Order OrderToUpdate)
{
sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
object obj;
SqlCommand cmd = new SqlCommand(PROC_UPDATE_ORDER, sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@OrderID", OrderToUpdate.OrderID));
cmd.Parameters.Add(new SqlParameter("@CustomerID", OrderToUpdate.CustomerID));
obj = OrderToUpdate.EmployeeID;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", OrderToUpdate.EmployeeID != null ? obj : DBNull.Value));
obj = OrderToUpdate.OrderDate;
cmd.Parameters.Add(new SqlParameter("@OrderDate", OrderToUpdate.OrderDate != null ? obj : DBNull.Value));
obj = OrderToUpdate.RequiredDate;
cmd.Parameters.Add(new SqlParameter("@RequiredDate", OrderToUpdate.RequiredDate != null ? obj : DBNull.Value));
obj = OrderToUpdate.ShippedDate;
cmd.Parameters.Add(new SqlParameter("@ShippedDate", OrderToUpdate.ShippedDate != null ? obj : DBNull.Value));
obj = OrderToUpdate.ShipVia;
cmd.Parameters.Add(new SqlParameter("@ShipVia", OrderToUpdate.ShipVia != null ? obj : DBNull.Value));
obj = OrderToUpdate.Freight;
cmd.Parameters.Add(new SqlParameter("@Freight", OrderToUpdate.Freight != null ? obj : DBNull.Value));
//Business Layer does not allow these last ones to be null
cmd.Parameters.Add(new SqlParameter("@ShipName", OrderToUpdate.ShipName));
cmd.Parameters.Add(new SqlParameter("@ShipAddress", OrderToUpdate.ShipAddress));
cmd.Parameters.Add(new SqlParameter("@ShipCity", OrderToUpdate.ShipCity));
cmd.Parameters.Add(new SqlParameter("@ShipRegion", OrderToUpdate.ShipRegion));
cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", OrderToUpdate.ShipPostalCode));
cmd.Parameters.Add(new SqlParameter("@ShipCountry", OrderToUpdate.ShipCountry));
cmd.ExecuteNonQuery();
sqlCon.Close();
}
public static Order GetOrder(int orderID)
{
Order order = new Order(orderID);
sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlDataAdapter da;
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(PROC_GET_ORDER_INFO, sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@OrderID", orderID));
da = new SqlDataAdapter(cmd);
da.FillSchema(dt, SchemaType.Source);
da.Fill(dt);
sqlCon.Close();
DataRow row = dt.Rows[0];
// Start: These will always have a value and will never equal a null.
string CustomerID = (string)row["CustomerID"];
// End.
int? EmployeeID = null;
DateTime? OrderDate = null;
DateTime? RequiredDate = null;
DateTime? ShippedDate = null;
int? ShipVia = null;
decimal? Freight = null;
string ShipName = null;
string ShipAddress = null;
string ShipCity = null;
string ShipRegion = null;
string ShipPostalCode = null;
string ShipCountry = null;
string ShipperName = null;
if (row["EmployeeID"] != DBNull.Value)
EmployeeID = (int)row["EmployeeID"];
if (row["OrderDate"] != DBNull.Value)
OrderDate = (DateTime)row["OrderDate"];
if (row["RequiredDate"] != DBNull.Value)
RequiredDate = (DateTime)row["RequiredDate"];
if (row["ShippedDate"] != DBNull.Value)
ShippedDate = (DateTime)row["ShippedDate"];
if (row["ShipVia"] != DBNull.Value)
ShipVia = (int)row["ShipVia"];
if (row["Freight"] != DBNull.Value)
Freight = (decimal)row["Freight"];
if (row["ShipName"] != DBNull.Value)
ShipName = (string)row["ShipName"];
if (row["ShipAddress"] != DBNull.Value)
ShipAddress = (string)row["ShipAddress"];
if (row["ShipCity"] != DBNull.Value)
ShipCity = (string)row["ShipCity"];
if (row["ShipRegion"] != DBNull.Value)
ShipRegion = (string)row["ShipRegion"];
if (row["ShipPostalCode"] != DBNull.Value)
ShipPostalCode = (string)row["ShipPostalCode"];
if (row["ShipCountry"] != DBNull.Value)
ShipCountry = (string)row["ShipCountry"];
if (row["ShipName"] != DBNull.Value)
ShipperName = (string)row["ShipName"];
order.CustomerID = CustomerID;
order.EmployeeID = EmployeeID;
order.OrderDate = OrderDate;
order.RequiredDate = RequiredDate;
order.ShippedDate = ShippedDate;
order.ShipVia = ShipVia;
order.Freight = Freight;
order.ShipName = ShipName;
order.ShipAddress = ShipAddress;
order.ShipCity = ShipCity;
order.ShipRegion = ShipRegion;
order.ShipPostalCode = ShipPostalCode;
order.ShipCountry = ShipCountry;
order.ShipperName = ShipperName;
return order;
}
public static void CommitOrder(Order newOrder)
{
sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
object obj;
SqlCommand cmd = new SqlCommand(PROC_COMMIT_ORDER, sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p = new SqlParameter();
p.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p);
cmd.Parameters.Add(new SqlParameter("@CustomerID", newOrder.CustomerID));
obj = newOrder.EmployeeID;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", newOrder.EmployeeID != null ? obj : DBNull.Value));
obj = newOrder.OrderDate;
cmd.Parameters.Add(new SqlParameter("@OrderDate", newOrder.OrderDate != null ? obj : DBNull.Value));
obj = newOrder.RequiredDate;
cmd.Parameters.Add(new SqlParameter("@RequiredDate", newOrder.RequiredDate != null ? obj : DBNull.Value));
obj = newOrder.ShippedDate;
cmd.Parameters.Add(new SqlParameter("@ShippedDate", newOrder.ShippedDate != null ? obj : DBNull.Value));
obj = newOrder.ShipVia;
cmd.Parameters.Add(new SqlParameter("@ShipVia", newOrder.ShipVia != null ? obj : DBNull.Value));
obj = newOrder.Freight;
cmd.Parameters.Add(new SqlParameter("@Freight", newOrder.Freight != null ? obj : DBNull.Value));
//Business Layer does not allow these last ones to be null
cmd.Parameters.Add(new SqlParameter("@ShipName", newOrder.ShipName));
cmd.Parameters.Add(new SqlParameter("@ShipAddress", newOrder.ShipAddress));
cmd.Parameters.Add(new SqlParameter("@ShipCity", newOrder.ShipCity));
cmd.Parameters.Add(new SqlParameter("@ShipRegion", newOrder.ShipRegion));
cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", newOrder.ShipPostalCode));
cmd.Parameters.Add(new SqlParameter("@ShipCountry", newOrder.ShipCountry));
cmd.ExecuteNonQuery();
// Change the OrderID of the order that is passed in to its new ID
newOrder.OrderID = (int)p.Value;
sqlCon.Close();
}
