OBA Part 5 – Building the SharePoint 2007 Workflow

In my last few app building posts we’ve been building a simple Office Business Application (OBA) for the new Northwind Traders. If you missed them:

Today we’re going to build out the SharePoint workflow using Visual Studio 2008 SharePoint 2007 workflow templates. There’s a plethora of information on building these in the MSDN library. In order to develop against SharePoint you will need to set up your development environment properly so I would read this first if you’re just getting started. My SharePoint development environment is a 32bit Windows 2008 Server running MOSS 2007 and Visual Studio Professional 2008 Service Pack 1. OBASPDiagram1

If you recall our architecture diagram of our Northwind Traders OBA involved our Sales Reps submitting purchase orders as Word 2007 documents up to SharePoint which kicked off a workflow to parse the document and update the database with the order data through our data service. This allows us to store the unstructured document on SharePoint and the structured order data in our database.

In Part 3 we built the code that does the parsing of the document, now we just need to get that code into our workflow. We’ll also build in a delay so that the workflow can check the database later to see if the order has been shipped and update the status appropriately. We’ll also take advantage of SharePoint’s workflow history and task list to report outcomes or any issues that may arise.


Creating a Document Library for the Purchase Order Documents

For this example we need to add a document library called Orders for the purchase order documents that are submitted. To create the document library, navigate to your SharePoint site and then in the right-had upper corner drop down the Site Actions, select Create (or just navigate to /_layouts/create.aspx) and then under Libraries select Document Library.


The only other property that I changed here is the document template, change that to Microsoft Office Word Document and then hit the Create button. This will bring you to the doc library and you’ll see the default fields in the column headers. We’re going to need to modify these to show the Order Status, an Order Number, the Shipped Date and the email address of the Sales Rep that submitted the order.

Click on Settings then select Create column to create new columns for the orders in the document library.


For this example I added the following columns:

  1. Order Status 
    Choice (New, Processed, Shipped, On Hold, Canceled)
    Required = No
    Default = New
    Add to Default View
  2. Order Number
    Single line of text
    Required = No 
    Add to Default View
  3. Shipped Date
    Date Only (no Time)
    Required = No
    Add to Default View
  4. Sales Rep Email
    Single line of text
    Required = Yes 
    Add to Default View


After I added these columns I added a new default view to display them in the order I want. Click on the Settings again and select Create view.


The only thing the user is required to fill out when they upload a new purchase order document is the sales rep’s email address. (Recall that we added this field to the database when we built the data service). The Order Status and Shipped Date will actually be filled out automatically by the workflow depending on whether the order is new or has been updated by the Excel client (which we built in the last OBA post) that the shipping department uses to mark orders as shipped. Order Number will also be assigned by the workflow. We’ll use this to correlate the data with the purchase order document here in the list so we’ll need to add this field to the database.

Adding the Order Number to Northwind

So now we have the document library set up, we’re almost ready to write the workflow. First we’ll need to add the OrderNumber field to the Orders table in the Northwind database and then refresh our data service service so that this starts to show up as an available field on the Order.

USE Northwind

    OrderNumber nvarchar(25) NULL

In order to pick up this new field we need to refresh the Entity Data Model behind our data service. In the NorthwindDataService project open the NorthwindModel.edmx and then right-click on the design surface and select Refresh from database. This will pick up the OrderNumber field on the Order entity. Rebuild the data service.

Options on Hosting the Data Service for Workflow Development

If you’ve been following along and playing with the sample, you’ll notice that the NorthwindDataService project is currently set to be hosted in the ASP.NET development server right now so that testing the Excel and Outlook clients is easy. However at this point you may opt to deploy this to IIS instead so that you don’t have to remember to start it up manually every time you want to test the SharePoint Workflow. And of course for deployment to production you’ll need to host it in IIS anyway.

It gets slightly tricky if you want to host the data service in IIS on the same server as SharePoint because SharePoint takes over Port 80. So the easiest thing to do is to open IIS Manager and just create a new web site on Port 82 called NorthwindHost and then create a new application virtual directory called Northwind under that. By default, when you create a new web site it should also create a new Application Pool for you running under the identity NETWORK SERVICE. The identity you use here will need to have read and execute file permissions on the physical locations of any virtual directories you create as well as proper database permissions.

It’s XCOPY deployment at that point. Just copy the Web.Config, Northwind.svc and the \bin directory over to the physical location of the Northwind virtual directory and you should be good to go.  You can do this in Visual Studio (if running as an administrator) just right-click on the NorthwindDataService project in the solution explorer and then select publish. Then enter http://localhost:82/Northwind/ (or whatever you named it) and it will build and publish the files there automatically.

