Task 4: Use the Windows Workflow Tracking Service

Article
03/05/2010

In the previous task, you enabled the SqlWorkflowPersistenceService service to persist your workflow when it enters the idle state.

In this task, you use the SqlTrackingService service to track workflow instance and activity events that occur running your workflow. The SqlTrackingService service is added by using the AddService method that is defined in the WorkflowRuntime to use the SqlTrackingQuery class.

After your workflow finishes running, you can use the SqlTrackingQuery class to extract the tracking information from the tracking database. When you create an instance of the SqlTrackingQuery class, passing a connection string for the tracking database that you are using, you can enumerate each tracking record by accessing the ActivityEvents property of the SqlTrackingWorkflowInstance object. This contains a collection of ActivityTrackingRecord objects to view tracking information for the activities in your workflow. Additionally, you can use the same procedure to track workflow instance events by accessing the WorkflowEvents property that is defined in the SqlTrackingWorkflowInstance class.

Note

The SQL services installed by Windows Workflow Foundation use Microsoft SQL Server to store information. You may use Microsoft SQL Server 2005 Express, Microsoft SQL Server 2000 or later versions, or Microsoft SQL Server 2000 Desktop Engine (MSDE) for these tasks.

Note

The databases that are required by these services are not installed by Windows Workflow Foundation Setup; however, Windows Workflow Foundation Setup installs the SQL scripts for creating and configuring the databases for these services.

Note

Although you are encouraged to follow the exercises in a linear manner, it is not required. You can start this exercise by opening the sample project and proceeding to the steps in the following section. Additionally, for the project to operate correctly, you must create the persistence database used by the application. To do this, follow the procedure "Creating the SQL Persistence Database" in Task 3: Use the Windows Workflow Persistence Service.

To create the SQL tracking database

Using Microsoft SQL Server 2005 Express, Microsoft SQL Server 2000 or later versions, or Microsoft SQL Server 2000 Desktop Engine (MSDE), create a new database named WorkflowTrackingStore using the following SQL query statement.
```
CREATE DATABASE WorkflowTrackingStore
```
In the SQL Query Analyzer workspace, select the database that you created in step 1 from the list of available databases.
On the File menu, click Open, and then open the SQL script %WINDIR%\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\<language>\Tracking_Schema.sql.
Run the query by clicking Execute or by pressing F5 to create the SQL Tracking Service tables.
On the File menu, click Open, and then open the SQL script %WINDIR%\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\<language>\Tracking_Logic.sql.
Run the query by clicking Execute or by pressing F5 to create the SQL Tracking Service stored procedures.

To import the SQL namespaces

Add the following directives to the file that contains the Program class to import the types that you must have to use tracking and the SqlTrackingQuery class.
```
using System.Workflow.Runtime.Tracking;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
```

To modify App.config for the SqlTrackingService

In the Services element in the App.config file, create a new element named add.
In the add element, add an attribute named type that has a value of System.Workflow.Runtime.Tracking.SqlTrackingService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35.

In the add element, add an attribute named connectionString that has a value of Initial Catalog=WorkflowTrackingStore;Data Source=localhost;Integrated Security=SSPI;

<add type="System.Workflow.Runtime.Tracking.SqlTrackingService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionString="Initial Catalog=WorkflowTrackingStore;Data Source=localhost;Integrated Security=SSPI;"/>

To track workflow events

In the Program class of your project, create a new static field of type String named connectionString.

Give this field the value "Initial Catalog=WorkflowTrackingStore;Data Source=localhost;Integrated Security=SSPI;".
```
public static string connectionString =
    "Initial Catalog=WorkflowTrackingStore;" +
    "Data Source=localhost;Integrated Security=SSPI;";
```
In the Program class of your project, create a new static method named GetInstanceTrackingEvents, which accepts a Guid parameter named instanceId.
In the GetInstanceTrackingEvents method, create a new SqlTrackingQuery object, passing the connectionString variable as a parameter to the constructor.
Create a SqlTrackingWorkflowInstance variable named sqlTrackingWorkflowInstance and pass that object together with the instanceId parameter to the TryGetWorkflow method that is defined in the SqlTrackingQuery object. The TryGetWorkflow method returns a Boolean value that indicates success or failure. If the method returns false, output an error message and return from the method.

Enumerate through each WorkflowTrackingRecord that is contained in the WorkflowEvents collection defined in the sqlTrackingWorkflowInstance object.

Display the TrackingWorkflowEvent and EventDateTime properties using the WriteLine method. Your GetInstanceTrackingEvents should appear similar to the following:

static void GetInstanceTrackingEvents(Guid instanceId)
{
    SqlTrackingQuery sqlTrackingQuery = new SqlTrackingQuery(connectionString);

    SqlTrackingWorkflowInstance sqlTrackingWorkflowInstance;
    if (!sqlTrackingQuery.TryGetWorkflow(instanceId, out sqlTrackingWorkflowInstance))
    {
        Console.WriteLine("Could not retrieve SqlTrackingWorkflowInstance");
    }
    else
    {
        Console.WriteLine("\nInstance Level Events:\n");

        foreach (WorkflowTrackingRecord workflowTrackingRecord in
            sqlTrackingWorkflowInstance.WorkflowEvents)
        {
            Console.WriteLine("EventDescription : {0}  DateTime : {1}",
                workflowTrackingRecord.TrackingWorkflowEvent,
                workflowTrackingRecord.EventDateTime);
        }
    }
}

