Ejemplo n.º 1
0
 // public method to delete product
 public static bool DeleteProduct(Product product)
 {
     SqlConnection connection = TravelExpertsDB.GetConnection();
     string deleteStatement =
         "DELETE FROM Products " +
         "WHERE ProdName = @ProdName";
     SqlCommand deleteCommand =
         new SqlCommand(deleteStatement, connection);
     deleteCommand.Parameters.AddWithValue(
         "@ProdName", product.ProdName);
     try
     {
         connection.Open();
         int count = deleteCommand.ExecuteNonQuery();
         if (count > 0)
             return true;
         else
             return false;
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         connection.Close();
     }
 }
Ejemplo n.º 2
0
        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
                product = new Product();
                this.AcceptProductData(product);
                try
                {
                    // call add product method to save the entered data and return the id of the new package
                    product.ProductId = ProductDB.AddProduct(product);
                    if (product.ProductId > 0)
                    {
                        this.DialogResult = DialogResult.OK;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.GetType().ToString());
                }
            }
            else // modify mode
            {
                Product newProduct = new Product();
                newProduct.ProductId = product.ProductId;
                this.AcceptProductData(newProduct);
                try
                {
                    // call the edit product methode and check if succeeded
                    if (ProductDB.EditProduct(product, newProduct))
                    {
                        product = newProduct;
                        this.DialogResult = DialogResult.OK;
                    }
                    else
                    {
                        MessageBox.Show("Another user has already updated or deleted that product.", "Database Error");
                        this.DialogResult = DialogResult.Retry;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.GetType().ToString());
                }
            }
        }
Ejemplo n.º 3
0
 private void btnAdd_Click(object sender, EventArgs e)
 {
     // open the add/modify form in the add mode
     frmProductDataChange addProductForm = new frmProductDataChange();
     addProductForm.addMode = true;
     DialogResult result = addProductForm.ShowDialog();
     if (result == DialogResult.OK)
     {
         // copy the newly added product from the add/modify form to this form and display the product data
         dgvSuppliers.DataSource = null;
         product = addProductForm.product;
         this.DisplayProduct();
     }
 }
Ejemplo n.º 4
0
        // public method AddProduct to insert into database
        public static int AddProduct(Product product)
        {
            // establish a connection with the database
            SqlConnection connection = TravelExpertsDB.GetConnection();
            // insert statement
            string insertStatement =
                "INSERT INTO Products " +
                "(ProdName) " +
                "VALUES (@ProdName)";
            SqlCommand insertCommand =
                new SqlCommand(insertStatement, connection);
            insertCommand.Parameters.AddWithValue(
                "@ProdName", product.ProdName);

            // try to catch exceptions
            try
            {
                // open the connection
                connection.Open();
                insertCommand.ExecuteNonQuery();
                string selectStatement =
                  "SELECT IDENT_CURRENT('Products') FROM Products";
                SqlCommand selectCommand =
                    new SqlCommand(selectStatement, connection);
                int productId = Convert.ToInt32(selectCommand.ExecuteScalar());
                return productId;
            }
            // catch exceptions and throw it the form to handle
            catch (SqlException ex)
            {
                throw ex; // throw the exception; let the form handle it cause we are in a data access class
            }
            finally
            {
                connection.Close(); // close the connection
            }
        }
Ejemplo n.º 5
0
        // public method EditProduct
        public static bool EditProduct(Product oldProduct, Product newProduct)
        {
            // establish a connection with the database
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string updateStatement =
                "UPDATE Products SET " +
                "ProdName = @NewProdName " +
                "WHERE ProdName = @OldProdName";
            // update command for new and old product
            SqlCommand updateCommand =
                new SqlCommand(updateStatement, connection);
            updateCommand.Parameters.AddWithValue(
                "@NewProdName", newProduct.ProdName);

            updateCommand.Parameters.AddWithValue(
                "@OldProdName", oldProduct.ProdName);
            // try for exceptions
            try
            {
                // open connection
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                    return true;
                else
                    return false;
            }
            // throw an exception to the form to handle
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close(); // close the connection string
            }
        }