Note however, that before the WCF service will activate you have to install the WCF service handlers into IIS. These may not be installed automatically so in Windows 2008 check the Server Manager, scroll down to the Features Summary and select “Add Features”. Then expand the first node “.NET  3.0 Features” and check WCF Activation.

Remember if you change the location of the data service then you need to update the settings in the Excel and Outlook client (app.config file).

Creating the SharePoint Workflow

If you’re not doing this already, restart Visual Studio and Run as Administrator. This is necessary so that you can deploy and debug your workflow. Next add a new project to the solution, File –> Add –> New Project. For this example we’re just going to create a simple SharePoint 2007 Sequential Workflow. (Note: You will need Visual Studio 2008 Professional and higher to get these templates.)


As I mentioned before the workflow will kick off when a new purchase order is added to the document library in order to parse the data in the document. So when the order is first added to the list it will have a status of New. After it is parsed we will set the status to Processed, which happens right away. Later, after the shipping department enters the shipped date in the Excel client and updates the Orders.ShippedDate field in the database, we need the workflow to wake back up and set the order status to Shipped. For this example I’m going to build a delay into the workflow to check the database (via our data service) every so often. For testing it can be a couple minutes but once we deploy we can set it to every couple hours. Note that if you’re building more statuses or states into your own workflows you may want to take a look at the State Machine Workflow template instead.

So the next step is to specify where your SharePoint site and document library resides, for development this is going to be http://YourServerName. Once you specify the site you can then select the Orders document library in the Library dropdown. Leave “Automatically associate workflow?” checked so that Visual Studio can deploy the workflow to the site automatically for us.


On the next page check off how you want to start the workflow. For this example we’ll select “When an item is created”. You can also check “Manually by users” which can be helpful when testing but it’s not necessary for our example. Note that you can change these settings later on the Workflow project — from the Project menu select “SharePoint debug settings..”. You need to be running Visual Studio as an administrator to open these settings. You’ll need to do this if you’re going to run the Workflow in the sample code against your own SharePoint site. 

When the project is created it will automatically add a workflow called Workflow1 and open the designer which will display an event activity for onWorkflowActivated. An OnWorkflowActivated activity must be the first activity in a Windows SharePoint Services workflow so Visual Studio automatically sets one up for us. I renamed Workflow1 to ProcessOrder in the Solution Explorer (I like descriptive names ;-)) which means I’ll also need to update the values for the CorrelationToken.OwnerActivityName as well as the WorkflowProperties.Name to set them to ProcessOrder:


Now you can start laying out the design of the workflow. If you have used the WF designer before then this part should be familiar. Take a look at your toolbox and you’ll see the SharePoint specific activities listed under the Workflow 3.0 and 3.5 tabs. The design of the workflow for this example will be simple.


There are three main blocks to the workflow that we’re going to build:

  1. First is the parsing of the order data in the document which is a standard code activity that you find in the Windows Workflow v3.0 tab on the toolbox.
  2. The second block is a standard While loop that contains a delay activity. This piece will periodically query the database until the Order.ShipDate is updated in the database by the Excel client.
  3. The third block will be an IfElse activity which will check the Order status and either write an informational message to the History list in the case the order shipped successfully, otherwise we create a Task and assign it to the administrator for further investigation. The LogToHistoryListActivity and the CreateTask items can be found in the SharePoint Workflow tab on the toolbox.

Before we start writing the workflow code, let’s create some constants that we can use to refer to the data service URI, our order statuses, and list column names (I just added a new Module called Constants.vb):

Friend Module Constants
    Public Const OrderStatusColumn As String = "Order Status"
    Public Const OrderNumberColumn As String = "Order Number"
    Public Const ShippedDateColumn As String = "Shipped Date"
    Public Const SalesRepEmailColumn As String = "Sales Rep Email"

    Public Const NewStatus As String = "New"
    Public Const ShippedStatus As String = "Shipped"
    Public Const ProcessedStatus As String = "Processed"
    Public Const OnHoldStatus As String = "On Hold"
    Public Const CanceledStatus As String = "Canceled"

    Public Const ServiceURL As String = "http://localhost:82/NorthwindService/Northwind.svc"
End Module

Parsing the Order Document (CodeActivity parseOrder)