Private Shared Sub GetInstanceTrackingEvents(ByVal instanceId As Guid)
    Dim sqlTrackingQuery As SqlTrackingQuery = _
        New SqlTrackingQuery(connectionString)

    Dim sqlTrackingWorkflowInstance As SqlTrackingWorkflowInstance = Nothing
    If sqlTrackingQuery.TryGetWorkflow(instanceId, sqlTrackingWorkflowInstance) = False Then
        Console.WriteLine("Could not retrieve SqlTrackingWorkflowInstance")
    Else
        Console.WriteLine(Constants.vbLf & "Instance Level Events:" & Constants.vbLf)

        For Each workflowTrackingRecord As WorkflowTrackingRecord In _
            sqlTrackingWorkflowInstance.WorkflowEvents
            Console.WriteLine("EventDescription : {0}  DateTime : {1}", _
                workflowTrackingRecord.TrackingWorkflowEvent, _
                workflowTrackingRecord.EventDateTime)
        Next workflowTrackingRecord
    End If
End Sub

In the Main method of the Program class, after the StopRuntime method call, call the GetInstanceTrackingEvents method that you just created, passing the InstanceId property of the workflowInstance object as a parameter.
```
GetInstanceTrackingEvents(workflowInstance.InstanceId);
```

To track activity events

In the Program class of your project, create a new static method named GetActivityTrackingEvents that accepts a Guid parameter named instanceId.
In the GetActivityTrackingEvents method, create a new SqlTrackingQuery object, passing the connectionString variable as a parameter to the constructor.
Create a SqlTrackingWorkflowInstance variable named sqlTrackingWorkflowInstance, and pass that object together with the instanceId parameter to the TryGetWorkflow method that is defined in the SqlTrackingQuery object. The TryGetWorkflow method returns a Boolean value that indicates success or failure. If the method returns false, output an error message and return from the method.

Enumerate through each ActivityTrackingRecord that is contained in the ActivityEvents collection defined in the sqlTrackingWorkflowInstance object.

Display the ExecutionStatus, EventDateTime, and QualifiedName properties of the activityTrackingRecord object using the WriteLine method. Your GetActivityTrackingEvents method should appear similar to the following:

static void GetActivityTrackingEvents(Guid instanceId)
{
    SqlTrackingQuery sqlTrackingQuery = new SqlTrackingQuery(connectionString);

    SqlTrackingWorkflowInstance sqlTrackingWorkflowInstance;
    if (!sqlTrackingQuery.TryGetWorkflow(instanceId, out sqlTrackingWorkflowInstance))
    {
        Console.WriteLine("Could not retrieve SqlTrackingWorkflowInstance");
    }
    else
    {
        Console.WriteLine("\nActivity Tracking Events:\n");

        foreach (ActivityTrackingRecord activityTrackingRecord in
            sqlTrackingWorkflowInstance.ActivityEvents)
        {
            Console.WriteLine(
                "StatusDescription: {0}  DateTime: {1} Activity Qualified ID: {2}",
                activityTrackingRecord.ExecutionStatus,
                activityTrackingRecord.EventDateTime,
                activityTrackingRecord.QualifiedName);
        }
    }
}

Private Shared Sub GetActivityTrackingEvents(ByVal instanceId As Guid)

    Dim sqlTrackingQuery As SqlTrackingQuery = _
        New SqlTrackingQuery(connectionString)

    Dim sqlTrackingWorkflowInstance As SqlTrackingWorkflowInstance = Nothing
    If sqlTrackingQuery.TryGetWorkflow(instanceId, sqlTrackingWorkflowInstance) = False Then
        Console.WriteLine("Could not retrieve SqlTrackingWorkflowInstance")
    Else
        Console.WriteLine(Constants.vbLf & "Activity Tracking Events:" & _
            Constants.vbLf)

        For Each activityTrackingRecord As ActivityTrackingRecord In _
            sqlTrackingWorkflowInstance.ActivityEvents
            Console.WriteLine( _
                "StatusDescription: {0}  DateTime: {1} Activity Qualified ID: {2}", _
                activityTrackingRecord.ExecutionStatus, _
                activityTrackingRecord.EventDateTime, _
                activityTrackingRecord.QualifiedName)
        Next activityTrackingRecord
    End If
End Sub

In the Main method of the Program class, after the StopRuntime method call, call the GetActivityTrackingEvents method that you just created, passing the InstanceId property of the workflowInstance object as a parameter.
```
GetActivityTrackingEvents(workflowInstance.InstanceId);
```
Build your project, and run your application.

Your output will appear similar to the following figure.

Compiling the Code

For information about compiling your code, see Compiling the Code.

In Task 5: Create a Tracking Profile, you create a custom tracking profile that tracks all the workflow events and activity events for CodeActivity activities.

Task 4: Use the Windows Workflow Tracking Service

To create the SQL tracking database

To import the SQL namespaces

To modify App.config for the SqlTrackingService

To track workflow events

To track activity events

Compiling the Code

See Also

Reference

Concepts

Other Resources

Additional resources