Beispiel #1
0
        public static int AddSupplier(Supplier supplier)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string insertStatement = "INSERT Into Suppliers " +
                                     "(SupplierId, SupName) " +
                                     "VALUES (@SupId, @SupName)";
            SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
            insertCommand.Parameters.AddWithValue("@SupName", supplier.SupName);

            try
            {
                // open the connection
                connection.Open();
                string selectStatement =
                  "SELECT max(SupplierId) FROM Suppliers"; // this is to select the last id so as to use the next one
                SqlCommand selectCommand =
                    new SqlCommand(selectStatement, connection);
                int supplierId = Convert.ToInt32(selectCommand.ExecuteScalar()) + 1;
                insertCommand.Parameters.AddWithValue("@SupId", supplierId);
                insertCommand.ExecuteNonQuery();
                return supplierId;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        private void btnsave_Click(object sender, EventArgs e)
        {
            if (!IsValidData()) return;

            // ask for confirmation before saving
            DialogResult result = MessageBox.Show("Save the entered data?", "Confirm Save", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (result == DialogResult.No) return;

            if (addMode) // add mode
            {
                // call method to accept the data from the message boxes
                supplier = new Supplier();
                this.AcceptSupplierData(supplier);
                try
                {
                    // call add supplier method to save the entered data and return the id of the new package
                    supplier.SupplierId = SupplierDB.AddSupplier(supplier);
                    if (supplier.SupplierId > 0)
                    {
                        this.DialogResult = DialogResult.OK;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.GetType().ToString());
                }
            }
            else // modify mode
            {
                Supplier newSupplier = new Supplier();
                newSupplier.SupplierId = supplier.SupplierId;
                this.AcceptSupplierData(newSupplier);
                try
                {
                    // call the edit package methode and check if succeeded
                    if (SupplierDB.UpdateSupplier(supplier, newSupplier))
                    {
                        supplier = newSupplier;
                        this.DialogResult = DialogResult.OK;
                    }
                    else
                    {
                        MessageBox.Show("Another user has already updated or deleted that supplier.", "Database Error");
                        this.DialogResult = DialogResult.Retry;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.GetType().ToString());
                }
            }
        }
Beispiel #3
0
        // make method to get all the suppliers of a specific product
        public static List<Supplier> GetSuppliersOfProduct(int productId)
        {
            List<Supplier> suppliers = new List<Supplier>();
            // establish a connection with the database
            SqlConnection connection = TravelExpertsDB.GetConnection();
            // create select statement to select from the database
            string selectStatement =
                "SELECT * " +
                "FROM Suppliers " +
                "WHERE SupplierId in (Select SupplierId from Products_Suppliers where ProductId = @ProductId)";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);
            selectCommand.Parameters.AddWithValue("@ProductId", productId);

            // try to catch exceptions
            try
            {
                // open the connection
                connection.Open();
                SqlDataReader reader =
                    selectCommand.ExecuteReader(); // retrieve multiple rows
                while (reader.Read()) // there is a row
                {
                    // process the row
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = Convert.ToInt32(reader["SupplierId"]);
                    supplier.SupName = reader["SupName"].ToString();
                    suppliers.Add(supplier);
                }
                return suppliers;
            }
            catch (SqlException ex)
            {
                throw ex; // throw the exception for the form to handle
            }
            finally
            {
                connection.Close();
            }
        }
 private void AcceptSupplierData(Supplier supplier)
 {
     supplier.SupName = txtSupplierName.Text;
 }
Beispiel #5
0
        public static List<string> GetProductsAndSuppliers(int packageId)
        {
            List<string> listOfProdSup = new List<string>();

            // establish a connection with the database
            SqlConnection connection = TravelExpertsDB.GetConnection();
            // create select statement to select from the database
            string selectStatement =
            "select packages_products_suppliers.packageId, products.prodname, " +
            "suppliers.Supname " +
            "from packages_products_suppliers inner join " +
            "(products inner join " +
                "(products_suppliers inner join suppliers on products_suppliers.supplierid = suppliers.supplierid) " +
                    "on products.productid = products_suppliers.productid) " +
                        "on packages_products_suppliers.productsupplierid = products_suppliers.productsupplierid " +
                            "where PackageId = @PackageId";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
            selectCommand.Parameters.AddWithValue("@PackageId", packageId);
            // try to catch exceptions
            try
            {
                // open the connection
                connection.Open();
                SqlDataReader reader =
                    selectCommand.ExecuteReader(); // retrieve multiple rows
                while (reader.Read()) // there is a row
                {
                    // process the row

                    Product product = new Product();
                    product.ProdName = reader["ProdName"].ToString();

                    Supplier supplier = new Supplier();
                    supplier.SupName = reader["SupName"].ToString();

                    listOfProdSup.Add(product.ProdName + " from " + supplier.SupName);

                }
                return listOfProdSup;
            }
            catch (SqlException ex)
            {
                throw ex; // throw the exception for the form to handle
            }
            finally
            {
                connection.Close();
            }
        }
Beispiel #6
0
 public static bool DeleteSupplier(Supplier supplier)
 {
     SqlConnection connection = TravelExpertsDB.GetConnection();
     string deleteStatement = "DELETE FROM SupplierContacts " +
                              "WHERE SupplierId = @SupplierId ";
     SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
     deleteCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
     string deleteStatement2 = "DELETE FROM Suppliers " +
                              "WHERE SupplierId = @SupplierId " +
                              "AND SupName = @SupName";
     SqlCommand deleteCommand2 = new SqlCommand(deleteStatement2, connection);
     deleteCommand2.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
     deleteCommand2.Parameters.AddWithValue("@SupName", supplier.SupName);
     try
     {
         connection.Open();
         int count = deleteCommand.ExecuteNonQuery();
         count = deleteCommand2.ExecuteNonQuery();
         if (count > 0)
             return true;
         else
             return false;
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         connection.Close();
     }
 }
Beispiel #7
0
        // Method to get Supplier by name
        public static Supplier GetSupplierByName(string supName)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string selectStatement = "Select Top 1 SupplierId, SupName From Suppliers Where SupName Like @SupName Order By SupName";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
            supName = supName + "%"; // select where the supplier name starts with a string
            selectCommand.Parameters.AddWithValue("@SupName", supName);

            try
            {
                // Open Connection
                connection.Open();
                SqlDataReader supReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (supReader.Read())
                {
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = (int)supReader["SupplierId"];
                    supplier.SupName = supReader["SupName"].ToString();
                    return supplier;
                }
                else
                {
                    return null;
                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Beispiel #8
0
 public static bool UpdateSupplier(Supplier oldSupplier, Supplier newSupplier)
 {
     SqlConnection connection = TravelExpertsDB.GetConnection();
     string updateStatement = "UPDATE Suppliers SET " +
                              "SupplierId = @NewSupplierId, " +
                              "SupName = @NewSupName " +
                              "WHERE SupplierId = @OldSupplierId " +
                              "AND SupName = @OldSupName";
     SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
     updateCommand.Parameters.AddWithValue("@NewSupplierId", newSupplier.SupplierId);
     updateCommand.Parameters.AddWithValue("@NewSupName", newSupplier.SupName);
     updateCommand.Parameters.AddWithValue("@OldSupplierId", oldSupplier.SupplierId);
     updateCommand.Parameters.AddWithValue("@OldSupName", oldSupplier.SupName);
     try
     {
         connection.Open();
         int count = updateCommand.ExecuteNonQuery();
         if (count > 0)
             return true;
         else
             return false;
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         connection.Close();
     }
 }
Beispiel #9
0
        // Method to get Supplier
        public static Supplier GetSupplier(int supplierId)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string selectStatement
                = "SELECT SupplierId, SupName "
                + "FROM Suppliers "
                + "WHERE SupplierId = @SupplierId";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
            selectCommand.Parameters.AddWithValue("@SupplierId", supplierId);

            try
            {
                // Open Connection
                connection.Open();
                SqlDataReader supReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (supReader.Read())
                {
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = (int)supReader["SupplierId"];
                    supplier.SupName = supReader["SupName"].ToString();
                    return supplier;
                }
                else
                {
                    return null;
                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Beispiel #10
0
        // method to get all suppliers
        public static List<Supplier> GetAllSuppliers()
        {
            List<Supplier> suppliers = new List<Supplier>();

            // establish a connection with the database
            SqlConnection connection = TravelExpertsDB.GetConnection();
            // create select statement to select from the database
            string selectStatement =
                "SELECT SupplierId, SupName " +
                "FROM Suppliers Order By SupName";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            // try to catch exceptions
            try
            {
                // open the connection
                connection.Open();
                SqlDataReader reader =
                    selectCommand.ExecuteReader();
                while (reader.Read()) // there is row(s)
                {
                    // process the row(s)
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = Convert.ToInt32(reader["SupplierId"]);
                    supplier.SupName = reader["SupName"].ToString();
                    suppliers.Add(supplier);
                }
                return suppliers;
            }
            catch (SqlException ex)
            {
                throw ex; // throw the exception for the form to handle
            }
            finally
            {
                connection.Close();
            }
        }
Beispiel #11
0
 private void btnAdd_Click(object sender, EventArgs e)
 {
     // open the add/modify form in the add mode
     frmSupplierDataChange addSupplierForm = new frmSupplierDataChange();
     addSupplierForm.addMode = true;
     DialogResult result = addSupplierForm.ShowDialog();
     if (result == DialogResult.OK)
     {
         // copy the newly added product from the add/modify form to this form and display the product data
         dgvProducts.DataSource = null;
         dgvContacts.DataSource = null;
         supplier = addSupplierForm.supplier;
         this.DisplaySupplier();
     }
 }
Beispiel #12
0
 private void btnSearch_Click(object sender, EventArgs e)
 {
     if (Validator.IsNotEmpty(txtSupplierId))
     {
         try
         {
             // call the GetSupplier method which will search for the supplier by its id and retrieve it to supplier variable
             supplier = SupplierDB.GetSupplier(Convert.ToInt32(txtSupplierId.Text));
             if (supplier == null)
             {
                 MessageBox.Show("No supplier found with this code, please try again.", "Supplier Not Found");
                 this.ClearControls();
             }
             else
             {
                 // if a supplier found, displays the data and fill the products and contacts grid with the products of that supplier
                 this.DisplaySupplier();
                 FillProductGrid();
                 FillContactGrid();
             }
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.Message, ex.GetType().ToString());
         }
     }
 }
Beispiel #13
0
 private void btnModify_Click(object sender, EventArgs e)
 {
     // open the add/modify form in the modify mode
     frmSupplierDataChange modifySupplierForm = new frmSupplierDataChange();
     modifySupplierForm.addMode = false;
     modifySupplierForm.supplier = supplier;
     DialogResult result = modifySupplierForm.ShowDialog();
     if (result == DialogResult.OK)
     {
         // copy the modified supplier from the add/modify form to this form and display the supplier data after modification
         supplier = modifySupplierForm.supplier;
         this.DisplaySupplier();
     }
     else if (result == DialogResult.Retry)
     {
         btnSearch_Click(sender, e);
         if (supplier != null) this.DisplaySupplier();
         else this.ClearControls();
     }
 }