Best Practice to renumber items in a list? SQL or C#/VB.NET

Question

I have a database table that has a SortOrder integer column. In the UI for adding and editing table items, I have a drop down list of Integer to let the user select where in the sortorder they would like this item to appear. My question is, say the list, {1,2,3,4,5,"last"}, if the user picks a a number, I want that to be the items SortOrder attribute, and then the item with that 'SortOrder' currently would be bumped up one (+=1) as well as the items with a higher 'SortOrder' whilst not affecting items with a lower 'SortOrder'. I currently have a Public Module 'Utilities' using VB.NET and Linq

Private _db As New MyDataContext

Public Sub UpdateProductSortOrder(ByVal idx As Integer)

    Dim products = (From p As Product In _db.Products _
        Where p.SortOrder >= idx _
        Select p).ToList()

    For Each p As Product In products
        p.SortOrder += 1
    Next

    _db.SubmitChanges()

End Sub

It seems to work ok when adding items as I am doing this

Protected Overrides Sub AddItem()

    Dim sortOrder As Integer = Int32.Parse(Server.HtmlEncode(ddlNewSortOrder.SelectedValue))

    If (sortOrder >= 1) Then
        Utilities.UpdateProductSortOrder(sortOrder)
    Else
        sortOrder = Utilities.GetNextProductSortOrder()
    End If

    Dim dic As New System.Collections.Specialized.ListDictionary()
    dic.Add("PROD_Description", Server.HtmlEncode(txtNewDescription.Text))
    dic.Add("Abbrev", Server.HtmlEncode(txtNewAbbreviation.Text.ToUpper()))
    dic.Add("SortOrder", sortOrder)

    ProductsGridSource.Insert(dic)

End Sub

But I get some occasional errors when editing exisitng items like this

Protected Sub ProductsGridSource_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceUpdateEventArgs) Dim sortOrder As Integer = DirectCast(e.NewObject, Product).SortOrder Utilities.UpdateProductSortOrder(sortOrder) End Sub

Is there a more effecient maybe, best practice, way I should be doing this? Any advice is appreciated.

Thanks In Advance, ~ck in San Diego

Answer 1

The trick for editing is that you don't want to update every number, only the numbers affected by the change. For one item it can be easy, but if you are editing multiple items at once I would store the items in a List sorted by SortOrder:

Dim products = (From p As Product In _db.Products _
        Order By p.SortOrder _
        Select p).ToList()

Then I would rearrange the list according to the new values:

// do a RemoveAt first if you need to move an item
products.InsertAt(sortOrder, dic); 

Then renumber everything (or everything starting with SortOrder if you want to optimize):

Dim index As Integer = 0
For Each p As Product In products
    p.SortOrder = index
    index += 1
Next

If you do all your rearranging in one block then do the update in one block you'll save some redundant work.

This approach won't work so well if you have a large recordset. In that case you'll need to work out a more efficient approach, such as by using the database to do your work for you.

DECLARE @sortOrder INT
DECLARE @maxSortOrder INT
SET @sortOrder = 5
SET @maxSortOrder = 10
UPDATE Products SET SortOrder = SortOrder + 1 
WHERE SortOrder >= @sortOrder AND SortOrder < @maxSortOrder