Business Data Catalog (BDC) and WCF

I’m going to post a number of articles to show how we can use some exciting technologies. Our end goal will be to display some information from a Business Data Catalog application in a sharepoint page, but using some nice ajax and jQuery functionality to make it nice and sexy.

This first article will cover how to make our data available to be used in an ajax way. Unfortunately the BDC does not have any web services available out of the box that presents you with data from applications defined within it. It has the BDC object model of course, but for a nice ajax type web part we are going to want to supply our data as JSON and then be rendered out by client functionality. So we’re going to have to write our own BDC web services.

Pre-reqs

1, Visual Studio 2008 SP1

2, MOSS 2007 Enterprise Edition

3, The adventureworks2000 sql server database.

4, .NET Framework 3.5 has been installed on your SharePoint Server.

We will make use of Products table in the adventureworks2000 database and we’re only going to display in our web part the columns:
ProductId
Name
ProductNumber
ListPrice

Go ahead and create an application definition file based on the Products table. If you are unsure how to do this check out the Business Data Catalog – getting started tutorial.

Once you have your BDC application definition file imported we are ready to get on with building the WCF project.

1, Open up Visual Studio 2008 and select to create a Class Library project called ProductDisplay. The actual class library project we will be making use of later when we build our web part.

2, Right click on the main Solution and choose to Add –> New Project…

3, Select the Web project type, and then the WCF Service Application. Give the WCF Service Application a name of ProductService

4, In the project that is created for you delete the files IService1.cs and Service1.svc and create a new File of type Ajax Enabled WCF Service – name this file BDCService.svc

5, Open up BDCService.svc.cs. We want to define our Product class that we are going to use to pass data around in a JSON format. Outside of the existing BDCService class, add the following code:

[DataContract]
    public class Product
    {
        [DataMember]
        public int ProductId
        {
            get;
            set;
        }

        [DataMember]
        public string Name
        {
            get;
            set;
        }

        [DataMember]
        public string ProductNumber
        {
            get;
            set;
        }

        [DataMember]
        public double ListPrice
        {
            get;
            set;
        }
    }

 

 

6, Now we actually want to write our WCF method to return List<Product>. Remove the existing DoWork method that is contained within the BDCService class.

7, Before we can start writing some code using the BDC object model we need to add in the relevant references. Right click on the References folder in the solution explorer and choose Add Reference.

8, Browse to c:program filescommon filesmicrosoft sharedweb server extensions12ISAPI

and add the Microsoft.Office.Server.dll and microsoft.sharepoint.portal.dll

9, Add these important Using statements to the top of your code to make our lives easier:

using System.Collections.Generic;
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;

10, Now we can create our WCF method that is going to return our list of products. Add the following code in your BDCService class:

[OperationContract]
        public List<Product> GetBusinessData()
        {
            List<Product> products = new List<Product>();

            NamedLobSystemInstanceDictionary sysInstances = ApplicationRegistry.GetLobSystemInstances();
            LobSystemInstance instance = sysInstances[“AdventureWorks2000Instance”];

            Entity prodEntity = instance.GetEntities()[“dbo.Product”];
            FilterCollection fc = prodEntity.GetFinderFilters();
            IEntityInstanceEnumerator enumerator = prodEntity.FindFiltered(fc, instance);

            while (enumerator.MoveNext())
            {
                Product p = new Product();
                IEntityInstance entityInstance = enumerator.Current;
                p.ProductId = Convert.ToInt32(entityInstance[“ProductID”]);
                p.Name = entityInstance[“Name”].ToString();
                p.ProductNumber = entityInstance[“ProductNumber”].ToString();
                p.ListPrice = Convert.ToDouble(entityInstance[“ListPrice”]);

                products.Add(p);
            }

            return products;
        }

Check it builds ok.

This is our WCF service built, but we need to be able to deploy it and use it within SharePoint. The out of the box SharePoint web services live in the 12ISAPI folder, and we’ll want to deploy our WCF service to the same place.

