Treeview

  • This component is not suitable for users that require assistive technologies. This component is not suitable for users who require assistive technologies.

Displays a dynamic database generated tree

Component settings

Number of visible node links
The maximum number of links to show per node. Any additional links are made available in the Navigator.
Number of visible root node links
The maximum number of links to show for the root node. Any additional links are made available in the Navigator.

SQL

SQL Call: Retrieve nodes (mandatory)

SQL to retrieve root nodes and child nodes to other nodes.

May modify database: No

Parameters

@Id string
id-value of the node that should be filled with child nodes, @id=NULL for the top node.

Resultset: Nodes

Resultset with the nodes to add.
Table count: repeated exactly once
Row count: zero or more rows
Columns
<column with ordinal 1> mandatory string
Contains the 'id' of the node to be added. The 'id' must be unique in the tree. This 'id' is then used when expanding/dragging/dropping the node. The 'id' value may not contain any of the following characters: ; ' "
<column with ordinal 2> mandatory string
Contains the text of the node.

If this column is named [NodeText] then the colum content is treated as plain text. If the column is named [NodeHtml] then the content is treated as HTML.
<xxx> optional string
Additional columns can be added in the resultset, these can be used for passingfields, depends_on in row-links
CanDrag optional bit

Determines if the node can be dragged.

Default: Defaults to not dragable.
Possible value Description
0 Not dragable
1 Dragable
CanDrop optional bit

Determines if it is possible to drop other nodes on this node.

Default: Defaults to not dropable.
Possible value Description
0 Not dropable
1 Dropable
CanDropFile optional bit

Determines if it is possible to drop files on this node.

Default: Files can not be dropped.
CanExpand optional bit

Determines if it is possible to expand this node.

Default: Defaults to not expandable.
Possible value Description
0 Not expandable
1 Expandable
CanReorder optional bit

Determines if this node can be reordered with drag & drop.

Default: Defaults to not reorderable.
Possible value Description
0 Node can't be reordered
1 Node can be reordered with drag & drop
Expanded optional bit

Determines whether the node starts collapsed or expanded.

Default: Defaults to collapsed.
Possible value Description
0 Node starts collapsed
1 Node starts expanded
NodeColor optional string

The text color for the node. See Colors.

NodeStyle optional string
Sets the CSS style. Never use a string from a user as part of this column for security reasons.
NodeTooltip optional string
Sets the tooltip.

Resultset: Root node configuration (optional)

Result set to configure the root node. Only applicable the first time when @id = NULL.
Table count: repeated zero or one time
Row count: exactly one row
Columns
<column with ordinal 1> mandatory string
Contains the 'id' of the root node. The 'id' must be unique in the tree. This 'id' is then used when dropping on the node. The 'id' value can be NULL but may not contain any of the following characters: ; ' "
<column with ordinal 2> mandatory string
Contains the text of the root node. Use NULL to keep the default of the menu item name. Styling HTML (e.g. <b> tags) is allowed.
CanDrop optional bit

Determines if it is possible to drop other nodes on the root node.

Default: Defaults to not dropable.
Possible value Description
0 Not dropable
1 Dropable

SQL Call: Validate parameters

Allows you to validate the SQL parameters before any other SQL is run in the component. This call is only made if the SQL is a stored procedure and Validate parameters is checked.

May modify database: No

Parameters

@Force bit
Set to 1 if the last call to validate parameters used admin_force and the user clicked OK in the OK/Cancel dialog.
@ValidateParams bit
Set to 1 when this call is made.

Resultset: Messages (optional)

Table count: repeated zero or one time
Row count: zero or one row
Columns
ADMIN_Force optional string
Message asking the end user to confirm their parameters.
ADMIN_Message optional string
Message explaining why the parameters are rejected.

Insert/Update/Delete SP

SQL Call: Drag & drop (mandatory)

Stored procedure run when a 'drag and drop' event occurs (only if nodes are dragable and dropable).

May modify database: Yes

Parameters

