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