/// <summary>
/// Converts a Delete Database Operation into a valid T-SQL Statement
/// </summary>
/// <param name="delete">The <see cref="DeleteDbOperation"/></param>
/// <returns>A string contaiing the T-SQL</returns>
public override string Delete(DeleteDbOperation delete)
{
// String Builders
StringBuilder sbDelete = new StringBuilder();
// Validate Table
if (delete.Table == null)
{
throw new Exception("Delete requires a table. Table is null.");
}
if (String.IsNullOrEmpty(delete.Table.Name))
{
throw new Exception("Delete requires a table. Table is blank or empty.");
}
// ===================================================================
// DELETE FROM
// ===================================================================
sbDelete.Append("DELETE FROM ");
sbDelete.AppendLine(FormatTable(delete.Table));
// ===================================================================
// WHERE
// ===================================================================
if (delete.Where?.Count > 0)
{
sbDelete.AppendLine(" WHERE");
sbDelete.Append(FormatExpressions(delete.Where.ToArray(), 1));
}
return(sbDelete.ToString());
}
public void TestDeleteDbOperation_001_SimpleDelete()
{
// Initializes the Insert Statement
DeleteDbOperation delete = new DeleteDbOperation(new Table("testtable", "dbo", "0"))
{
Where = new WhereCollection()
{
new FilterExpression(new Field("field1"), FieldOperators.Equal, "Test", FieldAndOr.And),
new FilterExpression(new Field("field2"), FieldOperators.GreaterThan, 15, FieldAndOr.And),
new FilterExpression(new Field("field3"), FieldOperators.LessThan, 15, FieldAndOr.None),
}
};
string statement = myDialect.Delete(delete);
string expectedStatement = "DELETE FROM [dbo].[testtable]\r\n" +
" WHERE\r\n" +
" [field1]='Test' AND\r\n" +
" [field2]>15 AND\r\n" +
" [field3]<15\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
/// <summary>
/// Delete objects from the database based on a <see cref="List{T}"/> of <see cref="FilterField"/>
/// </summary>
/// <param name="filters">A <see cref="List{T}"/> of <see cref="FilterField"/> containing the filters</param>
/// <param name="dbConnection">Reference to an existing database connection</param>
/// <param name="dbTransaction">Reference to an existing database transaction</param>
public void Delete(List <FilterField> filters, IDbConnection dbConnection, IDbTransaction dbTransaction)
{
// Ensure Model is Valid
if (!IsModelValid)
{
throw new Exception("Model is invalid");
}
try
{
// Remove the Object from the Database
var delete = new DeleteDbOperation(DatabaseTableAttribute.Name);
delete.Where.AppendQueryFilters(filters, false);
// Initializes a transaction, if applicable
IDbConnection connection = dbConnection;
IDbTransaction transaction = dbTransaction;
try
{
// Creates a connection if no connection has been passed
if (connection == null)
{
connection = DbContext.GetConnection();
}
// Initializes a transaction if no transaction has been passed
if (transaction == null)
{
transaction = connection.BeginTransaction();
}
DbContext.ExecuteNonQuery(delete, dbConnection, dbTransaction);
if (dbTransaction == null)
{
transaction.Commit();
}
}
catch
{
// Check if this action is joining a transaction
if (dbTransaction == null)
{
transaction.Rollback();
}
throw;
}
finally
{
// Closes the connection created on this method
if (dbConnection == null)
{
if (connection?.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
catch
{
throw;
}
}
/// <summary> /// Converts a Delete Database Operation into a valid T-SQL Statement /// </summary> /// <param name="delete">The <see cref="DeleteDbOperation"/></param> /// <returns>A string contaiing the T-SQL</returns> public abstract string Delete(DeleteDbOperation delete);