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