ADO.NET Data Services - Building an Excel Client

Last few posts I've been building a WPF client against ADO.NET Data Services, if you missed them:

• Using ADO.NET Data Services
• ADO.NET Data Services - Building a WPF Client
• ADO.NET Data Services - Enforcing FK Associations and a Fix for Deleting Entities
• ADO.NET Data Services - Intercepting Queries and Adding Validation

Today I want to show a simple Excel client that queries our service and allows us to edit the customers in Northwind. I'm going to use Visual Studio 2008 (VSTO) to build a document-level customization against an Excel 2007 Workbook. We'll also build an Actions Pane that allows us to query and save customers to our ADO.NET data service. Excel's tabular data format lends itself well to CRUD database operations when we Add, Delete and Update rows in a Sheet. (NOTE: You cannot create Office solutions in Visual Studio Express editions.)

Setting Permissions on Customers

The first thing we need to do on our data service is allow access to the Customers entity set on our data model. In the first article I showed how to set up our service and permissions. For this example we'll be building upon the same data service we used for the WPF client which works with Product and Categories so I'll just need to add access to Customers:

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("Products", EntitySetRights.All)
        config.SetEntitySetAccessRule("Categories", EntitySetRights.AllRead)
        config.SetEntitySetAccessRule("Customers", EntitySetRights.All)
    End Sub
...

Creating an Excel Client

Next we'll add the Excel 2007 project to the same solution we've been working on to make things simple for debugging. However, it's important to note that when we want to deploy the Excel (or WPF) client we should place the project into it's own solution. (For more information on deploying Office solutions, read this article.) You would also need to deploy the ADO.NET Data Service just like any other WCF Service to an IIS server first before deploying any clients.

Right-click on the solution and Add --> New Project then select the Office --> 2007 category on the left and choose the Excel 2007 Workbook project.

Next add the service reference to the data service by right-clicking on the Excel project and selecting Add Service Reference. This is exactly how we added the service reference to our WPF client which generates the client proxy and entity types for us.

Now add a New Item and select Actions Pane Control. This will open up the designer and we can add Windows Forms or WPF controls onto the pane. If we want to add WPF controls then first create a WPF user control and then add an ElementHost onto the pane and then you can specify which WPF user control to display. In this example all I'm going to need is a label, a couple textboxes and a couple buttons so I'm going to just use Windows Forms controls, nothing fancy here. The pane will allow the user to specify a filter on country and find customers that match as well as save changes back. I also added a multi-line, readonly textbox for status messages below the buttons. We'll come back to the code for this actions pane after we set up our data source. The data source will be the list of customer entities returned from the service.

Tracking Changes on Customer Entities

Our actions pane is going to control how we query and save the customers. But instead of interacting with the service client proxy directly I'm going to create a class that inherits from a BindingList(Of Customer) so that we can also encapsulate the tracking of adds, updates and deletes of the customers that we bring down from the service. This class will also have a method to execute a customer query filtered by a specified country as well as a method to save changes. Finally we'll need to override some BindingList methods that will allow us to tell the data service client whether to add, update or delete as the customer entities are added to, updated or removed from the list.

Imports ExcelClient.NorthwindService
Imports System.ComponentModel

Public Class MyCustomerList
    Inherits BindingList(Of Customer)

    Private DataServiceContext As New NorthwindEntities(New Uri("http://localhost:1234/Northwind.svc"))
    Private _isAdding As Boolean
    Private _isSaving As Boolean
    Private _hasChanges As Boolean

    Public ReadOnly Property HasChanges() As Boolean
        Get
            Return _hasChanges
        End Get
    End Property

    ''' <summary>
    ''' Queries the customers from the ADO.NET Data Service 
    ''' and returns the number of customers returned in the results
    ''' </summary>
    ''' <param name="country">The country to use in the query filter</param>
    ''' <returns>number of customers returned from the service</returns>
    ''' <remarks></remarks>
    Public Function FillCustomersByCountry(ByVal country As String) As Integer
        Dim i = 0
        Dim customers = From c In DataServiceContext.Customers _
                        Where c.Country = country _
                        Order By c.CustomerID

        For Each c In customers
            Me.Add(c)
            i += 1
        Next
        Return i
    End Function

    ''' <summary>
    ''' Saves the customer changes back to the service.
    ''' </summary>
    ''' <returns>true if save was successful, otherwise an exception is thrown</returns>
    ''' <remarks></remarks>
    Public Function SaveChanges() As Boolean
        Dim saved As Boolean
        Try
            _isSaving = True
            Me.DataServiceContext.SaveChanges()
            _hasChanges = False
            saved = True
        Finally
            _isSaving = False
        End Try

        Return saved
    End Function

    Protected Overrides Function AddNewCore() As Object
        _isAdding = True
        Return MyBase.AddNewCore()
    End Function

    Protected Overrides Sub RemoveItem(ByVal index As Integer)
        Dim c = Me(index)
        Me.DataServiceContext.DeleteObject(c)
        _hasChanges = True
        MyBase.RemoveItem(index)
    End Sub

    Protected Overrides Sub OnListChanged(ByVal e As System.ComponentModel.ListChangedEventArgs)
        If Not _isSaving Then
            'The Customer partial class will need to implement 
            ' INotifyPropertyChanged for this will work
            If e.ListChangedType = ListChangedType.ItemChanged Then
                If _isAdding Then
                    Me.DataServiceContext.AddToCustomers(Me(e.NewIndex))
                    _isAdding = False
                Else
                    Me.DataServiceContext.UpdateObject(Me(e.NewIndex))
                End If
                _hasChanges = True
            End If
            MyBase.OnListChanged(e)
        End If
    End Sub

