SQL Server Compact Workflow Persistence Service

CREATED RELEASE: Version 1.2 (nov 11, 2008)

Tue, 11 Nov 2008 14:36:36 GMT

In this version I added support for retreiving all workflows using the GetAllWorkflows() function and for determining all workflows with an expired timer using the LoadExpiredTimerWorkflowIds() function. Both these functions mimic the behavior of these function in the SqlWorkflowPersistenceService.

NEW POST: Support for 64 bit

Maurice — Mon, 10 Nov 2008 01:17:54 GMT

At the time I posted that there was no 64 bits version of SqlCe publicly available. As you correctly observed this has changes as there are now both 32bit and 64bits versions of SqlCe. The SqlCeWorkflowPersistenceService sjould run just fine using the 64 bits version.

Maurice

NEW POST: Support for 64 bit

MattAdamson — Sun, 09 Nov 2008 17:03:08 GMT

I don't understand this comment you made in SqlCeWorkflowPersistenceService.cs

"Note: SQL Server Compact currently doesn't support running under 64 bits"

as a 64 bit edition of SQL Server Compact is available for download at

http://www.microsoft.com/downloads/details.aspx?FamilyId=DC614AEE-7E1C-4881-9C32-3A6CE53384D9&displaylang=en

NEW POST: Great Job!

RonJacobs — Wed, 23 Jul 2008 20:09:44 GMT

Nice work on this very useful utility. I was thinking that someday I would write this code. Now I don't have to.

COMMENTED ISSUE: Retrieving workflow instances containing SqlCeException ExceptionHandlers

Maurice — Tue, 22 Jul 2008 22:23:35 GMT

See my bug posting to the SqlServer connections website first for background on the source of the problem, and at least a partial workaround in the comments.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356397

There is a bug in the SqlCeException class (desktop version). In short, the SqlCeException is marked as serializable, but it doesn't implement the necessary code to support successful deserialization.

Basically, if a workflow activity has an ExceptionHandlerActivity that handles (and captures) an SqlCeException which will be subsequently handled by child activities in the fault handler, then the runtime will persist the activity prior to scheduling the child fault handler activities to perform whatever operations (e.g. logging) they are going to do. When the child fault handler activities are ready to run, the runtime goes to load the workflow instance and BANG, a deserialization exception is thrown.

Since I didn't write your (very appreciated) SqlCe Persistance Service, I don't pretend to fully understand how all of it works. I have tried wrapping the workflow instance deserialization logic with an exception handler, which prevents the application from shutting down, but that still leaves several problems which I don't know how to go about addressing.

1. The workflow instance is orphaned in the database with no way to retrieve it. You can't terminate it using the runtime engine unless you can retrieve a workflow instance. If you just go in and delete it, then the runtime and the host application have no way of knowing that anything happend, it just vanishes.

2. Returning null causes the calling layers of the runtime to throw an InvalidOperationException, which is just really complicates other code unnecessarily.

Any help you can provide in this matter would be greatly appreciated.

Comments: Hi Phil, First of all make sure you are using the 1.1 version of the SqlCeWorkflowPersistenceService otherwise the SerializationException won't be caught. Next add an event handler for the WorkflowRuntime ServicesExceptionNotHandled event and add something like the following code to it: static void workflowRuntime_ServicesExceptionNotHandled(object sender, ServicesExceptionNotHandledEventArgs e) { Console.WriteLine("====> workflowRuntime_ServicesExceptionNotHandled"); if (e.Exception is SerializationException) { // Delete workflow we cannot rehydrate anymore WorkflowRuntime runtime = (WorkflowRuntime)sender; SqlCeWorkflowPersistenceService persistence = runtime.GetService<SqlCeWorkflowPersistenceService>(); using (SqlCeConnection connextion = new SqlCeConnection(persistence.ConnectionString)) { connextion.Open(); string commandStr = "delete from InstanceState where InstanceId = @pInstanceId"; SqlCeCommand command = new SqlCeCommand(commandStr, connextion); command.Parameters.Add("pInstanceId", e.WorkflowInstanceId); if (command.ExecuteNonQuery() == 1) Console.WriteLine("Deleted workflow {0}", e.WorkflowInstanceId); else Console.WriteLine("Deleting workflow {0} failed", e.WorkflowInstanceId); } } } You might want to add some more checks and logging about the workflows you are deleting but this basically deletes all workflows that cannot be rehydrated from the store. And you are right there is no way to do so using workflow infrastructure so you need to use the SQL Ce database directly. Maurice

