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

Welcome back to Part 7 of our 8 Business Data Catalog (BDC) series. In this article, we will be focusing on the BDC Object Model. We start off by explaining why we would need the BDC Object Model and what it can do for us. From there we will look at some examples. Then we’ll step into the code and develop our own data access layer and custom applications to read from and write back to the LOB (line of business) systems. Finally we’ll conclude with best practices.

For your reference, these are our previous six articles:

1. Part 1, BDC Purpose and technical architecture
2. Part 2, Application Definition File (ADF) and its Development
3. Part 3, Developing an ADF to Connect to Web Services
4. Part 4, Consuming Business Data through Web Parts and SharePoint Lists
5. Part 5, Implementing Enterprise Search with Business Data
6. Part 6, Integrating User Profiles with Business Data

Until now, we have been working strictly with the out-of-the-box features and capabilities of BDC. MOSS Enterprise has also provided us another powerful approach for working with BDC, which is to employ the BDC Object Model and programmatically interact with a LOB system. This gives us as developers the power and control over BDC to further meet growing business needs.

You might be wondering why we use the BDC Object Model when you can just use ADO.NET. The reason is two-fold: 1) you already have BDC in place that has already abstracted the complexity of the business data and you want to maximize your investments, and 2) BDC takes care of authentication, authorization, and other security requirements, which is extremely helpful if you are writing a custom application that calls into a back-end system. So this makes the BDC Object Model a compelling API for us.

Using the BDC Object Model, we can accomplish many things such as the following examples:

1. Auto-populate a SharePoint list that has a Business Data Column
2. Directly edit a customer in SAP
3. Synchronize content between SharePoint and an LOB system
4. Update and manipulate the ADF schema

We now know what the Object Model can do so let’s first exam where the BDC Object Model fits in BDC architecturally.

The BDC Object Model Overview

To see where the BDC Object Model fits in the BDC world, let’s revisit the BDC architecture from Part 1 with Custom Applications highlighted in Figure 1.

Figure 1 The BDC Object Model Creates Custom Applications.

As you can see from the figure above, BDC allows developers to connect to disparate LOB systems and these systems are exposed as data sources to various MOSS features such as Web Parts, list columns, enterprise search, user profiles, and custom applications that can be developed to interface with SharePoint.

Custom applications can be also developed to customize and extend the existing capabilities of BDC. That’s where the BDC Object Model fits in the picture and becomes the focus of this article. The BDC Object Model provides us with new namespaces and classes (methods and properties) we can use in Visual Studio the same way we use a standard set of .NET classes and interfaces. The OOB Web Parts such as Business Data List Web Part goes through this same API that we are going to learn about. You can also create your custom Applications using the same API.

As we have learned already, BDC abstracts the business logic in an XML-based ADF file and stores it in a shared service provider (SSP) database. This file contains all the necessary configuration data that determines how the BDC will connect to the remote systems and which requested data to make available through the BDC Object Model. The following figure shows the interaction between the ADF and the Object Model.

Figure 2 The BDC Object Model and How It Works

As you can see, BDC Object Model consists of Runtime and Administration object models. Each of these performs different tasks:

1. Runtime object model

• Browse metadata, execute methods, retrieve instances, traverse relationships
• Used to build custom applications

2. Administration object model

• Create, read, update, and delete metadata and manage permissions
• Used to edit the metadata internally or to build metadata management tools

Using the BDC Object Model

In order to connect to the BDC from our code, we will use the root namespace within the BDC Object model Microsoft.Office.Server.ApplicationRegistry. If you’re curious as to the term Application Registry, just know that it was the original name for BDC. It’s defined in the assembly Microsoft.sharepoint.portal.dll. Through the root namespace we have access to the child namespaces and its classes that we are going to use later in the article. Here they are listed in this table:

Table 1: Namespaces and Classes

Tip: MetadataModel and Administration use some of the same class names such as LobSystem and Entity so one way to avoid conflict when using them together is to use an alias as follows:

using BDCAdmin = Microsoft.Office.Server.ApplicationRegistry.Administration;

MSDN already has a code sample linked here on how to use the Administration object model to create and import an ADF. Since it’s more practical to us, we will use the Runtime object model primarily throughout this article.

Now we have the basic understanding of the BDC Object Model. Let’s make use of it by applying it to the following three scenarios:

