I just finished my last talk of the conference on LINQ to XML and it was lots of fun as always. I've spoken a lot on this topic in the past but talks always come out slightly different. This time it was great, I got through all the demos and showed a good amount of Open XML SDK to manipulate Office 2007 documents. I also showed off the VSTO Power tools a bit for viewing the Open XML packages. Amongst the many demos we went through, we built a letter generator from scratch like I've shown before here. I also walked through selecting data from Custom XML parts like I've shown here. I also did my famous Virtual Earth demo that people always enjoy. A few people came up afterwards and told me they enjoyed the talk so that was good :-). One thing that was interesting this time was that most people in the room had seen XML Literals in Visual Basic so I didn't have as much "shock-and-awe". But that's a good thing -- folks are really digging into this stuff!

Last night I was on a panel with a lot of the other speakers and it was interesting to say the least especially being the only one from Microsoft up there! ;-) We had a lot of lively discussions around Azure and Windows 7 but nobody complained about languages or Visual Studio so that made me happy :-).

Now I'm sitting in a talk by Robert Green on building SharePoint Workflows in Visual Studio 2008 and SharePoint 2007. He's walking though the development of a simple workflow that will kick off when a document is created in a doc library. This is exactly where I'm at in our OBA series so I'll get back that that as soon! It's great to validate what I'm building with what Robert is doing here. Here's a great resource for SharePoint development including these workflows: http://www.microsoft.com/click/SharePointDeveloper/

I'm going to go to dinner with Robert after this and pick his brain on a few things. :-)

I've had a great time here at VSLive! SF this year and can't wait until it comes through the city again. It was fun to take BART into the city this week. Many many thanks to all the organizers.

Next stop, MVP Summit in Redmond!

I'm here at VSLive! at the BEAUTIFUL Hyatt Regency at Embarcadero Center in San Francisco. I love this hotel. Lots of great memories here. And besides the funky architecture of the buildings, there's a cool atrium that has a mesmerizing fountain in the middle of it as the floors wrap around it.

I love VSLive! because the developers here tend to be like me, writing some sort of business application, be that web or Windows or Office/SharePoint based. There are a lot of locals here but I also met a few people that came from other countries.

The first day was off to a great start. Jason Zander, GM of Visual Studio, showed off the latest build of Visual Studio 2010 which is re-written in WPF. He showed a few of the cool productivity features too. You can read about it here and here.

I then had two talks back-to-back. The first was on LINQ best practices and tips and tricks and I had a good turnout of about 60 people. (Considering tat registration was down from last year because of the economy, I thought it was a good turnout.) It was fun to show off the language features that enable LINQ and then start ripping into LINQ to Entities and LINQ to XML providers. Here's some LINQ talks I used to pull it together that you can check out.

The next session was the Future Directions for Visual Basic and it was basically the same session as Paul Vick and Lucian Wischik did at PDC. I was having a slight issue with a slow VPC but other than that I think it went well. I hope I did Paul and Lucian proud. :-)

After my sessions were over I attended Bill Sheldon's talk on VSTO and SharePoint where he walked through the OBA Sales demo that is available on CodePlex. I've been writing a series of posts on a simple OBA Northwind scenario so it was helpful to see what kind of techniques are used in this more complex example. (BTW, I will be back to the OBA series soon -- lot's of conferences lately!) This demonstration included defining a custom content type in the document library that opened a customized Excel document that pulls in sales forecast data. It also is a good demonstration of a more complex workflow so that was also pretty cool.

Later that evening there was a welcome reception and an Ask The Experts lounge. I manned the Visual Basic "booth" -- it wasn't a booth it was a whiteboard with some chairs -- but I still managed to write out code on the board. Man though, my hand writing stinks! You can tell I'm a keyboard-only person. :-) I ended up having some good conversations with folks about SharePoint development. Then dinner and drinks and a few more drinks. Very fun.

Today Tim Huckaby and Scott Stanfield showed off the Microsoft Surface and multi-touch applications. VERY COOL. I had the opportunity to play with this at PDC and it is pretty darn fun. Now I'm sitting in Walt Ritscher's WPF data binding 101 talk and it's goooood. I'm learning a lot of nifty types of binding  -- not just to properties on data objects like I show in the videos, but also how to bind to other UIElement dependency properties, and the detailed features of the binding class. He also is showing off some cool formatting and error handling techniques. I'll have to play with some of that when I get home and do a couple more videos.

Tonight I have a panel discussion with a bunch of folks so that should be a lot of fun. I can't wait to see what questions the attendees will throw at us. I'll report back tomorrow and let you know how it went! Now it's time to head off to Billy Hollis's talk on managing data in Silverlight (yes, I like data and shiny UIs today). Here's Billy and me and a few other pictures I snapped today:

Enjoy!

I'll be speaking in my own home town of San Francisco next week for VSLive! so if you're in the area you should register and come on out! There's an amazing line up of speakers and sessions. Also when you register you get free access to the MSDN Developer Conference on Monday.

I'll be showing off some super-cool new Visual Studio 2010 features as well as some tips and tricks of LINQ in Visual Basic 2008 -- including a whole talk devoted to working with LINQ to XML (my personal favorite).

VSLive! is a great conference because it really shows you solutions and practical applications you can build today not just tomorrow. And this year you can really get up close and personal with the experts. 

Register here.

Hope to see you there!

Julie Lerman's Programming Entity Framework book just arrived today! I've been skimming around it all day and so far Chapter 8 is my favorite but I'm sure once I get to Chapter 14 that will take the cake :-)

Thanks Julie! (especially for all the great VB code!)

How Do I videos here we come.....

Enjoy!

