Пример #1
0
        public static void UpdateProduct(Product oldProduct, Product newProduct)
        {
            //instantiate conn object to hold connection string.
            SqlConnection conn = DatabaseDB.GetConnection();

            //Update query, with optimistic concurancy.
            string strUp =
                "UPDATE Products " +
                "SET Description = @newDescription, " +
                "UnitPrice = @newPrice " +
                "WHERE ProductCode = @ProductCode " +
                "AND Description = @oldDescription " +
                "AND UnitPrice = @oldPrice";

            //declare commandn object and set sql statement and connections string.
            SqlCommand cmd = new SqlCommand(strUp, conn);

            //declare paramaters with old and new values.
            cmd.Parameters.AddWithValue("@newDescription", newProduct.Description);
            cmd.Parameters.AddWithValue("@newPrice", newProduct.Price);
            cmd.Parameters.AddWithValue("@oldDescription", oldProduct.Description);
            cmd.Parameters.AddWithValue("@oldProductCode", oldProduct.ProdCode);
            cmd.Parameters.AddWithValue("@oldPrice", oldProduct.Price);
            cmd.Parameters.AddWithValue("@ProductCode", oldProduct.ProdCode);

            try
            {
                //open database connection
                conn.Open();

                //update record
                cmd.ExecuteNonQuery();

                //display a successful execution.
                MessageBox.Show("Record Successfully Updated", "Updated");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
Пример #2
0
        public static void AddProduct(Product product)
        {
            //set a new connection object
            SqlConnection conn = DatabaseDB.GetConnection();

            //set string for sql statement
            string strIns =
                "INSERT INTO Products " +
                "(ProductCode, Description, UnitPrice) " +
                "Values (@Code, @Description, @UnitPrice)";

            //set command object to the sql statement and connection
            SqlCommand cmd = new SqlCommand(strIns, conn);

            //identify paramaters and the property which will populate the value.
            cmd.Parameters.AddWithValue("@Code", product.ProdCode);
            cmd.Parameters.AddWithValue("@Description", product.Description);
            cmd.Parameters.AddWithValue("@UnitPrice", product.Price);


            try
            {
                //open the connection to the database.
                conn.Open();

                //execute the insert statement
                cmd.ExecuteNonQuery();

                //inform user the record has been inserted.
                MessageBox.Show("Record Added", "Success");
            }
            catch (Exception ex)
            {
                //thow exception if insert fails.
                throw ex;
            }
            finally
            {
                //close the database connection.
                conn.Close();
            }
        }
Пример #3
0
        public static void SelectProduct(Product product)
        {
            //set sql connection string
            SqlConnection conn = DatabaseDB.GetConnection();

            //Set Select query to string. select query gets users input for product code.
            string strSel =
                "SELECT * FROM Products " +
                "WHERE ProductCode = @ProductCode";


            //declare command object and assign sql query and connection
            SqlCommand cmd = new SqlCommand(strSel, conn);

            cmd.Parameters.AddWithValue("@ProductCode", product.ProdCode);

            try
            {
                //open database
                conn.Open();

                //execute Reader for the select statement
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    product.ProdCode    = dr["ProductCode"].ToString();
                    product.Description = dr["Description"].ToString();
                    product.Price       = Convert.ToDecimal(dr["UnitPrice"]);
                }
            }
            catch (Exception ex)
            {
                //throw error
                throw ex;
            }
            finally
            {
                //close database connection
                conn.Close();
            }
        }
Пример #4
0
        public static List <string> SelectAllProducts()
        {
            //declare List to hold values.
            List <string> lstProductCode = new List <string>();


            //set sql connection string
            SqlConnection conn = DatabaseDB.GetConnection();

            //Set Select query to string. select query gets users input for product code.
            string strSel =
                "SELECT ProductCode FROM Products";

            //declare command object and assign sql query and connection
            SqlCommand cmd = new SqlCommand(strSel, conn);

            try
            {
                //open database
                conn.Open();

                //execute Reader for the select statement
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    lstProductCode.Add(dr["ProductCode"].ToString());
                }

                return(lstProductCode);
            }
            catch (Exception ex)
            {
                //throw error
                throw ex;
            }
            finally
            {
                //close database connection
                conn.Close();
            }
        }
Пример #5
0
        public static void DeleteProduct(Product product)
        {
            //set new connection object.
            SqlConnection conn = DatabaseDB.GetConnection();

            //set string strDelete to equal the sql delete syntax
            string strDelete =
                "DELETE FROM Products " +
                "WHERE ProductCode = @ProductCode";

            //declare command object and set the query and connection.
            SqlCommand cmd = new SqlCommand(strDelete, conn);

            //declare paramaters and paramater value
            cmd.Parameters.AddWithValue("@ProductCode", product.ProdCode);

            //try executing the delete
            try
            {
                //open database connection.
                conn.Open();

                //Execute Command NonQuery.
                cmd.ExecuteNonQuery();

                //Display Success
                MessageBox.Show("Successfully Deleted!", "Row Removed");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }