Stored Procedure for obtaining WF4 bookmarks

Windows Workflow 4 stores its persisted bookmarks inside of the [System.Activities.DurableInstancing].[InstancesTable] table in the column [BlockingBookmarks]

So I was having an issue in my OpenAccess Domain Model getting a list of items in order to run queries on (give me all bookmarks pending across instances for X user…for example)

So that led me to make this…it’ll dump bookmarks into this format
[ID][BOOKMARKNAME][CREATED]
Such that each row denoted a pending bookmark, and the ID column is not unique (lets say there’s 4 bookmarks for the same InstanceID, thats 4 rows)

	-- =============================================
	-- Author: Steve
	-- Create date: Auguest 2, 2010
	-- Description: Converts the Pending Bookmarks into rows
	-- =============================================
	CREATE PROCEDURE [dbo].[workflow_Get_Bookmarks]
	@instanceID uniqueidentifier = NULL
	AS
	BEGIN
	SET NOCOUNT ON;

	DECLARE @Bookmarks TABLE(
	ID uniqueidentifier,
	Bookmark varchar(1000),
	Created datetime
	)

	Declare @ID as uniqueidentifier
	Declare @Bookmark as nvarchar(1000)
	Declare @Created as datetime

	Declare BookmarkCursor CURSOR FAST_FORWARD FOR

	SELECT [Id],[BlockingBookmarks], [CreationTime]
	FROM [authdb].[System.Activities.DurableInstancing].[InstancesTable]
	ORDER BY [CreationTime] desc

	OPEN BookmarkCursor
	FETCH NEXT FROM BookmarkCursor
	INTO @ID, @Bookmark, @Created

	WHILE @@FETCH_STATUS = 0
	BEGIN
	INSERT INTO @Bookmarks(ID, Bookmark, Created)
	SELECT @ID, REPLACE(string,']',''), @Created
	FROM [authdb].[dbo].[SplitToRows] (
	'['
	,@Bookmark)

	FETCH NEXT FROM BookmarkCursor
	INTO @ID, @Bookmark, @Created
	END

	CLOSE BookmarkCursor
	DEALLOCATE BookmarkCursor


	IF @instanceID IS NULL
	BEGIN
	SELECT *
	FROM @Bookmarks
	WHERE (Bookmark <> null) or (Bookmark <> '')
	END
	ELSE
	BEGIN
	SELECT *
	FROM @Bookmarks
	WHERE (Bookmark <> null) or (Bookmark <> '') AND
	ID = @instanceID
	END
	END

	GO
	Ok, now there's one other component required, and that's a table function to convert the initial bookmark string ([value][value][value]) into living on their own rows

	/***********************************************************/
	/ This is to help convert Instance Bookmarks to a table /
	/***********************************************************/
	CREATE FUNCTION [dbo].[SplitToRows] (@sep char(1), @s varchar(512))
	RETURNS table
	AS
	RETURN (
	WITH Pieces(pn, start, stop) AS (
	SELECT 1, 1, CHARINDEX(@sep, @s)
	UNION ALL
	SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
	FROM Pieces
	WHERE stop > 0
	)
	SELECT pn as id, LTRIM(RTRIM(SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END))) AS string
	FROM Pieces
	)


	GO

view raw getwf4bookmarks.sql hosted with ❤ by GitHub

Boost your online presence.