Beth's Chinese blog
It’s extremely common to have to hook up lookup tables on your data entry forms in order to populate foreign keys in a database. I’ve talked about how to do this in Winforms and WPF with Datasets and LINQ to SQL before:
The common theme between all of these is that the data sources, either the LINQ to SQL classes or the DataTables that we bind to, uses a navigation path based on the foreign key and that foreign key is exposed as a property (or DataColumn). For instance if we have a Customer related to Orders we would have a CustomerID property on Orders.
DataSets (like databases) rely on this type of navigation. So when you want to find the parent Customer of an Order you have to know the relation. Using typed datasets helps you more but you still end up having to know the details of relationships and foreign keys of the DataSet. That’s why people who are familiar with databases are usually comfortable with working with DataSets.
LINQ to SQL classes also include navigation properties as direct object references and collections. So they have both the foreign key and the navigation properties. Customer will have a collection of Orders and Order will have a reference back to Customer but the classes also contain the CustomerID property. This isn’t “pure” I suppose but it does make data binding and subsequent saves back to the database pretty much a no brainer.
For instance say I want to hook up a lookup combobox using a LINQ to SQL class on an Order with a reference to Customer. I want to display a list of Customers the user can pick from and that Customer should be associated with that Order. It’s pretty straight forward and works the same with DataTables.
XAML:
<Window.Resources> <CollectionViewSource x:Key="OrdersSource" /> <CollectionViewSource x:Key="CustomerLookup" /> </Window.Resources>
<Grid Grid.Row="1" Name="Grid1" DataContext="{Binding Source={StaticResource OrdersSource}}"><ComboBox Height="23" Name="ComboBox1" Width="177" Margin="2" HorizontalAlignment="Left" IsEditable="False" ItemsSource="{Binding Source={StaticResource CustomerLookup}}" DisplayMemberPath="LastName" SelectedValuePath="CustomerID" SelectedValue="{Binding Path=CustomerID}" />...
Code-behind:
Class Window1 Private db As New MyDataObjectContext Private OrderData As IEnumerable(Of Order) Private Sub Window1_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded 'Load all the orders from the database Me.OrderData = db.Orders 'Get the customer lookup list (this is the Combobox ItemsSource) Dim customerList = From c In db.Customers _ Where c.Orders.Count > 0 _ Order By c.LastName, c.FirstName Dim ordersSource = CType(Me.FindResource("OrdersSource"), CollectionViewSource) ordersSource.Source = Me.OrderData Dim custSource = CType(Me.FindResource("CustomerLookup"), CollectionViewSource) custSource.Source = customerList.ToList() End Sub...
Almost all of this code is just to set up the context of this discussion, you can watch this video for details on building a complete example. Here I’m using CollectionViewSources in the XAML and setting their Source property in code. This technique is handy especially if you are using nested DataTemplates. The important piece to note are the four properties on the Combobox. ItemsSource, DisplayMemberPath, SelectedValue, and SelectedValuePath. To set up your combobox:
This is how you always bind DataTables and it also works well for LINQ to SQL classes. But since LINQ to SQL classes also include the navigation properties (the Orders collection on Customer and the Customer object reference on Order) you can use a different technique by binding directly to the Customer reference. This is the only choice we have with Entity Framework entities in .NET 3.5 SP1.
What’s unique with Entity Framework is that the associations between other entities use only navigation properties -- so there isn’t a CustomerID foreign key property value on the Order at all.
What you have to do instead is bind directly to the Customer object reference. The change in the above example is the Combobox binding in XAML:
<ComboBox Height="23" Name="ComboBox1" Width="177" Margin="2" HorizontalAlignment="Left" IsEditable="False" ItemsSource="{Binding Source={StaticResource CustomerLookup}}" DisplayMemberPath="LastName" SelectedItem="{Binding Path=Customer}" />
The important pieces in this case are the three properties on the Combobox. ItemsSource, DisplayMemberPath, and SelectedItem.
This will work properly with LINQ to SQL classes and Entity Framework entities but there is one caveat. You need to make sure that you pull the entire Customer entity into in the lookup list from the same ObjectContext you used to query the Orders. This is because the same ObjectContext (DataContext in LINQ to SQL) needs to resolve the entity references between the queries. Entity Framework does this based on the EntityKeys. The neat side effect of this with EF is that you don’t have to pull down the Customers with the Orders query, they will automatically become references when the Customer lookup list is queried through the same context. (See my post on explicit load for more details on how to bring down related EF entities when you only make one call.)
This behavior may be desired in a lot of scenarios but if we do not need to modify the Customer, like in our example, this can be overkill especially if the Customer has a lot of large fields you aren’t using. In the first example we could have only pulled a subset of fields from the Customer table – the only required ones would be the ones used in the data binding, CustomerID and LastName. So with DataTables and LINQ to SQL classes that bind on the values we could have optimized our lookup list query to:
Dim customerList = From c In db.Customers _ Where c.Orders.Count > 0 _ Order By c.LastName, c.FirstName _ Select c.CustomerID, c.LastName
This will not work if we bind directly to the navigation properties because the customerList is now a list of anonymous types and not a list of Customer entities. So binding to the values gives you greater flexibility with your lookup list queries. Unfortunately in the current version of EF you cannot bind this way but they are planning to enable this in the next version. In .NET 4.0 the EF team will add support for a new type of association called "FK Associations". Read more about that here.
And I’m working on the next set of WPF Forms over Data How Do I videos, this time with Entity Framework, that will hopefully explain how to use EF in practical way by building WPF data applications. Stay tuned!
Enjoy!
Codecast is a short (~25min) podcast by Ken Levy who runs around Microsoft grabbing interviews with folks. In this episode Lisa talks about what Visual Basic developers can expect from the Visual Studio 2010 editor and IDE and other cool new features in Visual Basic. She also talks about the developer community and even mentions me and the VB Developer Center :-)
Check it out!
CodeCast Episode 17: Visual Basic 2010 Preview with Lisa Feigenbaum
Also here’s some links to check out that Lisa talked about:
Last post I was talking about DataSets and aggregating data with LINQ and the example I gave was around finding rows of data where the Status = “Unread”. This got me thinking about how we could do this by asking Outlook for my unread email count – one of the programs I spend a lot of time in ;-). This actually is a pretty easy thing to do if you use the Primary Interop Assembly (PIA) for Outlook – Microsoft.Office.Interop.Outlook. Add a reference to your project and then import the namespace at the top of your program.
Imports Microsoft.Office.Interop.Outlook
Then you can write the following code:
Dim OutlookApp As New Application() Dim myMail As [NameSpace] = OutlookApp.GetNamespace("MAPI") myMail.Logon(ShowDialog:=True, NewSession:=False) Dim count As Integer = myMail.GetDefaultFolder(OlDefaultFolders.olFolderInbox).UnReadItemCount
Note that if you have selected an Outlook Add-in project template in Visual Studio then you already have reference to the Application and you’re already logged in so the code is even simpler:
Dim myMail As [NameSpace] = Me.Application.GetNamespace("MAPI") Dim unread As Integer = myMail.GetDefaultFolder(OlDefaultFolders.olFolderInbox).UnReadItemCount
Today I got a question that comes up often in data application programming about how to count rows in a DataSet that matched a condition. The DataSet may be bound to a DataGridView or other list control and it’s tempting to start looking at the control to see if you can coax it into returning what you need but usually there is a much better way.
For instance, say we have a table in our database called “Inbox” that has varchar fields Subject, From, and Status and we’d like to tally all the rows where the Status = “Unread”. Suppose we’ve also created a typed DataSet that contains this Inbox table. You create a data-bound Windows Form with a DataGridView on it by dragging the table from the Data sources window onto the form (like I showed in this video). When you do this, Visual Studio generates code that hooks up your DataGridView’s DataSource property to a BindingSource object which in turn has it’s DataSource set to your DataSet. This is a good thing. The BindingSource is a simple controller that provides currency between your DataSet and the DataGidView UI. (In WPF this is similar to the CollectionView object). You write code against the BindingSource instead so that it doesn’t matter what kind of control is being used to display the data.
BindingSource to the Rescue
The BindingSource in this case is actually working with a DataView, not the actual DataTable, which may seem confusing. This is needed for related data binding to work (see this video and this one). Since the BindingSource manages the currency (current row position) that is being displayed by the controls, in this example the BindingSource.List will return the DataView and the BindingSource.Current property will always return the DataRowView. You access your typed DataRow by casting the DataRowView.Row property. So to get a count of items in the DataView we could just simply ask the BindingSource for a count of it’s rows no matter what kind of control is being used for the display:
Dim count = Me.InboxBindingSource.Count
Or we could grab the DataView and ask for its count:
Dim dv = CType(Me.InboxBindingSource.List, DataView) Dim count = dv.Count
Once you have the DataView you can apply more filtering on it directly or we can loop through it to tally the rows where Status = “Unread”.
Dim dv = CType(Me.InboxBindingSource.List, DataView) Dim count = 0 For Each drv As DataRowView In dv Dim inboxRow = CType(drv.Row, EmailDataSet.InboxRow) If inboxRow.Status = "Unread" Then count += 1 End If Next
But the neat thing about using the BindingSource is that you always know the current row being displayed. So if we want to tally the rows based on a condition in the current row we go through the BindingSource. You can easily write the code that gets the current row as your typed data row by using a code snippet. Right-click in the editor, select Data – LINQ, XML, Designer, ADO.NET > Designer Features and ADO.NET > Converts BindingSource.Current to a specific row in a DataTable:
This code returns a tally of rows in the current view that have the same status as the selected row. Instead of looping through the data manually with For Each, this code sets the Filter property of the DataView instead:
Dim currentRow As EmailDataSet.InboxRow currentRow = CType(CType(Me.InboxBindingSource.Current, DataRowView).Row, EmailDataSet.InboxRow) Dim dv = CType(Me.InboxBindingSource.List, DataView) dv.RowFilter = "Status ='" & currentRow.Status & "'" Dim count = dv.Count
This technique causes any bound controls to update to the new filter. To remove the filter, set the Filter property to Nothing or the empty string. Although this code may work for a lot of scenarios, we may have a situation where we do not want to affect any of the bound controls by changing the filter. And even though this is a simple condition it would be nice not to have to manually write the loop ourselves. This is a perfect place to use LINQ instead.
LINQ to the Rescue
If we want to tally the entire DataTable for rows matching a condition we can use LINQ to DataSets with Visual Basic’s Aggregate clause. This doesn’t involve a BindingSource at all but be aware that the DataView that a BindingSource may be displaying data from may be filtered. In the case of this example the DataView contains the same rows as the DataTable (no filter) so we can write a simple Aggregate query to count the rows:
Dim count As Integer = Aggregate row In Me.EmailDataSet.Inbox _ Where row.Status = "Unread" _ Into UnreadCount = Count()
This will return an integer indicating the number of rows in the entire DataTable that matched the Where clause. But what if we do want to use the BindingSource and take into account any filters that are being applied to the DataView in which controls are bound? In this case we can still use an Aggregate LINQ query it will just be against the DataView so we will need to cast a few things:
Dim dv = CType(Me.InboxBindingSource.List, DataView) Dim count As Integer = Aggregate row In dv.Cast(Of DataRowView).AsQueryable _ Where CType(row.Row, EmailDataSet.InboxRow).Status = "Unread" _ Into UnreadCount = Count()
Now what would be really nice is to automatically tally this information from the DataSet and display it in a label on the Form anytime the user made any changes to the rows. We can do this easily by handling the BindingSource’s ListChanged event and putting our Aggregate LINQ query in there:
Private Sub InboxBindingSource_ListChanged() Handles InboxBindingSource.ListChanged Dim count As Integer = Aggregate row In Me.EmailDataSet.Inbox _ Where row.Status = "Unread" _ Into UnreadCount = Count() Me.lblStatus.Text = count.ToString() & " unread email." End Sub
Now this will give us a “live” running tally and we’re not bound to the UI controls at all. Nice. For more information on Aggregate queries and DataSets check out this video.
More resources:
I’ve had a couple questions lately on how to change the Visual Studio start page news feed so I thought I’d write a quick post on how to do it. When you open Visual Studio for the first time it will ask you what development environment setting it should use. You can pick Visual Basic, C#, General, Web, etc. Depending on what you pick you will see different news feed in your start page. If you pick Visual Basic, you will see the same news feed that is on the Visual Basic Developer Center which showcases articles, tutorials, videos and other important resources:
You can change the development settings by going to Tools –> Import and Export Settings and then picking a set of default settings. However, sometimes you just want to change the feed and not the development settings. You can do that by going to Tools –> Options then check “Show all settings” at the bottom, expand the Environment node and select Startup. In the Start Page news channel specify: http://go.microsoft.com/fwlink/?linkid=96703&clcid=409 which will route you to this feed: http://services.social.microsoft.com/feeds/feed/VB_featured_resources
Of course you can put any RSS feed here and it will display on your start page, but I would recommend staying up to date on Visual Basic ;-). For more Visual Studio tips check out Sara’s blog and book.
Oliver Sturm and Gary Short have recently started a new podcast called Sod This: http://www.sodthis.com. I met Oliver at SDC in the Netherlands last Fall and he is a really funny dude who loves to drink beer like me so naturally we clicked. ;-) I’m listening to Episode #2 right now with guest star Richard Campbell.
In Oliver’s words:
Sod This is a mixture of two rather technical people having a chat, discussions about relevant or not so relevant technology, programming questions and sock monsters, and interviews that Oliver and Gary record with people they meet at the events they attend around the world. It doesn't have a fixed length and they don't tell how often new episodes become available, so you'll just have to go to the web site and check it out!
Good luck with the show, guys!
In my last few app building posts we've been building a simple Office Business Application (OBA) for the new Northwind Traders. If you missed them:
Today we're going to build out the SharePoint workflow using Visual Studio 2008 SharePoint 2007 workflow templates. There's a plethora of information on building these in the MSDN library. In order to develop against SharePoint you will need to set up your development environment properly so I would read this first if you're just getting started. My SharePoint development environment is a 32bit Windows 2008 Server running MOSS 2007 and Visual Studio Professional 2008 Service Pack 1.
If you recall our architecture diagram of our Northwind Traders OBA involved our Sales Reps submitting purchase orders as Word 2007 documents up to SharePoint which kicked off a workflow to parse the document and update the database with the order data through our data service. This allows us to store the unstructured document on SharePoint and the structured order data in our database.
In Part 3 we built the code that does the parsing of the document, now we just need to get that code into our workflow. We’ll also build in a delay so that the workflow can check the database later to see if the order has been shipped and update the status appropriately. We’ll also take advantage of SharePoint’s workflow history and task list to report outcomes or any issues that may arise.
Creating a Document Library for the Purchase Order Documents
For this example we need to add a document library called Orders for the purchase order documents that are submitted. To create the document library, navigate to your SharePoint site and then in the right-had upper corner drop down the Site Actions, select Create (or just navigate to /_layouts/create.aspx) and then under Libraries select Document Library.
The only other property that I changed here is the document template, change that to Microsoft Office Word Document and then hit the Create button. This will bring you to the doc library and you’ll see the default fields in the column headers. We’re going to need to modify these to show the Order Status, an Order Number, the Shipped Date and the email address of the Sales Rep that submitted the order.
Click on Settings then select Create column to create new columns for the orders in the document library.
For this example I added the following columns:
After I added these columns I added a new default view to display them in the order I want. Click on the Settings again and select Create view.
The only thing the user is required to fill out when they upload a new purchase order document is the sales rep’s email address. (Recall that we added this field to the database when we built the data service). The Order Status and Shipped Date will actually be filled out automatically by the workflow depending on whether the order is new or has been updated by the Excel client (which we built in the last OBA post) that the shipping department uses to mark orders as shipped. Order Number will also be assigned by the workflow. We’ll use this to correlate the data with the purchase order document here in the list so we’ll need to add this field to the database.
Adding the Order Number to Northwind
So now we have the document library set up, we’re almost ready to write the workflow. First we’ll need to add the OrderNumber field to the Orders table in the Northwind database and then refresh our data service service so that this starts to show up as an available field on the Order.
USE Northwind GO ALTER TABLE dbo.Orders ADD OrderNumber nvarchar(25) NULL GO
In order to pick up this new field we need to refresh the Entity Data Model behind our data service. In the NorthwindDataService project open the NorthwindModel.edmx and then right-click on the design surface and select Refresh from database. This will pick up the OrderNumber field on the Order entity. Rebuild the data service.
Options on Hosting the Data Service for Workflow Development
If you’ve been following along and playing with the sample, you’ll notice that the NorthwindDataService project is currently set to be hosted in the ASP.NET development server right now so that testing the Excel and Outlook clients is easy. However at this point you may opt to deploy this to IIS instead so that you don’t have to remember to start it up manually every time you want to test the SharePoint Workflow. And of course for deployment to production you’ll need to host it in IIS anyway.
It gets slightly tricky if you want to host the data service in IIS on the same server as SharePoint because SharePoint takes over Port 80. So the easiest thing to do is to open IIS Manager and just create a new web site on Port 82 called NorthwindHost and then create a new application virtual directory called Northwind under that. By default, when you create a new web site it should also create a new Application Pool for you running under the identity NETWORK SERVICE. The identity you use here will need to have read and execute file permissions on the physical locations of any virtual directories you create as well as proper database permissions.
It’s XCOPY deployment at that point. Just copy the Web.Config, Northwind.svc and the \bin directory over to the physical location of the Northwind virtual directory and you should be good to go. You can do this in Visual Studio (if running as an administrator) just right-click on the NorthwindDataService project in the solution explorer and then select publish. Then enter http://localhost:82/Northwind/ (or whatever you named it) and it will build and publish the files there automatically.
Note however, that before the WCF service will activate you have to install the WCF service handlers into IIS. These may not be installed automatically so in Windows 2008 check the Server Manager, scroll down to the Features Summary and select “Add Features”. Then expand the first node “.NET 3.0 Features” and check WCF Activation.
Remember if you change the location of the data service then you need to update the settings in the Excel and Outlook client (app.config file).
Creating the SharePoint Workflow
If you’re not doing this already, restart Visual Studio and Run as Administrator. This is necessary so that you can deploy and debug your workflow. Next add a new project to the solution, File –> Add –> New Project. For this example we’re just going to create a simple SharePoint 2007 Sequential Workflow. (Note: You will need Visual Studio 2008 Professional and higher to get these templates.)
As I mentioned before the workflow will kick off when a new purchase order is added to the document library in order to parse the data in the document. So when the order is first added to the list it will have a status of New. After it is parsed we will set the status to Processed, which happens right away. Later, after the shipping department enters the shipped date in the Excel client and updates the Orders.ShippedDate field in the database, we need the workflow to wake back up and set the order status to Shipped. For this example I’m going to build a delay into the workflow to check the database (via our data service) every so often. For testing it can be a couple minutes but once we deploy we can set it to every couple hours. Note that if you’re building more statuses or states into your own workflows you may want to take a look at the State Machine Workflow template instead.
So the next step is to specify where your SharePoint site and document library resides, for development this is going to be http://YourServerName. Once you specify the site you can then select the Orders document library in the Library dropdown. Leave “Automatically associate workflow?” checked so that Visual Studio can deploy the workflow to the site automatically for us.
On the next page check off how you want to start the workflow. For this example we’ll select “When an item is created”. You can also check “Manually by users” which can be helpful when testing but it’s not necessary for our example. Note that you can change these settings later on the Workflow project -- from the Project menu select “SharePoint debug settings..”. You need to be running Visual Studio as an administrator to open these settings. You’ll need to do this if you’re going to run the Workflow in the sample code against your own SharePoint site.
When the project is created it will automatically add a workflow called Workflow1 and open the designer which will display an event activity for onWorkflowActivated. An OnWorkflowActivated activity must be the first activity in a Windows SharePoint Services workflow so Visual Studio automatically sets one up for us. I renamed Workflow1 to ProcessOrder in the Solution Explorer (I like descriptive names ;-)) which means I’ll also need to update the values for the CorrelationToken.OwnerActivityName as well as the WorkflowProperties.Name to set them to ProcessOrder:
Now you can start laying out the design of the workflow. If you have used the WF designer before then this part should be familiar. Take a look at your toolbox and you’ll see the SharePoint specific activities listed under the Workflow 3.0 and 3.5 tabs. The design of the workflow for this example will be simple.
There are three main blocks to the workflow that we’re going to build:
Before we start writing the workflow code, let’s create some constants that we can use to refer to the data service URI, our order statuses, and list column names (I just added a new Module called Constants.vb):
Friend Module Constants Public Const OrderStatusColumn As String = "Order Status" Public Const OrderNumberColumn As String = "Order Number" Public Const ShippedDateColumn As String = "Shipped Date" Public Const SalesRepEmailColumn As String = "Sales Rep Email" Public Const NewStatus As String = "New" Public Const ShippedStatus As String = "Shipped" Public Const ProcessedStatus As String = "Processed" Public Const OnHoldStatus As String = "On Hold" Public Const CanceledStatus As String = "Canceled" Public Const ServiceURL As String = "http://localhost:82/NorthwindService/Northwind.svc" End Module
Parsing the Order Document (CodeActivity parseOrder)
The Workflow kicks off immediately after a purchase order document is checked into the document library. The first code activity out of the gate is parsing the order document’s customXML for the order data we need. I already demonstrated a simple console application for extracting the order data from a Word document in Part 3. This code uses the Open XML SDK so you’ll need to download that and install it on your SharePoint server so that the assemblies are in the GAC. Then add an assembly reference to DocumentFormat.OpenXML.
From that console application project I copied the DocumentOderData.vb, Extensions.vb code files and the OrderEntry.xsd into the workflow project. Module1.vb had a method called ParseOrderDocument which parsed the docx file and populated a DocumentOrderData class with the information. I took that code and created a new class called Shredder and modified the interface a bit so that we could pass it a Microsoft.Sharepoint.SPFile class instead:
Imports DocumentFormat.OpenXml.Packaging Imports <xmlns="urn:microsoft:examples:oba"> Public Class Shredder Public Function ParseOrderDocument(ByVal docFile As SPFile) As DocumentOrderData Using docStream = docFile.OpenBinaryStream Return ParseOrderDocument(WordprocessingDocument.Open(docStream, False)) End Using End Function Public Function ParseOrderDocument(ByVal wordDoc As WordprocessingDocument) As DocumentOrderData...
We also had a means for adding this data to the database via a method called AddNewOrder. The only change we need here is to create a unique OrderNumber string, add it to the Order entity, and return it to the workflow so we can also set it on the list item in SharePoint. You can decide how you want to generate unique order numbers, whether you want them to be GIUDs or timestamps or some other random number. I like using a combination of the CustomerID and the current date&time so that it’s human readable.
Dim orderNum = Replace(Replace(Replace(cust.CustomerID & Now.ToString(), "/", ""), ":", ""), " ", "")
This will work as long as there aren’t two orders for the same customer submitted within the same second so depending on your actual scenario you may opt for a different strategy :-). So this AddNewOrder code I placed into a class called OrderManager which also encapsulates all calls to the data service. Speaking of our data service, we’ll need to add a Service Reference to the workflow project called NorthwindService (right-click on the workflow project and select Add Service Reference) which will add the database entity types to our project.
Now drop a CodeActivity onto the Workflow designer and in the properties window name it parseOrder. Then right-click on it in the designer and select Generate Handlers to automatically generate the parseOrder_ExecuteCode handler. If the order data is extracted and added to the database successfully then an order number is assigned and the status is set to Processed. If the document does not contain the <OrderEntry> CustomXML then the status will be set to Canceled. If there is a problem adding the data to the database then the status is set to On Hold. (Notice that this code also uses the Application.Log to write messages to the Event Log which you can see how to set up here.)
Public Class ProcessOrder Inherits SequentialWorkflowActivity Public workflowProperties As New SPWorkflowActivationProperties Public Sub New() MyBase.New() InitializeComponent() End Sub Private Sub parseOrder_ExecuteCode(ByVal sender As System.Object, _ ByVal e As System.EventArgs) 'This method is executed once the purchase order is uploaded ' and checked into the document library Try Dim orderNumber As String = "" Dim shredder As New Shredder 'Valid OrderStatus: New, Processed, Shipped, Cancelled, On Hold ' (See Constants.vb for column and status strings) Dim status = workflowProperties.Item(OrderStatusColumn).ToString() If status = NewStatus Then 'Get order data out of the document Dim docData = shredder.ParseOrderDocument(Me.workflowProperties.Item.File) If docData IsNot Nothing Then Dim manager As New OrderManager Dim employeeEmail = workflowProperties.Item(SalesRepEmailColumn).ToString() 'Add order data to the DB (through the service) and return the order number. orderNumber = manager.AddNewOrder(docData, employeeEmail) If orderNumber <> "" Then status = ProcessedStatus My.Application.Log.WriteEntry( _ String.Format("Order {0} added to database successfully.", _ orderNumber), TraceEventType.Information) Else status = OnHoldStatus My.Application.Log.WriteEntry( _ String.Format("Order could not be added to database for {0}.", _ Me.workflowProperties.Item.Name), TraceEventType.Error) End If Else status = CanceledStatus My.Application.Log.WriteEntry( _ String.Format("Invalid purchase order submitted for {0}", _ Me.workflowProperties.Item.Name), TraceEventType.Error) End If End If If status <> "" Then Me.workflowProperties.Item(OrderStatusColumn) = status If orderNumber.Length() > 0 Then Me.workflowProperties.Item(OrderNumberColumn) = orderNumber End If Me.workflowProperties.Item.Update() End If Catch ex As Exception My.Application.Log.WriteException(ex) End Try End Sub
Now that the order is in the database, you can open the Excel client we wrote in part 4 and enter a ShippedDate and click save to update the order.
Querying the Database Periodically (WhileActivity waitForProcessed)
Next we need a WhileActivity so that we can check the database to see if the ShippedDate has been updated. Drag a standard While activity from the toolbox under the parseOrder and name it waitForProcessed in the properties window. Right-click on the designer and select View Code and write the following method:
Private Sub orderProcessed(ByVal sender As System.Object, ByVal e As ConditionalEventArgs) Try Dim status = Me.workflowProperties.Item(OrderStatusColumn).ToString() Dim orderNumber = Me.workflowProperties.Item(Constants.OrderNumberColumn).ToString() If status <> ProcessedStatus Then 'End while branch and go to next step in sequence if status is: ' Canceled = invalid purchase order (no <OrderEntry> CustomXML found) ' On Hold = Could not add data to database (see error log for details) e.Result = False Else 'Get order from DB Dim manager As New OrderManager Dim processedOrder = manager.GetOrder(orderNumber) 'If order was shipped (ShippedDate not NULL) then set Order Status to Shipped. If processedOrder IsNot Nothing Then If processedOrder.ShippedDate IsNot Nothing AndAlso _ processedOrder.ShippedDate.HasValue Then My.Application.Log.WriteEntry( _ String.Format("Order {0} has shipped as of {1}", _ orderNumber, Now()), TraceEventType.Information) Me.workflowProperties.Item(OrderStatusColumn) = ShippedStatus Me.workflowProperties.Item(ShippedDateColumn) = processedOrder.ShippedDate Me.workflowProperties.Item.Update() 'end while branch and go to next step in sequence e.Result = False Else My.Application.Log.WriteEntry( _ String.Format("Order {0} has not shipped as of {1}", _ orderNumber, Now()), TraceEventType.Warning) 'keep workflow running until shipped or canceled e.Result = True End If Else My.Application.Log.WriteEntry( _ String.Format("Order {0} has been deleted from the database", _ orderNumber, Now()), TraceEventType.Error) Me.workflowProperties.Item(OrderStatusColumn) = CanceledStatus Me.workflowProperties.Item.Update() 'end while branch and go to next step in sequence e.Result = False End If End If Catch ex As Exception My.Application.Log.WriteException(ex) End Try End Sub
Notice in this code we’re referring to a method on the OrderManager called GetOrder which simply queries the Order entity from the database via the data service using LINQ:
Imports OBADemoWorkflow.NorthwindServiceReference Public Class OrderManager Private ctx As New NorthwindEntities(New Uri(Constants.ServiceURL)) Public Function AddNewOrder(ByVal docData As DocumentOrderData, _ ByVal employeeEmail As String) As String... Public Function GetOrder(ByVal orderNumber As String) As Order Try Dim theOrder = (From o In ctx.Orders _ Where o.OrderNumber = orderNumber).FirstOrDefault() Return theOrder Catch ex As Exception My.Application.Log.WriteException(ex) Return Nothing End Try End Function End Class
Now you can go back to the designer and set the Condition on the waitForProcessed WhileActivity to the orderProcessed method. Set the Condition to a Code Condition and the dropdown will contain only those methods that have the right signature, i.e. have a ConditionalEventArgs parameter. The orderProcessed method will set the e.Result = True only if the order is in the Processed state, is still in the database, and its ShippedDate is still NULL. This means that the While activity will continue to run.
Because the shipping department most likely will not ship the order out the door the second the order is entered into the database (<g>), we need a way for our workflow to delay and check periodically. Depending on your business you may want to check hourly or even daily. For testing you probably want to check every minute or two. This is where the DelayActivity comes in. Drag a standard Delay activity onto the designer inside the While Activity and set the Timeout Duration in the properties window to 3 minutes or so.
Before we go on I want to mention that there were known issues with the Delay activities not firing correctly in SharePoint that are addressed by this KB Article: http://support.microsoft.com/kb/953630/. The SharePoint timer service (OWSTIMER.exe) is set to only wake up every 5 minutes, so you probably want to set that to a shorter interval in your testing environment otherwise you’ll have to wait at least 5 minutes no matter how short you set your Delay activity. Another other issue I noticed was sometimes on rebuild & redeployment I needed to restart the timer service manually from an admin command prompt:
>Net Stop SPTimerV3 >iisreset >Net Start SPTimerV3
Also there is an issue with debugging delays. I couldn’t just press F5 to debug the workflow after the delay. Up until that point I debugged just fine (which is where almost all our code is in this example) but if I wanted to see the rest of the code execute after the delay then I needed to attach to the OWSTIMER.exe process (while you’re debugging, on the main menu select Tools –> Attach to Process). This is also one of the reasons why I enabled the Application.Log to send messages to the Event Log.
Wrapping Up the Workflow (History Logging and Creating Tasks)
The last piece of the workflow is simple, if the order is shipped we’ll just log a message to the workflow history list otherwise we’ll create a task for the administrator to investigate. Drop an IfElse activity under the While activity set and name it checkIfShipped. Then name the first branch ifShipped and the second branch elseNotShipped. In the ifShipped branch drop a LogToHistoryListActivity which is found in the SharePoint Workflow tab on the Toolbox. In the elseNotShipped drop a CreateTask activity. On the properties window for the CreateTask create a new field binding for both the TaskID and TaskProperties and enter a correlationToken with the parent activity set to ProcessOrder (the workflow).
Then right-click on both the CreateTask and LogToHistoryActivity and select Generate Handlers in order to generate the method stubs for each of them. We’ll also need a method called isOrderShipped with the same signature as the orderProcessed method above to be able to set it as the Code Condition of the ifShipped branch. We need to check the workflowProperties.Item for the order status and set the e.Result = True if the status is Shipped.
Private Sub isOrderShipped(ByVal sender As Object, ByVal e As ConditionalEventArgs) 'Returns True if Order Status equals Shipped Dim status = Me.workflowProperties.Item(OrderStatusColumn).ToString() e.Result = (status = ShippedStatus) End Sub Private Sub createTask1_MethodInvoking(ByVal sender As System.Object, ByVal e As System.EventArgs) Try 'This will execute if the workflow is ending but the Order Status ' is not Shipped (i.e. Canceled or On Hold). ' This code creates a workflow task item so the AssignedTo can investigate. Dim status = Me.workflowProperties.Item(OrderStatusColumn).ToString() Dim name = Me.workflowProperties.Item.Name createTask1_TaskId1 = Guid.NewGuid() createTask1_TaskProperties1.AssignedTo = "obavm09\wssdeveloper" createTask1_TaskProperties1.Title = String.Format("Problem with order {0}", name) createTask1_TaskProperties1.Description = String.Format("Order {0} as of {1}", status, Now) Catch ex As Exception My.Application.Log.WriteException(ex) End Try End Sub Private Sub logToHistoryListActivity1_MethodInvoking(ByVal sender As System.Object, _ ByVal e As System.EventArgs) logToHistoryListActivity1.HistoryOutcome = "Order has been shipped." End Sub
Testing the Workflow
In Visual Studio (still running as Administrator) Rebuild the solution. Then right-click on the OBADemoWorkflow project and select Deploy from the context menu. Now we can head over to the document library and upload some purchase orders. I used the one we created previously in Part 3 with ALFKI as the CustomerID. Go to the Orders document library, click Upload, Browse for the purchase order and click OK. Then fill out the required field Sales Rep Email (this is the Employee.EmailAddress field we added to the database in Part 1). Once you click “Check In” the workflow will kick off.
The Order status is first set to “New”….
But if you quickly refresh the list you will see that the Order Status and Order Number change as the order is processed and entered into the database.
Now the workflow is in its delay activity waiting for us to update the Order.ShippedDate. Open up the Excel client and enter something in the ShippedDate field and save the sheet (or manually modify the database). The next time the SharePoint timer runs it will waken our delay which will check the database again and set the status appropriately.
If you select the OBADemoWorkflow status (the last column in the view which is added automatically for us when we deploy) then you can see the status message in the Workflow History list. To see a task get created you can perform the same process but instead of updating the ShippedDate, delete the Order from the database (or delete the OrderNumber) and the workflow will set the status to Canceled and you will see a task in the list instead. And remember in testing we set the delay to a few minutes but in production Northwind Traders will be good with a 12 hour delay (gourmet foods don’t ship that quickly ;-)).
I’ve added this workflow to the sample code we’ve been building up on Code Gallery so have a look. I hope this real(er)-world, componentized sample has given you a good introduction into OBA and some of the awesome VSTO features of Visual Studio 2008.
Yesterday Carl posted another dnrTV show with yours truly. This is a follow up from the last show where I went over the Open XML SDK and LINQ to XML in order to manipulate and query Office documents. Last show focused on CustomXML parts and Word content controls. In this show I demonstrate how to create Word and Excel docs from database data using XML Literals.
dnrTV Show #137: Beth Massi on the Open XML SDK dnrTV Show #138: Beth Massi on Open XML and Office
Here are some links to check out from the shows:
And here's the source code for the demos I showed.
I just posted another interview on Channel 9. I sit down again with Saurabh Bhatia, a Program Manager on the Office Client team, who is responsible for the ClickOnce publishing functionality in Visual Studio. We chat about trust issues and certificates and he sets me straight on how ClickOnce deployment and application manifests work. He then shows how to re-sign them outside of Visual Studio using a tool called Mage. This is really handy for folks that need to modify the files within a deployment package, like the application settings (app.config) file, but don't have Visual Studio installed.
Channel 9: Resigning ClickOnce Application and Deployment Manifests with MAGE
Saurabh draws on the whiteboard in this one and since I'm a one (wo)man show I couldn't jump up and zoom in so I redrew it for you all here.
Saurabh has also posted more details in an article he wrote here.
Links from the show:
Yesterday Carl posted another dnrTV show with yours truly. This time I’m talking about the Open XML SDK, something that I’ve written and spoken about many times. In this episode I show how to use Word 2007 content controls and map them to custom XML parts so that you can query structured data from unstructured documents using Visual Basic’s LINQ to XML. What’s really cool about the Open XML format is that you don’t need Office installed at all – no COM Interop needed. You’re working with the file formats directly. And it’s super simple using VB’s XML literals and LINQ with the SDK.
Unfortunately I didn’t have a lot of time when I did the show with Carl and I couldn’t finish my demos. So this morning I filmed another quick episode that shows how to create documents from database data directly, so keep on the lookout for the follow up show :-).
dnrTV Show: Beth Massi on the Open XML SDK
And here are the links from the show:
[UPDATE: Here's code samples (also includes a presentation pptx)]