Sql – Change item sortorder in gridview – LINQToSQL


I've got a gridview with a list of categories. In the database (MSSQL2008), the category table has a SortOrder(INT) NULL field.

Currently categories are retrieved using:

Galleries.DataSource = From G In DB.GalleryCategories Order By G.SortOrder, G.Name

Now, what I need to be able to do is add "Move Up" and "Move Down" buttons to each row to allow the user to sort the items in an arbitrary way.

My initial thoughts are along the lines of:
Identify ID of selected item.
Identify ID of item before/after selected item.
Swap of identified items in the DB SortOrders.

I would then have make the sortorder NOT NULL and make sure it's initialised to a unique number

I'd appreciate any alternative suggestions / comments on this approach

Many thanks

Best Solution

I have generally seen it done this way, and have done it myself

  • SortOrder is an int
  • Each item increases by 10 (so, 10,20,30,40) or suitable increment
  • To move an item up, subtract 15
  • To move an item down, add 15
  • To insert an item, take the target and add/subtract 1
  • Apply a NormalizeSort() routine which resets the values to even intervals
    • 10,20,25,30,40 => 10,20,30,40,50

That makes it all pretty simple, since inserting something above something else is just:

list.Add( New Item(..., target.SortOrder +1) )

// or 
item.SortOrder += 11, etc

If you want to make it a decimal, then you can just make it all sequential and just add .1, etc to the sort order and re-normalize again.

// Andrew

Related Question