The Workflow kicks off immediately after a purchase order document is checked into the document library. The first code activity out of the gate is parsing the order document’s customXML for the order data we need. I already demonstrated a simple console application for extracting the order data from a Word document in Part 3. This code uses the Open XML SDK so you’ll need to download that and install it on your SharePoint server so that the assemblies are in the GAC. Then add an assembly reference to DocumentFormat.OpenXML.

From that console application project I copied the DocumentOderData.vb, Extensions.vb code files and the OrderEntry.xsd into the workflow project. Module1.vb had a method called ParseOrderDocument which parsed the docx file and populated a DocumentOrderData class with the information. I took that code and created a new class called Shredder and modified the interface a bit so that we could pass it a Microsoft.Sharepoint.SPFile class instead:

Imports DocumentFormat.OpenXml.Packaging
Imports <xmlns="urn:microsoft:examples:oba">

Public Class Shredder

    Public Function ParseOrderDocument(ByVal docFile As SPFile) As DocumentOrderData
        Using docStream = docFile.OpenBinaryStream
            Return ParseOrderDocument(WordprocessingDocument.Open(docStream, False))
        End Using
    End Function

    Public Function ParseOrderDocument(ByVal wordDoc As WordprocessingDocument) As DocumentOrderData

We also had a means for adding this data to the database via a method called AddNewOrder. The only change we need here is to create a unique OrderNumber string, add it to the Order entity, and return it to the workflow so we can also set it on the list item in SharePoint. You can decide how you want to generate unique order numbers, whether you want them to be GIUDs or timestamps or some other random number. I like using a combination of the CustomerID and the current date&time so that it’s human readable.

Dim orderNum = Replace(Replace(Replace(cust.CustomerID & Now.ToString(), "/", ""), ":", ""), " ", "")

This will work as long as there aren’t two orders for the same customer submitted within the same second so depending on your actual scenario you may opt for a different strategy :-). So this AddNewOrder code I placed into a class called OrderManager which also encapsulates all calls to the data service. Speaking of our data service, we’ll need to add a Service Reference to the workflow project called NorthwindService (right-click on the workflow project and select Add Service Reference) which will add the database entity types to our project.

Now drop a CodeActivity onto the Workflow designer and in the properties window name it parseOrder. Then right-click on it in the designer and select Generate Handlers to automatically generate the parseOrder_ExecuteCode handler. If the order data is extracted and added to the database successfully then an order number is assigned and the status is set to Processed. If the document does not contain the <OrderEntry> CustomXML then the status will be set to Canceled. If there is a problem adding the data to the database then the status is set to On Hold. (Notice that this code also uses the Application.Log to write messages to the Event Log which you can see how to set up here.)

Public Class ProcessOrder
    Inherits SequentialWorkflowActivity

    Public workflowProperties As New SPWorkflowActivationProperties
    Public Sub New()
    End Sub

    Private Sub parseOrder_ExecuteCode(ByVal sender As System.Object, _
                                       ByVal e As System.EventArgs)
        'This method is executed once the purchase order is uploaded 
        ' and checked into the document library 
            Dim orderNumber As String = ""
            Dim shredder As New Shredder

            'Valid OrderStatus: New, Processed, Shipped, Cancelled, On Hold
            ' (See Constants.vb for column and status strings)
            Dim status = workflowProperties.Item(OrderStatusColumn).ToString()

            If status = NewStatus Then
                'Get order data out of the document
                Dim docData = shredder.ParseOrderDocument(Me.workflowProperties.Item.File)

                If docData IsNot Nothing Then
                    Dim manager As New OrderManager
                    Dim employeeEmail = workflowProperties.Item(SalesRepEmailColumn).ToString()

                    'Add order data to the DB (through the service) and return the order number.
                    orderNumber = manager.AddNewOrder(docData, employeeEmail)

                    If orderNumber <> "" Then
                        status = ProcessedStatus
                        My.Application.Log.WriteEntry( _
                            String.Format("Order {0} added to database successfully.", _
                                          orderNumber), TraceEventType.Information)
                        status = OnHoldStatus
                        My.Application.Log.WriteEntry( _
                            String.Format("Order could not be added to database for {0}.", _
                                          Me.workflowProperties.Item.Name), TraceEventType.Error)
                    End If
                    status = CanceledStatus
                    My.Application.Log.WriteEntry( _
                        String.Format("Invalid purchase order submitted for {0}", _
                                      Me.workflowProperties.Item.Name), TraceEventType.Error)
                End If
            End If

            If status <> "" Then
                Me.workflowProperties.Item(OrderStatusColumn) = status
                If orderNumber.Length() > 0 Then
                    Me.workflowProperties.Item(OrderNumberColumn) = orderNumber
                End If
            End If

        Catch ex As Exception
        End Try

    End Sub

