SqlTrackingService in Windows Workflow Foundation
Talking about SqlWorkflowPersistenceService leads to talking about SqlTrackingService. SqlWorkflowPersistenceService helps you to store and retrieve the state of a workflow runtime into/from SQL database but SqlTrackingService helps you to store and retrieve all information about a workflow instance and its events into/from SQL database.
In this post I'll talk about SqlTrackingService which helps you to monitor your workflow changes and events.
Write a Simple Workflow
I start by writing a simple workflow which doesn't do anything more than waiting for five seconds and writing a text in Console.
Source code for codeActivity1 is this:
private void codeActivity1_ExecuteCode(object sender, EventArgs e)
{
Console.WriteLine("Hello Gholi!");
}
Later I'll use this workflow to track its changes and events.
Configure SQL Server Database
Like SqlWorkflowPersistenceService, you need to run some SQL scripts on your database to be able to get all benefits of SqlTrackingService. These script files are located at C:\WINDOWS\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\EN and are named Tracking_Schema.sql and Tracking_Logic.sql. First file creates the structure of database and adds roles and second file adds Stored Procedures.
Now my database is ready to be used to store workflow tracking data.
Write a WinFX Windows Application
Now I write a very simple WinFX application which doesn't contain anything more than a Button. User clicks on Button (named btnOk) and my code creates a workflow runtime and a SqlTrackingService object by passing database connection string to its constructor. Then it creates an instance of my workflow and calls its events such as Start and Terminate. Finally workflow runtime stops. At this point all events should be stored into database.
void btnOk_Click(object sender, RoutedEventArgs args)
{
SqlTrackingService trackingService = new SqlTrackingService
("Data Source=KEYVANNAYYERI;Initial Catalog=SqlTrackingServiceSample;Integrated Security=True");
WorkflowRuntime workflowRuntime = new WorkflowRuntime();
workflowRuntime.AddService(trackingService);
workflowRuntime.StartRuntime();
WorkflowInstance instance =
workflowRuntime.CreateWorkflow(typeof(MyWorkflow.Workflow1));
instance.Load();
instance.Start();
instance.Terminate("Terminate Workflow for Example!");
workflowRuntime.StopRuntime();
MessageBox.Show("Finished!", "SqlTrackingService Sample");
}
Retrieve Workflow Instance Events
Now that I have all data for my workflow instance that are stored into database, can retrieve them from SQL database. There are some great Stored Procedures created by default SQL scripts but none of them can help me in this scenario so I create two Stored Procedures. One of them, SelectWorkflows, selects all instances of workflows from database with their InstanceId and InstanceInternalId.
CREATE PROCEDURE dbo.SelectWorkflows
AS
SELECT Type.TypeFullName, WorkflowInstance.WorkflowInstanceId, WorkflowInstance.WorkflowInstanceInternalId
FROM Type INNER JOIN
WorkflowInstance ON Type.TypeId = WorkflowInstance.WorkflowTypeId
Second Stored Procedure, SelectWorkflowInstanceWithTracking, gets the integer value of workflow's InstanceInternalId and selects all events that are happened for that workflow with the data and time that they're happened.
CREATE PROCEDURE dbo.SelectWorkflowInstanceWithTracking
@InstanceID int
AS
SELECT TrackingWorkflowEvent.Description, WorkflowInstanceEvent.EventDateTime
FROM WorkflowInstanceEvent INNER JOIN
TrackingWorkflowEvent ON WorkflowInstanceEvent.TrackingWorkflowEventId = TrackingWorkflowEvent.TrackingWorkflowEventId AND
WorkflowInstanceEvent.TrackingWorkflowEventId = TrackingWorkflowEvent.TrackingWorkflowEventId
WHERE (WorkflowInstanceEvent.WorkflowInstanceInternalId = @InstanceID)
Now I write a Windows application to show all events for workflow instances. This Windows form has two DataGridView controls. Upper one (dataGridView1) shows all workflow instances and is bound to data that are retrieved from SelectWorkflows Stored Procedures. Lower DataGridView (dataGridView2) shows the list of events for the selected workflow instance from dataGridView1 and gets its data from SelectWorkflowInstanceWithTracking Stored Procedure.
private void Form1_Load(object sender, EventArgs e)
{
SqlConnection connection =
new SqlConnection(this.connectionString);
SqlCommand selectWorkflows =
new SqlCommand("SelectWorkflows", connection);
selectWorkflows.CommandType =
CommandType.StoredProcedure;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectWorkflows;
adapter.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
dataGridView1.Refresh();
dataGridView1.SelectionChanged +=
new EventHandler(dataGridView1_SelectionChanged);
}
void dataGridView1_SelectionChanged(object sender, EventArgs e)
{
SqlConnection connection =
new SqlConnection(this.connectionString);
SqlCommand selectWorkflows =
new SqlCommand("SelectWorkflowInstanceWithTracking", connection);
selectWorkflows.CommandType =
CommandType.StoredProcedure;
selectWorkflows.Parameters.AddWithValue
("InstanceID", dataGridView1.SelectedRows[0].Cells[2].Value);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectWorkflows;
adapter.Fill(ds);
dataGridView2.DataSource = ds.Tables[0].DefaultView;
dataGridView2.Refresh();
}
And output:
You can configure your application via its configuration files to use SqlTrackingService. Probably I'll talk about Tracking Profiles later.
Full source code for this post is attached to it.
[advertisement] Axosoft OnTime 2008 is four developer tools in one: bug tracking, project wiki, feature management, and help desk. It manages your development process so developers can focus on coding. Installed or Hosted – Free Single-user license -- Free 30-day team trial.
4 Comments : 10.15.06
#1
Keyvan Nayyeri
10.21.2006 @ 1:09 AM