Blake Rivell Blake Rivell - 1 year ago 74
C# Question

Update statement to keep tracking of SortOrder in database

I have a list of product images on screen. When the user drags and drops an image to resort I have access to the oldIndex and the newIndex.

Lets say I have the following:

Product1[index: 0]

Product2[index: 1]

Product3[index: 2]

Product4[index: 3]

Product5[index: 4]

Product6[index: 5]

And the user decides to put Product2 before Product4

I am trying to figure out what my db update statement would be. I was reading another post and saw that someone said you should update the SortOrder field of all Products that are >= the newIndex and then afterwards update the new product to be in the newIndex.

Something about this seems off to me, this would leave me with the following:

Product1[index: 0]

Product2[index: 2]

Product3[index: 3]

Product4[index: 4]

Product5[index: 5]

Product6[index: 6]

I am really trying to achieve this where my indexes stay in perfect sequence with no gaps. Any suggestions?

Answer Source

So I wrote a void to handle the grunt-work. Since I needed to do this with multiple types of entities, all of which had a SortOrder column, I created an interface called ISortable that contained a SortOrder column. If you're just doing this with one db table you can substitute any instances of ISortable with whatever your entity is named.

What needs to happen outside of this void is:

Get your item from the db and store its old sort order in a variable (oldSortOrder), then set the new sort order (newSortOrder) of your item, then set a variable to be all other items that aren't the one you just updated (list). This also accounts for items being removed from the db, just set newSortOrder to 0 in your ajax call.

WebApi method:

// Code is activated when accessed via http PUT
public void PutItem(int itemId, int newSortOrder)
    // using makes sure Context is disposed of properly
    using (var Context = new MyDbContext())
        var oldSortOrder = 0;

        IEnumerable<ISortable> itemsToReorder = null;

        // Get moved item from database
        var item = Context.Items.FirstOrDefault(x => x.ItemId == itemId);

        // Before we set the new sort order, set a variable to the
        // old one. This will be used to reorder the other items.
        oldSortOrder = item.SortOrder;

        // Get all items except the one we grabbed above.
        itemsToReorder = Context.Items.Where(x => x.ItemId != itemId);

        // Set the new sort order.
        item.SortOrder = newSortOrder;

        // Pass other items into reordering logic.
        ReOrder(itemsToReorder, oldSortOrder, newSortOrder);

        // Save all those changes back to the database

Reordering void:

public static void ReOrder(IEnumerable<ISortable> list, 
    int oldSortOrder, 
    int newSortOrder)
    IEnumerable<ISortable> itemsToReorder;

    // Pare down the items to just those that will be effected by the move.
    // New sort order of 0 means the item has been deleted.
    if (newSortOrder == 0)
        itemsToReorder = list.Where(x => x.SortOrder > oldSortOrder);
        // This is just a long inline if statement. Applies Where()
        // conditions depending on the old and new sort variables.
        itemsToReorder = list.Where(x => (oldSortOrder < newSortOrder
            ? x.SortOrder <= newSortOrder &&
            x.SortOrder > oldSortOrder
            : x.SortOrder >= newSortOrder &&
            x.SortOrder < oldSortOrder));

    foreach (var i in itemsToReorder)
        // Original item was moved down
        if (newSortOrder != 0 && oldSortOrder < newSortOrder)
            i.SortOrder -= 1;
        // Original item was moved up
        else if (newSortOrder != 0 && oldSortOrder > newSortOrder)
            i.SortOrder += 1;
        } // Original item was removed.
            i.SortOrder -= 1;

Let me know if you need some clarification. I'm almost certain I didn't explain everything adequately.