Treeview

Component settings

SQL

Insert/Update/Delete SP

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
)

CREATE TABLE #FileData
(
FileData varbinary(max) NULL,
FileName nvarchar(max) NOT NULL,
ContentSize bigint NOT NULL,
ContentType nvarchar(max) NOT NULL
)

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

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;