@DragId string
Id of the node that initiated the drag.
@DropId string
Id of the node that drop occured on.
@DropLocation int
In case of reorder, if the node should be inserted before (-1) or after (1) the node with id dropid.
@Force bit
Set to '1' after 'Ok' is chosen in an ADMIN_FORCE-message

SQL Call: Verify files

Called when files are dropped onto a node. The procedure may either accept the files (do nothing) or reject the files with a message.

Files metadata will be passed in the following temp table

CREATE TABLE #FileData
(
FileData varbinary(max) NULL, -- Will contain NULL for all rows
FileName nvarchar(max) NOT NULL,
ContentSize bigint NOT NULL,
ContentType nvarchar(max) NOT NULL
)
May modify database: No

Parameters

@Action string
Set to 'VerifyFiles'.
@Id string
The id of the node that the files were dropped on.

Resultset: Messages (optional)

Table count: repeated zero or one time
Row count: zero or one row
Columns
ADMIN_ErrorMessage optional string

Message explaining why the files were rejected.

SQL Call: Save files

Called when files have finished uploading.

File data will be passed in the following temp table

CREATE TABLE #FileData
(
FileData varbinary(max) NULL,
FileName nvarchar(max) NOT NULL,
ContentSize bigint NOT NULL,
ContentType nvarchar(max) NOT NULL
)
May modify database: Yes

Parameters

@Action string
Set to 'SaveFiles'.
@Id string
The id of the node that the files were dropped on.

Resultset: Forwarding definitions (optional)

Table count: repeated zero or one time
Row count: zero or one row
Columns
ADMIN_ErrorMessage optional string

Displays a user friendly error message to the user.

ADMIN_ForwardLink optional string

Alias of the link to forward to.

ADMIN_ForwardMenuItem optional string

Alias of the menu item to forward to.

InfoSQL

See the InfoSQL documentation for details.

Custom access control and logging

SQL Call: Custom access control and logging

Use this call to restrict which entries a user is allowed to view and edit, and to log which entries a user views.

Access to a menu item is normally controlled through functions and roles alone but some entities need more fine grained control. For example, a user may have access to the View Member menu item for normal members but not for members with a protected identity.

The menu items a user visits are always logged (in ADMINLogMenuItem) but for sensitive data you may need to log exactly what entries are viewed. Do the logging in this call as the common ways of viewing data (grid and InfoSQL) are not allowed to modify the database.

If you bind a scalar function instead of a stored procedure to this call then its name must end with '_GrantAccess'.

May modify database: Yes

Resultset: Access permissions

Return whether the user is allowed to visit the menu item with the current parameters.
Table count: repeated exactly once
Row count: exactly one row
Columns
GrantAccess mandatory bit
1 if the user is allowed to view the menu item, 0 if the user should not be allowed to view the menu item.

If 0 then an error will be logged as the user should not have been able to reach the menu item with the given parameters in the first place.

Querystring parameters

menuitemheading optional
String that replaces the menu item name when the menu item is loaded. It does not replace the name before that (like for example in the navigator). It is ignored if the menu item is displayed as a part in a multipart in which case the name can be set from the multipart procedure.

Examples

Treeview procedure (hierarchical table)

Simple treeview procedure used when the database model has a straight forward child-parent structure. In the example drag and drop is supported by all nodes and reorder is not but that can easily be changed by modifying the columns [CanDrag], [CanDrop] and [CanReorder].

CREATE PROCEDURE Example.Node_TreeView
	@Id		int = null
