Concept: Leverage SharePoint Workflows with External Lists - Part 1

  • Article

PLEASE NOTE THAT THIS IS STRICTLY A CONCEPT AND IN NO WAY IS RECOMMENDED TO BE USED IN PRODUCTION ENVIRONMENTS.

In SharePoint 2010, workflows cannot be directly associated with external lists. This is because the data is not stored in SharePoint, so SharePoint does not have a mechanism that can get notified when items change in the external data source. However, external data can be consumed in a workflow.

Some blog posts to get you started:

So, the question is: If you really want to leverage workflows with external lists, such that when items change (add, edit, delete) and start a SharePoint workflow - can you?

The answer is: Yes, if you know your external data source and external items, sure you can. This also depends whether the external data source provides the options to do so.

Below is a simple concept I came up with SQL Server 2008 R2 being my back end data source for my external list. This concept works only with SQL Server 2008 R2 (though it may work with SQL 2005, SQL 2008 - I haven't tested it).

But before diving into this cool concept, let me show some options we have that will help us:

Site Workflows

Site workflows are a type of workflow that is independent of a list item. In simple words, there is no association with a list or document library or content type.

SQL Server CLR Assembly Stub

Beginning with SQL Server 2005, SQL Server features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. This means that you can now write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.

So, Using SQL 2008 R2 CLR integration, triggers, a simple WCF service - we are able to come up with a concept where when the external data changes in the data source (SQL Server), it triggers the workflow in SharePoint.

 

The Workflow Service

  • This is the service which will start the workflow for an external list for a specific external data event – add, update, delete
  • This is a WCF Service installed in the ISAPI folder. The service  can be accessed via the following URL – https://your-site/_vti_bin/WorkflowService.svc
  • For this concept, this service is available for anyone to connect  
  • For a walkthrough of how to create a custom WCF service in SharePoint 2010, click here - https://msdn.microsoft.com/en-us/library/ff521581.aspx

SQL CLR Assembly Stub

  • This is the stub that we will register with SQL server
  • In order to register this with SQL Server, we have to:
    • Create a database called ewsdb
    • Register all the required assemblies (dependencies) with the ewsdb database
    • Create a stored procedure that calls a method in this assembly stub

Trigger – Insert, Update, Delete

Now that we have our external method wrapped in a stored procedure in the ewsdb database, we can invoke it. That is what we will do in our trigger.

Below is the trigger code and how it calls the external method:

Thanks to BCS - Since this resides in SQL Server, this trigger is called even when the item is updated via the external list SharePoint interface.

How do we associate and validate?

I built a custom workflow settings page for external lists:

The workflow settings will allow you to register for database events:

This information is then stored in a custom list which can be queried from our WCF service to be validated for the datbase event:

The trigger constructs the data that is sent to the WCF service (which is the CLR .Net assembly integration):

SET @workflowEventData = '<Data><SalesOrderId>' +
cast(@primaryKey as varchar(20)) + '</SalesOrderId></Data>'

This is sent as a parameter when we invoke the WCF service from SQL:

Some things to note:

  • https://intranet.contoso.com is the site url
  • Trigger is for update event
  • 'Update' is the database event
  • 'ExternalListWorkflow' is the name of the site workflow
  • 'Sales Order' is the external list
  • We identify the external item from its primary key (look in the @workflowEventData)

With everything working - the update in SQL server invokes the WCF service and WCF service validates for the database event starts the site workflow!

In the next blog post, let us look into the actual implementation.

PLEASE NOTE THAT THIS IS STRICTLY A CONCEPT AND IN NO WAY IS RECOMMENDED TO BE USED IN PRODUCTION ENVIRONMENTS.