Microsoft & .NETBusiness Data Catalog in Microsoft Office SharePoint Server 2007 and the Object...

Business Data Catalog in Microsoft Office SharePoint Server 2007 and the Object Model

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Microsoft Office SharePoint Services (MOSS) 2007 is Microsoft’s innovative tool that provides the infrastructure to advance collaboration and communication within organizations based on the smooth flow of documents and information.

The Business Data Catalog (BDC), a fresh approach in SharePoint 2007, ensures integration between SharePoint and back-end systems such as databases as well as Line of Business (LOB) processes like SAP and Siebel applications.

There are two approaches for working with BDC: creating XML schemas manually or employing SharePoint’s Object Model to programmatically interact with the LOB system.

The first part of the article, “Business Data Catalog in Microsoft Office SharePoint Server 2007: Architecture and Schemas,” addresses the architecture within the Business Data Catalog and the creation of the BDC XML-based schema definitions. This section looks at SharePoint’s Object Model and examines the tools for working with schemas, thus giving developers control over all aspects of the BDC.

NameSpaces and Classes

There are three NameSpaces that contain the main classes, methods and properties needed to exploit the capabilities of the Business Data Catalog from code:

  1. Microsoft.Office.Server.ApplicationRegistry.MetadataModel. Contains the classes necessary to read the Catalog objects and to execute queries. The classes are read-only and optimized to permit fast (read) access to the BDC.
  2. Microsoft.Office.Server.ApplicationRegistry.Runtime. Provides the object-oriented interface to access the components of the BDC schema. This abstraction layer simplifies the use of the XML schemas and ensures their programmability.
  3. Microsoft.Office.Server.ApplicationRegistry.Administration. Comprises the classes to create, update, and delete Business Data Catalogs metadata, but also may be used for reading. This NameSpace is used to manage schemas because its classes have read-write access to the Catalog. It is not advisable to use the NameSpace to read data because the output is not cached and is slower than the MetadataModel NameSpace. Changes made with the Administration NameSpace run with a SharePoint Timed Job; that means that the modifications can be applied after a short period of time.

There are three additional NameSpaces for the BDC:

  1. Microsoft.Office.Server.ApplicationRegistry.Infrastructure. Provides the classes for security and connections to physical sources of data.
  2. Microsoft.Office.Server.ApplicationRegistry.Search. This NameSpace is not documented by Microsoft and the MOSS Software Development Kit (SDK) marks it as “reserved for internal use.”
  3. Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db. Contains two classes for manipulating entities instances in Data Bases and one not documented class.

In addition to using the Object Model, there are also a number of standard WebServices for the Business Data Catalog grouped under the Microsoft.Office.Server.ApplicationRegistry.WebService NameSpace. The ten classes of the NameSpace are noted as “reserved for internal use and not intended to be used directly from your code” by the MOSS SDK. Note that there is no supplementary documentation available in the MOSS SDK, but it is certainly possible to use them; however, caution is advised. For example, the class “BdcWebService” has methods to read LOB system instances, entities, and methods. The WebService can be reached from IIS under the URL ‘http://[ServerName]/_vti_bin/businessdatacatalog.asmx’.

BDC Schema under the Loop

The Business Data Catalog is a constituent part of the MOSS Shared Services Provider (SSP), so the first action in the code is to make a connection to the SSP that holds the BDC schema:

SqlSessionProvider.Instance().
   SetSharedResourceProviderToUse("SharedServices1");

The only parameter of the “SqlSessionProvider” is a string with the name of the Shared Service, “SharedServices1”, in the above example, the default name for the first SSP. Note that it is only possible to make a connection in the local farm.

Note: The next example is a Visual Studio 2005 console application project. Make references in the project to “Microsoft.Office.Server” (“Microsoft.Office.Server.dll”) and “microsoft.sharepoint.portal” (“Microsoft.SharePoint.Portal .dll”), and directives at the beginning of the code page to:

using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db;
using myAdmin =
   Microsoft.Office.Server.ApplicationRegistry.Administration;

The directive to the Administration NameSpace has a special name (“myAdmin”) to avoid a conflict with methods from the Runtime and MetadaModel NameSpaces. An extra directive to “using System.Data” may be necessary.

In the following code samples, the hypothetical zoo Business Data Catalog from the first article will be used. The information is read from a SQL database and is displayed in the BDC WebParts of the portal:

Figure 1: Information from “MyZoo” DataBase (“AnimalsEntity List”) in the portal

