2

I have a table with OrderSequence, the schema looks roughly like this:

  • id
  • Name
  • OrderSequence

I have an arrays of ids ("1", "2", "3", "4") in my MVC Action codes, i want to set the SequenceOrder for sorting purpose. I don't want to loop it via .Net and execute N number of sql queries. How should i do it using dapper/raw ado.net?

Lee Gary
  • 2,357
  • 2
  • 22
  • 38
  • 1
    Not with Dapper, but with ADO.NET. Look into structured parameters for Stored Procedures and how to pass them. You basically will send your whole array at once to a SP as a table-valued variable and then do an `UPDATE FROM` to update your table. So, only one roundtrip to the database. – Marcel N. Jul 29 '14 at 14:03
  • i know that dapper can execute arbitary sql statements, can it be done that way? – Lee Gary Jul 29 '14 at 14:04
  • Yes, but that means to have the update query hardcoded somewhere in your app and just pass the array in a `WHERE ... IN (...)` statement. All in all, a lot uglier than structured params. – Marcel N. Jul 29 '14 at 14:09
  • @marceln i don't mind having hardcoded query in this instance, do you have a sample? – Lee Gary Jul 29 '14 at 14:25
  • Is the SequenceOrder just going to be incrementing from some start position? Meaning: what are the expected OrderSequence values at the end? – Marc Gravell Jul 29 '14 at 14:41

1 Answers1

2

Dapper can do that via:

connection.Execute("update Orders set Sequence=@seq where Id=@id",
            ids.Select((id, seq) => new { id, seq }));

However, this is just using dapper to unroll a loop - in reality it is calling Execute per row on your behalf, exactly the same as if you had done:

int seq = 0;
foreach(var id in ids) {
    connection.Execute("update Orders set Sequence=@seq where Id=@id",
         new { id, seq });
    seq++;
}

If you want to pass all the data down in one go, you could look at table-valued-parameters, which dapper supports in recent builds.

Community
  • 1
  • 1
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900