コード例 #1
0
        public static ProductsSuppliers GetSelected(int pId)
        {
            ProductsSuppliers selected        = new ProductsSuppliers();
            SqlConnection     connection      = TRAVELEXPERTSDB.GetConnection();
            string            selectStatement = "select p.ProductId, ProdName, ProductSupplierId, ps.SupplierId, s.SupName " +
                                                "from Products p, Products_Suppliers ps, suppliers s " +
                                                "where p.ProductId = ps.ProductId and ps.SupplierId = s.SupplierId and ProductSupplierId = @ProductSupplierId";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@ProductSupplierId", pId);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                if (reader.Read())
                {
                    selected.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
                    selected.SupplierId        = Convert.ToInt32(reader["SupplierId"]);
                    selected.ProductId         = Convert.ToInt32(reader["ProductId"]);
                    selected.SupName           = reader["SupName"].ToString();
                    selected.ProdName          = reader["ProdName"].ToString();
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(selected);
        }
コード例 #2
0
        public static List <ProductsSuppliers> GetSupplierName(int selectedprodid)
        {
            List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
            SqlConnection            connection         = TRAVELEXPERTSDB.GetConnection();
            string selectStatement = "select SupName, ps.SupplierId " +
                                     "from Products_Suppliers ps, Suppliers s " +
                                     "where ps.SupplierId = s.SupplierId and ProductId = @ProductId " +
                                     "order by SupName";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@ProductId", selectedprodid);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    ProductsSuppliers p = new ProductsSuppliers();
                    p.SupName    = reader["SupName"].ToString();
                    p.SupplierId = Convert.ToInt32(reader["SupplierId"]);
                    productssupplierid.Add(p);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(productssupplierid);
        }
コード例 #3
0
        public static List <ProductsSuppliers> GetProductSupplierId2Inputs(int selectedprodid, int?selectedsupplier)
        {
            List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
            SqlConnection            connection         = TRAVELEXPERTSDB.GetConnection();
            string selectStatement = "select ProductSupplierId  from Products_Suppliers " +
                                     "where ProductId= @ProductId and SupplierId=@SupplierId";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@ProductId", selectedprodid);
            selectCommand.Parameters.AddWithValue("@SupplierId", selectedsupplier);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    ProductsSuppliers p = new ProductsSuppliers();
                    p.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
                    productssupplierid.Add(p);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(productssupplierid);
        }
コード例 #4
0
        public static bool UpdateProductSupplier(ProductsSuppliers oldProd, ProductsSuppliers newProd)
        {
            bool          successful   = false;
            SqlConnection connection   = TRAVELEXPERTSDB.GetConnection();
            string        updateString = "update Products_Suppliers set ProductId = @newProd, SupplierId = @newSup " +
                                         "where ProductSupplierId = @oldProdSupId and ProductId = @oldProdId and SupplierId = @oldSupId";

            SqlCommand updateCommand = new SqlCommand(updateString, connection);

            updateCommand.Parameters.AddWithValue("@newProd", newProd.ProductId);
            updateCommand.Parameters.AddWithValue("@newSup", newProd.SupplierId);
            updateCommand.Parameters.AddWithValue("@oldProdSupId", oldProd.ProductSupplierId);
            updateCommand.Parameters.AddWithValue("@oldProdId", oldProd.ProductId);
            updateCommand.Parameters.AddWithValue("@oldSupId", oldProd.SupplierId);

            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //connection.Close();
            }
            return(successful);
        }
コード例 #5
0
        public static List <ProductsSuppliers> GetProductsSupplierId()
        {
            List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
            SqlConnection            connection         = TRAVELEXPERTSDB.GetConnection();
            string selectStatement = "select ProductSupplierId, ProductId, SupplierId "
                                     + "from Products_Suppliers  "
                                     + "ORDER BY ProductSupplierId";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    ProductsSuppliers p = new ProductsSuppliers();
                    p.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
                    p.SupplierId        = Convert.ToInt32(reader["SupplierId"]);
                    p.ProductId         = Convert.ToInt32(reader["ProductId"]);
                    productssupplierid.Add(p);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(productssupplierid);
        }