1. Auto-populate a SharePoint list that has a Business Data Column
2. Create a BDC Utility Class that can be used in custom Applications and Web services
3. Create a custom application to update the LOB systems

Since all three of these will use the Runtime object model, we can initialize our code examples by first referencing the assembly Microsoft.sharepoint.portal.dll and then import the namespaces accordingly depending on the tasks:

Now that namespaces have been imported, the next line of code designates the Shared Service Provider (SSP) that provides the ADF. This is accomplished using the following call to the SqlSessionProvider singleton Instance property:

using Microsoft.SharePoint;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;

SqlSessionProvider.Instance().SetSharedResourceProviderToUse(sspName);

The next set of code is the actual connection to the appropriate BDC and system instance. The connection is established by indicating the LobSystemInstance and Entity objects with the following statements:

LobSystemInstance lobInstance = ApplicationRegistry.GetLobSystemInstanceByName(“SynergyData”);

//Connect to the customers entity

Entity customers = lobInstance.GetEntities()["Customers"];

Following are the top steps we perform to call into the BDC and retrieve the data:

1. Connect to the named SSP
2. Connect to LOB Instance
3. Connect to entity
4. Connect to method for the entity
5. Execute the method
6. Parse the results

Now that you understand how to initialize your projects that use the Runtime API, let’s now examine each scenario:

1. Auto-populate a SharePoint list with Business Data Column

This application automatically populates a SharePoint list that has a Business Data Column storing customer records. First we create a SharePoint teamsite named teamsite and a SharePoint list called Customers. Then we reference both the SharePoint assembly Microsoft.SharePoint.dll and BDC assembly Microsoft.SharePoint.Portal.dll. Then code the using clauses to import the namespaces and code against the BDC Object Model by connecting to BDC and execute the method instance which will return all customers.

As my colleague Randy Williams has already demoed in his blog, here’s the complete code:

//Get SPList handle

SPSite site = new SPSite(http://MOSS);
SPWeb web = site.OpenWeb(“teamsite”);
SPList customerList = web.Lists["Customers"];

//Connected named SSP using the following call to the SqlSessionProvider singleton instance property
SqlSessionProvider.Instance().SetSharedResourceProviderToUse(“SSP”);

//Connect to the LOBSystemInstance
LobSystemInstance lobInstance = ApplicationRegistry.GetLobSystemInstanceByName(“SynergyData”);

//Connect to the customers entity
Entity customers = lobInstance.GetEntities()["Customers"];

//Connect to our finder method for customers
MethodInstance getCustomers = customers.GetFinderMethodInstance();

//Execute finder method
IEntityInstanceEnumerator results = (IEntityInstanceEnumerator ) customers.Execute(getCustomers, lobInstance);

//iterate through each row returned and add a new item
while (results.MoveNext())
{
SPListItem item = customerList.Items.Add();
item["Customers_ID"] = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { results.Current["CustomerID"].ToString() });
item.Update();
}

2. Create a BDC Utility Class

We’re going to create a custom library that uses the BDC Object Model to connect to an ADF.  We treat the BDC source simply as a data reader and to build our create/update/delete logic in an assembly. The BDC object model provides the means to retrieve collections, list items and enumerators, and we do the rest. In this class, we are going to create the following three static methods:

1. ExecuteFinder()
2. ExecuteSpecificFinder()
3. ExecuteGenericInvoker()

Let’s go through the code one by one. Here’s the first static method named ExecuteFinder that returns all of the entity records.

namespace SharePointMag
{
public class BDCUtil
{
public static DataSet ExecuteFinder(string SSPName, string LobSystemInstance, string Entity)
{
DataSet ds = new DataSet(Entity);

//Connect to named SSP
if (SSPName != null)
SqlSessionProvider.Instance().SetSharedResourceProviderToUse(SSPName);

//Connect to LOB Instance
LobSystemInstance lobInstance = ApplicationRegistry.GetLobSystemInstanceByName(LobSystemInstance);

//Connect to customers entity
Entity entity = lobInstance.GetEntities()[Entity];

//Connect to Finder method for customers
MethodInstance finder = entity.GetFinderMethodInstance();

//Execute Finder method
DbEntityInstanceEnumerator records = (DbEntityInstanceEnumerator)entity.Execute(finder, lobInstance);

//Load matching entities into a DataTable
DataTable entitiesTable = new DataTable();
while (records.MoveNext())
{
//Load each entity and include the Action URL
DbEntityInstance record = (DbEntityInstance)records.Current;
DataTable entityTable = record.EntityAsDataTable;
entityTable.AcceptChanges();
entitiesTable.Merge(entityTable);
}

//DataTable ordersTable = customerOrders.Tables.Add(“Orders”);
ds.Tables.Add(entitiesTable);
return ds;
}
}
}

