Esempio 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();
     }
 }
Esempio n. 2
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
            }
        }
Esempio n. 3
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
            }
        }
Esempio n. 4
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();
            }
        }