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