Recently the workflow team at Microsoft released a first CTP to the ADO.NET Activity Pack. In this blog post I am going to take a quick look at how to get started with these activities.
First step is installing the activity pack. Download the setup MSI from http://wf.codeplex.com/releases/view/43585 and run the installer. This will install the activities in the folder C:\Program Files (x86)\Microsoft WF ADO.NET Activity Pack\CTP 1 and register them with VS2010 so they automatically appear in the toolbox. Note that the main assembly containing the activities is called Microsoft.Data.Activities.dll.
If we start VS2010 and create a new workflow project the 3 ADO.NET activities are right there in the toolbox.
All three activities operate in a similar manner so I am going to use the ExecuteSqlQuery<T> to demonstrate how to use these activities. The sample is simple, all I am going to do is load a set of customers from the Northwind database and display these on the screen.
First step is to define a customer type the workflow can work with. This is as simple as they come:
public class Customer
{
public string CustomerId { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
}
Next you can drag a ExecuteSqlQuery<T> onto the design surface. I put them inside of a Sequence so I can print the result after the ExecuteSqlQuery<T> is finished. When prompted for the type select Brose for Types and select the Customer from the current project.
Now the activity is on the design surface but as can be seen from the Error List both the connection and the command need to be specified.
First the connection because that is a bit misleading, at least for me the first time I tried.
Clicking the connection button in the property sheet allows you to use an existing database connection or create a new one. Nice but that does mean your connection string will be part of the workflow, not so good, and is a literal instead of an expression so not easy to change. The proper way to do this is to add a connection string setting to the project first. And once that is done this connection will also appear in the Configure Database Connection dialog for the ExecuteSqlQuery<T>,
Next step is to specify the command to execute.
Here we are doing a simple select from the customers table and filtering the results to only include the UK based companies. All we have is a simple textbox to enter a SQL command, table or stored procedure name. Quite primitive [:(]. Keep in mind that the select * is quite a bad idea as well and you are far better of specifying the field names as we will see in a bit.
Next we need to map the data loaded to our customer type. For this purpose we get a record variable that points to a SqlDataReader and we can use the record.GetString(index) and similar functions to load the data. This requires passing an index, the reason doing a SELECT * is not a smart mover
When this is done all we need to do is capture the Result property, which contains a List<Customer> and loop over this printing the content.
The complete workflow in XAML:
<Activity mc:Ignorable="sap" x:Class="WorkflowConsoleApplication7.Workflow1" sap:VirtualizedContainerService.HintSize="356,1058" mva:VisualBasic.Settings="Assembly references and imported namespaces for internal implementation" xmlns="http://schemas.microsoft.com/netfx/2009/xaml/activities" xmlns:local="clr-namespace:WorkflowConsoleApplication7" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mda="clr-namespace:Microsoft.Data.Activities;assembly=Microsoft.Data.Activities" xmlns:mv="clr-namespace:Microsoft.VisualBasic;assembly=System" xmlns:mva="clr-namespace:Microsoft.VisualBasic.Activities;assembly=System.Activities" xmlns:s="clr-namespace:System;assembly=mscorlib" xmlns:s1="clr-namespace:System;assembly=System" xmlns:s2="clr-namespace:System;assembly=System.Xml" xmlns:s3="clr-namespace:System;assembly=System.Core" xmlns:s4="clr-namespace:System;assembly=System.ServiceModel" xmlns:sa="clr-namespace:System.Activities;assembly=System.Activities" xmlns:sad="clr-namespace:System.Activities.Debugger;assembly=System.Activities" xmlns:sap="http://schemas.microsoft.com/netfx/2009/xaml/activities/presentation" xmlns:scg="clr-namespace:System.Collections.Generic;assembly=System" xmlns:scg1="clr-namespace:System.Collections.Generic;assembly=System.ServiceModel" xmlns:scg2="clr-namespace:System.Collections.Generic;assembly=System.Core" xmlns:scg3="clr-namespace:System.Collections.Generic;assembly=mscorlib" xmlns:sd="clr-namespace:System.Data;assembly=System.Data" xmlns:sl="clr-namespace:System.Linq;assembly=System.Core" xmlns:st="clr-namespace:System.Text;assembly=mscorlib" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
<Sequence sad:XamlDebuggerXmlReader.FileName="c:\users\maurice\documents\visual studio 2010\Projects\WorkflowConsoleApplication7\WorkflowConsoleApplication7\Workflow1.xaml" sap:VirtualizedContainerService.HintSize="316,1018">
<Sequence.Variables>
<Variable x:TypeArguments="scg3:List(local:Customer)" Name="Customers" />
</Sequence.Variables>
<sap:WorkflowViewStateService.ViewState>
<scg3:Dictionary x:TypeArguments="x:String, x:Object">
<x:Boolean x:Key="IsExpanded">True</x:Boolean>
</scg3:Dictionary>
</sap:WorkflowViewStateService.ViewState>
<mda:ExecuteSqlQuery x:TypeArguments="local:Customer" ProviderName="{x:Null}" CommandText="Select * from Customers where Country = @Country" ConnectionConfigurationName="WorkflowConsoleApplication7.Properties.Settings.NorthwindConnection" sap:VirtualizedContainerService.HintSize="294,647" Result="[Customers]">
<mda:ExecuteSqlQuery.RecordProcessor>
<ActivityFunc x:TypeArguments="sd:IDataRecord, local:Customer">
<ActivityFunc.Argument>
<DelegateInArgument x:TypeArguments="sd:IDataRecord" Name="record" />
</ActivityFunc.Argument>
<ActivityFunc.Result>
<DelegateOutArgument x:TypeArguments="local:Customer" Name="target" />
</ActivityFunc.Result>
<Sequence DisplayName="Map Customer Fields" sap:VirtualizedContainerService.HintSize="264,476">
<sap:WorkflowViewStateService.ViewState>
<scg3:Dictionary x:TypeArguments="x:String, x:Object">
<x:Boolean x:Key="IsExpanded">True</x:Boolean>
<x:Boolean x:Key="IsPinned">False</x:Boolean>
</scg3:Dictionary>
</sap:WorkflowViewStateService.ViewState>
<Assign sap:VirtualizedContainerService.HintSize="242,58">
<Assign.To>
<OutArgument x:TypeArguments="local:Customer">[target]</OutArgument>
</Assign.To>
<Assign.Value>
<InArgument x:TypeArguments="local:Customer">[New Customer]</InArgument>
</Assign.Value>
</Assign>
<Assign sap:VirtualizedContainerService.HintSize="242,58">
<Assign.To>
<OutArgument x:TypeArguments="x:String">[target.CustomerId]</OutArgument>
</Assign.To>
<Assign.Value>
<InArgument x:TypeArguments="x:String">[record.GetString(0)]</InArgument>
</Assign.Value>
</Assign>
<Assign sap:VirtualizedContainerService.HintSize="242,58">
<Assign.To>
<OutArgument x:TypeArguments="x:String">[target.CompanyName]</OutArgument>
</Assign.To>
<Assign.Value>
<InArgument x:TypeArguments="x:String">[record.GetString(1)]</InArgument>
</Assign.Value>
</Assign>
<Assign sap:VirtualizedContainerService.HintSize="242,58">
<Assign.To>
<OutArgument x:TypeArguments="x:String">[target.ContactName]</OutArgument>
</Assign.To>
<Assign.Value>
<InArgument x:TypeArguments="x:String">[record.GetString(2)]</InArgument>
</Assign.Value>
</Assign>
</Sequence>
</ActivityFunc>
</mda:ExecuteSqlQuery.RecordProcessor>
<InArgument x:TypeArguments="x:String" x:Key="Country">UK</InArgument>
</mda:ExecuteSqlQuery>
<ForEach x:TypeArguments="local:Customer" DisplayName="ForEach<Customer>" sap:VirtualizedContainerService.HintSize="294,207" Values="[Customers]">
<ActivityAction x:TypeArguments="local:Customer">
<ActivityAction.Argument>
<DelegateInArgument x:TypeArguments="local:Customer" Name="item" />
</ActivityAction.Argument>
<WriteLine sap:VirtualizedContainerService.HintSize="257,100" Text="[item.CustomerId & " " & item.CompanyName & " " & item.ContactName]" />
</ActivityAction>
</ForEach>
</Sequence>
</Activity>
and in design mode:
Conclusion
These activities work well enough but result in using plain old ADO.NET. Now there is nothing wrong with plain ADO.NET but with the current emphasis on Object Relational Mappers like the Entity Framework this seems a bit out of place. And the option to use connection strings from the application settings instead of embedding them in the workflow isn’t all that obvious either. Still a nice set of activities to add to the toolbox.
Enjoy!
www.TheProblemSolver.nl
Wiki.WindowsWorkflowFoundation.eu