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

When editing an item's SortOrder, you'll need both the original and new values.

If you have 5 items in your list:

1. Item A
2. Item B
3. Item C
4. Item D
5. Item E

And you want to move Item D up to position 2, then you'll only need to increment the SortOrder value for items B and C.

Or if you're moving Item B down to position 4, then you'll need to decrement the SortOrder value for items C and D.

I would rename your UpdateProductSortOrder method to InsertProductSortOrder and then write a new method UpdateProductSortOrder, which takes 2 arguments.

Public Sub UpdateProductSortOrder(ByVal originalIdx As Integer, ByVal newIdx As Integer)

When I'm working on problems like this, it helps me to write out all the scenarios on paper and draw arrows to see what changes in the list will be necessary.

Answer 2

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

Answer 3

Is this a multi user system? If so you need to take that into consideration. One way is to lock the database while you are incrementing the values. Otherwise, you risk getting corrupt data if two users run the code at the same time.