Ejemplo n.º 6
0
        // make method to get product by ProductId
        public static Product GetProductId(int productId)
        {
            // establish a connection with the database
            SqlConnection connection = TravelExpertsDB.GetConnection();
            // create select statement to select from the database
            string selectStatement =
                "SELECT ProductId, ProdName " +
                "FROM Products " +
                "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(CommandBehavior.SingleRow); // only one row; resolve CommandBehavior
                if (reader.Read()) // there is a row
                {
                    // process the row
                    Product product = new Product();
                    product.ProductId = Convert.ToInt32(reader["ProductId"]);
                    product.ProdName = reader["ProdName"].ToString();
                    return product;
                }
                else // no product
                {
                    return null;
                }
            }
            catch (SqlException ex)
            {
                throw ex; // throw the exception for the form to handle
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 7
0
        // method to get all products
        public static List<Product> GetAllProducts()
        {
            List<Product> products = new List<Product>();

            // establish a connection with the database
            SqlConnection connection = TravelExpertsDB.GetConnection();
            // create select statement to select from the database
            string selectStatement =
                "SELECT ProductId, ProdName " +
                "FROM Products Order By ProdName";
            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)
                    Product product = new Product();
                    product.ProductId = Convert.ToInt32(reader["ProductId"]);
                    product.ProdName = reader["ProdName"].ToString();
                    products.Add(product);
                }
                return products;
            }
            catch (SqlException ex)
            {
                throw ex; // throw the exception for the form to handle
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 8
0
 private void AcceptProductData(Product product)
 {
     product.ProdName = txtProductName.Text;
 }
Ejemplo n.º 9
0
 private void btnModify_Click(object sender, EventArgs e)
 {
     // open the add/modify form in the modify mode
     frmProductDataChange modifyProductForm = new frmProductDataChange();
     modifyProductForm.addMode = false;
     modifyProductForm.product = product;
     DialogResult result = modifyProductForm.ShowDialog();
     if (result == DialogResult.OK)
     {
         // copy the modified product from the add/modify form to this form and display the product data after modification
         product = modifyProductForm.product;
         this.DisplayProduct();
     }
     else if (result == DialogResult.Retry)
     {
         btnSearch_Click(sender, e);
         if (product != null) this.DisplayProduct();
         else this.ClearControls();
     }
 }
Ejemplo n.º 10
0
        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (Validator.IsNotEmpty(txtProductId))
            {
                try
                {
                    // call the GetProduct method which will search for the product by its id and retrieve it to product variable
                    product = ProductDB.GetProductId(Convert.ToInt32(txtProductId.Text));
                    if (product == null)
                    {
                        MessageBox.Show("No product found with this code, please try again.", "Product Not Found");
                        this.ClearControls();
                    }
                    else
                    {
                        // if a product found, displays the data and fill the grid with the suppliers of that product
                        this.DisplayProduct();
                        List<Supplier> suppliers = ProductDB.GetSuppliersOfProduct(Convert.ToInt32(txtProductId.Text));
                        dgvSuppliers.DataSource = suppliers;
                        DataGridViewColumn column = dgvSuppliers.Columns[0];
                        column.Width = 80; // adjust the ID column width
                        column = dgvSuppliers.Columns[1];
                        column.Width = 350; // adjust the Name column width
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.GetType().ToString());
                }

            }
        }
Ejemplo n.º 11
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();
            }
        }
Ejemplo n.º 12
0
        // make method to get all the suppliers of a specific product
        public static List<Product> GetProductsOfSupplier(int supId)
        {
            List<Product> products = new List<Product>();
            // establish a connection with the database
            SqlConnection connection = TravelExpertsDB.GetConnection();
            // create select statement to select from the database
            string selectStatement =
                "SELECT * " +
                "FROM Products " +
                "WHERE ProductId in (Select ProductId from Products_Suppliers where SupplierId = @SupplierId)";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);
            selectCommand.Parameters.AddWithValue("@SupplierId", supId);

            // 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.ProductId = Convert.ToInt32(reader["ProductId"]);
                    product.ProdName = reader["ProdName"].ToString();
                    products.Add(product);
                }
                return products;
            }
            catch (SqlException ex)
            {
                throw ex; // throw the exception for the form to handle
            }
            finally
            {
                connection.Close();
            }
        }