11, We are going to cheat a little and deploy our WCF assembly to the Global Assembly Cache (GAC). To do this our assembly needs to be strongly named. To do this right click on the BDCService project file and choose Properties.

12, Move to the Signing Tab and tick the box to Sign the Assembly. Finally use the combo box to add a filename for the snk file.

13, Build the project again. We are now going to need to get the public key token for our strongly named assembly.

14, From the Windows start menu open a visual studio 2008 command prompt and type

sn -T “c:UsersAdministra
torDocumentsVisual Studio 2008ProjectsProductDisplayProductServicebinProd
uctService.dll”

The path to your ProductService.dll may of course be different so please check this. Once running the command copy out the PublicKeyToken from the command window as we’ll be needing this shortly.

15, We are going to use the main ProductDisplay class project for developing our SharePoint components. Within the project re-create the folder structure of the 12 hive by adding an ISAPI folder to the solution, and within the ISAPI folder add a ProductDisplay folder.

16, Within our ProductDisplay folder we want to create our WCF web service endpoint. We’ll simplify this however, right click on the ProductDisplay folder and choose to create a new text file, but change the file to be BDCService.svc

17, Now we are simply going to copy the contents of BDCService.svc in our WCF project to the one created in our Class library project, this is:

<%@ ServiceHost Language=”C#” Debug=”true” Service=”ProductService.BDCService” CodeBehind=”BDCService.svc.cs” %>

But we need to change this slightly as we are now going to be referring to the assembly that gets deployed to the GAC. Edit it to be:

<%@ ServiceHost Language=”C#” Debug=”true” Service=”ProductService.BDCService, ProductService, Version=1.0.0.0, Culture=neutral, PublicKeyToken=[your token]” %>

Ensure you swap out [your token] section with the public key token you got in step 14.

18, We need to copy and paste web.config from our ProductService project into the ISAPI/ProductDisplay folder. This is because the web.config has the WCF configuration elements in it we need. We need to make one small but very important change in the web.config though, make sure you comment out or remove the line:

<authentication mode=”Windows” />

19, Now we are ready to deploy our WCF service to SharePoint. We are simply going to do this with a couple of post build events.

Right click the ProductService project file and choose Properties, then go to the Build Events tab. In the Post Build Events textbox add

“c:program filesmicrosoft sdkswindowsv6.0aBinGacUtil.exe” /i “$(TargetPath)” /f

Once running that you can check the c:windowsassembly folder to see if you dll is in there.

Please note I am building this on Windows Server 2008 so the location of gacutil.exe may be different for you.

20, Now we want to copy the WCF Service and web.config files over. Right click on the ProductDisplay project, move to the Build Events tab. In the post build textbox add

xcopy “$(ProjectDir)ISAPI” “C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12ISAPI” /y /s

Build the Solution and check that the ProductDisplay folder has been copied to the 12ISAPI folder.

21, There are now a couple of fixes we need to do to get WCF working with SharePoint. The first one is simple, we need to enable anonymous access to the SharePoint web application in IIS.

22, Second we need to configure our SharePoint web application to work nicely with .NET Framework 3.5 and also fix a bug that stops WCF web services working with SharePoint. We have created both of the WSP packages that implement these fixes for you as we use them in our other projects. You can download them from:

https://www.lightningtools.com/downloads/lightningtools.webconfig3.5.zip

https://www.lightningtools.com/downloads/wcfvirtualpathprovider.zip

Add both of these solutions and deploy them. Then in central administration go to Application Management –> Web Application Features and activate them for the web application that you are going to use (click on the image below to expand)

image

Make sure you are activating the features on the correct web application also! (the selection area is highlighted in red).

And that is it, now try navigating to your WCF BDC Service:

http://localhost:81/_vti_bin/productdisplay/bdcservice.svc

And you should see the page below:

image

And that’s good! In the next article we’re going to build our SharePoint page to retrieve the data and display it nicely!

You can read the second article here : SharePoint, WCF, jQuery, Ajax