コード例 #6
0
 // STEP 2
 private void DisplayPS(ProductsSuppliers displayPs)
 {
     if (displayPs.ProductSupplierId != 0)
     {
         lstOutput.Items.Clear();
         lstOutput.Items.Add(displayPs.ProductSupplierId);
         lstOutput.Items.Add(displayPs.ProdName + "," + "\t" + "\t" + displayPs.ProductId);
         lstOutput.Items.Add(displayPs.SupName + "," + "\t" + displayPs.SupplierId);
     }
 }
コード例 #7
0
        // STEP 3
        private void cboProductsSupplierrId_SelectedIndexChanged(object sender, EventArgs e)
        {
            int pid = 0;

            try
            {
                pid = Convert.ToInt32(cboProductsSupplierrId.SelectedValue);
            }
            catch { }
            SelectedPS = ProductSupplierIdDB.GetSelected(pid);
            this.DisplayPS(SelectedPS);
        }
コード例 #8
0
        //*******************************************************************
        //*                                                                 *
        //*                     JUSTIN ALEX MIKE AYI                        *
        //*                                                                 *
        //*******************************************************************


        // STEP 1
        private void btnAdd_Click(object sender, EventArgs e)
        {
            frmAddModifyProductsSuppliers addProductSupplierForm = new frmAddModifyProductsSuppliers();

            addProductSupplierForm.addProductsSuppler = true;
            DialogResult result = addProductSupplierForm.ShowDialog();

            if (result == DialogResult.OK)
            {
                productsupplier = addProductSupplierForm.productsupplier;
                this.GetProductsSuppliers();
                cboProductsSupplierrId.SelectedValue = productsupplier.ProductSupplierId;
                MessageBox.Show("New Products Suppliers were added");
            }
        }
コード例 #9
0
        public static List <ProductsSuppliers> GetUniqueProSupId(string logic)
        {
            List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
            SqlConnection            connection         = TRAVELEXPERTSDB.GetConnection();
            string selectStatement;

            if (logic == "p")
            {
                selectStatement = "select ProductId, ProdName from Products ";
            }
            else
            {
                selectStatement = "select SupplierId, SupName from Suppliers ";
            }
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    ProductsSuppliers p = new ProductsSuppliers();
                    if (logic == "p")
                    {
                        p.ProductId = Convert.ToInt32(reader["ProductId"]);
                        p.ProdName  = reader["ProdName"].ToString();
                    }
                    else
                    {
                        p.SupplierId = Convert.ToInt32(reader["SupplierId"]);
                        p.SupName    = reader["SupName"].ToString();
                    }
                    productssupplierid.Add(p);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(productssupplierid);
        }
コード例 #10
0
        private void btnAccept_Click(object sender, EventArgs e)
        {
            if (Validator.IsPresent(cboProdId) &&
                Validator.IsPresent(cboSupId))
            {
                if (addProductsSuppler)
                {
                    productsupplier = new ProductsSuppliers();
                    this.PutProductSupplierData(productsupplier);
                    try
                    {
                        productsupplier.ProductSupplierId = ProductSupplierIdDB.AddProductSupplier(productsupplier);
                        this.DialogResult = DialogResult.OK;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, ex.GetType().ToString());
                    }
                }

                else
                {
                    ProductsSuppliers newProductSupplier = new ProductsSuppliers();
                    newProductSupplier.ProductSupplierId = productsupplier.ProductSupplierId;
                    this.PutProductSupplierData(newProductSupplier);

                    try
                    {
                        if (!ProductSupplierIdDB.UpdateProductSupplier(productsupplier, newProductSupplier))
                        {
                            MessageBox.Show("Another user has updated or " +
                                            "deleted that Product Supplier.", "Database Error");
                            this.DialogResult = DialogResult.Retry;
                        }
                        else
                        {
                            productsupplier   = newProductSupplier;
                            this.DialogResult = DialogResult.OK;
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, ex.GetType().ToString());
                    }
                }
            }
        }
