C# 在保持排序的同时将有序列表保存到数据库的最佳方法

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/330482/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-03 23:27:52  来源:igfitidea点击:

Best way to save a ordered List to the Database while keeping the ordering

c#databasenhibernatesql-order-by

提问by Tigraine

I was wondering if anyone has a good solution to a problem I've encountered numerous times during the last years.

我想知道是否有人对我在过去几年中多次遇到的问题有很好的解决方案。

I have a shopping cart and my customer explicitly requests that it's order is significant. So I need to persist the order to the DB.

我有一个购物车,我的客户明确要求它的订单很重要。所以我需要将订单保存到数据库中。

The obvious way would be to simply insert some OrderField where I would assign the number 0 to N and sort it that way.

显而易见的方法是简单地插入一些 OrderField,我会将数字 0 分配给 N 并以这种方式对其进行排序。

But doing so would make reordering harder and I somehow feel that this solution is kinda fragile and will come back at me some day.

但是这样做会使重新排序变得更加困难,我不知何故觉得这个解决方案有点脆弱,总有一天会回到我身边。

(I use C# 3,5 with NHibernate and SQL Server 2005)

(我在 NHibernate 和 SQL Server 2005 中使用 C# 3,5)

Thank you

谢谢

采纳答案by Galwegian

FWIW, I think the way you suggest (i.e. committing the order to the database) is not a bad solution to your problem. I also think it's probably the safest/most reliable way.

FWIW,我认为您建议的方式(即将订单提交到数据库)对于您的问题来说并不是一个糟糕的解决方案。我也认为这可能是最安全/最可靠的方式。

回答by harriyott

I would recommend keeping gaps in the order number, so instead of 1,2,3 etc, use 10,20,30... If you need to just insert one more item, you could put it at 15, rather than reordering everything at that point.

我建议在订单号中保留间隙,因此不要使用 1、2、3 等,而是使用 10、20、30...如果您需要再插入一件商品,您可以将其放在 15,而不是重新排序所有商品在那时候。

回答by Binary Worrier

Unfortunately there is no magic bullet for this. You cannot guarentee the order of any SELECTstatement WITHOUT an order by clause. You need to add the column and program around it.

不幸的是,这没有灵丹妙药。您不能保证SELECT没有 order by 子句的任何语句的顺序。您需要添加列并围绕它进行编程。

I don't know that I'd recommend adding gaps in the order sequence, depending on the size of your lists and the hits on the site, you might gain very little for the over head of handling the logic (you'd still need to cater for the occasion where all the gaps have been used up). I'd take a close look to see what benifits this would give you in your situation.

我不知道我会建议在顺序序列中添加间隙,这取决于列表的大小和网站上的点击量,您可能会因处理逻辑的开销而获得的收益很少(您仍然需要以迎合所有空隙都用完的场合)。我会仔细看看这会给你带来什么好处。

Sorry I can't offer anything better, Hope this helped.

对不起,我不能提供更好的东西,希望这有帮助。

回答by Hyman Ryan

I would just insert an order field. Its the simplest way. If the customer can reorder the fields or you need to insert in the middle then just rewrite the order fields for all items in that batch.

我只想插入一个订单字段。它的最简单的方法。如果客户可以对字段重新排序,或者您需要在中间插入,则只需重写该批次中所有项目的订单字段。

If down the line you find this limiting due to poor performance on inserts and updates then it is possible to use a varchar field rather than an integer. This allows for quite a high level of precision when inserting. eg to insert between items 'A' and 'B' you can insert an item ordered as 'AA'. This is almost certainly overkill for a shopping cart though.

如果您发现由于插入和更新的性能不佳而导致此限制,则可以使用 varchar 字段而不是整数。这允许在插入时具有相当高的精度。例如,要在项目“A”和“B”之间插入,您可以插入按“AA”排序的项目。不过,这对于购物车来说几乎肯定是矫枉过正。

回答by sindre j

Well, I would say the short answer is:

好吧,我会说简短的答案是:

Create a primary key of autoidentity in the cartcontents table, then insert rows in the correct top-down order. Then by selecting from the table with order by the primary key autoidentity column would give you the same list. By doing this you have to delete all items and reinsert then in case of alterations to the cart contents. (But that is still quite a clean way of doing it) If that's not feasible, then go with the order column like suggested by others.

在cartcontents 表中创建一个autoidentity 的主键,然后按正确的自上而下的顺序插入行。然后通过按主键自动标识列的顺序从表中选择会给你相同的列表。通过这样做,您必须删除所有项目,然后在更改购物车内容的情况下重新插入。(但这仍然是一种非常干净的方法)如果这不可行,那么请按照其他人的建议使用 order 列。

回答by Adam

I wouldn't recommend the A, AA, B, BA, BB approach at all. There's a lot of extra processing involved to determine hierarchy and inserting entries in between is not fun at all.

我根本不推荐 A、AA、B、BA、BB 方法。确定层次结构涉及很多额外的处理,并且在它们之间插入条目一点也不有趣。

Just add an OrderField, integer. Don't use gaps, because then you have to either work with a non-standard 'step' on your next middle insert, or you will have to resynchronize your list first, then add a new entry.

只需添加一个 OrderField,整数。不要使用间隙,因为那样您必须在下一个中间插入中使用非标准的“步骤”,或者您必须先重新同步您的列表,然后添加一个新条目。

Having 0...N is easy to reorder, and if you can use Array methods or List methods outside of SQL to re-order the collection as a whole, then update each entry, or you can figure out where you are inserting into, and +1 or -1 each entry after or before it accordingly.

拥有 0...N 很容易重新排序,如果您可以使用 SQL 之外的 Array 方法或 List 方法将集合作为一个整体重新排序,然后更新每个条目,或者您可以找出插入的位置,并且相应地在其之后或之前的每个条目+1或-1。

Once you have a little library written for it, it'll be a piece of cake.

一旦你为它编写了一个小库,这将是小菜一碟。

回答by redzedi

On a level of abstraction above the cart Items let's say CartOrder (that has 1-n with CartItem) you can maintain a field called itemOrder which could be just a comma - separated list of id(PK) of cartItem records relevant . It will be at application layer that you require to parse that and arrange your item models accordingly . The big plus for this approach will be in case of order reshufflings , there might not be changes on individual objects but since order is persisted as an index field inside the order item table rows you will have to issue an update command for each one of the rows updating their index field. Please let me know your criticisms on this approach, i am curious to know in which ways this might fail.

在购物车项目之上的抽象级别上,假设 CartOrder(与 Ca​​rtItem 有 1-n 个),您可以维护一个名为 itemOrder 的字段,它可能只是一个逗号分隔的 cartItem 记录相关的 id(PK) 列表。您需要在应用程序层解析它并相应地安排您的项目模型。这种方法的一大优点是在订单改组的情况下,单个对象可能不会发生变化,但由于订单作为订单项表行内的索引字段持久存在,因此您必须为每个对象发出更新命令。行更新其索引字段。请让我知道您对这种方法的批评,我很想知道这可能会以哪些方式失败。

回答by Js.

How about using a linked list implementation? Having one column the will hold the value (order number) of the next item. I think it's by far the easiest to use when doing insertion of orders in between. No need to renumber.

如何使用链表实现?有一列将保存下一个项目的值(订单号)。我认为在两者之间插入订单时,它是迄今为止最容易使用的。无需重新编号。

回答by sean wagner

Best solution is a Doubly Linked list. O(1) for all operations except indexing. Nothing can index SQL quickly though except a where clause on the item you want.

最佳解决方案是双向链表。O(1) 对于除索引之外的所有操作。除了您想要的项目上的 where 子句之外,没有什么可以快速索引 SQL。

0,10,20 types fail. Sequence column ones fail. Float sequence column fails at group moves.

0,10,20 类型失败。序列列失败。浮动序列列在组移动时失败。

Doubly Linked list is same operations for addition, removal, group deletion, group addition, group move. Single linked list works ok too. Double linked is better with SQL in my opinion though. Single linked list requires you to have the entire list.

双向链表的添加、删除、组删除、组添加、组移动操作都是一样的。单链表也可以正常工作。不过在我看来,双链接用 SQL 更好。单链表要求您拥有整个列表。

回答by Kickaha

Ok here is my solution to make programming this easier for anyone that happens along to this thread. the trick is being able to update all the order indexes above or below an insert / deletion in one update.

好的,这是我的解决方案,可以让遇到此线程的任何人都可以更轻松地进行编程。诀窍是能够在一次更新中更新插入/删除上方或下方的所有订单索引。

Using a numeric (integer) column in your table, supported by the SQL queries

在表中使用数字(整数)列,由 SQL 查询支持

CREATE TABLE myitems (Myitem TEXT, id INTEGER PRIMARY KEY, orderindex NUMERIC);

To delete the item at orderindex 6:

要删除 orderindex 6 处的项目:

DELETE FROM myitems WHERE orderindex=6;    
UPDATE myitems SET orderindex = (orderindex - 1) WHERE orderindex > 6;

To swap two items (4 and 7):

交换两个项目(4 和 7):

UPDATE myitems SET orderindex = 0 WHERE orderindex = 4;
UPDATE myitems SET orderindex = 4 WHERE orderindex = 7;
UPDATE myitems SET orderindex = 7 WHERE orderindex = 0;

i.e. 0 is not used, so use a it as a dummy to avoid having an ambiguous item.

即 0 未使用,因此将它用作虚拟项以避免出现歧义项。

To insert at 3:

在 3 处插入:

 UPDATE myitems SET orderindex = (orderindex + 1) WHERE orderindex > 2;
 INSERT INTO myitems (Myitem,orderindex) values ("MytxtitemHere",3)