The next routine loops through the complete schema, showing the relevant information:

static void GetProperties()
{
   NamedLobSystemInstanceDictionary mySysInstances =
      ApplicationRegistry.GetLobSystemInstances();

   foreach (string mySysInstanceName in mySysInstances.Keys)
   {
      LobSystemInstance myLobSystemInstance =
         mySysInstances[mySysInstanceName];
      LobSystem myLobSystem = myLobSystemInstance.GetLobSystem();
      Console.WriteLine("SystemLOB  - " + myLobSystem.Name);
      Console.WriteLine("* Instance - " + mySysInstanceName);

      NamedEntityDictionary myEntities =
         myLobSystemInstance.GetEntities();
      foreach (string myEntity in myEntities.Keys)
      {
         Entity oneEntity =
            myLobSystemInstance.GetEntities()[myEntity];
         Console.WriteLine("** Entity - " + oneEntity.Name);

         NamedMethodDictionary myMethods = oneEntity.GetMethods();
         foreach (string myMethod in myMethods.Keys)
         {
            Method oneMethod = oneEntity.GetMethods()[myMethod];
            Console.WriteLine("*** Method - " + oneMethod.Name);

            NamedPropertyDictionary myProperties =
               oneMethod.GetProperties();
            Console.WriteLine("nr" + "**** Query - " +
               myProperties["RdbCommandText"].ToString().Trim());
         }
      }
   }
}

The first element of a BDC schema identifies the schema with a specific name. The “NamedLobSystemInstanceDictionary” contains the collection of schemas in the Catalog, and the “GetLobSystemInstances” method fills the variable with the schemas present in the system.

By looping through the collection, it is possible to find all the relevant information about the LOB’s Instances and systems, and the routine prints the name of the LOB system and the name of the Instance.

Each LOB System Instance contains one or more Entities. The “NamedEntityDictionary” contains the collection and the “GetEntities” method loads the variable with the Entities in the Instance. Continue using a ‘foreach’ statement to examine each Entity and make an instance of them to display and use its properties (the name of the entity will be printed in the example).

In a similar way, each Entity embraces one or more Methods. These are contained in the “NamedMethodDictionary” collection and can be filled using the method “GetMethods” of the entity. Looping through the different methods, an instance of the method is made and one of the properties printed (its name). Apart from properties reached from the Object Model, the Method object has a “NamedPropertyDictionary” bag with its properties; in the example, an instance of the collection is made, filled with the “GetProperties” method, and one of the properties is printed on the screen; namely, the query used in the schema to retrieve the data from the database.

Figure 2: Routine results for reading the BDC schema

Reading the Output

It also is possible to execute a Business Data Catalog and read the output using the MetadataModel and Runtime NameSpaces. The next routine shows the results from the example’s BDC:

static void GetData()
{
   NamedLobSystemDictionary myLobSysDictionary =
      ApplicationRegistry.GetLobSystems();

   foreach (LobSystem system in myLobSysDictionary.Values)
   {
      NamedLobSystemInstanceDictionary mySysInstances =
         system.GetLobSystemInstances();

      foreach (LobSystemInstance oneInstance in mySysInstances.Values)
      {
         Entity myEntity =
            oneInstance.GetEntities()["AnimalsEntity"];
         FilterCollection myFilterCollection =
            myEntity.GetFinderFilters();
         IEntityInstanceEnumerator myEntityInstanceEnumerator =
            myEntity.FindFiltered(myFilterCollection, oneInstance);

         while (myEntityInstanceEnumerator.MoveNext())
         {
            DataTable myZooDataTable =
               (myEntityInstanceEnumerator.Current as
                DbEntityInstance).EntityAsDataTable;
            foreach (DataColumn oneAnimal in
                     myZooDataTable.Rows[0].Table.Columns)
            {
               Console.Write(myZooDataTable.Rows[0][oneAnimal] + "t");
            }
            Console.WriteLine()
         }
      }
   }
}

Essentially, the code follows the guidelines of the first routine to walk through the BDC schema, beginning with the LobSystem and the instances contained in the “NamedLobSystemInstancesDictionary”. Inside the Instances collection, there is a check to search for the correct Entity (“AnimalsEntity” in the example), and using an empty filter, the results of the BDC are loaded in the Instance Enumerator. The example uses an empty filter to put on view all the content of the database, but it is possible to define a subset of the results in the filter.

