/*
        * Author: Aaron Mill
        * Date: July 6, 2015
        */
        public static bool AddProduct(Product product)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string insertStatement =
                "INSERT Products " +
                "(ProdName) " +
                "VALUES (@ProdName)";
            SqlCommand insertCommand =
                new SqlCommand(insertStatement, connection);
            insertCommand.Parameters.AddWithValue(
                "@ProdName", product.ProdName);

            try
            {
                connection.Open();
                int count = insertCommand.ExecuteNonQuery();

                // Inform user and return execution status
                if (count > 0)
                {
                    // MessageBox.Show("Inserted record: " + product.ProductId);
                    return true;
                }
                else
                    return false;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
 private void btnAdd_Click(object sender, EventArgs e)
 {
     //if selected on left is not null check for duplicate and if not pass into other list
     if (lstAllProducts.SelectedItem != null)
     {
         int productId = ((Product)(lstAllProducts.SelectedItem)).ProductId;
         string prodName = ((Product)(lstAllProducts.SelectedItem)).ProdName;
         Product ProductToAdd = new Product(productId, prodName, SupplierToEdit.SupName, SupplierToEdit.SuplierId);
         if (lstSuppliersProducts.Items.Contains(ProductToAdd))
         {
             MessageBox.Show("I am afraid product: "+ProductToAdd.ToString()+" has already been added.");
         }
         else
         {
             lstSuppliersProducts.Items.Add(ProductToAdd);
         }
     }
 }
        //function to delete the SQL data
        /*Chen Code Start, Aaron helped by deleting Products_Products_Suppliers*/
        public static bool DeleteProduct(Product product)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();  //connect the SQL7
            string deleteForeign =
                "DELETE FROM Products_Products_Suppliers " +
                "WHERE ProductId = @ProductId ";
            SqlCommand deleteForeignCommand =
                new SqlCommand(deleteForeign, connection);  //call the SQL deleteForeign command to delete the dependent table
            deleteForeignCommand.Parameters.AddWithValue(
                "@ProductId", product.ProductId);

            string deleteStatement =
                "DELETE FROM Products " +
                "WHERE ProductId = @ProductId ";
            SqlCommand deleteCommand =
                new SqlCommand(deleteStatement, connection);  //call the SQL delete command to delete the data
            deleteCommand.Parameters.AddWithValue(
                "@ProductId", product.ProductId);

            try
            {
                connection.Open();
                int count2 = deleteForeignCommand.ExecuteNonQuery();
                int count = deleteCommand.ExecuteNonQuery();
                if (count > 0 && count2 > 0)
                    return true;
                else
                    return false;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /*
        * Author: Aaron Mill
        * Date: July 6, 2015
        */
        public static bool UpdateProduct(Product product)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string updateStatement =
                "UPDATE Products SET " +
                "ProdName = @ProdName " +
                "WHERE ProductId = @ProductId";
            SqlCommand updateCommand =
                new SqlCommand(updateStatement, connection);
            updateCommand.Parameters.AddWithValue(
                "@ProdName", product.ProdName);
            updateCommand.Parameters.AddWithValue(
                "@ProductId", product.ProductId);

            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                    return true;
                else
                    return false;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /*Chen Code Start*/
        public static List<Product> GetProducts(string filter)
        {
            List<Product> products = new List<Product>();

            SqlConnection connection = TravelExpertsDB.GetConnection();
            string query = "SELECT ProductId, ProdName FROM Products " +
                " WHERE ProductId > -1 " + filter;
            SqlCommand command = new SqlCommand(query, connection);

            try
            {
                // Open db connection and run query
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                // Read dataset fill Products list
                while (reader.Read())
                {
                    Product product = new Product();

                    product.ProductId = (int)reader["ProductId"];
                    product.ProdName = reader["ProdName"].ToString();

                    products.Add(product);

                }
                return products;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /* Author: Pooja Jairath
        * Date: 6th July 2015
        * Purpose: Function to retrieve product data based on ProductId from table Products, to make it available to be edited
        */
        public static Product GetProduct(int productid)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string selectStatement = "SELECT * from Products WHERE ProductId = @ProductId ";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
            selectCommand.Parameters.AddWithValue("@ProductId", productid);

            try
            {
                connection.Open();
                SqlDataReader packReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (packReader.Read())
                {
                    Product product = new Product();
                    product.ProductId = (int)packReader["ProductId"];
                    product.ProdName = packReader["ProdName"].ToString();
                    return product;
                }
                else
                {
                    return null;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        // Author: Mark Poffenroth
        /// <summary>
        /// Gets list of Products objects NOT already included w/ specfied Package Id
        /// </summary>
        /// <param name="packageid">Package Id used to get Packages</param>
        /// <returns>List of Product objects available to add to specfied Package Id.</returns>
        public static List<Product> GetPackageProductsToAdd(int packageid)
        {
            List<Product> products = new List<Product>();

            // Define query string
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string query = "SELECT DISTINCT pr.ProductId, pr.ProdName " +
                " FROM Products pr " +
                " INNER JOIN Products_Suppliers ps ON pr.ProductId = ps.ProductId " +
                " INNER JOIN Packages_Products_Suppliers pps ON pps.ProductSupplierId = ps.ProductSupplierId " +
                " INNER JOIN Packages pa ON pa.PackageId = pps.PackageId " +
                " WHERE pa.PackageId <> @PackageId";
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@PackageId", packageid);

            try
            {
                // Open db connection and run query
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                // Read dataset fill Packages list
                while (reader.Read())
                {
                    Product product = new Product();
                    product.ProductId = (int)reader["ProductId"];
                    product.ProdName = reader["ProdName"].ToString();
                    products.Add(product);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return products;
        }