RowReorder

Manual ordering of rows in a grid

This option will prepare the app for manual ordering of rows in a grid. Only set this option, if you want users to be able to order grid rows by drag and drop.

Setting this option will make the following changes to your app:

• Create a new field for the underlying datatable. The default name will be brixx_RowOrder. Type will be set to HierarchyId.

• The View for this app will get a new column to show the RowOrder with hex value.

CONVERT([varchar](512), CAST([ExampleTableName].[brixx_RowOrder] as varbinary), 2) as brixx_RowOrder]

• A InsertTrigger will be created for the new field. Every time a new row is inserted without a value for brixx_RowOrder an order value will be generated to place the new row at the end of already existing data.

CREATE  Trigger [dbo].[brixx_ExampleTableNameInsert_RowOrder]
ON  [dbo].[ExampleTableName]
AFTER INSERT

AS

SET NOCOUNT ON;

/* IF all brixx_RowOrder fields are valid, no need for any processing */
IF (SELECT COUNT(*) FROM inserted WHERE brixx_RowOrder IS NULL) = 0
BEGIN
	--PRINT ('No Action needed for Trigger on ExampleTableName');
	RETURN;
END

DECLARE @RowId BIGINT;
DECLARE @InsertAfterHierarchyId as HierarchyId;
DECLARE @ParentHierarchyId as HierarchyId;

DROP TABLE IF EXISTS #tmpTable;
SELECT Id INTO #tmpTable FROM inserted WHERE brixx_RowOrder IS NULL;

WHILE EXISTS (SELECT * FROM #tmpTable)
BEGIN
	SET @RowId = (SELECT TOP(1) ID FROM #tmpTable ORDER BY Id);

	/* If the INSERT does not provide a HierarchyId, generate a value at the end of list */
	SET @InsertAfterHierarchyId = (SELECT TOP 1 brixx_RowOrder FROM [dbo].[ExampleTableName] WHERE brixx_RowOrder IS NOT NULL ORDER BY brixx_RowOrder DESC );
	IF (@InsertAfterHierarchyId IS NULL)
	BEGIN
	    -- Default Parent ID
	    SET @ParentHierarchyId = '/1/';
	END
	ELSE
	BEGIN
	    -- Take Parent ID from Child
	    SET @ParentHierarchyId = @InsertAfterHierarchyId.GetAncestor(1);
	END

	UPDATE [dbo].[ExampleTableName] SET brixx_RowOrder = @ParentHierarchyId.GetDescendant(@InsertAfterHierarchyId, NULL) WHERE Id = @RowId;

	--PRINT ('INSERT Trigger on ExampleTableName. Parent: ' + @ParentHierarchyId.ToString() + ' Insert After: ' + @InsertAfterHierarchyId.ToString() + ' New RowOrder: ' + @NewHierarchyId.ToString() + ' for Id: ' + CONVERT(varchar(10),@RowId));

	DELETE #tmpTable WHERE Id = @RowId;
END

DROP TABLE #tmpTable;

• If no ORDER BY is provided for the SELECT Statement, ORDER BY will use the brixx_RowOrder Field

• All Existing data will be updated! If rows exist when saving your app brixx_RowOrder will get a default value for ordering.

UPDATE ExampleTableName SET brix_RowOrder =  '/1/' + CONVERT ( nvarchar(30) , Id) + '/' FROM ExampleTableName WHERE brix_RowOrderField IS NULL