COMMENTED ISSUE: Retrieving workflow instances containing SqlCeException ExceptionHandlers

pshaffer — Tue, 22 Jul 2008 17:55:49 GMT

See my bug posting to the SqlServer connections website first for background on the source of the problem, and at least a partial workaround in the comments.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356397

There is a bug in the SqlCeException class (desktop version). In short, the SqlCeException is marked as serializable, but it doesn't implement the necessary code to support successful deserialization.

Basically, if a workflow activity has an ExceptionHandlerActivity that handles (and captures) an SqlCeException which will be subsequently handled by child activities in the fault handler, then the runtime will persist the activity prior to scheduling the child fault handler activities to perform whatever operations (e.g. logging) they are going to do. When the child fault handler activities are ready to run, the runtime goes to load the workflow instance and BANG, a deserialization exception is thrown.

Since I didn't write your (very appreciated) SqlCe Persistance Service, I don't pretend to fully understand how all of it works. I have tried wrapping the workflow instance deserialization logic with an exception handler, which prevents the application from shutting down, but that still leaves several problems which I don't know how to go about addressing.

1. The workflow instance is orphaned in the database with no way to retrieve it. You can't terminate it using the runtime engine unless you can retrieve a workflow instance. If you just go in and delete it, then the runtime and the host application have no way of knowing that anything happend, it just vanishes.

2. Returning null causes the calling layers of the runtime to throw an InvalidOperationException, which is just really complicates other code unnecessarily.

Any help you can provide in this matter would be greatly appreciated.

Comments: It was caused by something else entirely. It was merely caught in the fault handlers wrapping a custom activity that was doing data access and persisted by the runtime prior to the fault handler's child activities being scheduled to act upon it. What would be the best way in which to clean up (i.e. remove) the orphaned workflow instance from the persistence store? I can't just leave it in there and I can't remove it using the workflow runtime without first retrieving a WorkflowInstance, which is what is failing here. I very much appreciate your efforts. -Phil

CLOSED ISSUE: Retrieving workflow instances containing SqlCeException ExceptionHandlers

Maurice — Mon, 21 Jul 2008 14:42:30 GMT

COMMENTED ISSUE: Retrieving workflow instances containing SqlCeException ExceptionHandlers

Maurice — Mon, 21 Jul 2008 14:42:10 GMT

See my bug posting to the SqlServer connections website first for background on the source of the problem, and at least a partial workaround in the comments.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356397

There is a bug in the SqlCeException class (desktop version). In short, the SqlCeException is marked as serializable, but it doesn't implement the necessary code to support successful deserialization.

Basically, if a workflow activity has an ExceptionHandlerActivity that handles (and captures) an SqlCeException which will be subsequently handled by child activities in the fault handler, then the runtime will persist the activity prior to scheduling the child fault handler activities to perform whatever operations (e.g. logging) they are going to do. When the child fault handler activities are ready to run, the runtime goes to load the workflow instance and BANG, a deserialization exception is thrown.

Since I didn't write your (very appreciated) SqlCe Persistance Service, I don't pretend to fully understand how all of it works. I have tried wrapping the workflow instance deserialization logic with an exception handler, which prevents the application from shutting down, but that still leaves several problems which I don't know how to go about addressing.

1. The workflow instance is orphaned in the database with no way to retrieve it. You can't terminate it using the runtime engine unless you can retrieve a workflow instance. If you just go in and delete it, then the runtime and the host application have no way of knowing that anything happend, it just vanishes.

2. Returning null causes the calling layers of the runtime to throw an InvalidOperationException, which is just really complicates other code unnecessarily.

Any help you can provide in this matter would be greatly appreciated.

Comments: I added additional error handling when a workflow is reloaded from the persistence store but cannot be deserialized for some reason. The WorkflowRuntime ServicesExceptionNotHandled event will now be raised with the original exception and the workflow instance id of the offending workflow. The workflow itself is left as is so you will still need to decide what to do with the error and take the appropriate action. I would like to know if the original SqlCeException was caused by the SqlCeWFPersistence or something else. Maurice