The following code calls ExecuteFinder of BDCUtil class to return the customers records:

public static DataSet GetEntity()
{
return BDCUtil. ExecuteFinder(“SSP”, “SynergyDataInstance”, “Customers”);
}

The following is a Web service example to call the BDCUtil class to return all the customer records:

[WebMethod]
public DataSet GetCustomers()
{
string SSPName=“SSPPS”;
string strLOBInstance=“SynergyDataInstance”;
string Entity=“Customers”;
DataSet ds = new DataSet(Entity);
ds = BDCUtil. ExecuteFinder(SSPName, strLOBInstance, Entity);
return ds;
}

Note: For simplicity, we explicitly pass in the name of LOB Instance and Entity in our examples. They could also be “discovered” if you desire. Please check out this MSDN article titled How to: Get Started with Using the Runtime Object Model.

The second method named ExecuteSpecificFinder returns a single row of record identified by its ID.

public static DataSet ExecuteSpecificFinder(string SSPName, string LobSystemInstance, string Entity, object[] parameters)
{
DataSet ds = new DataSet(Entity);

//Connect to named SSP
if (SSPName != null)
SqlSessionProvider.Instance().SetSharedResourceProviderToUse(SSPName);

try
{
//Connect to LOB Instance
NamedLobSystemInstanceDictionary lobInstances = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance lobInstance = lobInstances[LobSystemInstance];
Entity entity = lobInstance.GetEntities()[Entity];

//Get the specific mathcing record
DbEntityInstance record = (DbEntityInstance)entity.FindSpecific(parameters, lobInstance);

//Loading matching entity into DataTable
DataTable entityTable = record.EntityAsDataTable;

//Return the results as a dataset

//DataSet ds = new DataSet(Entity);
ds.Tables.Add(entityTable);
return ds;
}
}

The following code calls the ExecuteSpecificFinder of BDCUtil class to return one row of employee record.

public static DataSet ExecuteSpecificFinder()
{
object[] parameters = new object[1];
parameters[0] = 1; //Employee ID;
return BDCUtil.ExecuteSpecificFinder(“SSP”, “SynergyDataInstance”, “Employees”, parameters);
}

This is the returned employee record that’s displayed in XML format:

<Employees>
<Employees>
<EmployeeID>1</EmployeeID>
<LastName>Davolio</LastName>
<FirstName>Nancy</FirstName>
<Title>Sales Representative</Title>
<TitleOfCourtesy>Ms.</TitleOfCourtesy>
<BirthDate>12/8/1948 12:00:00 AM</BirthDate>
<HireDate>5/1/1992 12:00:00 AM</HireDate>
<Address>507 – 20th Ave. E.Apt. 2A</Address>
<City>Seattle</City>
<Region>WA</Region>
<PostalCode>98122</PostalCode>
<Country>USA</Country>
<HomePhone>(206) 555-9857</HomePhone>
<Extension>5467</Extension>
<Photo>FRwvAAIAAAA…truncated…</Photo>
<Notes>Education includes a BA in psychology from Colorado State University in 1970. She also completed “The Art of the Cold Call.” Nancy is a member of Toastmasters International.</Notes>
<ReportsTo>2</ReportsTo>
<PhotoPath>http://portal.synergy.com/hr/employeephotos/ndavolio.jpg</PhotoPath>
<LoginAccount>synergy\ndavolio</LoginAccount>
</Employees>

Note: The <Photo> element has been truncated to save space here.

Now let’s move on to GenericInvoker by adding ExecuteGenericInvoker method in our BDCUtili class. The ExecuteGenericInvoker method calls the GenericInvoker method instance to write back to LOB systems.

public static void ExecuteGenericInvoker(string SSPName, string lobSystemInstance, string entityName, string methodInstance, object[] parameters)

