c# - Is there a more efficient way to update the sort order of an entire table? -

c# - Is there a more efficient way to update the sort order of an entire table? -

i'm using client side drag , drop sort ordering table of data. sort order persisted in database simple integer value. schema looks following:

id | title | sortorder 1 | | 0 2 | b | 1 3 | c | 2

data can pulled out simple order by , well.

the client side drag , drop i've implemented posts array of ids in new order, persisted database using dapper follows (removing service layers brevity):

[httppost] public actionresult updatesort(int[] ids) { if (ids != null && ids.length > 0) { using (var con = connection.opensql()) { (var = 0; < ids.length; ++i) con.execute("update table set sortorder = @sort id = @id", new { sort = i, id = ids[i] }); } } homecoming new httpstatuscoderesult(200); }

this works fine, wondered if there improve way handle update assuming client side of things can't change. have 1 sql statement beingness executed per row @ moment, there way maintain parametrized , batch them? possible remove loop entirely?

the number of records beingness used code small, it's not huge problem - question 1 of curiosity couldn't think of way order sql arbitrary array of identity values

i hope question doesn't break site rules - did have check , think it's ok. realize reply may 'no', beingness case i'd still interested in confirmation.

assuming ids wont alter , these options think have.

below summary of changes need.

move update within stored procedure.(sp)

as parameter sp either pass in xml of id's or pass table type in. (http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx).

if using xml pass in values, insert xml entries table variable.keep identity column here become sort order.

join table have temporary table id.

write update statement bring together updates sortorder field identitycolumn data.

eg

update tab set sortorder = [idnetityfield] table tab inner bring together temptable temp on temp.id=tab.id

i think way in can approach scenario.

c# sql-server asp.net-mvc tsql dapper