// get all supp and prods from IDs
        public static List <ProductSuppliers> GetInfo(int ID, string type)
        {
            List <ProductSuppliers> list = new List <ProductSuppliers>();
            ProductSuppliers        ProSup;
            SqlConnection           con = TravelExpertsDB.GetConnection();
            string selectQuery;

            if (type == "Product")
            {
                selectQuery = "Select SP.ProductSupplierId, SP.ProductId, P.ProdName, S.SupName, SP.SupplierId " +
                              "from Products P " +
                              "Join Products_Suppliers SP on P.ProductId = SP.ProductId " +
                              "Join Suppliers S on SP.SupplierId = S.SupplierId " +
                              "where SP.ProductId = @prodID";
            }
            else
            {
                selectQuery = "Select SP.ProductSupplierId, SP.ProductId, P.ProdName, S.SupName, SP.SupplierId " +
                              "from Suppliers S " +
                              "Join Products_Suppliers SP on S.SupplierId = SP.SupplierId " +
                              "Join Products P on SP.ProductId = P.ProductId " +
                              "where SP.SupplierId = @supID";
            }
            SqlCommand cmd = new SqlCommand(selectQuery, con);

            if (type == "Product")
            {
                cmd.Parameters.AddWithValue("@prodID", ID);
            }
            else
            {
                cmd.Parameters.AddWithValue("@supID", ID);
            }
            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        ProSup            = new ProductSuppliers();
                        ProSup.ProdName   = (string)reader["ProdName"];
                        ProSup.SupName    = (string)reader["SupName"];
                        ProSup.ProductId  = (int)reader["ProductId"];
                        ProSup.SupplierId = (int)reader["SupplierId"];
                        list.Add(ProSup);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return(list);
        }
        // get prodsupp from db
        public static List <ProductSuppliers> GetProductSuppliers()
        {
            List <ProductSuppliers> ProSups = new List <ProductSuppliers>();
            ProductSuppliers        ProSup;
            SqlConnection           con = TravelExpertsDB.GetConnection();
            string selectQuery          = "Select SP.ProductSupplierId, SP.ProductId, P.ProdName, S.SupName, SP.SupplierId " +
                                          "from Products P " +
                                          "Join Products_Suppliers SP on P.ProductId = SP.ProductId " +
                                          "Join Suppliers S on SP.SupplierId = S.SupplierId";
            SqlCommand cmd = new SqlCommand(selectQuery, con);

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        ProSup                   = new ProductSuppliers();
                        ProSup.ProdName          = (string)reader["ProdName"];
                        ProSup.ProductSupplierId = (int)reader["ProductSupplierId"];
                        ProSup.SupName           = (string)reader["SupName"];
                        ProSup.ProductId         = (int)reader["ProductId"];
                        ProSup.SupplierId        = (int)reader["SupplierId"];
                        ProSups.Add(ProSup);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return(ProSups);
        }