Enable easy database access and reintroduce support for modeling workflows with state machines
A frequent question I’m asked from veterans of building workflows and workflow services with .NET 3.5 looking to migrate to .NET 4.0 is: "What happened to my state machine?" The RTM version of .NET 4.0 does not include a state machine runtime or designer, and while you can certainly build one, this is not for the faint of heart. Another feature notably absent from the framework’s activity library is activities to help you with performing CRUD operations against a relational database. Until now, your only recourse was to build your own custom activities. Microsoft has heard these developer requests and is providing activity libraries that introduce a state machine model and ADO.NET activities. These are released for evaluation in a pilot approach to provide new activity libraries to Workflow Foundation (WF) developers out of band with releases of the .NET Framework. These are called Activity Packs and you can get them from CodePlex (see the Additional Resource links at the end of this article for the website URLs). If they are successful, you may see these Activity Packs rolled into the next version of .NET.
In this article we will introduce the WF ADO.NET Activity Pack, which provides three new activities that let you perform CRUD operations against any database you can access with ADO.NET Data Providers. We will also introduce the much desired WF State Machine Activity Pack that provides a state machine activity, and the designer to support modeling workflows as state machines.
WF ADO.NET Activity Pack
Most workflows, whether they are self-hosted or hosted in Windows Server AppFabric, need to be able to access and manipulate data stored in relational databases. The WF ADO.NET Activity Pack, currently in CTP 1, provides three activities that mimic the code based on the approach to database access using ADO.NET. This is done by using DbCommands with ExecuteDataReader() and ExecuteNonQuery() to execute either inline SQL statements or stored procedures.
The three activities included with this Activity Pack are:
- ExecuteSqlQuery lets your workflows query a database using an SQL statement or a stored procedure, and then process each row returned via a DataReader within the activity body (which acts as foreach). You place whatever activities you need within the activity to process an individual row. The DataReader is closed when the activity completes, so you have no access to the row values unless you explicitly wrote their values out to workflow variables within the activity body. You would typically use this activity to query for a result set using either a SELECT statement or a recordset returning stored procedure.
- ExecuteSqlQuery<T> offers a syntactic sugar coating on top of the behavior of ExecuteSqlQuery. Here the foreach within the activity body is used to map the column values of each row to an instance of an object of an entity type T. When ExecuteSqlQuery<T> finishes, its Result property contains a list of initialized instances of type T. You would also use this activity to query for a result set using either a SELECT statement or a recordset returning stored procedure.
- ExecuteSqlNonQuery lets your workflow execute either an SQL statement or stored procedure, but it is not concerned with processing any resulting record sets. You would typically use this activity to perform INSERTS, UPDATES, DELETES, or execute a stored procedure that is not recordset returning.
We’ll dive into the details of using each for CRUD operations next.
Let’s start by taking a look at how we can query data using simple SELECT statements or stored procedures. Let’s say we are only interested in getting the top five records with the greatest quantity in inventory from the AdventureWorks database:
SELECT Top 5 ProductId, Quantity
ORDER BY Quantity DESC
To execute this SELECT, we can use either an ExecuteSqlQuery or ExecuteSqlQuery<T> that we drag from the toolbox. For simplicity, assume we have already defined a class called InventoryItem that has two integer properties: ItemId and Quantity. First, we need to configure a connection string. We do this by clicking the Configure… button on the activity’s design surface. The dialog that appears lets us specify a connection string whose value is stored within the workflow XAML. With the connection string in place, clicking the Edit… button lets us define the SQL Statement or stored procedure to call (see Figure 1). If we select the Stored Procedure radio button, we can use the drop-down list to pick from the available stored procedures at the database our connection string points to.
With our command in place, we now need to turn our attention to processing the rows returned by the DataReader. This is accomplished by adding your field (column value) processing activities to the body of the activity. Figure 2 shows how we map the ProductId and Quantity for each row returned in the result set of the query to an instance of InventoryItem. The left-hand side shows using ExecuteSqlQuery<T>, and the right shows the more verbose ExecuteSqlQuery. Notice the two differences: With ExecuteSqlQuery<T>, we do not have to initialize the collection (as done with the Assign labeled Init Collection) that will store the results of our processing when the activity completes. (Remember the DataReader is closed at this point, and any attempts to access records from it will result in an error.) Also, we do not need to actually add the instance to the collection (as performed with the Add To Collection activity). In this example we map the columns by ordinal with the calls to record.GetInt16(xx) and record.GetInt32(xx), but we can also map using the column name by nesting a call to record.GetOrdinal(columnName). At the end of either sequence we will have an initialized List<InventoryItem> usable by the rest of the workflow.
Most queries aren’t quite so simple—they might need to pass in values to input parameters to the queries or stored procedures, or retrieve values after execution as output parameters. Let’s return to AdventureWorks, and say we wanted to query the inventory quantity of a particular item. Our query, defined in the Edit Command window, would look as Figure 3 shows. We have to parameterize our query using the token syntax of the particular ADO.NET provider. For SQL Server, we use the System.Data.SqlClient provider, and it defines parameters by prefixing them with @ signs. Now to actually pass in the product ID from the workflow to the query, we click the Parameters button and add a row for each parameter. The Name column must match the name used in the SQL Query. The Value column is where we provide an expression to acquire the value from a workflow variable (in this a variable called lineItem). The Direction can specify In, Out, or In/Out, and the Type allows you to use the familiar Browse for Types… dialog to pick the type of the parameter. With some ADO.NET providers, the order of the parameters determines the mapping. The parameters dialog allows you to control the ordering of parameters by using the up or down arrows in the top right of the dialog.
Inserting, updating, and deleting records is performed similarly, though typically you will use an ExecuteSqlNonQuery activity since the operations tend to not return result sets. Figure 4 shows how we configure the command of an ExecuteSqlNonQuery to perform a parameterized update of the AdventureWorks database.
With the basic CRUD operations under your belt, the last thing to observe is that you can use the Data activities under a single transaction by placing them within a TransactionScope. When the TransactionScope completes, the transaction is committed. If an error occurs, the effects of each individual ExecuteSqlQuery or ExecuteSqlNonQuery within the TransactionScope is rolled back.
WF State Machine Activity Pack
State machines provide a way to describe how a set of inputs (data or events) can be used to determine the next state of the program. The State Machine activity lets you describe states (e.g., Create and Unbooked boxes in Figure 5) as well as the transitions between those states (e.g., the lines labeled Create Order and Update in the figure).
Conceptually, designing a state machine is completely the opposite of what you are used to with the Sequence and FlowChart models. In those models, the logic is described in the activities themselves, with the lines connecting them representing the primary execution flow. With state machines, however, the lines are transitions that tend to have most of the logic. The states themselves are just glorified labels and tend to have minimal logic.
A transition has a trigger (as Figure 6 shows), which is always the completion of some child activity. This child activity could be a Receive, a Delay, or a complex sequence of activities (in the figure, it’s the completion of the Ship Complete Sequence). When the child activity completes the trigger, it is said to have fired. When this happens, the transition next evaluates its Condition property (the expression UnbookedLineItems.Count = 0 in the figure) to see if it should actually move to the next state. If the Condition expression evaluates to true, the child activity defined for the action is executed and the state machine advances to the next state.
Transitions have some interesting aspects that make them convenient for modeling workflows. The trigger of a transition can be shared among multiple transitions and are indicated by multiple transitions leaving the same node on the state. When it fires, only the transition whose condition evaluates to true is actually followed to the next state. For example in Figure 5, the In Process state has the transitions Ship Complete and Close Short that both share the trigger Ship Complete Sequence shown in Figure 6. However, the Close Short transition has a Condition expression of UnbookedLineItems.Count > 0, which ensures that only one of the transitions is followed. This is important because a state machine can only be in a single state at a given point in time.
Figure 5 also shows that transitions can be described that causes a state to transition back to itself (the transition is labeled Update).
States are not without value, however. You may have noticed the green Initial or red Final states. There is exactly one initial state that is provided for you when you drag in a state machine activity. The first state that follows it (in the case of Figure 5, this is the Create state) is executed immediately and will effectively wait on the triggers of the transition leaving it. This is subtle, but if you have an event that will start your workflow, it actually needs to go in one of the transitions following the first state as no triggers can be defined for the transition moving from the initial state to the first state. In Figure 5, for example, we have a Receive activity (this is not shown in the figure) as the trigger for the Create Order transition. This Receive is configured with CanCreateInstance as true and thus will launch a new instance of the OrderingProcess when invoked, and subsequently transition to the Unbooked state.
If you look closely at the Cancelled, Complete, or Closed Short states in Figure 5, you’ll notice a small circle with a green, right pointing arrow. States can describe entry and exit logic (shown as left facing, red arrow) for when the state machine transitions into a state and when it transitions away from a state. Figure 7 shows how we defined the entry logic for the Cancelled stated that uses an ExecuteSqlNonQuery activity to cancel the order in the database.
The activity packs provide valuable and functional implementation that’s ready for you to use now. I highly suggest you try them out; they will likely make your workflow modeling experience much more productive.
- Windows Workflow Foundation on CodePlex
- WF State Machine Activity Pack CTP
- WF ADO.NET Activity Pack CTP 1
- WF Migration Kit