Store extensibility

SqlWorkflowInstanceStore allows users to promote custom, application-specific properties that can be used to query for instances in the persistence database. The act of promoting a property causes the value to be available within a special view in the database. These promoted properties (properties that can be used in user queries) can be of simple types, such as Int64, Guid, String, and DateTime, or of a serialized binary type (byte[]).

The SqlWorkflowInstanceStore class has the Promote method that you can use to promote a property as a property that can be used in queries. The following example is an end-to-end example of store extensibility.

1. In this example scenario, a document processing (DP) application has workflows, each of which uses custom activities for document processing. These workflows have a set of state variables that need to be made visible to the end user. To achieve this, the DP application provides an instance extension of type PersistenceParticipant, which is used by activities to supply the state variables.

   class DocumentStatusExtension : PersistenceParticipant
   {
       public string DocumentId;
       public string ApprovalStatus;
       public string UserName;
       public DateTime LastUpdateTime;
   }
   

2. The new extension is then added to the host.

   static Activity workflow = CreateWorkflow();
   WorkflowApplication application = new WorkflowApplication(workflow);
   DocumentStatusExtension documentStatusExtension = new DocumentStatusExtension ();
   application.Extensions.Add(documentStatusExtension);
   

   For more details about adding a custom persistence participant, see the Persistence Participants sample.

3. The custom activities in the DP application populate various status fields in the Execute method.

   public override void Execute(CodeActivityContext context)
   {
       // ...
       context.GetExtension<DocumentStatusExtension>().DocumentId = Guid.NewGuid();
       context.GetExtension<DocumentStatusExtension>().UserName = "John Smith";
       context.GetExtension<DocumentStatusExtension>().ApprovalStatus = "Approved";
       context.GetExtension<DocumentStatusExtension>().LastUpdateTime = DateTime.Now();
       // ...
   }
   

4. When a workflow instance reaches a persistence point, the CollectValues method of the DocumentStatusExtension persistence participant saves these properties into the persistence data collection.

   class DocumentStatusExtension : PersistenceParticipant
   {
       const XNamespace xNS = XNamespace.Get("http://contoso.com/DocumentStatus");
   
       protected override void CollectValues(out IDictionary<XName, object> readWriteValues, out IDictionary<XName, object> writeOnlyValues)
       {
           readWriteValues = new Dictionary<XName, object>();
           readWriteValues.Add(xNS.GetName("UserName"), this.UserName);
           readWriteValues.Add(xNS.GetName("ApprovalStatus"), this.ApprovalStatus);
           readWriteValues.Add(xNS.GetName("DocumentId"), this.DocumentId);
           readWriteValues.Add(xNS.GetName("LastModifiedTime"), this.LastUpdateTime);
   
           writeOnlyValues = null;
       }
       // ...
   }
   

   Note

   All these properties are passed to SqlWorkflowInstanceStore by the persistence framework through the SaveWorkflowCommand.InstanceData collection.

5. The DP application initializes the SQL Workflow Instance Store and invokes the Promote method to promote this data.

   SqlWorkflowInstanceStore store = new SqlWorkflowInstanceStore(connectionString);
   
   List<XName> variantProperties = new List<XName>()
   {
       xNS.GetName("UserName"),
       xNS.GetName("ApprovalStatus"),
       xNS.GetName("DocumentId"),
       xNS.GetName("LastModifiedTime")
   };
   
   store.Promote("DocumentStatus", variantProperties, null);
   

   Based on this promotion information, SqlWorkflowInstanceStore places the data properties in the columns of the InstancePromotedProperties view.

6. To query a subset of the data from the promotion table, the DP application adds a customized view on top of the promotion view.

   create view [dbo].[DocumentStatus] with schemabinding
   as
       select  P.[InstanceId] as [InstanceId],
           P.Value1 as [UserName],
           P.Value2 as [ApprovalStatus],
           P.Value3 as [DocumentId],
           P.Value4 as [LastUpdatedTime]
   from [System.Activities.DurableInstancing].[InstancePromotedProperties] as P
   where P.PromotionName = N'DocumentStatus'
   go
   

[System.Activities.DurableInstancing.InstancePromotedProperties] view

Column Name	Column Type	Description
InstanceId	GUID	The workflow instance that this promotion belongs to.
PromotionName	nvarchar(400)	The name of the promotion itself.
Value1, Value2, Value3,..,Value32	sql_variant	The value of the promoted property itself. Most SQL primitive data types except binary blobs and strings over 8000 bytes in length can fit in sql_variant.
Value33, Value34, Value35, …, Value64	varbinary(max)	The value of promoted properties that are explicitly declared as varbinary(max).