End Class

In order for the OnListChanged event to fire when a property on a customer entity is changed we will need to extend the partial class generated on the client. We'll need to Implement INotifyPropertyChanged and overwrite some partial methods just like we did to our product entity in the last post when we added validation. It's important to make sure the partial class is in the same namespace as the client service reference.

Imports ExcelClient.NorthwindService
Imports System.ComponentModel

Namespace NorthwindService

    Partial Public Class Customer
        Implements INotifyPropertyChanged

        Public Event PropertyChanged(ByVal sender As Object, ByVal e As PropertyChangedEventArgs) _
                     Implements INotifyPropertyChanged.PropertyChanged

        Private Sub FirePropertyChanged(ByVal propertyName As String)
            If propertyName <> "" Then
                RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
            End If
        End Sub

        Private Sub OnAddressChanged()
            FirePropertyChanged("Address")
        End Sub

        Private Sub OnCityChanged()
            FirePropertyChanged("City")
        End Sub

        Private Sub OnCompanyNameChanged()
            FirePropertyChanged("CompanyName")
        End Sub

        Private Sub OnContactNameChanged()
            FirePropertyChanged("ContactName")
        End Sub

        Private Sub OnContactTitleChanged()
            FirePropertyChanged("ContactTitle")
        End Sub

        Private Sub OnCountryChanged()
            FirePropertyChanged("Country")
        End Sub

        Private Sub OnCustomerIDChanged()
            FirePropertyChanged("CustomerID")
        End Sub

        Private Sub OnFaxChanged()
            FirePropertyChanged("Fax")
        End Sub

        Private Sub OnPhoneChanged()
            FirePropertyChanged("Phone")
        End Sub

        Private Sub OnPostalCodeChanged()
            FirePropertyChanged("PostalCode")
        End Sub

        Private Sub OnRegionChanged()
            FirePropertyChanged("Region")
        End Sub
       
    End Class
End Namespace

Binding an Excel Sheet to the Customer List

Now that we have a way to track our customer entities we can work with them on an Excel Sheet by binding them to a ListObject control. First open up the data sources window (on the main menu select Data --> Show Data Sources) and then add a new data source and select the Object data source type. Then expand the ExcelClient.NorthwindService namespace and select the Customer object. Now open up Sheet1 in the project to open the Excel designer and drag the Customer onto the Sheet, this will create a ListObject control that is bound to a CustomerBindingSource.

Right-click on the sheet and View Code. All we need to do is set the CustomerBindingSource.DataSource property to an instance of our MyCustomerList. I'm going to make the list a public field of the Sheet so the Actions Pane can interact with it easily.

Public Class Sheet1

    Public CustomerList As New MyCustomerList()

    Private Sub Sheet1_Startup() Handles Me.Startup
        Me.CustomerBindingSource.DataSource = Me.CustomerList
    End Sub

End Class

Hooking Up the Actions Pane

In order to add an Actions Pane to an Excel Workbook we need to open up the Workbook code-behind so double-click on ThisWorkbook in the project and add the pane to the ActionPane.Controls collection like so:

Public Class ThisWorkbook

    Public CustomerActions As New CustomerActionsPane()

    Private Sub ThisWorkbook_Startup() Handles Me.Startup
        Me.ActionsPane.Controls.Add(Me.CustomerActions)
    End Sub

End Class

Now back in the CustomerActionsPane we can write code to interact with the CustomerList on Sheet1. We'll just add some code to the click event handlers of our buttons. First the Find button click handler will attempt to grab a reference to the CustomerList on Sheet1 and then it will call the FillCustomersByCountry method we created:

