Everything You Need to Know about BDC: Part 3 of 8

Welcome to part three of our BDC series.  In this article our focus will be on consuming Web Services from BDC.  It will cover the value of Web Services, how to develop the Web Services to be BDC capable, and introduce how the Application Definition File describes these Web Services.  We will building on what we learned in the first two articles, so if you’re still learning BDC and haven’t gone through those yet, you are encouraged to go back and read them.  Since this article covers the programming of Web Services, it will be more developer-oriented.

Why Web Services

While relational databases have proliferated greatly over the last 20 years, there are some inherent difficulties with them.  For starters, they are often proprietary.  While structured query language (SQL) is an ANSI-standard language, vendors choose to develop their own flavor of SQL such as PL-SQL with Oracle or T-SQL with Microsoft.  In order to query from a database, you need a vendor-specific driver, making integration between systems difficult.  Furthermore, the network connection is often based on remote procedure calls (or RPCs) that won’t typically work over an HTTP connection.  This makes connecting to them over the Internet or through firewalls a challenge.

Web Services have matured in the last few years as the de-facto way of integrating systems together.  Service-oriented architecture (SOA), which is based on Web Service technology, offers us a very compelling approach to building and integrating systems today.  Its loosely-coupled nature and vendor-neutral design make it far more flexible and practical for the changing needs of organizations.  Exposing line of business data over Web Services gives you more control, more universal access, HTTP compatibility, and is in line with emerging technologies such as cloud computing.

Since a primary goal of SharePoint is to be the central interface for critical data assets, it’s likely that SharePoint will need to integrate with external systems.  In article two of this series, we covered how the ADF can describe an external database.  In addition to the advantages we just covered, there may be other reasons why Web Services might be the better choice.  For example, perhaps you already have a Web Services architecture in place that you want to leverage.  Or you have data that comes from a myriad of data sources such as databases, XML files, or services in the Internet cloud.  With Web Services, your content can originate in virtually any source.  By exposing these data sources through a Web Service, you provide a single way for SharePoint to consume this data, regardless where it is located.  This is depicted in Figure 1 here:

Figure 1: Exposing Web Services to MOSS

Developing Your Web Services

Now that we have covered why Web Services might be right technology, let’s dive into a sample Web Service that we can consume from BDC.  As you can see in Figure 1 above, the data can originate from any back-end data source.  In this first example, it will come from the AdventureWorks database from a Microsoft SQL Server.  The data we want will be returned using a simple stored procedure named GetEmployees.  Here is the code for the stored procedure:

create proc [dbo].[GetEmployees]
as
select Employee.EmployeeID,
        Contact.FirstName,
        Contact.LastName,
        Employee.Title,
        Employee.HireDate
from HumanResources.Employee
join Person.Contact
on Employee.ContactID = Contact.ContactID

As you can see, we are returning four columns.  We join from the Employee to the Contact table to retrieve all the columns we need.  We then call into this stored procedure from our C#-based Web Method.  This method is coded within an ASP.NET Web Service project as shown here:

[WebMethod]
public List<Employee> GetEmployees()
{
    Database db = DatabaseFactory.CreateDatabase("Connection String");
    IDataReader employeesReader;
    List<Employee> employees = new List<Employee>();

    DbCommand cmd = db.GetStoredProcCommand("GetEmployees");

    employeesReader = db.ExecuteReader(cmd);
    while (employeesReader.Read())
    {
        employees.Add(new Employee((int)employeesReader["EmployeeID"],
                      employeesReader["FirstName"].ToString(),
                      employeesReader["LastName"].ToString(),
                      (DateTime)employeesReader["HireDate"]));
    }
    employeesReader.Close();

    return employees;
}

Version 4.1 of the data access application block from the Enterprise Library is used to return the Employee data.  This is not required as any form of ADO.NET code will work, but this simplifies our data retrieval and is a good practice.  The data returned from the Web Method is a generic list based on the Employee class.  You can consider this Employee class to be our business object representing a single employee.  Here is the part of the class with the four columns and two constructors.

public class Employee
{
    private int _id;
    private string _first;
    private string _last;
    private DateTime _hireDate;