I'll be speaking at the Rocky Mountain Tech Tri-fecta this Saturday Feb 21st in Denver, CO. So what the heck is a Tech Tri-fecta, you ask? It's three different technology families, all on the same day at the same place at the same time.  It’s a Code Camp.  It’s a PASS Camp.  It’s a Windows Server Camp. Did I mention it's FREE!?

Some great speakers will be there like Scott Hanselman, Kathleen Dollard, Jeff Certain, Tim Huckaby, Craig Bernston, Eric Johnson, David Yack, Paul Neilson, Don Jones, Greg Shields, and more!

I'll be speaking on a few fun language, LINQ and WPF topics. ;-) Here's the complete schedule and tracks.

Register here.

Hope to see you there!

I just posted an interview on Channel 9 with Dmitry Robsman, the Product Unit Manager for ASP.NET. In this interview he shows us how he implemented ASP.NET MVC views using Visual Basic's XML Literals instead of .aspx pages. Dmitry shows us how this makes coding the views much cleaner using standard OOP principals. He also makes some very interesting observations about DSLs and Visual Basic XML literals. You can download the code Dmitry shows in the interview from his blog here.

Enjoy!

In my last few posts we've been building a simple Office Business Application (OBA) for the new Northwind Traders. If you missed them:

• OBA Part 1 - Exposing Line-of-Business Data
• OBA Part 2 - Building and Outlook Client against LOB Data

In this post I'm going to talk about how we can create a purchase order in Word 2007 that contains data about the items being purchased and how we can query that data and place it into our database. We'll use this code as a basis for our SharePoint Workflow which we will build out in the next post.

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.