CREATED RELEASE: version 1.1 (Jul 21, 2008)

Mon, 21 Jul 2008 14:38:25 GMT

Added additional error handling when a workflow is reloaded from the persistence store but cannot be deserialized for some reason. The WorkflowRuntime ServicesExceptionNotHandled event will now be raised with the original exception and the workflow instance id of the offending workflow. The workflow itself is left as is so you will still need to decide what to do with the error and take the appropriate action

CREATED ISSUE: Retrieving workflow instances containing SqlCeException ExceptionHandlers

pshaffer — Thu, 17 Jul 2008 22:40:27 GMT

UPDATED WIKI: Home

Maurice — Thu, 10 Apr 2008 14:11:58 GMT

   static void Main(string[] args)
   {
       using(WorkflowRuntime workflowRuntime = new WorkflowRuntime())
       {
           string connectionStr = @"Data Source=WorkflowPersistenceDatabase.sdf";
   
           SqlCeWorkflowPersistenceService persistence = new SqlCeWorkflowPersistenceService(connectionStr, true);
           workflowRuntime.AddService(persistence);
   
           workflowRuntime.WorkflowTerminated += delegate(object sender, WorkflowTerminatedEventArgs e)
           {
               Console.WriteLine(e.Exception.Message);
           };
   
           WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(TestClient.Workflow1));
           instance.Start();
   
           Console.WriteLine("Done...");
           Console.ReadLine();
       }
   }

UPDATED WIKI: Home

Maurice — Thu, 10 Apr 2008 14:11:08 GMT

Resource Page Description
This library enabled the use of SQL Server Compact edition as the data store for workflow persistence.

This project uses LINQ to SQL as the data access technology. Although the main point of this project is to use SQL Server Compact edition as the database using LINQ to SLQ means it could equally well be used with a full SQL Server edition. The datbase schema is slightly different to the default Workflow Foundation persistence database used. The main difference is that no provision is made or an owner or an ownedUntill as I consider this less than useful with SQL Server Compact as this is a local in process database and not really meant as a shared database.