Now that the order is in the database, you can open the Excel client we wrote in part 4 and enter a ShippedDate and click save to update the order.

Querying the Database Periodically (WhileActivity waitForProcessed)

Next we need a WhileActivity so that we can check the database to see if the ShippedDate has been updated. Drag a standard While activity from the toolbox under the parseOrder and name it waitForProcessed in the properties window. Right-click on the designer and select View Code and write the following method:

    Private Sub orderProcessed(ByVal sender As System.Object, ByVal e As ConditionalEventArgs)
            Dim status = Me.workflowProperties.Item(OrderStatusColumn).ToString()
            Dim orderNumber = Me.workflowProperties.Item(Constants.OrderNumberColumn).ToString()

            If status <> ProcessedStatus Then
                'End while branch and go to next step in sequence if status is:
                '  Canceled = invalid purchase order (no <OrderEntry> CustomXML found)
                '  On Hold = Could not add data to database (see error log for details)
                e.Result = False
                'Get order from DB
                Dim manager As New OrderManager
                Dim processedOrder = manager.GetOrder(orderNumber)

                'If order was shipped (ShippedDate not NULL) then set Order Status to Shipped.
                If processedOrder IsNot Nothing Then
                    If processedOrder.ShippedDate IsNot Nothing AndAlso _
                       processedOrder.ShippedDate.HasValue Then
                        My.Application.Log.WriteEntry( _
                            String.Format("Order {0} has shipped as of {1}", _
                                          orderNumber, Now()), TraceEventType.Information)

                        Me.workflowProperties.Item(OrderStatusColumn) = ShippedStatus
                        Me.workflowProperties.Item(ShippedDateColumn) = processedOrder.ShippedDate
                        'end while branch and go to next step in sequence
                        e.Result = False
                        My.Application.Log.WriteEntry( _
                            String.Format("Order {0} has not shipped as of {1}", _
                                          orderNumber, Now()), TraceEventType.Warning)
                        'keep workflow running until shipped or canceled
                        e.Result = True
                    End If
                    My.Application.Log.WriteEntry( _
                        String.Format("Order {0} has been deleted from the database", _
                                      orderNumber, Now()), TraceEventType.Error)
Me.workflowProperties.Item(OrderStatusColumn) = CanceledStatus Me.workflowProperties.Item.Update() 'end while branch and go to next step in sequence e.Result = False End If End If Catch ex As Exception My.Application.Log.WriteException(ex) End Try End Sub

Notice in this code we’re referring to a method on the OrderManager called GetOrder which simply queries the Order entity from the database via the data service using LINQ:

Imports OBADemoWorkflow.NorthwindServiceReference

Public Class OrderManager
    Private ctx As New NorthwindEntities(New Uri(Constants.ServiceURL))
Public Function AddNewOrder(ByVal docData As DocumentOrderData, _
ByVal employeeEmail As String) As String... Public Function GetOrder(ByVal orderNumber As String) As Order Try Dim theOrder = (From o In ctx.Orders _ Where o.OrderNumber = orderNumber).FirstOrDefault() Return theOrder Catch ex As Exception My.Application.Log.WriteException(ex) Return Nothing End Try End Function End Class

SPOBA11 Now you can go back to the designer and set the Condition on the waitForProcessed WhileActivity to the orderProcessed method. Set the Condition to a Code Condition and the dropdown will contain only those methods that have the right signature, i.e. have a ConditionalEventArgs parameter. The orderProcessed method will set the e.Result = True only if the order is in the Processed state, is still in the database, and its ShippedDate is still NULL. This means that the While activity will continue to run.

Because the shipping department most likely will not ship the order out the door the second the order is entered into the database (<g>), we need a way for our workflow to delay and check periodically. Depending on your business you may want to check hourly or even daily. For testing you probably want to check every minute or two. This is where the DelayActivity comes in. Drag a standard Delay activity onto the designer inside the While Activity and set the Timeout Duration in the properties window to 3 minutes or so.

Before we go on I want to mention that there were known issues with the Delay activities not firing correctly in SharePoint that are addressed by this KB Article: http://support.microsoft.com/kb/953630/. The SharePoint timer service (OWSTIMER.exe) is set to only wake up every 5 minutes, so you probably want to set that to a shorter interval in your testing environment otherwise you’ll have to wait at least 5 minutes no matter how short you set your Delay activity. Another other issue I noticed was sometimes on rebuild & redeployment I needed to restart the timer service manually from an admin command prompt:

