示例#1
0
        /// <summary>
        /// Retrieves each Product Supplier in a list
        /// </summary>
        /// <returns></returns>
        public static List <Product_Supplier> getAllProductSuppliers()
        {
            SqlConnection connection      = MMATravelExperts.GetConnection();
            string        selectStatement = "select ProdName, SupName, Products.ProductId, Suppliers.SupplierId, ProductSupplierId from Products_Suppliers, Products, Suppliers " +
                                            "where Products.ProductId=Products_Suppliers.ProductId AND Suppliers.SupplierId=Products_Suppliers.SupplierId";

            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();

                List <Product_Supplier> productSuppliersList = new List <Product_Supplier>();
                while (reader.Read())
                {
                    Product_Supplier myPS = new Product_Supplier();
                    myPS.ProductSupplierId = (int)reader["ProductSupplierId"];
                    myPS.ProductName       = reader["ProdName"].ToString();
                    myPS.ProductId         = (int)reader["ProductID"];
                    myPS.SupName           = reader["SupName"].ToString();
                    myPS.SupplierId        = (int)reader["SupplierID"];
                    productSuppliersList.Add(myPS);
                }
                return(productSuppliersList);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
示例#2
0
        /// <summary>
        /// Adds the input product supplier to the input package with a new row inserted into the packages_products_suppliers table
        /// </summary>
        /// <param name="ps">the product-supplier to add</param>
        /// <param name="pack">the package to add to</param>
        /// <returns>true if successful, false if fail</returns>
        public static bool addProductSupplierToPackage(Product_Supplier ps, Package pack)
        {
            SqlConnection connection      = MMATravelExperts.GetConnection();
            string        insertStatement = "Insert into Packages_Products_Suppliers values (@packageId,@productSupplierId)";
            SqlCommand    insertCommand   = new SqlCommand(insertStatement, connection);

            insertCommand.Parameters.AddWithValue("@PackageId", pack.PackageId);
            insertCommand.Parameters.AddWithValue("@productSupplierId", ps.ProductSupplierId);
            try
            {
                connection.Open();
                int result = insertCommand.ExecuteNonQuery();
                if (result == 1)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
 //get the product supplier selected by the user.
 private void dgvProdSups_SelectionChanged(object sender, EventArgs e)
 {
     if (dgvProdSups.SelectedRows.Count != 0)
     {
         selectedProductSupplier = (Product_Supplier)dgvProdSups.SelectedRows[0].DataBoundItem;
     }
 }
示例#4
0
        public static bool UpdateProduct_Supplier(Product_Supplier oldSupp, Product_Supplier newSupp)
        {
            // Updates the Product_Suppliers Table
            // parameter oldProdSupp ... the old row as an instance of Product_Supplier class
            // parameter newProdSupp ... the new row as an instance of Product_Supplier class
            // returns true row updated, false row not updated
            // throws SqlException and Exception
            SqlConnection connection      = MMATravelExperts.GetConnection();
            string        updateStatement = "UPDATE Products_Suppliers SET ProductSupplierId=@newProductSupplierId, ProductId=@newSupplierId, " +
                                            "SupplierID=@newSupplierId WHERE " +
                                            "ProductSupplierId=@oldProductSupplierId and ProductId=@newProductId and SupplierId=@oldSupplierId";
            SqlCommand updateCommand = new SqlCommand(updateStatement, connection);

            // new products_supplier listing
            updateCommand.Parameters.AddWithValue("@newProductSupplierId", newSupp.ProductSupplierId);
            updateCommand.Parameters.AddWithValue("@newProductId", newSupp.ProductId);
            updateCommand.Parameters.AddWithValue("@newSupplierId", newSupp.SupplierId);

            // old products_supplier listing
            updateCommand.Parameters.AddWithValue("@oldProductSupplierId", oldSupp.ProductSupplierId);
            updateCommand.Parameters.AddWithValue("@newProductId", oldSupp.ProductId);
            updateCommand.Parameters.AddWithValue("@oldSupplierId", oldSupp.SupplierId);
            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true); // rows updated
                }
                else
                {
                    return(false); //rows not updated
                }
            }
            catch (SqlException SqlEx)
            {
                throw SqlEx;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                connection.Close();
            }
        }
