// 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(); } }
// 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 } }
// 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 } }
// 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(); } }