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

by

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 

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

To delete the item at orderindex 6:

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

To swap two items (4 and 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.

To insert at 3:

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

More Related Contents:

  1. How To Access Two Databases in a Single connection String in C#?
  2. Query on HasMany reference
  3. Automatically create an Enum based on values in a database lookup table?
  4. Entity Framework .Remove() vs. .DeleteObject()
  5. Use own IComparer with Linq OrderBy
  6. NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  7. EF Code-First One-to-one relationship: Multiplicity is not valid in Role * in relationship
  8. many-to-many with extra columns nhibernate
  9. LINQ Orderby Descending Query
  10. How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?
  11. SQL WHERE clause matching values with trailing spaces
  12. C# with MySQL INSERT parameters
  13. How to disable cascade delete for link tables in EF code-first?
  14. SqlConnection.Close() inside using statement
  15. NHibernate How do I query against an IList property?
  16. Use the dynamic keyword/.NET 4.6 feature in Unity
  17. Is DbContext the same as DataContext?
  18. How to use TransactionScope in C#?
  19. What does N’ stands for in a SQL script ? (the one used before characters in insert script)
  20. How to use textbox to search data in data grid view?
  21. Accessing directly a Sql Server Database in Xamarin.Forms
  22. Polymorphism: Is ORM entity a Domain Entity or Data Entity?
  23. How to partially project a child object with many fields in nHibernate
  24. Fluent NHibernate, working with interfaces
  25. NHibernate.MappingException: No persister for: XYZ
  26. Problem using SQLite :memory: with NHibernate
  27. Could not load file or assembly ‘MySql.Data, Version=6.2.2.0
  28. Populate TreeView from DataBase
  29. Databind a dropdownlist
  30. Best way to convert IList or IEnumerable to Array

Leave a Comment