コード例 #11
0
        // STEP 2
        private void btnModify_Click(object sender, EventArgs e)
        {
            frmAddModifyProductsSuppliers modifyProductSupplierForm = new frmAddModifyProductsSuppliers();

            productsupplier = SelectedPS;
            modifyProductSupplierForm.addProductsSuppler = false;
            modifyProductSupplierForm.productsupplier    = productsupplier;
            DialogResult result = modifyProductSupplierForm.ShowDialog();

            if (result == DialogResult.OK)
            {
                this.GetProductsSuppliers();
                cboProductsSupplierrId.SelectedValue = productsupplier.ProductSupplierId;
            }
            else if (result == DialogResult.Retry)
            {
                this.GetProductsSuppliers();
                cboProductsSupplierrId.SelectedValue = productsupplier.ProductSupplierId;
            }
            MessageBox.Show("Products Suppliers was updated");
        }
コード例 #12
0
        private void btnDelete_Click(object sender, EventArgs e)
        {
            ProductsSuppliers pps  = new ProductsSuppliers();
            package           psup = new package();

            psup.PackageId        = ((package)cboPkgPPS.SelectedItem).PackageId;
            pps.ProductSupplierId = ((ProductsSuppliers)cboPSPPS.SelectedItem).ProductSupplierId;


            bool logic = Packages_Products_SuppliersDB.Delete(psup, pps);

            dataGridPackProdSup.DataSource = Packages_Products_SuppliersDB.GetPackages_Products_Supplierss();

            if (logic == true)
            {
                MessageBox.Show("You have Deleted Product from the package Package!");
            }
            else
            {
                MessageBox.Show("The selected product does not exists in the selected package");
                dataGridPackProdSup.Rows[3].Selected = true;
            }
        }
コード例 #13
0
        public static int AddProductSupplier(ProductsSuppliers productsupplier)
        {
            int prodID = 0;
            // prepare connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // prepare the statement
            string     insertString  = "insert into Products_Suppliers(ProductId, SupplierId) Values(@ProductId, @SupplierId)";
            SqlCommand insertCommand = new SqlCommand(insertString, connection);

            insertCommand.Parameters.AddWithValue("@ProductId", productsupplier.ProductId);
            insertCommand.Parameters.AddWithValue("@SupplierId", productsupplier.SupplierId);
            try
            {
                // open connection
                connection.Open();

                // execute the statement
                int i = insertCommand.ExecuteNonQuery();
                if (i == 1) // one record inserted
                {
                    // retrieve customer id from the added record
                    string     selectString  = "select ident_current('Products_Suppliers') from Products_Suppliers";
                    SqlCommand selectCommand = new SqlCommand(selectString, connection);
                    prodID = Convert.ToInt32(selectCommand.ExecuteScalar()); // (int) does not work!!!
                }
            }
            catch (Exception ex)
            {
                throw ex; // pass the buck
            }
            finally
            {
                connection.Close();
            }
            return(prodID);
        }
コード例 #14
0
        //method to delete Alex
        public static bool Delete(package psup, ProductsSuppliers pps)
        {
            bool successful = false;

            SqlConnection connection   = TRAVELEXPERTSDB.GetConnection();
            string        deleteString = "delete from Packages_Products_Suppliers where PackageId = @PackageId and ProductSupplierId=@ProductSupplierId";

            SqlCommand deleteCommand = new SqlCommand(deleteString, connection);

            deleteCommand.Parameters.AddWithValue("@PackageId", psup.PackageId);
            deleteCommand.Parameters.AddWithValue("@ProductSupplierId", pps.ProductSupplierId);

            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
                else
                {
                    return(false);
                }
            }
            catch (Exception ex)
            {
                //throw ex;
                MessageBox.Show("Delete supplier from other tables first");
            }
            finally
            {
                connection.Close();
            }

            return(successful);
        }
コード例 #15
0
 private void PutProductSupplierData(ProductsSuppliers productsupplier)
 {
     productsupplier.ProductId  = (int)cboProdId.SelectedValue;
     productsupplier.SupplierId = (int)cboSupId.SelectedValue;
 }