更新语句以保持对数据库中SortOrder的跟踪

所以我写了一个void来处理这些繁琐的工作。因为我需要处理多种类型的实体，它们都有一个SortOrder列，所以我创建了一个名为ISortable的接口，它包含一个SortOrder列。如果您只对一个db表执行此操作，则可以用您的实体命名的任何名称替换ISortable的任何实例。

在这个void之外需要发生的是:

从db中获取项目并将其旧排序顺序存储在变量(oldSortOrder)中，然后设置项目的新排序顺序(newSortOrder)，然后设置变量为所有其他项目，而不是您刚刚更新的项目(list)。这也说明了从数据库中删除的项目，只需在ajax调用中将newSortOrder设置为0。

WebApi方法:

// 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);
        // Delete if the item is set for deletion (newSortOrder = 0)
        if (newSortOrder == 0)
        {
            Context.Items.Remove(item);
        }
        // Otherwise, set the new sort order.
        else
        {
            item.SortOrder = newSortOrder;
        }
        // Pass other items into reordering logic.
        ReOrder(itemsToReorder, oldSortOrder, newSortOrder);
        // Save all those changes back to the database
        Context.SaveChanges();
    }
}

重新排序无效:

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);
    }
    else
    {
        // 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.
        else
        {
            i.SortOrder -= 1;
        }
    }
}

如果你需要澄清，请告诉我。我几乎可以肯定，我没有把每件事都解释清楚。

IF OBJECT_ID('tempdb..#Product') IS NOT NULL
    DROP TABLE #Product
CREATE TABLE #Product(
    Name VARCHAR(100) NOT NULL
    ,SortOrder INT NOT NULL
)
INSERT INTO #Product (Name, SortOrder)
VALUES 
 ('Product1', 0)
,('Product2', 1)
,('Product3', 2)
,('Product4', 3)
,('Product5', 4)
,('Product6', 5)
DECLARE 
    @NewIndex INT
    ,@OldIndex INT
SET @OldIndex = 4   --'Product5'
SET @NewIndex = 2   -- After 'Product2'
IF @NewIndex < @OldIndex    -- Move UP
    UPDATE #Product
        SET SortOrder = CASE 
                        WHEN SortOrder = @OldIndex THEN @NewIndex 
                        ELSE SortOrder + 1
                        END
    WHERE SortOrder BETWEEN @NewIndex AND @OldIndex;
ELSE                        -- Move DOwn
    UPDATE #Product
        SET SortOrder = CASE 
                        WHEN SortOrder = @OldIndex THEN @NewIndex 
                        ELSE SortOrder - 1
                        END
    WHERE SortOrder BETWEEN @OldIndex AND @NewIndex;
SELECT * FROM #Product ORDER BY SortOrder