    public int ID
    {
        get { return _id; }
        set { _id = value; }
    }
    //Additional getter/setter for remaining columns go here 

    public Employee(int id, string first, string last, DateTime hireDate)
    {
        this._id = id;
        this._first = first;
        this._last = last;
        this._hireDate = hireDate;
    }

    public Employee()
    {
        this._id = 0;
        this._first = string.Empty;
        this._last = string.Empty;
        this._hireDate = DateTime.Parse("1/1/2000");
    }
}

One key advantage of using a generic list when writing the Web Method is that it serializes nicely into XML.  It also describes the complex types in the XML Schema (XSD) in a BDC-friendly way which will make the authoring of the ADF file much easier.  When Employees are returned from the Web Method, here is how one of them will be described in XML:

Figure 2: Employee element returned from Web Service

In the Web Service, we also define two additional methods, one for an IdEnumerator and one for a Specific Finder.  For brevity sake, I only show the interface for each.  They are coded similarly, and you can download the full code at the end of this article.

public List<int> GetEmployeeIDs () //IdEnumerator 
public Employee GetEmployeeByID(int EmployeeID) //Specific Finder

GetEmployeeIDs (the IdEnumerator) just returns the ID for each Employee.  The Specific Finder returns a single Employee object based on the supplied EmployeeID.

Creating the Application Definition File

Now that we have defined a simple Web Service exposing employees, let’s look at how this is represented in our ADF.  Since the basic structure of the ADF remains the same with a Web Service as compared to a database, we’ll only focus on the areas that pertain to calling Web Services.

In Figure 3 below is our LobSystem element which is the root of the ADF.  You’ll notice that the Type attribute indicates WebService.  Inside Properties, a namespace prefix is defined for this LOB.  In our case, we call this HR (for Human Resources).  We also set the URL to the Web Service.  When this ADF is loaded into MOSS, the Web Services Description Language (WSDL) for this URL is retrieved and an internal proxy class is generated that BDC uses.  This concept is very similar when adding a Web Reference to a Visual Studio project.

Figure 3: LobSystem Root Element in ADF

Moving down in the schema we find our LobSystemInstance which is where connection details are specified.  For a Web Service, there are several options such as how to authenticate and how many simultaneous connections are allowed.  In our case, our needs are simple, so we only need to list the name of our LobSystem which is HR.  (Note: since we didn’t specify how to authenticate, this will use RevertToSelf settings which means that the application pool identity will be the credentials used if the Web Service requires authentication.)

Figure 4: LobSystemInstances in ADF

Where it gets interesting is how we start to define our methods for each entity.  The secret to writing the Web Service-based ADF is to define the TypeDescriptor elements correctly.  Each TypeDescriptor points to an element node in the Web Service XML request or response, and the basic rule is that you need to nest your TypeDescriptors to match the hierarchy.  For example, here is our Finder method that will call the GetEmployees Web Method and return a list of Employees.

Figure 5: Return Parameter for GetEmployees Web Method

Let’s go through what we’re seeing here.  First off, the Name attribute for the Method must match the name of the Web Method.  In this example, it is GetEmployees, which we introduced above.  The TypeDescriptor elements that are highlighted reflect our hierarchy in the response.  The first one defines an array of Employee elements (denoted in part by the IsCollection=”true”).  The second one defines each Employee, and the final four define each column that is returned.  In a snippet of the actual XML response shown in Figure 6 below, notice the Name attribute for the TypeDescriptor matches the Element name.  This name match-up is not required in all cases but is recommended.

Figure 6: Actual XML Response from Web Service (GetEmployees Method)

Let’s look at one more example.  Here is our IdEnumerator which returns the IDs for all Employees.  Here is the method we define in the ADF:

Figure 7: ADF Entry for IdEnumerator

In this case, we return an array of Int32 (32-bit Integers).  Below, here is the first part of the the corresponding XML that is returned from the Web Service.  Notice how the two levels in the XML, ArrayOfInt and int, align (i.e. int is a child element of ArrayOfInt).

Figure 8: Actual XML Response from Web Service (GetEmployeeIDs Method)