AS
BEGIN

	------------------------------
	-- Fetch nodes for the tree --
	------------------------------

	SELECT
		-- Id and display text
		N.NodeId AS [Id],
		N.NodeName AS [NodeText],

		-- Node settings
		CONVERT(bit, 1) AS [CanDrag],
		CONVERT(bit, 1) AS [CanDrop],
		NodeInfo.CanExpand,
		CONVERT(bit, 0) AS [CanReorder],
		CONVERT(bit, 0) AS [Expanded],

		-- Passing fields
		N.NodeId,
		
		-- Dependson
		CONVERT(bit, NULL) AS [ShowSomeKindOfLink]
	FROM
		Example.Node N
		-- Check if there are child nodes
		LEFT JOIN (
			SELECT DISTINCT
				NC.NodeIdParent
			FROM
				Example.Node NC
		) Parents ON
			Parents.NodeIdParent = N.NodeId
		CROSS APPLY (
			SELECT
				CONVERT(
					bit,
					CASE
						-- Only nodes with children are expandable
						WHEN Parents.NodeIdParent IS NOT NULL THEN
							1
						ELSE
							0
					END
				) AS [CanExpand]
		) NodeInfo
	WHERE
		-- When @Id is NULL we only want nodes that have no parent
		(
			@Id IS NULL AND
			N.NodeIdParent IS NULL
		)
		OR
		N.NodeIdParent = @Id
	ORDER BY
		N.NodeName
		
	-- Defines root-node properties
	IF @Id IS null
	BEGIN
		SELECT
			null AS [Id],
			'Root' AS [NodeText],
			convert(bit, 0) AS [CanDrop]
	END

END

Treeview procedure (different kinds of nodes)

Basic treeview procedure when having a tree consisting of different kinds of nodes, say for example some kind of category at the top level and actual nodes at the other levels. Reorder is not supported.

CREATE PROCEDURE Example.Category_TreeView
	@Id		varchar(100) = null
AS
BEGIN

	----------
	-- Init --
	----------

	-- Prefixes used to distinguish the different kind of nodes
	DECLARE
		@CategoryPrefix	varchar(10)	= 'C',
		@NodePrefix		varchar(10)	= 'N'

	-- Parse relevant information from our @Id parameter
	DECLARE
		@CategoryId		int,
		@NodeIdParent	int

	-- @Id specifies a CategoryId (e.g. C7, C12, ...)
	IF @Id LIKE @CategoryPrefix + '%'
	BEGIN
		SELECT
			@CategoryId =
				-- The parameter should be NULL if nothing is specified after the prefix
				NULLIF(
					-- Remove the prefix from @Id, leaving the CategoryId
					STUFF(
						@Id,
						-- Start from the beginning of the string
						1,
						-- The number of characters to replace should be the length of the prefix
						LEN(@CategoryPrefix),
						-- Replace with the empty string
						''
					),
					''
				)
	END
	-- @Id specifies a NodeId (e.g. N17, N32, ...)
	ELSE IF @Id LIKE @NodePrefix + '%'
	BEGIN
		SELECT
			@NodeIdParent =
				-- The parameter should be NULL if nothing is specified after the prefix
				NULLIF(
					-- Remove the prefix from @Id, leaving the NodeId
					STUFF(
						@Id,
						-- Start from the beginning of the string
						1,
						-- The number of characters to replace should be the length of the prefix
						LEN(@NodePrefix),
						-- Replace with the empty string
						''
					),
					''
				)
	END

	/*
		Fetch nodes for our tree, different modes depending on the level
	*/

	----------------
	-- Root level --
	----------------

	IF @Id IS NULL
	BEGIN
		SELECT
			-- Id and display text
			@CategoryPrefix + ISNULL(CONVERT(varchar(100), C.CategoryId), '') AS [Id],
			C.CategoryName AS [NodeText],

			-- Node settings
			CONVERT(bit, 0) AS [CanDrag],
			CONVERT(bit, 1) AS [CanDrop],
			NodeInfo.CanExpand,
			CONVERT(bit, 0) AS [CanReorder],
			CONVERT(bit, 0) AS [Expanded],

			-- Passing fields
			C.CategoryId,
			CONVERT(int, NULL) AS [NodeId],
 
			-- Dependson
			CONVERT(bit, NULL) AS [ShowSomeKindOfLink]
		FROM
			Example.Category C
			-- Check if there are child nodes
			LEFT JOIN (
				SELECT DISTINCT
					N.CategoryId
				FROM
					Example.Node N
			) Parents ON
				Parents.CategoryId = C.CategoryId
			CROSS APPLY (
				SELECT
					CONVERT(
						bit,
						CASE
							-- Only nodes with children are expandable
							WHEN Parents.CategoryId IS NOT NULL THEN
								1
							ELSE
								0
						END
					) AS [CanExpand]
			) NodeInfo
		ORDER BY
			C.SortOrder

		-- Defines root-node properties
		SELECT
			null AS [Id],
			'Root' AS [NodeText],
			convert(bit, 0) AS [CanDrop]

		RETURN

	END

	------------------------------------------------------------------------------------
	-- The other levels, either nodes for a category or nodes with a specified parent --
	------------------------------------------------------------------------------------

	SELECT
		-- Id and display text
		@NodePrefix + ISNULL(CONVERT(varchar(100), N.NodeId), '') AS [Id],
		N.NodeName AS [NodeText],

		-- Node settings
		CONVERT(bit, 1) AS [CanDrag],
		CONVERT(bit, 1) AS [CanDrop],
		NodeInfo.CanExpand,
		CONVERT(bit, 0) AS [CanReorder],
		CONVERT(bit, 0) AS [Expanded],

		-- Passing fields
		CONVERT(int, NULL) AS [CategoryId],
		N.NodeId,
		
		-- Dependson
		CONVERT(bit, NULL) AS [ShowSomeKindOfLink]
	FROM
		Example.Node N
		-- Check if there are child nodes
		LEFT JOIN (
			SELECT DISTINCT
				NC.NodeIdParent
			FROM
				Example.Node NC
		) Parents ON
			Parents.NodeIdParent = N.NodeId
		CROSS APPLY (
			SELECT
				CONVERT(
					bit,
					CASE
						-- Only nodes with children are expandable
						WHEN Parents.NodeIdParent IS NOT NULL THEN
							1
						ELSE
							0
					END
				) AS [CanExpand]
		) NodeInfo
	WHERE
		-- At the next to topmost level we specify a CategoryId and want only nodes for that category
		(
			@CategoryId IS NULL
			OR
			N.CategoryId = @CategoryId
		) AND
		-- At the next to topmost level @NodeIdParent is NULL and the column should also be NULL.
		-- At the other levels we have a @NodeIdParent and want only nodes with that parent.
		(
			(
				@NodeIdParent IS NULL AND
				N.NodeIdParent IS NULL
			)
			OR
			N.NodeIdParent = @NodeIdParent
		)
	ORDER BY
		N.NodeName

