update N rows with performance considerations

asp.net-mvc c# dapper sql-server

Question

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?

Accepted Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why