Consuming Employees from UI

So now that we have our Web Service ready, and our ADF loaded and secured within our SSP (as covered in the Article #2), it’s time to see the what this effort yields us in the user interface.  Let’s first take a look at the Business Data List Web Part.  We will examine this Web Part in more detail in the next article, but for now, here is a basic view of the Employee entity from our catalog:

Figure 9: Employees showing in a Business Data List Web Part

Here is a recap of how this executes:  These records are pulled in real time through a proxy class created from the ADF, through the Web Service and into the stored procedure running inside SQL Server.  Since we have defined IdEnumerator and Specific Finder methods, we can also set up a Content Source and crawl this BDC data.  This allows us to issue search queries against the external system, and this will be covered in depth in Article 5.  For now, I have set this up with the Employee entity in the ADF, and this is the result when we search for Rob (someone’s first name):

Figure 10: Search Results for Employees Based on Name

And finally, let’s look at an auto-created profile page.  This is created by the BDC that allows us to view all the details for a single record.  These types of pages will be covered in more detail in Article 8.

Figure 11: Profile Page for a Single Employee

With that, you should have a basic idea in how the ADF needs to match the response from the Web Service.  If you’ll be developing these ADFs from scratch, I advise you to look at the WSDL provided by the Web Service to understand the Complex Types in the XSD.  I also recommend looking at the actual XML data that is coming back.  From here, and with some practice, you will be able to construct your ADFs.  The process for input parameters (for example, with FilterDescriptors) is the same.

Amazon Web Service

The second example we’ll look at is a bit more complex.  Within the MOSS SDK, a sample ADF file exists to call into Amazon to do a search for a product.  This is quite powerful and great demonstration of services in the cloud.  For this section, we will look at a modified form of the sample ADF, so even if you’ve seen this, I recommend you keep reading.  Let’s first show you how this looks from the user’s perspective.  I have added a Business Data List Web Part to a web page and have pointed it to the Amazon entity in the catalog.  Here is how it shows the results when searching for SharePoint books:

Figure 12: Keyword Search Against Amazon Web Service

As you can see, it has returned the first ten titles which match these keywords.  The book’s ISBN and Title are shown, and Title links to the actual book URL on Amazon’s web site.  This was done by pulling the raw data from Amazon through the BDC and displaying it with a custom XSLT Stylesheet.

To understand how this works, let’s first understand the XML that will be delivered to BDC.  Here is what a single book (an Item) looks like in the XML Response from the Web Service (Note: I have collapsed the ItemLinks element since it’s not used in this demonstration):

Figure 13: Actual XML Response from Amazon Web Service

As you can see, we have the ISBN returned in an element called ASIN.  We also have URL stored in the DetailPageURL element.  Lastly, details about the book are stored in a child element called ItemAttributes, and this is where we need to extract the book’s title.  Taking a look at the XSD defined in the WSDL provided by Amazon, we see the definition for each Item and ItemAttributes.  (Note: I have edited the file to only focus on these two complex types).

Figure 14: A Selected Portion of Amazon’s Web Service WSDL

Now that we know what we’re dealing with in terms of schema, let’s see how the ADF comes together.  Again, I have modified the sample Amazon ADF file.  Specifically, I have removed an unused entity, and added additional logic to expose the book’s title.  Here is our single entity, called Item.

Figure 15: Item Entity Defined in ADF

Keywords is the friendly name for our filter as you can see in the screenshot in Figure 12 above.  As part of the input parameters, we provide the value of this Keyword, a subscription ID (required by Amazon to call into the Web Service), and some default values for what we’re searching (Books in this example).  These won’t be shown for brevity sake.  The concept for these input parameters is the same as the returned output in that you must define hierarchical TypeDescriptor elements to match the XML request.  With that, let’s focus on the returned response.  The hierarchy here is pretty deep, so we’ll just focus on the inner-most section that begins with an array of items.  Here is how this looks:

Figure 16: TypeDescriptor Hierarchy for Returning Items

It might look imposing at first glance, but if you follow the arrows, you can see how this maps up with the actual XML response shown in Figure 13 above.  The tricky part with this example is that we want to return Title to be at the same level as ASIN (the ISBN) and DetailPageURL.  In the XML response, Title is nested in a child node called ItemAttributes.  This becomes a problem because we need to return a flat set of columns and rows just like in a sheet in Excel.  In our case, the desired columns are ISBN, DetailPageURL and Title.  In other words, hierarchies are not allowed.  So, we somehow need to “promote” Title up one level.

We solve this problem by first setting a localized name for ItemAttributes to be Title.  This just means we rename this column to Title.  To get the actual value for Title, we use what’s called ComplexFormatting.  ComplexFormatting allows us to generate the value, and we do this using the FormatString property.  The {0} (just as with the String.Format method) will generate a new string from the value of a child element called Title.

With this described ADF, we have now effectively mapped the fairly complex result from Amazon into a pretty simple display.  If we load the ADF as is and add a Business Data List Web Part that points to this Item entity, we get the following result:

Figure 17: Raw Output of our Item Entity

Here are our three columns, ISBN, DetailPageURL and Title.  Of course, we would like the Title to just be a hyperlink to the specified URL.  We can handle this with a small change to the Web Part’s XSLT.  This is what yields the display shown in Figure 12.

Recommendations

Now that you have a pretty good idea how to structure your own, let me leave you with some recommendations that should help you construct your Web Service-based ADFs.

• The BDC only supports Simple Object Access Protocol (SOAP)-based Web Services, not REST-ful (REpresentational State Transfer) ones.
• Due to the structure of the ADF files, you will not easily be able to consume existing Web Services unless they closely match the concepts of Finder, Specific Finder, and IdEnumerator.  You might find yourself having to write specific Web Services, which is why I provided a sample on how this can work.
• If your Web Services are developed using the approach used above, you should have some success working with ADF generation tools like BDC Design Studio, BDC Meta Man, and BDC Definition Editor.  If not, you will likely need to generate the ADFs by hand.  Even when some design tools, the effort is more involved as the utility cannot infer the Identifier or FilterDescriptors for entities.  In some cases, you might get part of the ADF described using a tool, and then you’ll need to go in and make some tweaks to it.
• If you find yourself needing to write a lot of Web Service wrappers around your existing databases, you might find a utility from the same company that makes BDC Meta Man helpful.  It’s called BDC Web Man, and you can find it here.
• Keep your returned Web Service XML structures simple.  As you can see, deep hierarchies are much harder to represent in the ADF.  You’ll probably find it much easier to adjust the Web Service output than trying to craft the ADF to work with a complex structure.  For example, while you can return and consume a DataSet in the ADF, the amount of nesting makes this a very complex task, and you’ll need to code it all by hand.
• As much as possible, review the WSDL file and actual XML results when structuring the ADF file.  This will help ensure you are representing your TypeDescriptor hierarchies correctly.
• If you are developing new Web Services using .NET technologies, you can use either classic Web Services (i.e. ASMX) or the newer Windows Communications Framework (WCF) style, provided you configure it to support SOAP.
• Since your Web Service WSDL will be downloaded and parsed when you load the ADF into your SSP, make sure the Web Service is physically accessible at that time.
• Authenticating to the Web Service through HTTP or SOAP headers is supported through the BDC.  See the following article from the MOSS SDK for details on how this works.
• When troubleshooting problems with your ADF, be sure to check both the Windows Application EventLog and SharePoint’s diagnostic log files (C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\LOGS).  These will provide you with much more detail on the nature of the problem.
• As I’ve suggested, you want to review the actual XML results from your Web Services.  These can be difficult to capture at times.  To help, you might find some value in using a network sniffer such as Network Monitor.  This allows you to capture the actual network packets allowing you to study the traffic between the SharePoint server and the Web Service server.  You can download Microsoft’s Network Monitor for free from here.

Download Sample Code

In the sample code available here, you will find the following code:

• Amazon ADF (Note: you will need your own Amazon Web Service (AWS) Account)
• Modified Amazon XSLT for the Business Data List Web Part
• The complete Employee Web Service (VS.NET 2008 project)
• The Employee ADF
• Employee Stored Procedures for AdventureWorks database