END

File Drag & Drop

Create these tables if you want to run the example procedures. There is one table with files and one with folders, and folders can contain files or other folders.

CREATE TABLE Example.FileTreeFolder
(
	FileTreeFolderId int IDENTITY(1,1)
		CONSTRAINT PK_FileTreeFolder PRIMARY KEY,
	FileTreeFolderIdParent int NULL
		CONSTRAINT FK_FileTreeFolder_FileTreeFolder_Parent
		FOREIGN KEY REFERENCES Example.FileTreeFolder (FileTreeFolderId),
	FolderName varchar(200) NOT NULL
)

CREATE TABLE Example.FileTreeFile
(
	FileTreeFileId int IDENTITY(1,1)
		CONSTRAINT PK_FileTreeFile PRIMARY KEY,
	FileTreeFolderIdParent int NOT NULL
		CONSTRAINT FK_FileTreeFile_FileTreeFolder
		FOREIGN KEY REFERENCES Example.FileTreeFolder (FileTreeFolderId),
	FileName nvarchar(255) NOT NULL,
	ContentType varchar(300) NOT NULL,
	FileData varbinary(max) NOT NULL
)

INSERT Example.FileTreeFolder ( FileTreeFolderIdParent, FolderName )
VALUES ( NULL, 'Secret' )

INSERT Example.FileTreeFolder ( FileTreeFolderIdParent, FolderName )
VALUES ( (SELECT FileTreeFolderId FROM Example.FileTreeFolder WHERE FolderName = 'Secret'), 'Top Secret' )

INSERT Example.FileTreeFolder ( FileTreeFolderIdParent, FolderName )
VALUES ( NULL, 'Public' )

The component procedure populates the tree.

