OBA Part 2 - Building and Outlook Client against LOB Data

 

OBA Part 2 - Building and Outlook Client against LOB Data

Published 07 February 09 02:36 PM

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 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 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 and the new Northwind logo that I added to the project as a Resource. 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 Order Total 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
                    If Me.Order_Details IsNot Nothing Then
                        For Each detail In Me.Order_Details
                            _total += (detail.Quantity * detail.UnitPrice)
                        Next
                    End If
                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. 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!

by Beth Massi

Beth Massi - Sharing the goodness that is VB : OBA Part 2 - Building and Outlook Client against LOB Data