Windows Workflow: Custom persistence service, same schema and logic?

Question

So I've been using the Windows Workflow default SQL persistence service that handles everything on its own with no fuss. Now, one of the environments I deploy to does not have direct access to the database; it pushes stored procedure calls through a web service over a proxy for added security. This of course does not please the SQL persistence gods, though I'd like to be able to leverage it from that environment. So really the only difference I have between the default behavior and potentially the one I code for our environment would be another way of calling the stored procedures: remotely instead of direct access (the web service shuffles datasets back and forth).

If you were to go about solving this problem so you could leverage the persistence services, what would you do? Theoretically I'd like to keep all the same tables and procedures, but I can't find a good reference as to what I need to call the stored procedures with.

Answer 1

I have written a WorkflowPersistenceService that works against SQL Compact. It's structure closely matches the internal structure of the original SqlWorkflowPersistenceService with the exception that it doesn't use stored procedures, it can't as SQL Compact doesn't support them. The provider uses Linq to SQL instead. Because L2S works equally well with SQL Compact as SQL Server the provider does a perfectly good job with either. The only difference in the database scheme is the removal of the ownership because SQL Compact is a single user database. That said adding it would be quite easy. Taking this and adding a WCF layer between the WorkflowPersistenceService part and the L2S part should not prove to be very hard.

You can download the project from http://code.msdn.microsoft.com/SqlCeWFPersistence.

The code you would need to change is the SqlCeDataAccess class.