I’m in the process of getting MergeQueryObject setup on github. This was one of my personal projects. Previously, I had been maintaining it in a private repository, but I think it is in a form where it would be usable by others.
https://github.com/JasonDV/MergeQueryObject
https://www.nuget.org/packages/ivaldez.Sql.SqlMergeQueryObject/
List of features:
- Support for surrogate or natural primary keys
- Update from a target table given a collection of DTOs.
- Option to preform only Updates and no Inserts or Deletes.
- Insert from a target table given a collection of DTOs.
- When a collection of DTOs is “merged” into the target table, any records in the target table not in the collection of DTOs will be deleted.
- Option 1: Ignore Deletes
- Option 2: Delete from a target table given a collection of DTOs.
- Option 3: Mark for Delete (“soft delete”) on records in target table given a collection of DTOs.
- Target updates to only specific fields by using a DTO that partially matches the target table
- Target changes to a portion of a very large table.
- For example, target changes to a specific date range
- All features of BulkLoader are supported.
- Create a temporary source table or Create an concrete source table.
MergeQueryObject’s primary use is performing updates, inserts, and deletes into a large target table using minimal C# syntax. The basic “merge” concept comes from the fact that a source temp table is setup with the wanted values and the Merge statement executes the necessary actions to make the target table match the source table. This can be done for the entire table or portions of a table.
It is a low level utility that sits just above the database and works with POCO DTOs, using my other project BulkLoader to load the temp table.
Creating Query Objects is something that should be done for complex SQL. It helps with maintainability of the code and makes it easier to conceptually test and verify, since the query object has only one responsibility.