/// <summary>
/// Sets a records display order to to requested value, and also renumbers other records
/// in the group as required.</summary>
/// <param name="connection">
/// Connection (required).</param>
/// <param name="tableName">
/// Tablename (required).</param>
/// <param name="keyField">
/// ID field meta that will be used to locate the record (required).</param>
/// <param name="orderField">
/// Display order field meta.</param>
/// <param name="filter">
/// Filter that will determine the record group (can be null).</param>
/// <param name="recordID">
/// ID value of the record.</param>
/// <param name="newDisplayOrder">
/// New display order of the record.</param>
/// <param name="descendingKeyOrder">
/// Will records with same display order values be sorted in ascending or descending ID order?
/// For example, if records with ID's 1, 2, 3 has display order value of "0", their actual display
/// orders are 1, 2 and 3. If this parameter is set to true (descending), their display orders will
/// become 3, 2, 1. This parameter controls if records that are added recently and has no display
/// order value assigned (or 0) be shown at start or at the end.</param>
/// <returns>
/// If any of the display order values is changed true.</returns>
public static bool ReorderValues(IDbConnection connection, string tableName, Field keyField, Field orderField,
ICriteria filter = null, object recordID = null, int newDisplayOrder = 1,
bool descendingKeyOrder = false, bool hasUniqueConstraint = false)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (tableName == null || tableName.Length == 0)
{
throw new ArgumentNullException("tableName");
}
if (keyField is null)
{
throw new ArgumentNullException("keyField");
}
if (orderField is null)
{
throw new ArgumentNullException("orderField");
}
// last assigned display order value
int order = 0;
// a list that will contain an element for each record, and hold old and new display
// order values of records
List <OrderRecord> orderRecords = new List <OrderRecord>();
// link to the order entry for record whose display order value is asked to be changed
OrderRecord changing = null;
// query to fetch id and display order values of the records in the group
SqlQuery query = new SqlQuery()
.Select(
keyField,
orderField)
.From(
tableName, Alias.T0)
.Where(
filter)
.OrderBy(
orderField);
// determine display order for records with same display order values
// based on ID ordering set
query.OrderBy(keyField.Name, desc: descendingKeyOrder);
// read all existing records
using (IDataReader reader = SqlHelper.ExecuteReader(connection, query))
{
var recordIDStr = recordID == null ? null :
IdToSql(recordID, connection.GetDialect());
while (reader.Read())
{
// each records actual display order value is one more than previous one
order++;
// create an entry to hold current and new display order value of the record
OrderRecord r = new OrderRecord
{
// record ID
recordID = reader.GetValue(0),
// old display order field value (not the actual display order!)
oldOrder = Convert.ToInt32(reader.GetValue(1)),
// new display order value (actual one to be set)
newOrder = order
};
orderRecords.Add(r);
// if this is the one that is requested to be changed, hold a link to its entry
if (recordID != null && recordIDStr ==
IdToSql(r.recordID, connection.GetDialect()))
{
changing = r;
}
}
}
// ensure that the new display order is within limits
// if its lower than 1 or bigger than record count, fix it
if (newDisplayOrder <= 0)
{
newDisplayOrder = 1;
}
else if (newDisplayOrder > order)
{
newDisplayOrder = order;
}
// if the record whose display order is to be changed can be found, and its display order value is different
// than the one in database
if (changing != null && changing.newOrder != newDisplayOrder)
{
// let's say record had a display order value of 6and now it will become 10, the records with actual
// display orders of 7, 8, 9, 10 will become 6, 7, 8, 9 orders.
//
// WARNING: notice that array is 0 based, so record with actual display order of 7 is in the
// 6th index in the array)
for (int i = changing.newOrder; i < newDisplayOrder; i++)
{
orderRecords[i].newOrder = i;
}
// if the records display order is to be changed from 9 to 5, the records with actual orders of 5, 6, 7, 8
// is going to be 6, 7, 8, 9 ordered.
for (int i = newDisplayOrder - 1; i < changing.newOrder - 1; i++)
{
orderRecords[i].newOrder = i + 2;
}
// as the records that will be changing are assigned new orders, we may assign new display order
// directly.
changing.newOrder = newDisplayOrder;
}
return(UpdateOrders(connection, orderRecords, tableName, keyField, orderField, hasUniqueConstraint));
}
public static bool UpdateOrders(IDbConnection connection, List <OrderRecord> orderRecords,
string tableName, Field keyField, Field orderField, bool hasUniqueConstraint = false)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (tableName.IsEmptyOrNull())
{
throw new ArgumentNullException("tableName");
}
if (keyField is null)
{
throw new ArgumentNullException("keyField");
}
if (orderField is null)
{
throw new ArgumentNullException("orderField");
}
// StringBuilder that will contain query(s)
StringBuilder queries = new StringBuilder();
if (connection.GetDialect().NeedsExecuteBlockStatement)
{
queries.AppendLine("EXECUTE BLOCK AS");
queries.AppendLine("BEGIN");
}
int updateCount = 0;
void appendSingleUpdate(object id, long newOrder)
{
queries.AppendLine(string.Format(
"UPDATE {0} SET {1} = {2} WHERE {3} = {4};", tableName,
orderField.Name, newOrder, keyField.Name, IdToSql(id, connection.GetDialect())));
updateCount++;
}
if (hasUniqueConstraint)
{
var byCurrentOrder = new Dictionary <long, OrderRecord>();
foreach (var rec in orderRecords)
{
byCurrentOrder[rec.oldOrder] = rec;
}
var list = new List <OrderRecord>();
list.AddRange(orderRecords);
list.Sort((x, y) => x.newOrder - y.newOrder);
foreach (var rec in list)
{
if (rec.oldOrder != rec.newOrder)
{
byCurrentOrder.Remove(rec.oldOrder);
if (byCurrentOrder.TryGetValue(rec.newOrder, out OrderRecord congestion))
{
var empty = list.Count * 2;
while (byCurrentOrder.ContainsKey(empty))
{
empty++;
}
congestion.oldOrder = empty;
appendSingleUpdate(congestion.recordID, empty);
byCurrentOrder[empty] = congestion;
}
appendSingleUpdate(rec.recordID, rec.newOrder);
byCurrentOrder[rec.newOrder] = rec;
}
}
}
else
{
// StringBuilder that will contain IN(...) part of the latest query
StringBuilder sb = new StringBuilder();
// scan all display order changing records
int start = 0;
while (start < orderRecords.Count)
{
OrderRecord rs = orderRecords[start];
// if this records display order is not changed, skip it
if (rs.oldOrder == rs.newOrder)
{
start++;
continue;
}
// find the difference between old and new display orders
int difference = rs.oldOrder - rs.newOrder;
// clear the IN(...) list
sb.Length = 0;
// add this records ID to the IN (...) part
sb.Append(IdToSql(rs.recordID, connection.GetDialect()));
// now we'll find all following records whose display orders are changed same amount
// (difference between old and new is same), so we will update them with just one query
// like UPDATE ORDER = ORDER + 1 WHERE ID IN (X, Y, Z....).
int finish = start;
while (finish + 1 < orderRecords.Count)
{
// if we found more than 100 records whose display orders changed same amount, to
// limit IN(...) part to overgrow, break searching and run the query. Collect the
// rest in another query. If query is too complex, might result in performance
// degration in SQL server
if (finish - start >= 100)
{
break;
}
OrderRecord rf = orderRecords[finish + 1];
// is this records display order value changed same amount
if (rf.oldOrder - rf.newOrder != difference)
{
break;
}
sb.Append(',');
sb.Append(IdToSql(rf.recordID, connection.GetDialect()));
finish++;
}
// if only one record in batch, no need to use IN clause
if (start == finish)
{
queries.AppendLine(string.Format(
"UPDATE {0} SET {1} = {2} WHERE {3} = {4};", tableName,
orderField.Name, rs.newOrder, keyField.Name,
IdToSql(rs.recordID, connection.GetDialect())));
updateCount++;
}
else
{
// batch update, use IN (...)
OrderRecord rf = orderRecords[finish];
queries.AppendLine(string.Format(
"UPDATE {0} SET {1} = {1} - ({2}) WHERE ({3} IN ({4}));",
tableName,
orderField.Name,
rs.oldOrder - rs.newOrder,
keyField.Name,
sb.ToString()));
updateCount++;
}
start = finish + 1;
}
}
if (queries.Length > 0 && updateCount > 0)
{
if (connection.GetDialect().NeedsExecuteBlockStatement)
{
queries.AppendLine("END;");
}
SqlHelper.ExecuteNonQuery(connection, queries.ToString());
// one ore more records has changed display order values
return(true);
}
else
{
// nothing changed, all display orders are same
return(false);
}
}