{ //Connect to named SSP
if (SSPName != null)
SqlSessionProvider.Instance().SetSharedResourceProviderToUse(SSPName);

NamedLobSystemInstanceDictionary instances = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance instance = instances[lobSystemInstance];
Entity entity = instance.GetEntities()[entityName];
MethodInstance methInst = entity.GetMethodInstances()[methodInstance];
entity.Execute(methInst, instance, ref parameters);
}

In our third example, we look at GenericInvoker and then call the ExecuteGenericInvoker method we’ve just added.

3. Create a Custom Application to Update the LOB systems

BDC is very well known as read-only tool. It’s read-only because the OOB functionality does not make use of GenericInvoker methods. However, the object model and ADF support this type of method instance that we invoke just as we would invoke any other method. If you think of this as a stored procedure call and if I can call a stored procedure that runs a select statement, I should be able to call a stored procedure that inserts or updates as long as the underlining data repository supports them. In other words, the GenericInvoker method allows us to call other SQL commands. For our situation, we’ll use it to write back to our back-end system.

So let’s first add the GenericInvoker method in our ADF file and then call the ExecuteGenericInvoker method in our BDCUtil class . (Note: I’ve used BDC Meta Man to help generate the ADF and create ExecuteGenericInvoker. Thanks Lightning Tools! If you use BDC Definition Editor, you cannot select byte array byte[] as the return type of the field. You can instead select byte and edit the ADF by hand by changing byte to byte[].)  The following is the ADF and for the purpose of this example, only a single entity called Employees has been defined.

<Entity EstimatedInstanceCount=“0“ Name=“Employees“>
<Identifiers>
<Identifier TypeName=“System.Int32“ Name=“EmployeeID“ />
</Identifiers>
<Methods>
</Methods>
</Entity>
<Method Name=“insertEmployees“>
<Properties>
<Property Name=“RdbCommandText“ Type=“System.String“>
Insert into Employees(LastName,FirstName,Title) Values(@LastName,@FirstName,@Title);select NewEmployeeID = SCOPE_IDENTITY()</Property>
<Property Name=“RdbCommandType“ Type=“System.String“>Text</Property>
</Properties>
<Parameters>
<Parameter Direction=“In“ Name=“@LastName“>
<TypeDescriptor TypeName=“System.String“ Name=“LastName“ />
</Parameter>
<Parameter Direction=“In“ Name=“@FirstName“>
<TypeDescriptor TypeName=“System.String“ Name=“FirstName“ />
</Parameter>
<Parameter Direction=“In“ Name=“@Title“>
<TypeDescriptor TypeName=“System.String“ Name=“Title“ />
</Parameter>
<Parameter Direction=“Return“ Name=“Employees“>
<TypeDescriptor TypeName=“System.Int32“ Name=“NewEmployeeID“ />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name=“EmployeesInserter“ Type=“GenericInvoker“ ReturnParameterName=“Employees“ />
</MethodInstances>
</Method>
<Method Name=“updateEmployees“>
<Properties>
<Property Name=“RdbCommandText“ Type=“System.String“>Update Employees SET LastName=@LastName,FirstName=@FirstName,Title=@Title WHERE(EmployeeID=@EmployeeID)</Property>
<Property Name=“RdbCommandType“ Type=“System.String“>Text</Property>
</Properties>
<Parameters>
<Parameter Direction=“In“ Name=“@EmployeeID“>
<TypeDescriptor TypeName=“System.Int32“ Name=“EmployeeID“ />
</Parameter>
<Parameter Direction=“In“ Name=“@LastName“>
<TypeDescriptor TypeName=“System.String“ Name=“LastName“ />
</Parameter>
<Parameter Direction=“In“ Name=“@FirstName“>
<TypeDescriptor TypeName=“System.String“ Name=“FirstName“ />
</Parameter>
<Parameter Direction=“In“ Name=“@Title“>
<TypeDescriptor TypeName=“System.String“ Name=“Title“ />
</Parameter>
<Parameter Direction=“Return“ Name=“Employees“>
<TypeDescriptor TypeName=“System.Int32“ Name=“EmployeeID“ />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name=“EmployeesUpdater“ Type=“GenericInvoker“ ReturnParameterName=“Employees“ />
</MethodInstances>

</Method>

