public static bool AddSupplier(Supplier supplier)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string insertStatement =
                "INSERT Suppliers " +
                "(SupName, SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL) " +
                "VALUES (@SupName, @SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProv, @SupConPostal, @SupConCountry, @SupConBusPhone, @SupConFax, @SupConEmail, @SupConURL)";
            SqlCommand insertCommand =
                new SqlCommand(insertStatement, connection);
            insertCommand.Parameters.AddWithValue(
                "@SupName", supplier.SupName);
            insertCommand.Parameters.AddWithValue(
                "@SupplierContactId", supplier.SupplierContactId);
            insertCommand.Parameters.AddWithValue(
               "@SupConFirstName", supplier.SupConFirstName);
            insertCommand.Parameters.AddWithValue(
                "@SupConLastName", supplier.SupConLastName);
            insertCommand.Parameters.AddWithValue(
                "@SupConCompany", supplier.SupConCompany);
            insertCommand.Parameters.AddWithValue(
                "@SupConAddress", supplier.SupConAddress);
            insertCommand.Parameters.AddWithValue(
               "@SupConCity", supplier.SupConCity);
            insertCommand.Parameters.AddWithValue(
               "@SupConProv", supplier.SupConProv);
            insertCommand.Parameters.AddWithValue(
               "@SupConPostal", supplier.SupConPostal);
            insertCommand.Parameters.AddWithValue(
               "@SupConCountry", supplier.SupConCountry);
            insertCommand.Parameters.AddWithValue(
               "@SupConBusPhone", supplier.SupConBusPhone);
            insertCommand.Parameters.AddWithValue(
               "@SupConFax", supplier.SupConFax);
            insertCommand.Parameters.AddWithValue(
               "@SupConEmail", supplier.SupConEmail);
            insertCommand.Parameters.AddWithValue(
               "@SupConURL", supplier.SupConURL);
            try
            {
                connection.Open();
                int count = insertCommand.ExecuteNonQuery();

                // Inform user and return execution status
                if (count > 0)
                {
                    // MessageBox.Show("Inserted record: " + package.PackageId);
                    return true;
                }
                else
                    return false;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        //function to delete the SQL data
        public static bool DeleteSupplier(Supplier supplier)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();  //connect the SQL7
            string deleteForeign =
                "DELETE FROM SupplierContacts " +
                "WHERE SupplierId = @SupplierId ";
            SqlCommand deleteForeignCommand =
                new SqlCommand(deleteForeign, connection);  //call the SQL deleteForeign command to delete the dependent table
            deleteForeignCommand.Parameters.AddWithValue(
                "@SupplierId", supplier.SupplierId);

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

            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();
            }
        }
        public static bool UpdateSupplier(Supplier supplier)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string updateStatement =
                "UPDATE Suppliers SET " +
                "SupName = @SupName, " +
                "SupplierContactId = @SupplierContactId, " +
                "SupConFirstName = @SupConFirstName, " +
                "SupConLastName = @SupConLastName, " +
                "SupConCompany = @SupConCompany, " +
                "SupConAddress = @SupConAddress " +
                "SupConCity = @SupConCity " +
                "SupConProv = @SupConProv " +
                "SupConPostal = @SupConPostal " +
                "SupConCountry = @SupConCountry " +
                "SupConBusPhone = @SupConBusPhone " +
                "SupConFax = @SupConFax " +
                "SupConEmail = @SupConEmail " +
                "SupConURL = @SupConURL " +
                "WHERE SupplierId = @SupplierId";
            SqlCommand updateCommand =
                new SqlCommand(updateStatement, connection);
            updateCommand.Parameters.AddWithValue(
                "@SupName", supplier.SupName);
            updateCommand.Parameters.AddWithValue(
                "@SupplierContactId", supplier.SupplierContactId);
            updateCommand.Parameters.AddWithValue(
                "@SupConFirstName", supplier.SupConFirstName);
            updateCommand.Parameters.AddWithValue(
                "@SupConLastName", supplier.SupConLastName);
            updateCommand.Parameters.AddWithValue(
                "@SupConCompany", supplier.SupConCompany);
            updateCommand.Parameters.AddWithValue(
                "@SupConAddress", supplier.SupConAddress);
            updateCommand.Parameters.AddWithValue(
                "@SupConCity", supplier.SupConCity);
            updateCommand.Parameters.AddWithValue(
               "@SupConProv", supplier.SupConProv);
            updateCommand.Parameters.AddWithValue(
               "@SupConPostal", supplier.SupConPostal);
            updateCommand.Parameters.AddWithValue(
               "@SupConCountry", supplier.SupConCountry);
            updateCommand.Parameters.AddWithValue(
               "@SupConBusPhone", supplier.SupConBusPhone);
            updateCommand.Parameters.AddWithValue(
               "@SupConFax", supplier.SupConFax);
            updateCommand.Parameters.AddWithValue(
               "@SupConEmail", supplier.SupConEmail);
            updateCommand.Parameters.AddWithValue(
               "@SupConURL", supplier.SupConURL);

            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 End*/
        /* Author: Pooja */
        public static List<Supplier> GetSuppliers()
        {
            List<Supplier> suppliers = new List<Supplier>();

            SqlConnection connection = TravelExpertsDB.GetConnection();
            string query = "SELECT Suppliers.SupplierId, SupName " +
                "FROM Suppliers " +
                // " INNER JOIN SupplierContacts on SupplierContacts.SupplierId = Suppliers.SupplierId " +
                "WHERE Suppliers.SupplierId > -1 ";
            SqlCommand command = new SqlCommand(query, connection);

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

                // Read dataset fill Packages list
                while (reader.Read())
                {
                    Supplier supplier = new Supplier();

                    supplier.SupplierId = (int)reader["SupplierId"];
                    supplier.SupName = reader["SupName"].ToString();

                    suppliers.Add(supplier);
                }
                return suppliers;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /*Chen Code Start*/
        public static List<Supplier> GetSuppliers(string filter)
        {
            List<Supplier> suppliers = new List<Supplier>();

            SqlConnection connection = TravelExpertsDB.GetConnection();
            string query = "SELECT Suppliers.SupplierId, SupName, SupplierContactId, SupConFirstName, SupConLastName, " +
                " SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, " +
                " SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL " +
                " FROM Suppliers " +
                " INNER JOIN SupplierContacts on SupplierContacts.SupplierId = Suppliers.SupplierId " +
                " WHERE Suppliers.SupplierId > -1 " + filter;
            SqlCommand command = new SqlCommand(query, connection);

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

                // Read dataset fill Packages list
                while (reader.Read())
                {
                    Supplier supplier = new Supplier();

                    supplier.SupplierId = (int)reader["SupplierId"];
                    supplier.SupName = reader["SupName"].ToString();
                    supplier.SupplierContactId = (int)reader["SupplierContactId"];
                    supplier.SupConFirstName = reader["SupConFirstName"].ToString();
                    supplier.SupConLastName = reader["SupConLastName"].ToString();

                    supplier.SupConCompany = reader["SupConCompany"].ToString();
                    supplier.SupConAddress = reader["SupConAddress"].ToString();
                    supplier.SupConCity = reader["SupConCity"].ToString();
                    supplier.SupConProv = reader["SupConProv"].ToString();
                    supplier.SupConPostal = reader["SupConPostal"].ToString();

                    supplier.SupConCountry = reader["SupConCountry"].ToString();
                    supplier.SupConBusPhone = reader["SupConBusPhone"].ToString();
                    supplier.SupConFax = reader["SupConFax"].ToString();
                    supplier.SupConEmail = reader["SupConEmail"].ToString();
                    supplier.SupConURL = reader["SupConURL"].ToString();

                    suppliers.Add(supplier);
                }
                return suppliers;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /* Author: Pooja Jairath */
        public static List<int> GetSupplierId()
        {
            List<int> supplierids = new List<int>();

            SqlConnection connection = TravelExpertsDB.GetConnection();
            string query = "SELECT SupplierId FROM Suppliers";
            SqlCommand command = new SqlCommand(query, connection);

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

                // Read dataset fill Packages list
                while (reader.Read())
                {
                    Supplier supplier = new Supplier();

                    supplier.SupplierId = (int)reader["SupplierId"];

                    supplierids.Add(supplier.SupplierId);

                }
                return supplierids;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static Supplier GetSupplier(int supplierid)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string selectStatement = //"SELECT * from Suppliers WHERE SupplierId = @SupplierId ";
            "SELECT Suppliers.SupplierId, SupName, SupplierContactId, SupConFirstName, SupConLastName, " +
                " SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, " +
                " SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL " +
                " FROM Suppliers " +
                " INNER JOIN SupplierContacts on SupplierContacts.SupplierId = Suppliers.SupplierId " +
                " WHERE Suppliers.SupplierId > -1 ";

            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
            selectCommand.Parameters.AddWithValue("@SupplierId", supplierid);

            try
            {
                connection.Open();
                SqlDataReader suppReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (suppReader.Read())
                {
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = (int)suppReader["SupplierId"];
                    supplier.SupName = suppReader["SupName"].ToString();
                    supplier.SupplierContactId = (int)suppReader["SupplierContactId"];
                    supplier.SupConFirstName = suppReader["SupConFirstName"].ToString();
                    supplier.SupConLastName = suppReader["SupConLastName"].ToString();
                    supplier.SupConCompany = suppReader["SupConCompany"].ToString();
                    supplier.SupConAddress = suppReader["SupConAddress"].ToString();
                    supplier.SupConCity = suppReader["SupConCity"].ToString();
                    supplier.SupConProv = suppReader["SupConProv"].ToString();
                    supplier.SupConPostal = suppReader["SupConPostal"].ToString();
                    supplier.SupConCountry = suppReader["SupConCountry"].ToString();
                    supplier.SupConBusPhone = suppReader["SupConBusPhone"].ToString();
                    supplier.SupConFax = suppReader["SupConFax"].ToString();
                    supplier.SupConEmail = suppReader["SupConEmail"].ToString();
                    supplier.SupConURL = suppReader["SupConURL"].ToString();
                    return supplier;
                }
                else
                {
                    return null;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        // Author: Mark Poffenroth
        /// <summary>
        /// Gets list of Supplier objects for specfied Product Id
        /// </summary>
        /// <param name="packageid">Product Id used to get Suppliers</param>
        /// <returns>List of Supplier objects for specfied Product Id.</returns>
        public static List<Supplier> GetProductSuppliers(int productID)
        {
            List<Supplier> suppliers = new List<Supplier>();

            // Define connection and query info
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string query = "SELECT s.SupplierId, s.SupName " +
                " FROM Suppliers s " +
                " INNER JOIN Products_Suppliers ps ON s.SupplierId = ps.SupplierId " +
                " WHERE ps.ProductId = @ProductId";
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@ProductId", productID);

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

                // Read dataset fill Packages list
                while (reader.Read())
                {
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = (int)reader["SupplierId"];
                    supplier.SupName = reader["SupName"].ToString();
                    suppliers.Add(supplier);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return suppliers;
        }