示例#5
0
        public static int AddProduct_Supplier(Product_Supplier prodSupp)
        {
            // Add a Product_Supplier instance to the Product_Suppliers Table
            // ProdSupp is the instance of Product_Supplier class
            // returns the ProductSupplierId (pk) of the row inserted or -1 if not added to table
            // throws SqlException and Exception
            SqlConnection connection      = MMATravelExperts.GetConnection();
            String        insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierID) " +
                                            "VALUES (@ProductId, @SupplierId)";
            SqlCommand insertCommand = new SqlCommand(insertStatement, connection);

            insertCommand.Parameters.AddWithValue("@ProductId", prodSupp.ProductId);
            insertCommand.Parameters.AddWithValue("@SupplierId", prodSupp.SupplierId);
            try
            {
                connection.Open();
                int numRows = insertCommand.ExecuteNonQuery();
                if (numRows > 0)
                {
                    string     selectStatement = "SELECT ProductSupplierId FROM Products_Suppliers where ProductId=@ProductId and SupplierID=@SupplierID";
                    SqlCommand selectCommand   = new SqlCommand(selectStatement, connection);
                    selectCommand.Parameters.AddWithValue("@ProductId", prodSupp.ProductId);
                    selectCommand.Parameters.AddWithValue("@SupplierID", prodSupp.SupplierId);
                    int suppId = (int)(selectCommand.ExecuteScalar());
                    return(suppId);
                }
                else
                {
                    return(-1);
                }
            }
            catch (SqlException SqlEx)
            {
                throw SqlEx;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// Retrieves each Product Supplier in a list
        /// </summary>
        /// <returns></returns>
        public static List<Product_Supplier> getAllProductSuppliers()
        {

            SqlConnection connection = MMATravelExperts.GetConnection();
            string selectStatement = "select ProdName, SupName, Products.ProductId, Suppliers.SupplierId, ProductSupplierId from Products_Suppliers, Products, Suppliers "+
                                     "where Products.ProductId=Products_Suppliers.ProductId AND Suppliers.SupplierId=Products_Suppliers.SupplierId";
                                     
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);            
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();

                List<Product_Supplier> productSuppliersList = new List<Product_Supplier>();
                while (reader.Read())
                {
                    Product_Supplier myPS = new Product_Supplier();
                    myPS.ProductSupplierId = (int)reader["ProductSupplierId"];
                    myPS.ProductName = reader["ProdName"].ToString();
                    myPS.ProductId = (int)reader["ProductID"];
                    myPS.SupName = reader["SupName"].ToString();
                    myPS.SupplierId = (int)reader["SupplierID"];
                    productSuppliersList.Add(myPS);
                }
                return productSuppliersList;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static bool UpdateProduct_Supplier(Product_Supplier oldSupp, Product_Supplier newSupp)
        {
            // Updates the Product_Suppliers Table 
            // parameter oldProdSupp ... the old row as an instance of Product_Supplier class
            // parameter newProdSupp ... the new row as an instance of Product_Supplier class
            // returns true row updated, false row not updated
            // throws SqlException and Exception
            SqlConnection connection = MMATravelExperts.GetConnection();
            string updateStatement = "UPDATE Products_Suppliers SET ProductSupplierId=@newProductSupplierId, ProductId=@newSupplierId, "+
                    "SupplierID=@newSupplierId WHERE "+
                    "ProductSupplierId=@oldProductSupplierId and ProductId=@newProductId and SupplierId=@oldSupplierId";
            SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
            // new products_supplier listing
            updateCommand.Parameters.AddWithValue("@newProductSupplierId", newSupp.ProductSupplierId);
            updateCommand.Parameters.AddWithValue("@newProductId", newSupp.ProductId);
            updateCommand.Parameters.AddWithValue("@newSupplierId", newSupp.SupplierId);

            // old products_supplier listing
            updateCommand.Parameters.AddWithValue("@oldProductSupplierId", oldSupp.ProductSupplierId);
            updateCommand.Parameters.AddWithValue("@newProductId", oldSupp.ProductId);
            updateCommand.Parameters.AddWithValue("@oldSupplierId", oldSupp.SupplierId);
            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return true; // rows updated
                }
                else
                {
                    return false; //rows not updated
                }
            }
            catch (SqlException SqlEx)
            {
                throw SqlEx;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                connection.Close();
            }
        }
 public static int AddProduct_Supplier(Product_Supplier prodSupp)
 {
     // Add a Product_Supplier instance to the Product_Suppliers Table
     // ProdSupp is the instance of Product_Supplier class
     // returns the ProductSupplierId (pk) of the row inserted or -1 if not added to table
     // throws SqlException and Exception
     SqlConnection connection = MMATravelExperts.GetConnection();
     String insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierID) " +
                              "VALUES (@ProductId, @SupplierId)";
     SqlCommand insertCommand = new SqlCommand(insertStatement, connection);            
     insertCommand.Parameters.AddWithValue("@ProductId", prodSupp.ProductId);
     insertCommand.Parameters.AddWithValue("@SupplierId", prodSupp.SupplierId);
     try
     {
         connection.Open();
         int numRows = insertCommand.ExecuteNonQuery();
         if (numRows > 0)
         {
             string selectStatement = "SELECT ProductSupplierId FROM Products_Suppliers where ProductId=@ProductId and SupplierID=@SupplierID";
             SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
             selectCommand.Parameters.AddWithValue("@ProductId",prodSupp.ProductId);
             selectCommand.Parameters.AddWithValue("@SupplierID",prodSupp.SupplierId);
             int suppId = (int)(selectCommand.ExecuteScalar());
             return suppId;
         }
         else
         {
             return -1;
         }
     }
     catch (SqlException SqlEx)
     {
         throw SqlEx;
     }
     catch (Exception Ex)
     {
         throw Ex;
     }
     finally
     {
         connection.Close();
     }
 }
 /// <summary>
 /// Adds the input product supplier to the input package with a new row inserted into the packages_products_suppliers table
 /// </summary>
 /// <param name="ps">the product-supplier to add</param>
 /// <param name="pack">the package to add to</param>
 /// <returns>true if successful, false if fail</returns>
 public static bool addProductSupplierToPackage(Product_Supplier ps, Package pack)
 {
     SqlConnection connection = MMATravelExperts.GetConnection();
     string insertStatement = "Insert into Packages_Products_Suppliers values (@packageId,@productSupplierId)";
     SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
     insertCommand.Parameters.AddWithValue("@PackageId", pack.PackageId);
     insertCommand.Parameters.AddWithValue("@productSupplierId", ps.ProductSupplierId);
     try
     {
         connection.Open();
         int result = insertCommand.ExecuteNonQuery();
         if (result == 1)
             return true;
         else
             return false;
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         connection.Close();
     }
 }
        //checks if the product supplier the user wants to add exists.
        public bool ProdSupDoesNotExist(Product_Supplier newPS)
        {
            var prodSups = productSuppliers.Where<Product_Supplier>(x => x.ProductName == newPS.ProductName && x.SupName == newPS.SupName);
            if (prodSups.Count() == 0)
            {
                return true;
            }

            return false;
        }
 // creates a Product_Supplier obbject from the selected indices of each datagrid
 private Product_Supplier createProductSupplier()
 {
     Product_Supplier newProdSup = new Product_Supplier();
     newProdSup.ProductId = ActiveProduct.ProductId;
     newProdSup.ProductName = ActiveProduct.ProdName;
     newProdSup.SupName = activeSupplier.SupName;
     newProdSup.SupplierId = activeSupplier.SupplierID;
     return newProdSup;
 }
 private void cbProductSuppliers_SelectedIndexChanged(object sender, EventArgs e)
 {
     if (cbProductSuppliers.SelectedItem != null)
     {
         selectedProductSupplier = (Product_Supplier)cbProductSuppliers.SelectedItem;
         txtSearch.Text = (selectedProductSupplier.ToString() == null) ? "" : selectedProductSupplier.ToString();
         if (cbProductSuppliers.DroppedDown != false)
         {
             cbProductSuppliers.DroppedDown = false;
         }
     }
 }