Each item in the Instance Enumerator is read using a ‘while’ statement, and the content is downloaded in a DataTable (don’t forget to use a directive to System.Data). By looping through the columns of the DataTable, it is possible read the value of the row and print it onto the screen. Finally, a new row can be read and printed.

Figure 3: BDC output printed on the screen

Modifying the Schema

As mentioned earlier, the NameSpaces’ MetadataModel and Runtime are read-only and especially suitable to read data in a swift, cached way. To modify a BDC schema, it is necessary to use the Administration NameSpace.

The subsequent routine alters the query of the BDC’s schema in the example:

static void ChangeQuery()
{
   myAdmin.LobSystemInstanceCollection mySysInstances =
      myAdmin.ApplicationRegistry.Instance.LobSystemInstances;

   foreach (myAdmin.LobSystemInstance mySysInstance in mySysInstances)
   {
      if (mySysInstance.Name == "MyZooInstance")
      {
         myAdmin.EntityCollection myEntities =
            mySysInstance.LobSystem.Entities;
         foreach (myAdmin.Entity oneEntity in myEntities)
         {
            if (oneEntity.Name == "AnimalsEntity")
            {
               myAdmin.MethodCollection myMethods = oneEntity.Methods;
               foreach (myAdmin.Method oneMethod in myMethods)
               {
                  if (oneMethod.Name == "AnimalsMethod")
                  {
                     string myQuery =
                        "SELECT Animals.AnimalID AS AnimalID,
                                Animals.AnimalSurename + '
                                (' + Animals.AnimalFamilyname + ')'
                         AS Animal, " +
                            "DangerousLevels.DangerousLevelDescription
                         AS DangerousLevel
                         FROM Animals
                         INNER JOIN DangerousLevels
                         ON " + "Animals.AnimalDangerousLevel =
                            DangerousLevels.DangerousLevelID
                         WHERE (Animals.AnimalID " + ">".Normalize() +
                            "=@GenMinAnimalID) and (Animals.AnimalID "
                            + "<".Normalize() + "=@GenMaxAnimalID)
                         ORDER BY DangerousLevel";
                     oneMethod.Properties.Remove("RdbCommandText");
                     oneMethod.Properties.Add("RdbCommandText",
                                              myQuery);
                  }
               }
            }
         }
      }
   }
}

Be aware that the directive to the NameSpace “Microsoft.Office.Server.ApplicationRegistry.Administration” has been connected to the variable “myAdmin” because the NameSpaces Administration and MetadataModel have classes with the same name, and in this way the compiler can identify and separate them without problem.

In the same way as with the other routines, use a couple of loops to navigate the Instances, Entities, and Methods to find the correct values (“MyZooInstance”, “AnimalsEntity”, and “AnimalsMethod”). Finally, a new query is defined: in the example, the “ORDER BY” has been changed from “AnimalID” to “DangerousLevel”, to present the results in a different order.

Figure 4: BDC results before and after programmatically changing the query

In the query syntax, note the use of the characters for greater than (“>”) and lesser than (“<“); they need to be normalized to be understood by the XML schema. After the query construction, the former query is deleted from the properties bag of the method and the new one added in a similar way. All the changes made with the Object Model are persisted in the database by using a SharePoint Timed Job. This means that the replication can take a couple of seconds (in the worse case) before the schema can be used again.

Conclusions

The Business Data Catalog is an important method to bring information from the external world within SharePoint. Users with little or no programming knowledge are able to build BDC schemas in a relatively uncomplicated way, and developers are able to access the extended possibilities of the Object Model to read the information and present it in a desired manner; for example, in custom WebParts. The ability to make alterations is very important because it opens the possibility to interact with an external data source dynamically as well as allowing users to tailor schemas following specific requirements. The Object Model allows schemas to be constructed at run time without a manually-coded XML schema and, includes the possibility to construct adaptive or self-learning schemas that can change based on customer’s usage.

Download the Code

The code that accompanies this article may be downloaded here.

About the Author

Gustavo Velez is a MCSD Senior Application Developer for Winvision (http://www.winvision.nl), a Microsoft Gold Partner in the Netherlands. He has many years experience in developing Windows and Office applications, and more than five years of daily programming experience with SharePoint. The author’s articles, in English, Dutch, and Spanish, can be found in many of the leading trade magazines and he’s pleased to be Webmaster of http://www.gavd.net/servers, the only Spanish-language dedicated SharePoint site. Spanish-language readers may want to consult the author’s new book; “Programación con SharePoint 2007″ (http://www.dotnetmania.com/Libros/index.html).

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories