Using SqlTrackingService

Article
03/05/2010

The SqlTrackingService class represents a fully functional tracking service. You can use this service to collect and store tracking information, and to store tracking profiles and provide them when requested by the workflow run-time engine. The SQL tracking service writes tracking data sent to it by the runtime tracking infrastructure to a SQL database.

In many scenarios it is important for the SqlTrackingService service to maintain consistency between its database and the state of the workflow run-time engine. Therefore, SqlTrackingService class implements the IPendingWork interface and can participate in the workflow transaction-batching functionality that is provided by the WorkflowCommitWorkBatchService service. You can set the IsTransactional property or pass the appropriate parameters to the constructor to configure the SqlTrackingService service to participate in batching and add objects that represent pending changes to the SQL database to the work batch.

The SqlTrackingService service also maintains tracking profiles in its database. The workflow run-time engine knows about the changes to any of the tracking profiles that it is using. The SqlTrackingService service implements the IProfileNotification interface and periodically examines the tracking profiles in its database to notify the workflow run-time engine of changes to a TrackingProfile that is stored in its database. You can set the ProfileChangeCheckInterval property or pass the appropriate parameter to the constructor to set the interval at which the SqlTrackingService service examines its tracking profiles.

Note

The SqlTrackingService does not store the host information in the tracking database. In a multi-host scenario, you cannot retrieve host information using this service. To do this, you must implement a custom tracking service that stores any pertinent host information such as the host GUID or host name.

Note

When using both the SqlTrackingService and the SqlWorkflowPersistenceService in a workflow application, it is recommended to use a single database for both persistence and tracking.

Creating the SQL Tracking Database

Windows Workflow Foundation setup does not install the databases that are required by the SqlTrackingService service; however, setup installs the SQL scripts for creating and configuring the databases for this service. This section details the steps that you must take to correctly configure the SQL Server database that is used by the SqlTrackingService service.

Note

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

To create the SQL tracking database

Using Microsoft SQL Server 2005 Express, SQL Server 2000 or later versions, or SQL Server 2000 Desktop Engine (MSDE), create a new database named WorkflowTrackingStore by using the following SQL query statement.
```
CREATE DATABASE WorkflowTrackingStore
```
When using both the SqlTrackingService and the SqlWorkflowPersistenceService in a workflow application, it is recommended to use a single database for both persistence and tracking.
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>\SqlTracking_Schema.
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>\SqlTracking_Logic.
Run the query by clicking Execute, or by pressing F5, to create the SQL Tracking Service stored procedures.


When using both the SqlTrackingService and the SqlWorkflowPersistenceService in a workflow application, it is recommended to use a single database for both persistence and tracking.

Adding SqlTrackingService to the Run-time Engine

You can add run-time services to the Windows Workflow Foundation run-time engine programmatically or by using an application configuration file.

To modify App.config for the SqlTrackingService

In the Services element in the App.config file, create a new element named add.
Add an attribute named type to the add element that has a value of System.Workflow.Runtime.Tracking.SqlTrackingService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35.
Add an attribute named connectionString to the add element that has a value of Initial Catalog=WorkflowTrackingStore;Data Source=localhost;Integrated Security=SSPI;

Note