Public Class CustomerActionsPane

    Private Sub cmdFind_Click() Handles cmdFind.Click
        Dim msg = ""
        Try
            Dim searchTerm = Me.txtSearchTerm.Text.Trim()
            If searchTerm <> "" Then

                Dim list = ExcelClient.Globals.Sheet1.CustomerList
                Dim count = list.FillCustomersByCountry(searchTerm)

                msg = String.Format("{0} customers returned from server.", count)
            Else
                msg = "Please enter a country filter."
            End If

        Catch ex As Exception
            msg = ex.ToString
        End Try
        Me.txtStatus.Text = msg
    End Sub
...

Handling Exceptions from the Data Service

Next we need to write the code for our Save button click handler. Here we need to anticipate exceptions coming back from our database. By default we don't expose verbose exception details from our data service because this could give malicious hackers clues into the architecture of our service or worse our database structure. So what we get back from the ADO.NET data service is a DataServiceRequestException with a generic message. You can add additional information to this exception by overriding the HandleException method on the data service itself. For instance we could do something like this to return database exceptions:

Public Class Northwind
    Inherits DataService(Of NorthwindEntities)

    ' This method is called only once to initialize service-wide policies.
    Public Shared Sub InitializeService(...

    Protected Overrides Sub HandleException(ByVal args As HandleExceptionArgs)

        If args.Exception.InnerException IsNot Nothing Then
            If TypeOf args.Exception.InnerException Is SqlClient.SqlException Then
                'If we have config.UseVerboseErrors = True in the InitializeService
                ' then the InnerException (last parameter) will be serialized in detail
                ' including SQL message and full stack trace otherwise just the generic 
                ' message below will be returned. 
                args.Exception = New DataServiceException(args.ResponseStatusCode, _
                        TypeName(args.Exception.InnerException), _
                        "The database could not save the data. Check constraints.", _
                        "en-US", _
                         args.Exception.InnerException)

            End If
        End If
        'TODO: Log the exception
    End Sub
...

The message that is returned, however, is an XML document which contains different information depending on if we have config.UseVerboseErrors set to True in the InitializeService method or not. If we're not (the default) then the XML document returned from this handler would look like:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <code>SqlException</code>
    <message xml:lang="en-US">
         The database could not save the data. Check constraints.
    </message>
</error>

If we did have config.UseVerboseErrors = True in the InitializeService then the XML document would be:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <code>SqlException</code>
    <message xml:lang="en-US">
        The database could not save the data. Check constraints.
    </message>
    <innererror xmlns="xmlns">
        <message>
            The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Customers"...
        </message>
        <type>System.Data.SqlClient.SqlException</type>
        <stacktrace>
            at System.Data.SqlClient.SqlConnection.OnError(SqlException...full stack trace...
        </stacktrace>
    </innererror>
</error>

It's definitely not a good idea to return database details in error messages so make sure your production code does not have UseVerboseErrors set to True.

So back on our actions pane we can hook up the Save button click handler and write a few lines of code to extract any message from the XML. First we need to add an XML namespace Import because the error document declares a default namespace as seen above.

Imports System.Data.Services.Client
Imports <xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

Public Class CustomerActionsPane

    Private Sub cmdFind_Click(...

    Private Sub cmdSave_Click() Handles cmdSave.Click
        Dim msg = "Customers could not be saved." & vbCrLf
        Try
            Dim list = ExcelClient.Globals.Sheet1.CustomerList

            If list.SaveChanges() Then
                msg = "Customers saved."
            End If

        Catch ex As DataServiceRequestException When ex.InnerException IsNot Nothing
            Try
                Dim xmlError = XElement.Parse(ex.InnerException.Message)
                msg &= xmlError.<message>.Value

            Catch ex1 As Exception 'message isn't xml, so display string
                msg &= ex.InnerException.ToString()
            End Try

        Catch ex As Exception
            msg &= ex.ToString
        End Try

        Me.txtStatus.Text = msg
    End Sub
End Class

Running the Excel Client

Now that we've got all our code written let's open up Fiddler and SQL Profiler and run this baby. After the Actions Pane opens we can click the Find button and the Customer list is populated and we can see it on our Sheet. From here we can add a new row or modify rows and the changes will be tracked in our list. We can also delete rows as well by right-clicking on the row and selecting Delete. (click image to enlarge)

Slick! Note that we will trigger a constraint violation if we attempt to delete a Customer that has Orders and this will cause our ExceptionHandler to return a generic message that is displayed in the status message area. We're not doing it in this example but you can opt to continue processing the rest of the changes by indicating that option in the SaveChanges method. In that case you'll get a DataServiceRespons object back that you can inspect for errors on the offending submissions. I'll expand on that in a follow-up post.

I've added a new Code Gallery sample so you can download it and play with the Excel client. It also includes the WPF client we built in the previous posts.

Enjoy!