CODEWITHSUNDEEP
c#sql-serverasp.net-mvcdapper
Lee Gary
Lee Gary

Reputation: 2397

update N rows with performance considerations

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

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?

Upvotes: 2

Views: 92

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063734

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.

Upvotes: 2

Related Questions