public void TestUpdateDbOperation_001_SimpleUpdate()
{
// Initializes the Insert Statement
UpdateDbOperation update = new UpdateDbOperation(new Table("testtable", "dbo", "0"))
{
Assignments = new AssignExpression[]
{
new AssignExpression(new Field("field1"), "value1"),
new AssignExpression(new Field("field2"), "value2"),
new AssignExpression(new Field("field3"), 200),
new AssignExpression(new Field("field4"), new DateTime(2020, 1, 1)),
},
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.Update(update);
string expectedStatement = "UPDATE [dbo].[testtable]\r\n" +
" SET\r\n" +
" [field1]='value1',\r\n" +
" [field2]='value2',\r\n" +
" [field3]=200,\r\n" +
" [field4]='2020-01-01 00:00:00'\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>
/// Updates the Record to the Database
/// </summary>
/// <remarks>This action updates the entire records and all fields on it, not partial updates</remarks>
/// <param name="entity">The entity to be updated</param>
/// <param name="dbConnection">Reference to an existing database connection</param>
/// <param name="dbTransaction">Reference to an existing database transaction</param>
public virtual void Update(TUpdateDTO entity, IDbConnection dbConnection, IDbTransaction dbTransaction)
{
// Ensure Model is Valid
if (!IsModelValid)
{
throw new Exception("Model is invalid");
}
// Map Dto to Model
var model = entity.MapToModel();
// 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();
}
// Verify if the record exists
var primarykeys = GetPrimaryKeyFilters(model);
var models = Get(primarykeys, connection, transaction);
if (models.Count == 0)
{
throw new RecordNotFoundException($"Unable to find '{model.GetType().Name}' to update based on the given primary keys", primarykeys);
}
// Calls Model Format function
FormatModel(model);
// Calls the "OnBeforeUpdate" method
if (!OnBeforeUpdate(models[0], model, entity, connection, transaction))
{
throw new Exception("Update operation cancelled");
}
// Define AssignExpressions
var assignExpressions = new List <AssignExpression>();
foreach (var fieldInfo in Fields)
{
// Define the Assign Expression Object
AssignExpression assignExpression;
// For String type, shrink character count (when size is specified)
if ((fieldInfo.Attribute.Type == typeof(string)) && (fieldInfo.Attribute.Size > 0))
{
if (fieldInfo.Property.GetValue(model) != null)
{
// Shrink Field Contents, if applicable
var fieldContents = fieldInfo.Property.GetValue(model)?.ToString().PadRight(fieldInfo.Attribute.Size, ' ').Substring(0, fieldInfo.Attribute.Size).Trim();
// Apply New Contents to the Model
//if (fieldContents == null) fieldContents = ""; //TODO: fix it on the framework
fieldInfo.Property.SetValue(model, fieldContents);
// Apply to the AssignExpression
assignExpression = new AssignExpression(new Field(fieldInfo.Attribute.Name), fieldContents);
}
else
{
// Apply New Contents to the Model
fieldInfo.Property.SetValue(model, null);
// Apply to the AssignExpression
assignExpression = new AssignExpression(new Field(fieldInfo.Attribute.Name), "");
}
}
else
{
// Check for Enum types
if (fieldInfo.Property.PropertyType.IsEnum)
{
// Enumerations have a special treatment as its value need to be parsed from the string that represents it
var enumValue = fieldInfo.Property.GetValue(model);
object finalValue = null;
// If value is null, force it to zero
if (enumValue == null)
{
finalValue = 0;
}
else
{
finalValue = Convert.ChangeType(enumValue, fieldInfo.Property.PropertyType.GetEnumUnderlyingType());
}
assignExpression = new AssignExpression(new Field(fieldInfo.Attribute.Name), finalValue);
}
else
{
assignExpression = new AssignExpression(new Field(fieldInfo.Attribute.Name), fieldInfo.Property.GetValue(model));
}
}
assignExpressions.Add(assignExpression);
}
var update = new UpdateDbOperation(DatabaseTableAttribute.Name)
{
Assignments = assignExpressions.ToArray()
};
update.Where.AppendQueryFilters(GetPrimaryKeyFilters(model), false);
DbContext.ExecuteNonQuery(update, connection, transaction);
// Calls the OnAfterUpdate method
OnAfterUpdate(model, entity, connection, transaction);
// Commits the transaction
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();
}
}
}
}
/// <summary>
/// Converts an Update Database Operation into a valid T-SQL Statement
/// </summary>
/// <param name="update">The <see cref="UpdateDbOperation"/></param>
/// <returns>A string contaiing the T-SQL</returns>
public override string Update(UpdateDbOperation update)
{
// String Builders
StringBuilder sbUpdate = new StringBuilder();
// Validate Table
if (update.Table == null)
{
throw new Exception("Update requires a table. Table is null.");
}
if (String.IsNullOrEmpty(update.Table.Name))
{
throw new Exception("Update requires a table. Table is blank or empty.");
}
// Validate Assignments
if (update.Assignments == null)
{
throw new Exception("No assignments defined for the Update Operation");
}
if (update.Assignments.Length == 0)
{
throw new Exception("Zero assignments defined for the Update Operation");
}
// ===================================================================
// UPDATE
// ===================================================================
sbUpdate.Append("UPDATE ");
sbUpdate.AppendLine(FormatTable(update.Table));
// ===================================================================
// SET
// ===================================================================
sbUpdate.AppendLine(" SET");
string[] updateAssignments = new string[update.Assignments.Length];
for (int i = 0; i < update.Assignments.Length; i++)
{
var a = update.Assignments[i];
updateAssignments[i] = $" {FormatExpressionField(a.Field1)}={FormatExpressionField(a.Field2)}";
}
sbUpdate.AppendLine(String.Join(",\r\n", updateAssignments));
// ===================================================================
// WHERE
// ===================================================================
if (update.Where?.Count > 0)
{
sbUpdate.AppendLine(" WHERE");
sbUpdate.Append(FormatExpressions(update.Where.ToArray(), 1));
}
return(sbUpdate.ToString());
}
/// <summary> /// Converts an Update Database Operation into a valid T-SQL Statement /// </summary> /// <param name="update">The <see cref="UpdateDbOperation"/></param> /// <returns>A string contaiing the T-SQL</returns> public abstract string Update(UpdateDbOperation update);