<Method Name=“deleteEmployees“>
<Properties>
<Property Name=“RdbCommandText“ Type=“System.String“>Delete Employees WHERE(EmployeeID=@EmployeeID)</Property>
<Property Name=“RdbCommandType“ Type=“System.String“>Text</Property>
</Properties>
<Parameters>
<Parameter Direction=“In“ Name=“@EmployeeID“>
<TypeDescriptor TypeName=“System.Int32“ Name=“EmployeeID“ />
</Parameter>
<Parameter Direction=“Return“ Name=“Employees“>
<TypeDescriptor TypeName=“System.Int32“ Name=“EmployeeID“ />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name=“EmployeesDeleter“ Type=“GenericInvoker“ ReturnParameterName=“Employees“ />
</MethodInstances>
</Method>

I’ve created three GenericInvoker MethodInstances and they are EmployeesInserter,EmployeesUpdater, and EmployeesDeleter. When a method instance is executed, it will eventually call the appropriate SQL command as shown here:

• Insert into Employees(LastName,FirstName,Title) Values(@LastName,@FirstName,@Title);select NewEmployeeID = SCOPE_IDENTITY()
• Update Employees SET LastName=@LastName,FirstName=@FirstName,Title=@Title WHERE(EmployeeID=@EmployeeID)
• Delete Employees WHERE(EmployeeID=@EmployeeID)

The following method ExecuteGenericInvoker is to call the GenericInvoker method:

public static void InsertEmployee()

{
object[] parameters = new object[3];
parameters[0] = “He”; //Last Name;
parameters[1] = “Wen”; //First Name;
parameters[2] = “SharePoint Architect”; //Title;

try
{
BDCUtil.ExecuteGenericInvoker(“SSP”, “SynergyDataInstance”, “Employees”, “EmployeesInserter”, parameters);
}

catch (Exception exception)
{
Console.WriteLine(exception.ToString());
}
}

The result is as you can see here highlighted:

In the same token, the following code will update the employee record:

In the same token, the following code will update the employee record:

public static void UpdateEmployee()
{
object[] parameters = new object[4];
//The parameters must match the parameters defined in ADF
parameters[0] = “10″; //Employee ID;
parameters[1] = “He”; //Last Name;
parameters[2] = “Wen”; //First Name;
parameters[3] = “MOSS Architect”; //Title;

try
{
BDCUtil.ExecuteGenericInvoker(“SSP”, “SynergyDataInstance”, “Employees”, “EmployeesUpdater”, parameters);
}

catch (Exception exception)
{
Console.WriteLine(exception.ToString());
}
}

Result is as follows:

Note: the order of the parameters must match that of the parameters defined in the updateEmployees method in the ADF. The ReturnParameterName (ReturnParameterName=”Employees”) is required in the EmployeeUpdater and EmployeeDeleter MethodInstance, even though they have no parameters to return.

As we have just learned, the GenericInvoker method is extremely useful because it takes care of authentication and authorization while enabling our custom applications to call in and execute business logic stored in the back-end to insert/update/delete LOB systems.

Write-back Best Practice

Even though BDC can update the LOB systems, it’s not suited for all scenarios. I strongly recommend you to take it into consideration governance and change management that your organization might require. Here are some additional best practices:

1. Understand the vendor support implications for changes that you make. For example, directly querying SharePoint databases is not Microsoft supported because the risk involved in damaging it.
2. Understand data integrity rules before using GenericInvoker method to make direct changes.
3. Have error handling in place.
4. When full inserts and updates are needed, consider using the LOB vendor’s own API that provides transactional write-back capabilities from the client to the underlying LOB application. Another way this can be done is using custom actions which will be covered in detail in Part 8.

Conclusion

BDC Object Model provides a new and exciting solution by allowing direct integration with LOB systems. In this article we have explored the BDC Object Model and exploited the capabilities of the BDC from code. BDC encapsulates the business logic of the back-end systems and developers can just work with the BDC objects to interact with those systems. We’ve demonstrated how to retrieve customer records to populate a SharePoint list that has a BDC data column, and then created a BDC utility class to act as a data layer, finally called the utility class to update the back-end system. With this you can now develop custom applications to interface with SharePoint using the BDC Object Model.  And for organizations that have invested in BDC, you are on your way to leveraging the real power of BDC!