However before we build out the SharePoint Workflow we need a clean way to store and then retrieve the structured order data inside the Word 2007 document. Since Word 2007 documents are Open XML we can use the Open XML SDK and LINQ to XML to easily parse the document. (I've talked about how to manipulate documents with Open XML SDK before here.)

Content Controls

One way to store data in an Word 2007 document is by using content controls. These allow you to define specific data areas/fields in the document which are then bound to XML that is placed inside the document. When users enter data into these areas of the document the data is stored as a CustomXML Part inside the document. You can use Visual Studio to create content controls and map them to XML or you can use Word itself. There's also a nifty tool called the Word 2007 Content Control Toolkit that makes the mapping more visual. I'd also highly recommend installing the VSTO Power Tools as well which includes a VS Add-In for manipulating Open XML documents. This allows you to look inside the document easily to inspect all the parts directly within Visual Studio.

So the first thing to do is to create a purchase order template and lay out the content controls on the document. We'll create something very simple using Microsoft Word 2007. On the Developer tab you will see the Controls section. There you can choose which types of controls to lay out on the document. Click the properties button to assign a friendly title and tag to the control. Here I've laid out the minimum information we'll need to submit an order to the system:

Users can write anything else around the content controls but the system only cares about capturing the data we've specified. This gives us the ability to store structured and unstructured data completely inside the .docx file.

Creating and Mapping the XML

Now we are ready to map the values of the content controls to some custom XML. The XML document for our order looks like this. (Note that there are 10 <OrderDetail> elements I just snipped them for brevity):

<OrderEntry xmlns="urn:microsoft:examples:oba">
  <CustomerID />
  <OrderDate />
  <Shipper />
  <OrderDetails>
    <OrderDetail>
      <ProductName />
      <Quantity />
    </OrderDetail>
    <OrderDetail>...
  </OrderDetails>
</OrderEntry>

Now open up the Word 2007 Content Control Toolkit an open the OrderEntryTemplate.docx. Under Actions select "Create a new Custom XML Part", switch to edit view, and then paste in the XML:

Next switch to Bind View and then drag the elements onto the content controls on the left. Make sure you select the element first and then drag it.

Once you're done, save the document and then you can open it in Visual Studio if you've loaded the VSTO Power Tools. This will show the Open XML parts of the document and you can expand the customXml folder and see that our XML has been added to the document.

If you back into Word and fill out the content controls and then view the document in Visual Studio again, you will see that the item1.xml custom XML part will contain the data we entered.

Now that we have a purchase order template we can give this to our sales reps who can collaborate with our high volume customers via email to fill it out. They can then submit the purchase orders to a SharePoint list that can run a workflow to extract the order data and update the database through the data service.

Using the Open XML SDK to Retrieve the Order Data

The easiest way to programmatically manipulate Office 2007 Open XML documents is by using the Open XML SDK. Once you install it you can then Add Reference to the DocumentFormat.OpenXML assembly. In order to use LINQ to XML you'll also need a reference to System.Core and System.Xml.Linq. These are imported automatically when you create a new project in Visual Studio 2008. You'll also need to add a Service Reference to the ADO.NET Data Service like I've shown before. So let's start simple and just create a console application for now called NorthwindOrderDocParser. Later we'll talk about moving this to a SharePoint workflow.

Before we start parsing the document let's create a couple simple classes that store the data we're extracting from our document.

''' <summary>
''' These classes represents the order data that is inside the Word Document. 
''' </summary>
''' <remarks></remarks>
Public Class DocumentOrderData

    Sub New(ByVal customerID As String, ByVal orderDate As Date, ByVal shipperName As String)
        _CustomerID = customerID
        _OrderDate = orderDate
        _Shipper = shipperName
    End Sub

    Private _CustomerID As String
    Public Property CustomerID() As String
        Get
            Return _CustomerID
        End Get
        Set(ByVal value As String)
            _CustomerID = value
        End Set
    End Property

    Private _OrderDate As Date
    Public Property OrderDate() As Date
        Get
            Return _OrderDate
        End Get
        Set(ByVal value As Date)
            _OrderDate = value
        End Set
    End Property

    Private _Shipper As String
    Public Property Shipper() As String
        Get
            Return _Shipper
        End Get
        Set(ByVal value As String)
            If value Is Nothing OrElse value.Trim = "" Then
                value = "Speedy Express"
            End If
            _Shipper = value
        End Set
    End Property

    Private _details As New List(Of Detail)
    Public ReadOnly Property Details() As List(Of Detail)
        Get
            Return _details
        End Get
    End Property

    Public Class Detail
        Sub New(ByVal productName As String, ByVal quantity As Short)
            _ProductName = productName
            _Quantity = quantity
        End Sub

        Private _ProductName As String
        Public Property ProductName() As String
            Get
                Return _ProductName
            End Get
            Set(ByVal value As String)
                _ProductName = value
            End Set
        End Property

        Private _Quantity As Short
        Public Property Quantity() As Short
            Get
                Return _Quantity
            End Get
            Set(ByVal value As Short)
                _Quantity = value
            End Set
        End Property
    End Class
End Class

Next, let's add a schema for the OrderEntry XML data that is contained in the document. This will give us IntelliSense on our XML when we're using LINQ to XML. We can just open the document in Visual Studio like before and copy the OrderEntry XML data into the clipboard. Then we can Add a new XML to Schema Item and paste into the Wizard's dialog box. This will infer the schema and place the XSD file into the project automatically for us. Notice that I specified a namespace on our OrderEntry XML data. We now can import this namespace into our main program along with a few other .NET namespaces we'll need:

'Reference to our data service and data entities:
Imports NorthwindOrderDocParser.NorthwindService
'Open XML SDK:
Imports DocumentFormat.OpenXml.Packaging
Imports System.IO
'Default XML Namespace:
Imports <xmlns="urn:microsoft:examples:oba">

We are almost ready to start writing our main program to parse the purchase order. First we need a test document. For this test I filled out the following information in a document called MyTestOrder.docx.

Now we can write our main program:

Module Module1

    Sub Main()
        Try
            Dim docFile = My.Computer.FileSystem.GetFileInfo("MyTestOrder.docx")

            Dim docData As DocumentOrderData
            Using sr = docFile.OpenRead()
                'Attempt to parse the document for order data
                docData = ParseOrderDocument(sr)
                sr.Close()
            End Using

            If docData IsNot Nothing Then
                Dim employeeEmail = "sales@nwtraders.com"

                'Attempt to add the order data through the service
                AddNewOrder(docData, employeeEmail)
                Console.WriteLine("Order saved successfully.")
            Else
                Console.WriteLine("No order data was found in the document.")
            End If

        Catch ex As Exception
            Console.WriteLine("Order could not be processed." & vbCrLf & ex.ToString())
        End Try

        Console.ReadLine()
    End Sub

The ParseOrderDocument method is going to need to grab the XML data from our Custom XML parts as we iterate over the part collection. It's a collection because there can actually be many Custom XML definitions in our document. In order to make grabbing the XML data from the parts easier let's create an Extension method that extends the OpenXMLPart type. I like to place Extension methods in a separate file called Extensions.vb:

Imports DocumentFormat.OpenXml.Packaging
Imports System.IO
Imports System.Xml

Module Extensions

    ' Create an extension method so we can easily access the part XML
    <System.Runtime.CompilerServices.Extension()> _
    Function GetXDocument(ByVal part As OpenXmlPart) As XDocument
        Dim xdoc As XDocument

        Using sr As New StreamReader(part.GetStream())
            xdoc = XDocument.Load(XmlReader.Create(sr))
            sr.Close()
        End Using

        Return xdoc
    End Function
End Module

Now we can go back to our main Module1 and add a the ParseOrderDocument method. Notice that I'm using the Extension method we created in the For Each part... loop to return the custom XML as an XDocument. Then I use the child axis property <OrderEntry> (displayed in IntelliSense as I type the query) to see if the element exists. Also notice that since I imported our XML namespace at the top of the file it will only return an <OrderEntry> element in that namespace. So we're safe not to clash with other custom XML that may be added to the document by other processes.

''' <summary>
''' Attempts to parse the word document for order data and returns an order
'''  object with all the required. The document must have a customXML part 
'''  that adheres to the OrderEntry.xsd
''' </summary>
''' <param name="docStream">The document to parse</param>
''' <returns>The order data contained in the document</returns>
''' <remarks></remarks>
Function ParseOrderDocument(ByVal docStream As Stream) As DocumentOrderData
    Dim orderData As DocumentOrderData = Nothing

    Try
        'Use the Open XML SDK to open the document and access parts easily
        Dim wordDoc = WordprocessingDocument.Open(docStream, False)

        Using wordDoc
            'Get the main document part (document.xml)
            Dim mainPart = wordDoc.MainDocumentPart
            Dim docXML As XElement = Nothing

            'Find the order data custom XML part
            For Each part In mainPart.CustomXmlParts
                docXML = part.GetXDocument.<OrderEntry>.FirstOrDefault()
                If docXML IsNot Nothing Then
                    Exit For
                End If
            Next

            If docXML Is Nothing Then
                Throw New InvalidOperationException("This document does not contain order entry data.")
            End If

            'Grab the order data fields from the XML 
            Dim customerID = docXML.<CustomerID>.Value.Trim()
            Dim orderDate = docXML.<OrderDate>.Value.Trim()
            Dim shipper = docXML.<Shipper>.Value.Trim()

            If customerID <> "" AndAlso IsDate(orderDate) Then
                'Create and fill the DocumentOrderData  
                orderData = New DocumentOrderData(customerID, CDate(orderDate), shipper)

                For Each item In docXML.<OrderDetails>.<OrderDetail>
                    'Grab order details data fields
                    Dim product = item.<ProductName>.Value.Trim()
                    Dim quantity = item.<Quantity>.Value.Trim()

                    If product <> "" AndAlso IsNumeric(quantity) Then
                        'Add a new DocumentOrderData.Detail for each product found
                        orderData.Details.Add(New DocumentOrderData.Detail(product, CShort(quantity)))
                    End If

                Next
            End If
            wordDoc.Close()
        End Using

    Catch ex As Exception
        Throw New InvalidOperationException("Could not process this document.", ex)
    End Try

    Return orderData
End Function

Updating the Database through the Data Service

Now that we have our document parsed we're just left with adding the data through our data service.  What we need to do is query the reference data (entities) that we'll need to properly associate on our Order. For instance Order will need a reference to the Customer, the Employee and the Shipper. Then each Order_Detail will need a reference to the Product entity. Notice that we're passing the employee email address into this method so that we can associate the sales rep with the order. If you recall we had to add this field to the Customer and Employee tables in Northwind. (For this test program I'm hard-coding the value but later we'll get this information from the Outlook client when it submits the order to SharePoint.)

Once we have these entities queried and returned from the service we can link them up properly and add our new Order and Order_Details to the data service. For more information on updating data and setting proper linkage to entities returned from an ADO.NET data service read this post and this one.

    ''' <summary>
    ''' Adds a new order through the ADO.NET Data service and sets up all the required
    ''' associations to related entities.
    ''' </summary>
    ''' <param name="docData">The order data</param>
    ''' <param name="employeeEmail">EmailAddress of sales representitve</param>
    ''' <remarks></remarks>
    Private Sub AddNewOrder(ByVal docData As DocumentOrderData, ByVal employeeEmail As String)

        Dim ctx As New NorthwindEntities(New Uri("http://localhost:1234/Northwind.svc/"))
        Dim cust As Customer
        Try
            'Try to retrieve the customer
            cust = (From c In ctx.Customers _
                     Where c.CustomerID = docData.CustomerID).FirstOrDefault()

        Catch ex As Exception
            Throw New InvalidOperationException("Invalid customer ID.")
        End Try

        If cust IsNot Nothing Then

            Dim ship = (From s In ctx.Shippers _
                        Where s.CompanyName = docData.Shipper).FirstOrDefault()

            'Email Address will come from our Outlook client/sales person 
            Dim emp = (From e In ctx.Employees _
                       Where e.EmailAddress = employeeEmail).FirstOrDefault()

            Dim o As New Order()

            o.OrderDate = docData.OrderDate
            o.RequiredDate = Now.AddDays(2)
            o.ShipAddress = cust.Address
            o.ShipCity = cust.City
            o.ShipCountry = cust.Country
            o.ShipName = cust.ContactName
            o.ShipPostalCode = cust.PostalCode
            o.ShipRegion = cust.Region
            o.Freight = 25

            ctx.AddToOrders(o)

            o.Customer = cust
            ctx.SetLink(o, "Customer", cust)

            If ship IsNot Nothing Then
                o.Shipper = ship
                ctx.SetLink(o, "Shipper", ship)
            End If

            If emp IsNot Nothing Then
                o.Employee = emp
                ctx.SetLink(o, "Employee", emp)
            End If

            o.Order_Details = New System.Collections.ObjectModel.Collection(Of Order_Detail)

            For Each item In docData.Details
                Dim productName = item.ProductName.ToLower()
                Dim product = (From p In ctx.Products _
                               Where p.ProductName.ToLower() = productName).FirstOrDefault()

                If product IsNot Nothing Then
                    'Create a detail for each product being ordered
                    Dim detail As New Order_Detail()
                    o.Order_Details.Add(detail)

                    detail.Quantity = item.Quantity
                    detail.UnitPrice = If(product.UnitPrice.HasValue, _
                                          product.UnitPrice.Value, 1D)

                    ctx.AddToOrder_Details(detail)

                    detail.Product = product
                    ctx.SetLink(detail, "Product", product)

                    detail.Order = o
                    ctx.SetLink(detail, "Order", o)

                    ctx.AddLink(o, "Order_Details", detail)
                End If
            Next
            'Saving in Batch mode will update the data inside a database transaction
            'This will throw an exception if the service can't save the Order
            ctx.SaveChanges(Services.Client.SaveChangesOptions.Batch)
        End If
    End Sub
End Module

When we run this program we will see that Customer ALFKI now has a new Order and 4 Order Details entered into the database. Since we're sending the updates in Batch mode this will cause our order data to be properly wrapped in a database transaction.

Next post we'll talk about how we can create a SharePoint workflow to run this code when order documents are added to a SharePoint list. However, if SharePoint is not a requirement of your system (maybe you have no need to collaborate on documents or store this unstructured data) you could easily add this code directly to the Outlook client we built in the previous post.

I updated the sample on Code Gallery with this project so have a look.

Enjoy!

In the last couple posts we've been talking about an Office Business Application (OBA) architecture for the new Northwind Traders and how to expose line-of-business (LOB) data, in our case the Northwind SQL database, using ADO.NET Data Services.

Today we're going to talk about how to build an Outlook Add-In that pulls the order history from the database through our data service when a customer email arrives in the inbox of our sales reps. This allows the sales reps to easily communicate current order status and product inventory with the customer without having to open up another program. That's the idea -- we're choosing to build a solution that is instantly familiar to our users since they already live in Outlook.

We're going to display the order history and inventory information in a WPF control in an adjoining form region so that it displays on the email item itself. It will display when the email opens or is displayed in the preview pane. We could have used a Windows Forms control but WPF is going to look a lot better. We want to match the blue color gradients that Outlook uses so that the control looks built in. Using WPF in Office solutions built with Visual Studio is really easy so let's get started.

The Outlook Add-in Project

I'm going to build upon the Visual Studio Solution that we started in the previous post when we built the data service. This solution also contains a database project that I'm using to keep the change scripts organized. If you recall we needed to add an EmailAddress field to Customers and Employees tables in Northwind. We also added some Customer and Employee data that specify the email addresses we are going to use to test the sample.

So just add a new project to the solution and select Office 2007 Outlook Add-In which is available in Visual Studio 2008 Professional and higher:

Next we need to add a service reference to the data service like I showed here when building a simple console app client. Right-click on the project, select Add Service Reference, click the "Discover" button and name the reference NorthwindService. This will generate the proxy code and entity types used by the data service. It will also add a reference to the System.Data.Services.Client assembly.

Building the Outlook Form Region

Now we'll add the Outlook Form region. Add new item to the project and select Outlook Form Region and name it EmailForm. When you click "Add", a dialog will appear that will ask you if you want to design a new region yourself or if you want to import one. Select "Design a new form region" and click Next. Then it asks you how you want the region to appear, whether it should be on it's own tab or adjoining or a complete replacement. We want to display the order and product information below the customer emails so select adjoining.

When we click next we are asked what the Name of the adjoining region should be -- this is displayed in the separator between the email and our control -- so I have specified "Order History". And since we only want to display order history on emails in read mode, uncheck the compose mode checkbox. Finally we are asked which message classes will display the form region. Keep the default selection which specifies all messages. Click finish and the designer will display an empty Windows Forms user control.

Let's test this out real quick, even though we haven't written any code. Set the Outlook client as the Startup Project and hit F5 and you will see Outlook open. Select any message in your inbox and notice that below the email you will see a blank form region labeled "Order History". However we don't want the form region to display for every email we get, only the Northwind customers. So what we'll do is write some code that checks the Northwind database for any email addresses coming into the inbox to see if they match our customers and only display the region in that case. Back on the EmailForm user control right-click and view code, you will see the following template already set up for you:

Imports Microsoft.Office.Tools.Outlook

Public Class EmailForm
#Region "Form Region Factory"
    <Microsoft.Office.Tools.Outlook.FormRegionMessageClass(FormRegionMessageClassAttribute.Note)> _
    <Microsoft.Office.Tools.Outlook.FormRegionName("NorthwindOutlookClient.EmailForm")> _
    Partial Public Class EmailFormFactory

        ' Occurs before the form region is initialized.
        ' To prevent the form region from appearing, set e.Cancel to true.
        ' Use e.OutlookItem to get a reference to the current Outlook item.
        Private Sub EmailFormFactory_FormRegionInitializing(ByVal sender As Object, _
                                                     ByVal e As FormRegionInitializingEventArgs) _
                                                     Handles Me.FormRegionInitializing
        End Sub
    End Class
#End Region
    'Occurs before the form region is displayed. 
    'Use Me.OutlookItem to get a reference to the current Outlook item.
    'Use Me.OutlookFormRegion to get a reference to the form region.
    Private Sub EmailForm_FormRegionShowing(ByVal sender As Object, _
                                      ByVal e As System.EventArgs) Handles MyBase.FormRegionShowing
    End Sub

    'Occurs when the form region is closed.   
    'Use Me.OutlookItem to get a reference to the current Outlook item.
    'Use Me.OutlookFormRegion to get a reference to the form region.
    Private Sub EmailForm_FormRegionClosed(ByVal sender As Object, _
                                     ByVal e As System.EventArgs) Handles MyBase.FormRegionClosed
    End Sub
End Class

The EmailFormFactory nested class is what creates the EmailForm and it gives us a chance to cancel showing the region. Here's where we need to write some code that gets the email address from the mail item and checks the database through the data service. In the FormRegionShowing event handler of the EmailForm we will pull down the Order History based on the customer address along with the Order_Details and related Products. Since we're going to need to get the EmailAddress as well as connect to the same data service in both event handlers, a good place to write global code is in the ThisAddIn class which is automatically created for you when you create the Add-in project.

Double-click on ThisAddIn in the Solution Explorer to open the code. You will see StartUp and ShutDown event handlers for the Add-In automatically generated for you. We don't need to add any code into these handlers. All we need to do is add a public property to the service reference for our data service as well as a function to extract the SMTP email address from the mail item. Note that I added an application setting to specify the location of the data service. During development this is going to be http://localhost:1234:/Northwind.svc but when we deploy the application we'll change it to the location of our production service:

Imports NorthwindOutlookClient.NorthwindService

Public Class ThisAddIn

    Private _ctx As NorthwindEntities
    Public ReadOnly Property DataServiceContext() As NorthwindEntities
        Get
            If _ctx Is Nothing Then
                'Specify the Service URI in the app.config via project settings.
                _ctx = New NorthwindEntities(New Uri(My.Settings.ServiceURI))
            End If
            Return _ctx
        End Get
    End Property

    ''' <summary>
    ''' Extracts the SMTP email address from the mail item.
    ''' </summary>
    ''' <param name="mailItem">the Outlook mail item</param>
    ''' <returns>The SMTP email address, otherwise empty string</returns>
    ''' <remarks></remarks>
    Public Function GetEmailAddress(ByVal mailItem As Outlook.MailItem) As String
        Dim recip As Outlook.Recipient
        Dim exUser As Outlook.ExchangeUser
        Dim emailAddress As String = ""

        Try
            If mailItem IsNot Nothing AndAlso mailItem.SenderEmailType IsNot Nothing Then
                ''get SMTP Address for the sender of this email
                If mailItem.SenderEmailType.ToLower = "ex" Then
                    recip = Globals.ThisAddIn.Application.GetNamespace("MAPI") _
                            .CreateRecipient(mailItem.SenderEmailAddress)

                    exUser = recip.AddressEntry.GetExchangeUser()
                    emailAddress = exUser.PrimarySmtpAddress
                Else
                    emailAddress = mailItem.SenderEmailAddress
                End If
            End If

        Catch ex As Exception
            emailAddress = ""
        End Try
        Return emailAddress
    End Function

    Private Sub ThisAddIn_Startup(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) Handles Me.Startup
    End Sub

    Private Sub ThisAddIn_Shutdown(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) Handles Me.Shutdown
    End Sub
End Class

Now back in the EmailFormFactory we can add this code to check if we should even be displaying the form region at all. If we don't want to show it, just set e.Cancel =True:

' Occurs before the form region is initialized.
' To prevent the form region from appearing, set e.Cancel to true.
' Use e.OutlookItem to get a reference to the current Outlook item.
Private Sub EmailFormFactory_FormRegionInitializing(ByVal sender As Object, _
                                                    ByVal e As FormRegionInitializingEventArgs) _
                                                    Handles Me.FormRegionInitializing
    Dim emailAddress As String = ""
    Dim ctx = NorthwindOutlookClient.Globals.ThisAddIn.DataServiceContext
    Try
        Dim mailItem = TryCast(e.OutlookItem, Outlook.MailItem)
        emailAddress = NorthwindOutlookClient.Globals.ThisAddIn.GetEmailAddress(mailItem)

        If emailAddress <> "" Then
            'Make sure the email address is actually in the database
            Dim customer = (From c In ctx.Customers _
                            Where c.EmailAddress = emailAddress).FirstOrDefault()

            If customer Is Nothing Then
                emailAddress = ""
            End If
        End If
    Catch ex As Exception
        'TODO: Error logging
        emailAddress = ""
    End Try

    e.Cancel = (emailAddress = "")
End Sub

Note that a service call could experience unexpected delays depending on where the data service is located. We may want to consider just displaying the form region and writing the calls to the service asynchronously. This is possible using the ADO.NET Data Services client as described in this article. For now, we'll keep this OBA sample simple and assume that the data service is located somewhere on our intranet.

Now that we have this code in place we can write the code that will specify a LINQ query to retrieve the order history in the EmailForm's FormRegionShowing handler. Notice I'm using the ".Expand" syntax in the query to pull in the related entities on the Order. Then I'm pulling the Product entity down for each of the Order_Detail entities so I can display inventory information. (We may want to consider only pulling orders within a date range for better scalability but here I'm pulling all of them down for the specified customer for simplicity.)

'Occurs before the form region is displayed. 
'Use Me.OutlookItem to get a reference to the current Outlook item.
'Use Me.OutlookFormRegion to get a reference to the form region.
Private Sub EmailForm_FormRegionShowing(ByVal sender As Object, _
                ByVal e As System.EventArgs) Handles MyBase.FormRegionShowing

    Dim emailAddress As String = ""
    Dim ctx = NorthwindOutlookClient.Globals.ThisAddIn.DataServiceContext
    Try
        Dim mailItem = TryCast(Me.OutlookItem, Outlook.MailItem)
        emailAddress = NorthwindOutlookClient.Globals.ThisAddIn.GetEmailAddress(mailItem)

        If emailAddress <> "" Then
            'pull the order history from the database
            Dim customerOrders = From o In ctx.Orders.Expand("Order_Details").Expand("Customer") _
                              Where o.Customer.EmailAddress = emailAddress _
                              Order By o.OrderDate Descending

            For Each order In customerOrders
                For Each detail In order.Order_Details
                    ctx.LoadProperty(detail, "Product")
                Next
            Next

            Dim ordersList As New List(Of Order)(customerOrders)
            'TODO: Set this ordersList as the DataContext of our WPF user control

        End If
    Catch ex As Exception
        'Could not connect to the service
        'TODO: Error logging
    End Try
End Sub

You also should notice that we still need to pass this list of Order entities that were returned from the data service into our WPF control -- but first we need to create it!

Building the WPF User Control

So let's add a new WPF User control via Add New Item (listed under WPF) -- I named it OrderHistory. This will show the WPF designer. The WPF User Control simply specifies just the UI in XAML of what the control should look like and sets up the data binding to the properties on our entities.

Here we've set up two ListViews that display Order and Order_Details and below that the Product inventory information along with an Image Control showing off the new Northwind logo. This control is really easy to data bind because of how the entity associations are specified in our data model. Order has Order_Details collection and Order_Detail has a Product reference. When the WPF User Control's DataContext property is set to the list of Orders, the data binding will take care of displaying the related data properly. Here's how we set up the data bound controls (styles omitted for clarity):

<UserControl x:Class="OrderHistory"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Height="300" Width="541" Name="OrderList" Grid.IsSharedSizeScope="True">
    <UserControl.Resources>
        <Style ...

    </UserControl.Resources>
<Grid>
<Grid.RowDefinitions>
    <RowDefinition Height="120" />
    <RowDefinition Height="160*" />
</Grid.RowDefinitions>
       
<ListView IsSynchronizedWithCurrentItem="True" 
          ItemsSource="{Binding}" Name="ListViewOrder" Margin="0,0,0,6" 
          Style="{StaticResource ListViewStyle}">
    <ListView.View>
        
        <GridView ColumnHeaderContainerStyle="{StaticResource ColumnHeaderStyle}">
            <GridViewColumn DisplayMemberBinding="{Binding Path=ShipName}" 
                            Width="200">Ship to</GridViewColumn>
            <GridViewColumn DisplayMemberBinding="{Binding Path=OrderDate, StringFormat='d'}" 
                            Width="100">Order Date</GridViewColumn>
            <GridViewColumn DisplayMemberBinding="{Binding Path=ShippedDate, StringFormat='d'}" 
                            Width="100">Ship Date</GridViewColumn>
            <GridViewColumn DisplayMemberBinding="{Binding Path=OrderTotal, StringFormat='c2'}" 
                            Width="100">Order Total</GridViewColumn>
        </GridView>
    </ListView.View>
</ListView>
    <Grid Grid.Row="1" Margin="0" Name="DetailsGrid" 
          DataContext="{Binding Path=Order_Details}">
        <ListView ItemsSource="{Binding}" Style="{StaticResource ListViewStyle}"
                  IsSynchronizedWithCurrentItem="True" Name="ListViewDetails" Margin="0,0,0,81">
            <ListView.View>
                <GridView ColumnHeaderContainerStyle="{StaticResource ColumnHeaderStyle}">
                    <GridViewColumn Header="Product" Width="300" 
                                    DisplayMemberBinding="{Binding Path=Product.ProductName}" />
                    <GridViewColumn Header="Quantity" Width="100" 
                                    DisplayMemberBinding="{Binding Path=Quantity, StringFormat='n0'}" />
                    <GridViewColumn Header="Unit Price" Width="100" 
                                    DisplayMemberBinding="{Binding Path=UnitPrice, StringFormat='c2'}" />
                </GridView>
            </ListView.View>
        </ListView>
        <Image Height="75" HorizontalAlignment="Right" Name="Image1" 
               Stretch="None" VerticalAlignment="Bottom" Width="75" 
               Source="northwindlogo75.gif" SnapsToDevicePixels="True" 
               StretchDirection="DownOnly" MinHeight="75" MinWidth="75" 
               MaxHeight="75" MaxWidth="75" ClipToBounds="True" />
        <StackPanel Height="75" Name="StackPanel1" VerticalAlignment="Bottom" Margin="0,0,81,0">
            <TextBlock Height="25" Name="txtProductName" Width="Auto"
                       Text="{Binding Path=Product.ProductName, 
                                      StringFormat=Inventory information for \{0\}}" />
            <TextBlock Height="25" Name="txtUnitsInStock" Width="Auto" 
                       Text="{Binding Path=Product.UnitsInStock, 
                                      StringFormat=Units in stock: \{0:n0\}}" />
            <TextBlock Height="25" Name="txtUnitsOnOrder" Width="Auto" 
                       Text="{Binding Path=Product.UnitsOnOrder, 
                                      StringFormat=Units on order: \{0:n0\}}"/>
        </StackPanel>
    </Grid>
</Grid>
</UserControl>

Notice that there is a field I'm binding to called OrderTotal on the Order entity however if you go back to the data model (Northwind.edmx in the NorthwindService project) you won't see this field coming from the database. That's because it's a calculated property on the Order entity on the client. You can extend the entities on the client using Partial Classes. All you have to do is right click on the project and select Add-->Class, call it Order, and then make sure you specify the proper namespace of the service reference:

Imports NorthwindOutlookClient.NorthwindService

Namespace NorthwindService

    Partial Public Class Order

        Private _total As Decimal = 0
        Public ReadOnly Property OrderTotal() As Decimal
            Get
                If _total = 0 Then
                    _total = Aggregate detail In Me.Order_Details _
                                Into Sum(detail.Quantity * detail.UnitPrice)                    
                End If
                Return _total
            End Get
        End Property
    End Class

End Namespace

Now that we have our WPF control all designed we are ready to add it to our form region. EmailForm is a Windows Forms user control so in order to add the WPF user control we need to first add an ElementHost control. This is found in the WPF Interoperability Tab on your toolbox. When you drop this control onto the designer you can immediately select the WPF user control to display, in our case it shows the OrderHistory control we just designed (if you don't see it, build the project first).

I also set it to dock in the parent container so it takes up the entire region and set the font of the EmailForm region to Calibri 11. Lastly we can add the line of code back in our EmailForm_FormRegionShowing handler to set the list of Orders we returned from our data service to the DataContext of the WPF user control:

Dim ordersList As New List(Of Order)(customerOrders)
'Set this ordersList as the DataContext of our WPF user control
Me.OrderHistory1.DataContext = ordersList

That should do it! To test this you need to make sure the email address you'll be using is in the database so you'll have to add some test data. When we run it we should see the adjoining form region at the bottom of our customer emails (click to enlarge):

One tip to remove the Outlook Add-in when you're done debugging is to select Build --> Clean Solution menu item in Visual Studio to unregister the Add-In.

I've uploaded the code that we've talked about so far in this series to Code Gallery so have a look. I'll continue adding releases to this as we build the other pieces of the OBA. Next post I'll show how we can create a purchase order in Word 2007 and use that to automatically feed Order data into the Northwind database.

Enjoy!

Last post I talked about the high-level architecture of our Office Business Application for the new Northwind Traders. There are a lot of different architecture options to consider when building an OBA. OBA is all about using Microsoft Office with your Line of Business (LOB) data. Whether that involves using SharePoint as well depends on the application. Since we wanted to store the unstructured data (the Northwind customer P.O.) SharePoint is a good fit here.

There are a lot of options when thinking about how to expose your LOB data. For instance, you may already have a service oriented architecture at the enterprise that exposes data contracts and processes that you can consume from Office clients. Or maybe you have a small business and have decided to expose a simple service that returns and consumes n-tier DataSets directly. Or you already have a custom LOB data entry system using custom business objects and you want to reuse the business layer in the Office client. OBA doesn't dictate how you expose this data. Because you can consume data in Office clients the same way you do in Windows apps the same types of decisions need to be made.

When we sat down to write the new Northwind Traders application we thought about how our data would need to behave and what would be the best way for all the pieces to easily update and query the Northwind database. Because there was only going to be simple validations needed on the data and mostly CRUD operations we opted to expose an Entity Data Model via ADO.NET Data Services like I showed before. This allowed us to get a secure service up and running in minutes.

We did make some minor changes to our old friend, the Northwind database. First, since we wanted to be able to look up order history for a customer when they emailed the sales reps, we needed to add an EmailAddress field to the Customers table (amazing that we didn't have that field before!). We also added it to the Employees table.

ALTER TABLE dbo.Customers ADD
    EmailAddress varchar(50) NULL
GO
ALTER TABLE dbo.Employees ADD
    EmailAddress varchar(50) NULL
GO

Then we populated the data with some customers and employees that were actually folks on our team because we need real email addresses to work with :-)

Next I created a new ASP.NET Web Application and added an ADO.NET Data Service and an Entity Data Model just like how I showed in this post. (You will need Visual Studio 2008 Service Pack 1 in order to get these new item templates.) For testing we set the service to allow full access to all the entities in the model  -- we'll lock it down later. I also am passing detailed errors which we won't want to do once we're in production:

Public Class Northwind
    Inherits DataService(Of NorthwindEntities)

    ' This method is called only once to initialize service-wide policies.
    Public Shared Sub InitializeService(ByVal config As IDataServiceConfiguration)
        config.SetEntitySetAccessRule("*", EntitySetRights.All)
        config.UseVerboseErrors = True
    End Sub
End Class

One thing we did want to do is set up our data model so that it enforced constraints (i.e. there cannot be an Order without a Customer) but since some of our legacy data didn't specify all of these constraints we made the changes to the model instead, so that the integrity on all new data would be enforced through the service. This is often the case in projects, you cannot change the legacy databases but you still need to work with proper data models. So we changed the EDM so that all the entities were singular and not plural (Customer instead of Customers, Order instead of Orders, etc). We also changed the associations so that they were enforced and so that one to many collections were plural and the one-to-one were singular (i.e. Order has Order_Details collection and Order_Detail has Product reference). You can modify these from the Properties window of the Entity Data Model Designer.

Once I have the model and the data service code set up we can hit F5 and navigate our browser to the Northwind.svc and test the call to pull up all the customers (i.e. http://localhost:1234/Northwind.svc/Customers) just like how I showed in this post.

Now that we have our data exposed as a data service we can build the Office clients to interact with it just like I showed before here when we built a simple Excel client. Next post I'll show how we can use WPF controls in an Outlook Add-In in order to display the customer order history by querying the data through the data service.

Until next time...

The last week I've been working with a couple teammates on an Office Business Application (OBA) demonstration we wanted to put together for TechReady 8. TechReady is an internal conference we deliver to the field employees. It's my first one and so far it's been pretty fun. This morning Scott Hanselman hosted an "UnKeynote" that showcased a lot of the new stuff coming in Visual Studio 2010 that I can't talk about yet but believe me it was cool. ;-)

The application I helped build with Rachel and Mike on my team was a total blast. We wanted to demonstrate using Office as a client to Line-of-Business data as well as show some of the nifty features of SharePoint workflows. So we decided it was time to bring poor Northwind Traders into the 21st century. Here's what we came up with:

We needed a way to easily and securely expose our LOB data -- the Northwind database -- to the enterprise. So we created an entity data model against our SQL Server Northwind database and then exposed it using ADO.NET Data Services . The Northwind sales reps do all of their work in Outlook, communicating with our high-volume customers. So we built an Outlook Add-in that they can use to look up the customer order history from the database and easily work with purchase orders. The Add-in displays a form region with the customer order history in an adjoining area below when they open the email.

The orders are then submitted to a SharePoint document library as Word 2007 documents with XML data specified via content controls. A SharePoint Workflow then shreds this document data (using VB XML literals support and the Open XML SDK) and passes it to the ADO.NET data Service which persists the data in the database. This was written as a SharePoint sequential workflow.

We also built an Excel client for our shipping department to use that displays the orders that are ready to ship which allows them to update the ship date and save it back to through the data service. It also shows the products and quantities ordered in an Action Pane and does some data analysis using a pivot table and pie chart to show the breakdown of shipping cities and freight cost. We also used WPF user controls in the Outlook and Excel clients so that we could match the look-and-feel of Office.

Finally we created a web part for the SharePoint site that displays key performance indicators like oversold products and our top customer sales.

I have to say it was a very fun project to work on and I personally learned a lot about Excel and WPF as well as the OBA architecture. We spend about 3 days building this and I was pleasantly surprised how well the tools worked together. The pieces we built were predominantly in Visual Studio 2008 by Rachel and I and Mike handled the bleeding edge SharePoint parts.

In the next few blog posts I'll go into the details of building WPF user controls in the Excel and Outlook clients and how we exposed a simple, clean data model against the not-so-clean Northwind database. I'll also show the technique of how we easily stripped the order data out of the Word 2007 documents.

For now, some good links on OBA and VSTO development:

• Office Development Videos
• Office Development with Visual Studio
• ADO.NET Data Services - Building an Excel Client
• Office Development with Visual Studio Team Blog

Until next time....

UPDATE - Here is the full set of articles and code: http://code.msdn.microsoft.com/OBANorthwind