You may have to modify the connection string, depending on the configuration of your SQL Server. The connection string shown here assumes that the database name is WorkflowTrackingStore and that SQL Server is installed on the same system that you are using for application development.
```
<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 add SqlTrackingService to the run-time engine programmatically

Call the AddService method that is defined in the WorkflowRuntime class, passing a new instance of SqlTrackingService.

The following example shows how to create a SqlTrackingService using the same configuration as the example shown in the previous procedure.

[C#]

using (WorkflowRuntime workflowRuntime = new WorkflowRuntime())
{
    // Create the SqlTrackingService.
    string connectionString ="Initial Catalog=WorkflowTrackingStore;Data Source=localhost;Integrated Security=SSPI;";
    SqlTrackingService trackService = new SqlTrackingService(connectionString);

    // Add the SqlTrckingService to the runtime engine
    workflowRuntime.AddService( trackService );

    // ...
}

Associating a TrackingProfile with Workflow Types

After you add the SqlTrackingService service to the run-time engine, it can be used immediately. If a tracking profile is not specified, the SqlTrackingService service uses a default profile if the UseDefaultProfile property is true. In this instance, tracking information is saved for every workflow and activity event when you use a default profile. However, to control the information that is tracked, you should define a TrackingProfile to use with the SqlTrackingService. If UseDefaultProfile property is false however, the SqlTrackingService does not use the default tracking profile if there is not one associated with the workflow type that is starting.

The SQL stored procedures that are provided by Windows Workflow Foundation include a procedure named UpdateTrackingProfile. This procedure allows you pass in the type name of the workflow to track, the assembly the workflow is defined in, a version string for the tracking profile version, and the tracking profile serialized to XML.

The following example demonstrates how to programmatically create a TrackingProfile object. In this example, the TrackingProfile object that is created can be used to track only CodeActivity activities that are defined in a workflow.

static void CreateTrackingProfile()
{
    TrackingProfile profile = new TrackingProfile();
    ActivityTrackPoint trackPoint = new ActivityTrackPoint();

    // Track CodeActivity activities only.
    ActivityTrackingLocation location = new ActivityTrackingLocation
        (typeof(System.Workflow.Activities.CodeActivity));

    // Add all activity tracking events.
    foreach (ActivityExecutionStatus s in 
        Enum.GetValues(typeof(ActivityExecutionStatus)))
    {
        location.ExecutionStatusEvents.Add(s);
    }

    trackPoint.MatchingLocations.Add(location);
    profile.ActivityTrackPoints.Add(trackPoint);
    profile.Version = new Version("1.0.0.0");

    WorkflowTrackPoint wtp = new WorkflowTrackPoint();
    WorkflowTrackingLocation wtl = new WorkflowTrackingLocation();

    // Add all workflow tracking events.
    foreach (TrackingWorkflowEvent s in 
        Enum.GetValues(typeof(TrackingWorkflowEvent)))
    {
        wtl.Events.Add(s);
    }

    wtp.MatchingLocation = wtl;
    profile.WorkflowTrackPoints.Add(wtp);

    // Serialize tracking profile and save to SQL.
    TrackingProfileSerializer serializer = new TrackingProfileSerializer();
    StringWriter writer = new StringWriter(new StringBuilder(), 
        CultureInfo.InvariantCulture);
    serializer.Serialize(writer, profile);
    InsertTrackingProfile(writer.ToString());
}

In the last line of this code example, a method named InsertTrackingProfile is called. This method, which is defined in the next example, uses the UpdateTrackingProfile stored procedure to update the tracking database with a new TrackingProfile.

static void InsertTrackingProfile(string profile)
{
    SqlCommand cmd = new SqlCommand();

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "dbo.UpdateTrackingProfile";
    cmd.Connection = new SqlConnection(Program.connectionString);
    try
    {
        cmd.Parameters.Clear();

        SqlParameter typFullName = new SqlParameter();
        typFullName.ParameterName = "@TypeFullName";
        typFullName.SqlDbType = SqlDbType.NVarChar;
        typFullName.SqlValue = typeof(SimpleWorkflow).ToString();
        cmd.Parameters.Add(typFullName);

        SqlParameter assemblyFullName = new SqlParameter();
        assemblyFullName.ParameterName = "@AssemblyFullName";
        assemblyFullName.SqlDbType = SqlDbType.NVarChar;
        assemblyFullName.SqlValue = typeof(SimpleWorkflow).Assembly.FullName;
        cmd.Parameters.Add(assemblyFullName);

        SqlParameter versionId = new SqlParameter();
        versionId.ParameterName = "@Version";
        versionId.SqlDbType = SqlDbType.VarChar;

        // The version ID must match the tracking profile version 
        // number.
        versionId.SqlValue = "1.0.0.0";

        cmd.Parameters.Add(versionId);

        SqlParameter trackingProfile = new SqlParameter();
        trackingProfile.ParameterName = "@TrackingProfileXml";
        trackingProfile.SqlDbType = SqlDbType.NVarChar;
        trackingProfile.SqlValue = profile;
        cmd.Parameters.Add(trackingProfile);

        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
    catch (SqlException e)
    {
        Console.WriteLine(e.Message);
        Console.WriteLine("The Tracking Profile was not inserted. " + 
            "if you want to add a new Tracking Profile, modify the version " + 
            "string in the profile by specifying a higher version number.");
        return;
    }
    finally
    {
        if ((null != cmd) && (null != cmd.Connection) && 
            (ConnectionState.Closed != cmd.Connection.State))
            cmd.Connection.Close();
    }
}

The process of defining a TrackingProfile that is shown here should be called before creating and starting the workflow type that is tracked. When your workflow starts, the SqlTrackingService detects that a TrackingProfile should be used and processes the tracking information accordingly.

Note

It is important to update the version number of a tracking profile if the workflow type associated with the tracking profile is changed, otherwise the tracking service cannot associate the tracking data with the previous workflow type. This is particularly important when reloading workflows that were persisted when a different tracking profile is in use. To update the tracking profile associated with a workflow, use ReloadTrackingProfiles.

For more information about how to extract tracking information from the tracking database, see Querying SqlTrackingService Data With SqlTrackingQuery. For more information about data maintenance features in Windows Workflow Foundation that are designed to facilitate organization of tracking data in the SqlTrackingService database, see Data Maintenance with SqlTrackingService.

Partager via

Using SqlTrackingService

Creating the SQL Tracking Database

To create the SQL tracking database

Adding SqlTrackingService to the Run-time Engine

To modify App.config for the SqlTrackingService

To add SqlTrackingService to the run-time engine programmatically

Associating a TrackingProfile with Workflow Types

See Also

Reference

Concepts

Other Resources

Ressources supplémentaires