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