OBA Part 1 - Exposing Line-of-Business Data

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. https://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...

Comments

• Anonymous
  February 05, 2009
  PingBack from http://stevepietrek.com/2009/02/05/links-252009/

• Anonymous
  February 07, 2009
  In the last couple posts we've been talking about an Office Business Application (OBA) architecture for

• Anonymous
  February 07, 2009
  In the last couple posts we've been talking about an Office Business Application (OBA) architecture

• Anonymous
  February 12, 2009
  In my last few posts we've been building a simple Office Business Application (OBA) for the new Northwind

• Anonymous
  February 12, 2009
  In my last few posts we've been building a simple Office Business Application (OBA) for the new Northwind

• Anonymous
  February 19, 2009
  Beth Massi has been busy creating a number of great posts describing how to build add-ins to Excel, Outlook,

• Anonymous
  February 23, 2009
  The comment has been removed

• Anonymous
  March 08, 2009
  In my last few app building posts we've been building a simple Office Business Application (OBA) for

• Anonymous
  March 08, 2009
  In my last few app building posts we've been building a simple Office Business Application (OBA)

• Anonymous
  March 10, 2009
  I just started vb programming at my work. We use visual studio in our office for programming. I just love your web page and blogs. May be one day I want to be knowledgeable just like you. thanks Faiehul haq

• Anonymous
  April 21, 2009
  In my last few app building posts we've been building a simple Office Business Application (OBA) for