Using SqlTrackingService

Article
01/09/2007

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 runtime 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 runtime 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 runtime engine must be kept apprised of 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 in order to notify the workflow runtime 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 will not be able to retrieve host information using this service. In order to do this, you will have to implement a custom tracking service that stores any pertinent host information such as the host guid or host name.

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 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>\SqlTracking_Schema.
Run the query by clicking Execute, or by pressing F5, to create the SQL Persistence 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.

Adding SqlTrackingService to the Runtime Engine

You can add runtime services to the Windows Workflow Foundation runtime 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;"/>

Adding SqlTrackingService to the runtime 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.

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 runtime 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 will not use the default tracking profile if there isn't 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 lets 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.

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.

Share via

Using SqlTrackingService

Creating the SQL Tracking Database

To create the SQL tracking database

Adding SqlTrackingService to the Runtime Engine

To modify app.config for the SqlTrackingService

Adding SqlTrackingService to the runtime engine programmatically

Associating a TrackingProfile with Workflow Types

See Also

Reference

Concepts

Other Resources

Additional resources