// insert a new supplier into Suppliers table
// return new supplier ID
public static int AddSupplier(Suppliers supplier)
{
List <int> supList = SuppliersDB.GetSuppliersIds();
int maxCount = supList.Count();
int supplierId = maxCount + 1;
// create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatement = "INSERT INTO Suppliers(SupName) " +
"OUTPUT inserted.SupplierId " +
"VALUES(@SupName)";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
//supply paramter value, this way can avoid sql injection problem
cmd.Parameters.AddWithValue("@SupName", supplier.SupName);
// execute INSERT command
try
{
// open the connection
connection.Open();
// execute insert command and get inserted ID
supplierId = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
}
}
return(supplierId);
}
// delete a supplier from Suppliers table
// return indicator of success
public static bool DeleteSupplier(Suppliers supplier)
{
bool success = false;
// create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string deleteStatement1 =
"DELETE FROM Packages_Products_Suppliers " +
"WHERE ProductSupplierId IN " +
"(SELECT ProductSupplierId " +
"FROM Products_Suppliers " +
"WHERE SupplierId=@SupplierId)";
string deleteStatement2 =
"DELETE FROM Products_Suppliers " +
"WHERE SupplierId=@SupplierId";
string deleteStatement3 =
"DELETE FROM Suppliers " +
"WHERE SupplierId=@SupplierId " + // need for identification
"AND (SupName=@SupName OR SupName IS NULL AND @SupName IS NULL)"; // the AND is to ensure no one is updating this product
connection.Open();
// start a location transaction
SqlTransaction sqlTran = connection.BeginTransaction();
// Enlist a command in the current transaction
SqlCommand command = connection.CreateCommand();
command.Transaction = sqlTran;
//supply paramter value, this way can avoid sql injection problem
command.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
command.Parameters.AddWithValue("@SupName", supplier.SupName);
try
{
// Execute three separate commands
command.CommandText = deleteStatement1;
command.ExecuteNonQuery();
command.CommandText = deleteStatement2;
command.ExecuteNonQuery();
command.CommandText = deleteStatement3;
command.ExecuteNonQuery();
// Commit the transaction
sqlTran.Commit();
success = true;
}
catch (Exception)
{
try
{
//Atttemp to roll back the transaction
sqlTran.Rollback();
success = false;
}
catch (Exception exRollback)
{
throw exRollback;
}
}
finally // executes always
{
connection.Close();
}
}
//return the indicator of success
return(success);
}