Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Navigational Workflows Unleashed in WWF/ASP.NET 3.5

, 21 Oct 2008 CPOL
Case-study on the internals of a Navigational Workflow engine for a fictional dating website called “World Wide Dating”.
datesite.zip
DateSite
App_Data
bin
database setup procedure
DateSite.csproj.user
Global.asax
images
complete.png
critical.png
logo2.png
warning.png
Profile.dbml
Profile.dbml.layout
Properties
Service References
NavigationWorkflow
bin
NavigationWorkflow.layout
Properties
Settings.settings
��USE [master]

GO

/****** Object:  Database [Dating]    Script Date: 04/06/2008 15:17:30 ******/

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Dating')

DROP DATABASE [Dating]

GO



USE [master]

GO

/****** Object:  Database [Dating]    Script Date: 04/06/2008 15:18:09 ******/

CREATE DATABASE [Dating] ON  PRIMARY 

( NAME = N'Dating', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Dating.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'Dating_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Dating_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'Dating', @new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [Dating].[dbo].[sp_fulltext_database] @action = 'disable'

end

GO

ALTER DATABASE [Dating] SET ANSI_NULL_DEFAULT OFF 

GO

ALTER DATABASE [Dating] SET ANSI_NULLS OFF 

GO

ALTER DATABASE [Dating] SET ANSI_PADDING OFF 

GO

ALTER DATABASE [Dating] SET ANSI_WARNINGS OFF 

GO

ALTER DATABASE [Dating] SET ARITHABORT OFF 

GO

ALTER DATABASE [Dating] SET AUTO_CLOSE OFF 

GO

ALTER DATABASE [Dating] SET AUTO_CREATE_STATISTICS ON 

GO

ALTER DATABASE [Dating] SET AUTO_SHRINK OFF 

GO

ALTER DATABASE [Dating] SET AUTO_UPDATE_STATISTICS ON 

GO

ALTER DATABASE [Dating] SET CURSOR_CLOSE_ON_COMMIT OFF 

GO

ALTER DATABASE [Dating] SET CURSOR_DEFAULT  GLOBAL 

GO

ALTER DATABASE [Dating] SET CONCAT_NULL_YIELDS_NULL OFF 

GO

ALTER DATABASE [Dating] SET NUMERIC_ROUNDABORT OFF 

GO

ALTER DATABASE [Dating] SET QUOTED_IDENTIFIER OFF 

GO

ALTER DATABASE [Dating] SET RECURSIVE_TRIGGERS OFF 

GO

ALTER DATABASE [Dating] SET  ENABLE_BROKER 

GO

ALTER DATABASE [Dating] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

GO

ALTER DATABASE [Dating] SET DATE_CORRELATION_OPTIMIZATION OFF 

GO

ALTER DATABASE [Dating] SET TRUSTWORTHY OFF 

GO

ALTER DATABASE [Dating] SET ALLOW_SNAPSHOT_ISOLATION OFF 

GO

ALTER DATABASE [Dating] SET PARAMETERIZATION SIMPLE 

GO

ALTER DATABASE [Dating] SET  READ_WRITE 

GO

ALTER DATABASE [Dating] SET RECOVERY FULL 

GO

ALTER DATABASE [Dating] SET  MULTI_USER 

GO

ALTER DATABASE [Dating] SET PAGE_VERIFY CHECKSUM  

GO

ALTER DATABASE [Dating] SET DB_CHAINING OFF 

GO



USE [Dating]

GO



/****** Object:  Table [dbo].[Profile]    Script Date: 04/06/2008 15:14:54 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Profile]') AND type in (N'U'))

DROP TABLE [dbo].[Profile]

GO



/****** Object:  Table [dbo].[Profile]    Script Date: 04/06/2008 15:14:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Profile](

	[DatingId] [varchar](50) NOT NULL,

	[WorkflowId] [uniqueidentifier] NOT NULL,

	[EyeColor] [varchar](50) NULL,

	[HairColor] [varchar](50) NULL,

	[BestFeature] [varchar](50) NULL,

	[LookingFor] [varchar](50) NULL,

	[StartAge] [varchar](50) NULL,

	[EndAge] [varchar](50) NULL,

	[Feet] [varchar](50) NULL,

	[Inches] [varchar](50) NULL,

	[FunActivities] [varchar](max) NULL,

	[FavoriteDestinations] [varchar](max) NULL,

	[LastRead] [varchar](max) NULL,

	[ExerciseHabits] [varchar](50) NULL,

	[Smoking] [varchar](50) NULL,

	[Drinking] [varchar](50) NULL,

	[TimeStamp] [timestamp] NOT NULL,

 CONSTRAINT [PK_Profile] PRIMARY KEY CLUSTERED 

(

	[DatingId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]



GO

SET ANSI_PADDING OFF



-- Copyright (c) Microsoft Corporation.  All rights reserved.



SET NOCOUNT ON



--

-- ROLE state_persistence_users

--

declare @localized_string_AddRole_Failed nvarchar(256)

set @localized_string_AddRole_Failed = N'Failed adding the ''state_persistence_users'' role'



DECLARE @ret int, @Error int

IF NOT EXISTS( SELECT 1 FROM [dbo].[sysusers] WHERE name=N'state_persistence_users' and issqlrole=1 )

 BEGIN



	EXEC @ret = sp_addrole N'state_persistence_users'



	SELECT @Error = @@ERROR



	IF @ret <> 0 or @Error <> 0

		RAISERROR( @localized_string_AddRole_Failed, 16, -1 )

 END

GO





--

-- TABLE InstanceState

--

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InstanceState]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[InstanceState]

GO

CREATE TABLE [dbo].[InstanceState] (

	[uidInstanceID] [uniqueidentifier] NOT NULL ,

	[state] [image] NULL ,

	[status] [int] NULL ,

	[unlocked] [int] NULL ,

	[blocked] [int] NULL ,

	[info] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

	[modified] [datetime] NOT NULL,

	[ownerID] [uniqueidentifier] NULL ,

	[ownedUntil] [datetime] NULL,

	[nextTimer] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE  UNIQUE CLUSTERED  INDEX [IX_InstanceState] ON [dbo].[InstanceState]([uidInstanceID]) ON [PRIMARY]

-- CREATE  NONCLUSTERED  INDEX [IX_InstanceState_Ownership] ON [dbo].[InstanceState]([ownerID],[ownedUntil])

GO





--

-- TABLE CompletedScope

--

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[CompletedScope]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [dbo].[CompletedScope]

GO

CREATE TABLE [dbo].[CompletedScope] (

	[uidInstanceID] [uniqueidentifier] NOT NULL,

	[completedScopeID] [uniqueidentifier] NOT NULL,

	[state] [image] NOT NULL,

	[modified] [datetime] NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE  NONCLUSTERED  INDEX [IX_CompletedScope] ON [dbo].[CompletedScope]([completedScopeID]) ON [PRIMARY]

GO

CREATE  NONCLUSTERED  INDEX [IX_CompletedScope_InstanceID] ON [dbo].[CompletedScope]( [uidInstanceID] )

GO





DBCC TRACEON (1204)





-- Copyright (c) Microsoft Corporation.  All rights reserved.



--

-- PROCEDURE InsertInstanceState

--

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertInstanceState]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[InsertInstanceState]

GO

Create Procedure [dbo].[InsertInstanceState]

@uidInstanceID uniqueidentifier,

@state image,

@status int,

@unlocked int,

@blocked int,

@info ntext,

@ownerID uniqueidentifier = NULL,

@ownedUntil datetime = NULL,

@nextTimer datetime,

@result int output,

@currentOwnerID uniqueidentifier output

As

    declare @localized_string_InsertInstanceState_Failed_Ownership nvarchar(256)

    set @localized_string_InsertInstanceState_Failed_Ownership = N'Instance ownership conflict'

    set @result = 0

    set @currentOwnerID = @ownerID

    declare @now datetime

    set @now = GETUTCDATE()



    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    set nocount on



    IF @status=1 OR @status=3

    BEGIN

	DELETE FROM [dbo].[InstanceState] WHERE uidInstanceID=@uidInstanceID AND ((ownerID = @ownerID AND ownedUntil>=@now) OR (ownerID IS NULL AND @ownerID IS NULL ))

	if ( @@ROWCOUNT = 0 )

	begin

		set @currentOwnerID = NULL

    		select  @currentOwnerID=ownerID from [dbo].[InstanceState] Where uidInstanceID = @uidInstanceID

		if ( @currentOwnerID IS NOT NULL )

		begin	-- cannot delete the instance state because of an ownership conflict

			-- RAISERROR(@localized_string_InsertInstanceState_Failed_Ownership, 16, -1)				

			set @result = -2

			return

		end

	end

	else

	BEGIN

		DELETE FROM [dbo].[CompletedScope] WHERE uidInstanceID=@uidInstanceID

	end

    END

    

    ELSE BEGIN



  	    if not exists ( Select 1 from [dbo].[InstanceState] Where uidInstanceID = @uidInstanceID )

		  BEGIN

			  --Insert Operation

			  IF @unlocked = 0

			  begin

			     Insert into [dbo].[InstanceState] 

			     Values(@uidInstanceID,@state,@status,@unlocked,@blocked,@info,@now,@ownerID,@ownedUntil,@nextTimer) 

			  end

			  else

			  begin

			     Insert into [dbo].[InstanceState] 

			     Values(@uidInstanceID,@state,@status,@unlocked,@blocked,@info,@now,null,null,@nextTimer) 

			  end

		  END

		  

		  ELSE BEGIN



				IF @unlocked = 0

				begin

					Update [dbo].[InstanceState]  

					Set state = @state,

						status = @status,

						unlocked = @unlocked,

						blocked = @blocked,

						info = @info,

						modified = @now,

						ownedUntil = @ownedUntil,

						nextTimer = @nextTimer

					Where uidInstanceID = @uidInstanceID AND ((ownerID = @ownerID AND ownedUntil>=@now) OR (ownerID IS NULL AND @ownerID IS NULL ))

					if ( @@ROWCOUNT = 0 )

					BEGIN

						-- RAISERROR(@localized_string_InsertInstanceState_Failed_Ownership, 16, -1)

						select @currentOwnerID=ownerID from [dbo].[InstanceState] Where uidInstanceID = @uidInstanceID  

						set @result = -2

						return

					END

				end

				else

				begin

					Update [dbo].[InstanceState]  

					Set state = @state,

						status = @status,

						unlocked = @unlocked,

						blocked = @blocked,

						info = @info,

						modified = @now,

						ownerID = NULL,

						ownedUntil = NULL,

						nextTimer = @nextTimer

					Where uidInstanceID = @uidInstanceID AND ((ownerID = @ownerID AND ownedUntil>=@now) OR (ownerID IS NULL AND @ownerID IS NULL ))

					if ( @@ROWCOUNT = 0 )

					BEGIN

						-- RAISERROR(@localized_string_InsertInstanceState_Failed_Ownership, 16, -1)

						select @currentOwnerID=ownerID from [dbo].[InstanceState] Where uidInstanceID = @uidInstanceID  

						set @result = -2

						return

					END

				end

				

		  END





    END

		RETURN

Return

Go

GRANT EXECUTE ON [dbo].[InsertInstanceState] TO state_persistence_users

GO





--

-- PROCEDURE RetrieveAllInstanceDescriptions

-- 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RetrieveAllInstanceDescriptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[RetrieveAllInstanceDescriptions]

GO

Create Procedure [dbo].[RetrieveAllInstanceDescriptions]

As

	SELECT uidInstanceID, status, blocked, info, nextTimer

	FROM [dbo].[InstanceState]

GO



--

-- PROCEDURE UnlockInstanceState

--

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UnlockInstanceState]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[UnlockInstanceState]

GO

Create Procedure [dbo].[UnlockInstanceState]

@uidInstanceID uniqueidentifier,

@ownerID uniqueidentifier = NULL

As



SET TRANSACTION ISOLATION LEVEL READ COMMITTED

set nocount on



		Update [dbo].[InstanceState]  

		Set ownerID = NULL,

			ownedUntil = NULL

		Where uidInstanceID = @uidInstanceID AND ((ownerID = @ownerID AND ownedUntil>=GETUTCDATE()) OR (ownerID IS NULL AND @ownerID IS NULL ))

Go

GRANT EXECUTE ON [dbo].[UnlockInstanceState] TO state_persistence_users

GO





--

-- PROCEDURE RetrieveInstanceState

--

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RetrieveInstanceState]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[RetrieveInstanceState]

GO

Create Procedure [dbo].[RetrieveInstanceState]

@uidInstanceID uniqueidentifier,

@ownerID uniqueidentifier = NULL,

@ownedUntil datetime = NULL,

@result int output,

@currentOwnerID uniqueidentifier output

As

Begin

    declare @localized_string_RetrieveInstanceState_Failed_Ownership nvarchar(256)

    set @localized_string_RetrieveInstanceState_Failed_Ownership = N'Instance ownership conflict'

    set @result = 0

    set @currentOwnerID = @ownerID



	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

	BEGIN TRANSACTION

	

    -- Possible workflow status: 0 for executing; 1 for completed; 2 for suspended; 3 for terminated; 4 for invalid



	if @ownerID IS NOT NULL	-- if id is null then just loading readonly state, so ignore the ownership check

	begin

		  Update [dbo].[InstanceState]  

		  set	ownerID = @ownerID,

				ownedUntil = @ownedUntil

		  where uidInstanceID = @uidInstanceID AND (    ownerID = @ownerID 

													 OR ownerID IS NULL 

													 OR ownedUntil<GETUTCDATE()

													)

		  if ( @@ROWCOUNT = 0 )

		  BEGIN

			-- RAISERROR(@localized_string_RetrieveInstanceState_Failed_Ownership, 16, -1)

			select @currentOwnerID=ownerID from [dbo].[InstanceState] Where uidInstanceID = @uidInstanceID 

			if (  @@ROWCOUNT = 0 )

				set @result = -1

			else

				set @result = -2

			GOTO DONE

		  END

	end

	

    Select state from [dbo].[InstanceState]  

    Where uidInstanceID = @uidInstanceID

    

	set @result = @@ROWCOUNT;

    if ( @result = 0 )

	begin

		set @result = -1

		GOTO DONE

	end

	

DONE:

	COMMIT TRANSACTION

	RETURN



End

Go

GRANT EXECUTE ON [dbo].[RetrieveInstanceState] TO state_persistence_users

GO





--

-- PROCEDURE RetrieveNonblockingInstanceStateIds

--

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[RetrieveNonblockingInstanceStateIds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[RetrieveNonblockingInstanceStateIds]

GO

CREATE PROCEDURE [dbo].[RetrieveNonblockingInstanceStateIds]

@ownerID uniqueidentifier = NULL,

@ownedUntil datetime = NULL,

@now datetime

AS

    SELECT uidInstanceID FROM [dbo].[InstanceState] WITH (TABLOCK,UPDLOCK,HOLDLOCK)

    WHERE blocked=0 AND status<>1 AND status<>3 AND status<>2 -- not blocked and not completed and not terminated and not suspended

 		AND ( ownerID IS NULL OR ownedUntil<GETUTCDATE() )

    if ( @@ROWCOUNT > 0 )

    BEGIN

        -- lock the table entries that are returned

        Update [dbo].[InstanceState]  

        set ownerID = @ownerID,

	    ownedUntil = @ownedUntil

        WHERE blocked=0 AND status<>1 AND status<>3 AND status<>2

 		AND ( ownerID IS NULL OR ownedUntil<GETUTCDATE() )

	

    END

GO

GRANT EXECUTE ON [dbo].[RetrieveNonblockingInstanceStateIds] TO state_persistence_users

GO



--

-- PROCEDURE RetrieveANonblockingInstanceStateId

--

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[RetrieveANonblockingInstanceStateId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[RetrieveANonblockingInstanceStateId]

GO

CREATE PROCEDURE [dbo].[RetrieveANonblockingInstanceStateId]

@ownerID uniqueidentifier = NULL,

@ownedUntil datetime = NULL,

@uidInstanceID uniqueidentifier = NULL output,

@found bit = NULL output

AS

 BEGIN

		--

		-- Guarantee that no one else grabs this record between the select and update

		SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

		BEGIN TRANSACTION



SET ROWCOUNT 1

		SELECT	@uidInstanceID = uidInstanceID

		FROM	[dbo].[InstanceState] WITH (updlock) 

		WHERE	blocked=0 

		AND	status NOT IN ( 1,2,3 )

 		AND	( ownerID IS NULL OR ownedUntil<GETUTCDATE() )

SET ROWCOUNT 0



		IF @uidInstanceID IS NOT NULL

		 BEGIN

			UPDATE	[dbo].[InstanceState]  

			SET		ownerID = @ownerID,

					ownedUntil = @ownedUntil

			WHERE	uidInstanceID = @uidInstanceID



			SET @found = 1

		 END

		ELSE

		 BEGIN

			SET @found = 0

		 END



		COMMIT TRANSACTION

 END

GO

GRANT EXECUTE ON [dbo].[RetrieveANonblockingInstanceStateId] TO state_persistence_users

GO



--

-- PROCEDURE RetrieveExpiredTimerIds

--

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[RetrieveExpiredTimerIds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[RetrieveExpiredTimerIds]

GO

CREATE PROCEDURE [dbo].[RetrieveExpiredTimerIds]

@ownerID uniqueidentifier = NULL,

@ownedUntil datetime = NULL,

@now datetime

AS

    SELECT uidInstanceID FROM [dbo].[InstanceState]

    WHERE nextTimer<@now AND status<>1 AND status<>3 AND status<>2 -- not blocked and not completed and not terminated and not suspended

        AND ((unlocked=1 AND ownerID IS NULL) OR ownedUntil<GETUTCDATE() )

GO

GRANT EXECUTE ON [dbo].[RetrieveExpiredTimerIds] TO state_persistence_users

GO



--

-- PROCEDURE InsertCompletedScope

--

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[InsertCompletedScope]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[InsertCompletedScope]

GO

CREATE PROCEDURE [dbo].[InsertCompletedScope]

@instanceID uniqueidentifier,

@completedScopeID uniqueidentifier,

@state image

As



SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET NOCOUNT ON



		UPDATE [dbo].[CompletedScope] WITH(ROWLOCK UPDLOCK) 

		    SET state = @state,

		    modified = GETUTCDATE()

		    WHERE completedScopeID=@completedScopeID 



		IF ( @@ROWCOUNT = 0 )

		BEGIN

			--Insert Operation

			INSERT INTO [dbo].[CompletedScope] WITH(ROWLOCK)

			VALUES(@instanceID, @completedScopeID, @state, GETUTCDATE()) 

		END



		RETURN

RETURN

GO

GRANT EXECUTE ON [dbo].[InsertCompletedScope] TO state_persistence_users

GO





--

-- PROCEDURE DeleteCompletedScope

--

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[DeleteCompletedScope]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[DeleteCompletedScope]

GO

CREATE PROCEDURE [dbo].[DeleteCompletedScope]

@completedScopeID uniqueidentifier

AS

DELETE FROM [dbo].[CompletedScope] WHERE completedScopeID=@completedScopeID

Go

GRANT EXECUTE ON [dbo].[DeleteCompletedScope] TO state_persistence_users

GO



--

-- PROCEDURE RetrieveCompletedScope

--

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[RetrieveCompletedScope]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[RetrieveCompletedScope]

GO

CREATE PROCEDURE RetrieveCompletedScope

@completedScopeID uniqueidentifier,

@result int output

AS

BEGIN

    SELECT state FROM [dbo].[CompletedScope] WHERE completedScopeID=@completedScopeID

	set @result = @@ROWCOUNT;

End

GO

GRANT EXECUTE ON [dbo].[RetrieveCompletedScope] TO state_persistence_users

GO





DBCC TRACEON (1204)

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pero Matić
Software Developer (Senior) Plan A Software
United States United States
No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150123.1 | Last Updated 21 Oct 2008
Article Copyright 2008 by Pero Matić
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid