Example #1
0
        // 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();
            }
        }
Example #2
0
        // 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);
        }
Example #3
0
        } // 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);
        }
Example #4
0
        // 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);
        }