0

I am looking at Dapper as ORM for our next project, but something is not clear to me.

In this question there are answers on how to do inserts, updates and deletes.
Since this question is already a bit older, maybe there are better ways now a days..

But my biggest concern is how to do an ApplyUpdates on a list.

Suppose you have a List<Customer> that is build like shown here

And suppose you show this list in a DataGridView.
Now, the user will

  • alter the data of a few rows,
  • insert a few new rows,
  • delete a few rows

And when he clicks on the save button, at that time you want to save all these changes in this List<Customer> to your database, using Dapper.

How can I go about that ?

If I have to loop through the list and for each row call an insert, update or delete statement, then how can I determine what operation to use ? The deleted rows will be gone from the list.
I also want to make sure that if one statement fails, all will be rollbacked.
And I need the primary key for all new rows returned and filled in the DataGridView.

In other words, all that ADO DataAdapter/DataTable does for you.

What is the best way to do this using Dapper ?

EDIT
The best way I can think of now is to keep 3 list in memory and when the user alters some data, add a row in the update list, same for insert list and deleted list so I can run through these 3 list on the button click.
But I am hoping there is a better alternative build in Dapper for this kind of situation.

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • Would it be okay to remove everything and then (re-)insert it into the table? It would get a different database id. – Aage Apr 21 '21 at 09:32
  • 1
    The feature you are looking for is called Change Tracking. Dapper doesn't support it; some add-ons of Dapper does support it in limited way. See if [this](https://stackoverflow.com/a/49313833/5779732) or [this](https://stackoverflow.com/a/43590624/5779732) helps you. – Amit Joshi Apr 21 '21 at 09:32
  • 1
    @Aage not that is impossible, it could be used as a foreign key in another table – GuidoG Apr 21 '21 at 09:33
  • @AmitJoshi Thank you for the links, I will study them – GuidoG Apr 21 '21 at 09:33
  • For the rollbacking part you can use a `TransactionScope`: https://stackoverflow.com/a/10363978/2877982 – Aage Apr 21 '21 at 09:35
  • @AmitJoshi `EF` is not suitable for me. The queries it generates are way to bad and it has very poor performance on larger databases. I don't know `NH` yet – GuidoG Apr 21 '21 at 09:37
  • @Aage Thank you very much for the link about the transaction – GuidoG Apr 21 '21 at 09:39

1 Answers1

1

You need to handle this yourself, as Dapper doesn't manage it. There are several theories for how to do it.

  1. Delete all items and then add them again.

Easy to implement. Bad for DB performance, which is effectively 2 DB writes per row.

  1. Loop through the items and update without checking for changes

Not too difficult to implement.

DB performance better than option 1, but not ideal. Add and deletes are more complex to detect than updates.

  1. Loop through the items and update only if there are differences

More difficult to implement. Requires reading from the DB first to compare values (extra DB action)

  1. Store changes in a separate list

Even more difficult to implement, as you need to "wrap" List updates into another class (first class collection?) and store changes Most efficient for DB, as you execute only the minimum on each DB item.

In the end, you might select different approaches for different Entities depending on how you need to optimise. e.g. Option 1 is fine if you know you will only have a few entities and not many updates.

jason.kaisersmith
  • 8,712
  • 3
  • 29
  • 51
  • Option 4 seems best to me. Like I wrote in my Edit, using different lists will be the most performant method I think – GuidoG Apr 21 '21 at 09:38