>Net Stop SPTimerV3


>Net Start SPTimerV3

Also there is an issue with debugging delays. I couldn’t just press F5 to debug the workflow after the delay. Up until that point I debugged just fine (which is where almost all our code is in this example) but if I wanted to see the rest of the code execute after the delay then I needed to attach to the OWSTIMER.exe process (while you’re debugging, on the main menu select Tools –> Attach to Process). This is also one of  the reasons why I enabled the Application.Log to send messages to the Event Log.

Wrapping Up the Workflow (History Logging and Creating Tasks)

The last piece of the workflow is simple, if the order is shipped we’ll just log a message to the workflow history list otherwise we’ll create a task for the administrator to investigate. Drop an IfElse activity under the While activity set and name it checkIfShipped. Then name the first branch ifShipped and the second branch elseNotShipped. In the ifShipped branch drop a LogToHistoryListActivity which is found in the SharePoint Workflow tab on the Toolbox. In the elseNotShipped drop a CreateTask activity. On the properties window for the CreateTask create a new field binding for both the TaskID and TaskProperties and enter a correlationToken with the parent activity set to ProcessOrder (the workflow).


Then right-click on both the CreateTask and LogToHistoryActivity and select Generate Handlers in order to generate the method stubs for each of them. We’ll also need a method called isOrderShipped with the same signature as the orderProcessed method above to be able to set it as the Code Condition of the ifShipped branch. We need to check the workflowProperties.Item for the order status and set the e.Result = True if the status is Shipped.

Private Sub isOrderShipped(ByVal sender As Object, ByVal e As ConditionalEventArgs)
    'Returns True if Order Status equals Shipped
    Dim status = Me.workflowProperties.Item(OrderStatusColumn).ToString()
    e.Result = (status = ShippedStatus)
End Sub

Private Sub createTask1_MethodInvoking(ByVal sender As System.Object, ByVal e As System.EventArgs)
        'This will execute if the workflow is ending but the Order Status 
        ' is not Shipped (i.e. Canceled or On Hold). 
        ' This code creates a workflow task item so the AssignedTo can investigate.
        Dim status = Me.workflowProperties.Item(OrderStatusColumn).ToString()
        Dim name = Me.workflowProperties.Item.Name

        createTask1_TaskId1 = Guid.NewGuid()
        createTask1_TaskProperties1.AssignedTo = "obavm09\wssdeveloper"
        createTask1_TaskProperties1.Title = String.Format("Problem with order {0}", name)
        createTask1_TaskProperties1.Description = String.Format("Order {0} as of {1}", status, Now)
    Catch ex As Exception
    End Try
End Sub

Private Sub logToHistoryListActivity1_MethodInvoking(ByVal sender As System.Object, _
ByVal e As System.EventArgs) logToHistoryListActivity1.HistoryOutcome = "Order has been shipped." End Sub

Testing the Workflow

In Visual Studio (still running as Administrator) Rebuild the solution. Then right-click on the OBADemoWorkflow project and select Deploy from the context menu. Now we can head over to the document library and upload some purchase orders. I used the one we created previously in Part 3 with ALFKI as the CustomerID. Go to the Orders document library, click Upload, Browse for the purchase order and click OK. Then fill out the required field Sales Rep Email (this is the Employee.EmailAddress field we added to the database in Part 1). Once you click “Check In” the workflow will kick off.


The Order status is first set to “New”….


But if you quickly refresh the list you will see that the Order Status and Order Number change as the order is processed and entered into the database.


Now the workflow is in its delay activity waiting for us to update the Order.ShippedDate. Open up the Excel client and enter something in the ShippedDate field and save the sheet (or manually modify the database). The next time the SharePoint timer runs it will waken our delay which will check the database again and set the status appropriately.


If you select the OBADemoWorkflow status (the last column in the view which is added automatically for us when we deploy) then you can see the status message in the Workflow History list. To see a task get created you can perform the same process but instead of updating the ShippedDate, delete the Order from the database (or delete the OrderNumber) and the workflow will set the status to Canceled and you will see a task in the list instead. And remember in testing we set the delay to a few minutes but in production Northwind Traders will be good with a 12 hour delay (gourmet foods don’t ship that quickly ;-)).

I’ve added this workflow to the sample code we’ve been building up on Code Gallery so have a look. I hope this real(er)-world, componentized sample has given you a good introduction into OBA and some of the awesome VSTO features of Visual Studio 2008.