It is a quite usual case when you have a set of the child items of some parent one (order positions for instance) and need to update this set constantly. And “update” means insert, update, and delete at once. Of course, you can manually check which items already exist in the database and update them, which ones should be either created or deleted, but it needs a lot of SQL code and doesn’t look pretty. And it produces ineffective and slow queries.
If you are using a full-featured ORM (like Entity Framework) you can just rely on it and ignore this problem, because obviously it is somehow done automatically. But if you need to use a lightweight ORM (Dapper or so), and if you are using Microsoft SQL Server, let’s look at the better solution.
How does it look in code?
As you can see, you just put a parent identifier and an enumeration of the child items into the
Merge method, and it does everything else.
You don’t have to understand which child items should be created, updated, or deleted.
Let’s look at the Merge method’s code:
This code executes the
UpdateChildItems stored procedure and passes our child items as a table type parameter.
In order to make this work we should create both the stored procedure and the table type inside our database.
The table type repeats our table structure:
Now let’s look at the
UpdateChildItems stored procedure’s SQL:
It’s the most important part of our sample. This SQL uses the MERGE statement to perform insert, update, or delete operations on a target table based on the results of a join with a source table.
That’s all. I have prepared a demo project for you on GitHub. It contains a test application and a database script, so you can run everything and play with it. Please, feel free to ask any questions.