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