Sample usage:
{{ static void Main(string[] args) { using (WorkflowRuntime workflowRuntime = new WorkflowRuntime()) { string connectionStr = @"Data Source=WorkflowPersistenceDatabase.sdf"; SqlCeWorkflowPersistenceService persistence = new SqlCeWorkflowPersistenceService(connectionStr, true); workflowRuntime.AddService(persistence); workflowRuntime.WorkflowTerminated += delegate(object sender, WorkflowTerminatedEventArgs e) { Console.WriteLine(e.Exception.Message); }; WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(TestClient.Workflow1)); instance.Start(); Console.WriteLine("Done..."); Console.ReadLine(); } }

UPDATED WIKI: Home

Maurice — Thu, 10 Apr 2008 14:09:06 GMT

Resource Page Description
This library enabled the use of SQL Server Compact edition as the data store for workflow persistence.

This project uses LINQ to SQL as the data access technology. Although the main point of this project is to use SQL Server Compact edition as the database using LINQ to SLQ means it could equally well be used with a full SQL Server edition. The datbase schema is slightly different to the default Workflow Foundation persistence database used. The main difference is that no provision is made or an owner or an ownedUntill as I consider this less than useful with SQL Server Compact as this is a local in process database and not really meant as a shared database.

Sample usage:
{{ static void Main(string[] args) { using(WorkflowRuntime workflowRuntime = new WorkflowRuntime()) { string connectionStr = @"Data Source=WorkflowPersistenceDatabase.sdf"; SqlCeWorkflowPersistenceService persistence = new SqlCeWorkflowPersistenceService(connectionStr, true); workflowRuntime.AddService(persistence); workflowRuntime.WorkflowTerminated += delegate(object sender, WorkflowTerminatedEventArgs e) { Console.WriteLine(e.Exception.Message); }; WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(TestClient.Workflow1)); instance.Start(); Console.WriteLine("Done..."); Console.ReadLine(); } }

UPDATED RELEASE: Version 1.0 (Apr 09, 2008)

Wed, 09 Apr 2008 19:43:29 GMT

The initial version of the SqlCeWorkflowPersistenceService.

UPDATED WIKI: Home

Maurice — Wed, 09 Apr 2008 19:10:56 GMT

Resource Page Description
This library enabled the use of SQL Server Compact edition as the data store for workflow persistence.

This project uses LINQ to SQL as the data access technology. Although the main point of this project is to use SQL Server Compact edition as the database using LINQ to SLQ means it could equally well be used with a full SQL Server edition. The datbase schema is slightly different to the default Workflow Foundation persistence database used. The main difference is that no provision is made or an owner or an ownedUntill as I consider this less than useful with SQL Server Compact as this is a local in process database and not really meant as a shared database.

Sample usage:
static void Main(string[] args)
{
using(WorkflowRuntime workflowRuntime = new WorkflowRuntime())
{
string connectionStr = @"Data Source=WorkflowPersistenceDatabase.sdf";

SqlCeWorkflowPersistenceService persistence = new SqlCeWorkflowPersistenceService(connectionStr, true);
workflowRuntime.AddService(persistence);

workflowRuntime.WorkflowTerminated += delegate(object sender, WorkflowTerminatedEventArgs e)
{
Console.WriteLine(e.Exception.Message);
};

WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(TestClient.Workflow1));
instance.Start();

Console.WriteLine("Done...");
Console.ReadLine();
}
}

UPDATED WIKI: Home

Maurice — Wed, 09 Apr 2008 19:09:37 GMT

Resource Page Description
This library enabled the use of SQL Server Compact edition as the data store for workflow persistence.

This project uses LINQ to SQL as the data access technology. Although the main point of this project is to use SQL Server Compact edition as the database using LINQ to SLQ means it could equally well be used with a full SQL Server edition. The datbase schema is slightly different to the default Workflow Foundation persistence database used. The main difference is that no provision is made or an owner or an ownedUntill as I consider this less than useful with SQL Server Compact as this is a local in process database and not really meant as a shared database.

Sample usage:
static void Main(string[] args)
{
using(WorkflowRuntime workflowRuntime = new WorkflowRuntime())
{
string connectionStr = @"Data Source=WorkflowPersistenceDatabase.sdf";

SqlCeWorkflowPersistenceService persistence = new SqlCeWorkflowPersistenceService(connectionStr, true);
workflowRuntime.AddService(persistence);

workflowRuntime.WorkflowTerminated += delegate(object sender, WorkflowTerminatedEventArgs e)
{
Console.WriteLine(e.Exception.Message);
};

WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(TestClient.Workflow1));
instance.Start();

Console.WriteLine("Done...");
Console.ReadLine();
}
}

UPDATED WIKI: Home

Maurice — Wed, 09 Apr 2008 19:06:48 GMT

Resource Page Description
This library enabled the use of SQL Server Compact edition as the data store for workflow persistence.

This project uses LINQ to SQL as the data access technology. Although the main point of this project is to use SQL Server Compact edition as the database using LINQ to SLQ means it could equally well be used with a full SQL Server edition. The datbase schema is slightly different to the default Workflow Foundation persistence database used. The main difference is that no provision is made or an owner or an ownedUntill as I consider this less than useful with SQL Server Compact as this is a local in process database and not really meant as a shared database.

Sample usage:
static void Main(string[] args)
{
using(WorkflowRuntime workflowRuntime = new WorkflowRuntime())
{
string connectionStr = @"Data Source=WorkflowPersistenceDatabase.sdf";

SqlCeWorkflowPersistenceService persistence = new SqlCeWorkflowPersistenceService(connectionStr, true);
workflowRuntime.AddService(persistence);

workflowRuntime.WorkflowTerminated += delegate(object sender, WorkflowTerminatedEventArgs e)
{
Console.WriteLine(e.Exception.Message);
};

WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(TestClient.Workflow1));
instance.Start();

Console.WriteLine("Done...");
Console.ReadLine();
}
}

**Delete the following note before publishing **

This resource page is currently in setup mode and only available to coordinators and developers. Once you have finished setting up your resource page you can publish it to make it available to all MSDN Code Gallery visitors.

To get your Resource Page ready to publish, you should do the following:

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

UPDATED WIKI: Home

Maurice — Wed, 09 Apr 2008 19:04:58 GMT

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

CREATED RELEASE: Version 1.0 (Apr 09, 2008)

Wed, 09 Apr 2008 18:59:05 GMT

The initial version of the SqlCeWorkflowPersistenceService.