CREATE OR ALTER PROCEDURE Example.FileTree_Treeview
	@Id		varchar(100) = NULL
AS
BEGIN
	-- Fetch Id and Prefix
	DECLARE 
		@ParentIdPrefix varchar(50),
		@ParentId int;

	IF @Id IS NOT NULL
	BEGIN
		SELECT 
			@ParentIdPrefix = LEFT(@Id, CHARINDEX('_', @Id)-1), 
			@ParentId = CONVERT(int, RIGHT(@Id, LEN(@Id)-CHARINDEX('_', @Id)));
	END;

	-- Root level
	IF @ParentIdPrefix IS NULL
	BEGIN
		SELECT
			CONCAT('Folder_', F.FileTreeFolderId) AS [Id],
			F.FolderName AS [NodeText],

			0 AS CanDrag,
			1 AS CanDrop,
			1 AS CanDropFile,
			1 AS CanExpand,
			1 AS Expanded,

			-- Passing fields
			F.FileTreeFolderId
		FROM
			Example.FileTreeFolder F
		WHERE
			F.FileTreeFolderIdParent IS NULL
		ORDER BY
			F.FolderName;

		RETURN;
	END;

	-- Child folders and files in folder.
	IF @ParentIdPrefix = 'Folder'
	BEGIN
		SELECT
			CONCAT('Folder_', F.FileTreeFolderId) AS [Id],
			F.FolderName AS [NodeText],

			0 AS CanDrag,
			1 AS CanDrop,
			1 AS CanDropFile,
			1 AS CanExpand,
			1 AS Expanded,

			-- Passing fields
			F.FileTreeFolderId,
			NULL AS FileTreeFileId
		FROM
			Example.FileTreeFolder F
		WHERE
			F.FileTreeFolderIdParent = @ParentId

		UNION ALL

		SELECT
			CONCAT('File_', F.FileTreeFileId) AS [Id],
			F.FileName AS [NodeText],

			1 AS CanDrag,
			0 AS CanDrop,
			0 AS CanDropFile,
			0 AS CanExpand,
			0 AS Expanded,

			-- Passing fields
			NULL AS FileTreeFolderId,
			F.FileTreeFileId
		FROM
			Example.FileTreeFile F
		WHERE
			F.FileTreeFolderIdParent = @ParentId

		ORDER BY
			NodeText;

		RETURN;
	END;
END;

The InsertUpdate procedure handles drag & drop.

CREATE OR ALTER PROCEDURE Example.FileTree_Treeview_InsertUpdate
	@Id     varchar(MAX) = NULL,
	@DragId varchar(max) = NULL,
	@DropId varchar(max) = NULL,
	@Action varchar(50)  = NULL
AS
BEGIN
	-- Drag and drop nodes in tree.
	IF @DragId IS NOT NULL AND @DropId IS NOT NULL
	BEGIN
		UPDATE Example.FileTreeFile SET
			FileTreeFolderIdParent = RIGHT(@DropId, LEN(@DropId) - CHARINDEX('_', @DropId))
		WHERE
			FileTreeFileId = RIGHT(@DragId, LEN(@DragId) - CHARINDEX('_', @DragId));

		RETURN;
	END;

	-- Drop files on tree.
	DECLARE 
		@FileTreeFolderIdParent int = CONVERT(int, RIGHT(@Id, LEN(@Id) - CHARINDEX('_', @Id)));

	IF @Action = N'VerifyFiles'
	BEGIN
		IF EXISTS (SELECT * FROM #FileData WHERE ContentType NOT LIKE 'image/%')
		BEGIN
			SELECT 'Images only please!' AS ADMIN_ErrorMessage;
			RETURN;
		END;

		RETURN;
	END;

	IF @Action = N'SaveFiles'
	BEGIN
		INSERT Example.FileTreeFile
		(
			FileTreeFolderIdParent,
			FileName,
			ContentType,
			FileData
		)
		SELECT
			@FileTreeFolderIdParent,
			FileName,
			ContentType,
			FileData
		FROM
			#FileData;

		RETURN;
	END;
END;