Querying SqlTrackingService Data With SqlTrackingQuery

The SqlTrackingQuery class provides methods and properties that you can use to access certain kinds of tracking data that is stored in the SqlTrackingService database. Access to this data is provided through SqlTrackingWorkflowInstance objects. You can call the TryGetWorkflow method to try to get a SqlTrackingWorkflowInstance for a specific workflow instance. You can also optionally call the GetWorkflows method to get a collection of SqlTrackingWorkflowInstance objects that correspond to workflow instances that have tracking data that matches a set of query parameters that are contained in a SqlTrackingQueryOptions object that is passed as a parameter to the method. For more information about the available tracking data, see the SqlTrackingWorkflowInstance class. For more information about the kind of queries you can perform, see the SqlTrackingQueryOptions class.

Using SqlTrackingQuery

You create an instance of the SqlTrackingQuery class by passing a connection string for the tracking database. You can then call either the TryGetWorkflow or the GetWorkflows method to retrieve either a SqlTrackingWorkflowInstance object or a collection of SqlTrackingWorkflowInstance objects. The ActivityEvents property contains a collection of ActivityTrackingRecord objects that contain tracking information for the activities in the workflow. Additionally, you can use the same procedure to track workflow instance events by examining the WorkflowTrackingRecord objects that are contained in the WorkflowEvents collection. For user events that are inserted by calling the TrackData method, UserTrackingRecord objects are contained in the UserEvents collection, which is also defined in the SqlTrackingWorkflowInstance class.

Accessing the Tracking Database

The following example demonstrates how to access the SqlTrackingService tracking database using a SqlTrackingQuery object. The example retrieves a SqlTrackingWorkflowInstance by calling the TryGetWorkflow method and passing an instance ID of a workflow that it is interested in. After this is finished, the example enumerates the WorkflowEvents collection and outputs information that is contained in each WorkflowTrackingRecord.

Note

To use the SqlTrackingQuery class, you must make sure that it can discover the workflow types that it is querying for. If the types are in the global assembly cache (GAC) or in the same directory as the application, the types automatically resolve. If they are not, you must provide a custom Type resolver and handle the loading of the types to ensure correct operation.

Note

If you try to use the SqlTrackingQuery class and the SQL Server that it connects to is down or temporarily unavailable, the SqlTrackingQuery class throws a SqlException and does not try to reconnect to the database. You can catch this exception to implement your own retry logic.

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

    SqlTrackingWorkflowInstance sqlTrackingWorkflowInstance;
    sqlTrackingQuery.TryGetWorkflow(instanceId, out sqlTrackingWorkflowInstance);

    Console.WriteLine("\nInstance Level Events:\n");

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

This next example uses the same methodology to extract activity-level events from a SqlTrackingWorkflowInstance object. However, instead of enumerating the WorkflowEvents collection, it uses the ActivityEvents collection instead.

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

    SqlTrackingWorkflowInstance sqlTrackingWorkflowInstance;
    sqlTrackingQuery.TryGetWorkflow(instanceId, out sqlTrackingWorkflowInstance);

    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);
    }
}

See Also

Reference

SqlTrackingQuery
SqlTrackingService
SqlTrackingQueryOptions
SqlTrackingWorkflowInstance

Concepts

Windows Workflow Tracking Services
Using SqlTrackingService

Other Resources

Simple Tracking Sample
Query Using SQLTrackingService Sample