Treeview

Component settings

SQL

Insert/Update/Delete SP

InfoSQL

Custom access control and logging

Querystring parameters

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