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