Ever wondered how to write code against the BDC?

SharePoint 2007: BDC – The Business Data Catalog  (this is from http://blah.winsmarts.com/2007-4-SharePoint_2007__BDC_-_Writing_Custom_Code_against_the_runtime_object_model.aspx)

This post is in continuation to a series of blogposts I put up on BDC – the Business Data Catalog.

Table of Contents:

static void Main(string[] args)
{
	string ssp = "SharedServices1";

	SqlSessionProvider.Instance().SetSharedResourceProviderToUse(ssp);
	NamedLobSystemDictionary lobSystems = ApplicationRegistry.GetLobSystems();

	foreach (LobSystem system in lobSystems.Values)
	{
		Console.WriteLine("Application: " + system.Name);

		NamedDataClassDictionary lobClasses = system.GetDataClasses();

		foreach (DataClass dataClass in lobClasses.Values)
		{
			Console.WriteLine("  Entities: " + dataClass.Name);
		}
	}
}

If I run the above, I get this output –

Application: NorthWindApp
  Entities: Order
  Entities: Customer

If you are wondering, I am using namespaces

    Microsoft.Office.Server.ApplicationRegistry.MetaDataModel
    Microsoft.Office.Server.ApplicationRegistry.Infrastructure.

The references are

    Microsoft.Office.Server.dll
    Microsoft.SharePoint.dll
    Microsooft.SharePoint.Portal.dll

Now, Let us slice and dice the above code step by step.

The first thing you see in the above code is SqlSessionProvider. SqlSessionProvider represents a way to connect to the SSP. It immediately sticks out that the BDC runtime can target only the local farm. So, if you wish to allow BDC access over the network, you will need to write a webservice and deploy it on the farm, and access it via the web service.

Once you get a hold of an instance using SqlSessionprovider.Instance(), you can then start querying it for various stuff. For instance, the first thing I wanted to find out were all the LobSystems (i.e. applications) registered in the SSP. And once I have the applications found, I can find the individual entities in each one of those classes.

Once I have the individual entities, I can easily find the methods that entity has using the code below –

DataClass customers = lobClasses["Customer"];
NamedMethodDictionary methods = customers.GetMethods();

foreach (Method meth in methods.Values)
{
	Console.WriteLine("Method: " + meth.Name);
}

You can continue to sniff around, and you will see that you can find methods, method instances, properties, heck if you snooped around enough, you could literally smell the entire metadata for the BDC app.

So now that I have information about the BDC app, the next question is “How to use it”.

Using your BDC App.

In order to actually use your BDC App, you need to first get a hold of the LobSystemInstances. Check the code out below –

static void Main(string[] args)
{
	string ssp = "SharedServices1" ;

	SqlSessionProvider.Instance().SetSharedResourceProviderToUse(ssp);

	NamedLobSystemDictionary lobSystems = ApplicationRegistry.GetLobSystems();

	foreach (LobSystem system in lobSystems.Values)
	{
		Console.WriteLine("Application: " + system.Name);

		NamedLobSystemInstanceDictionary instances =

		lobSystem.GetLobSystemInstances();

		foreach (LobSystemInstance instance in instances.Values)
		{
			Console.WriteLine(instance.Name);
		}
	}
}

You might ask, what is the difference between a LobSystem and a LobSystemInstance? BDC in MOSS 2007 lets you put upto two instances on each LobSystem. This means, you get different authentication settings for the two instances, for the same application. This is incredibly useful, say when you are setting up search on BDC, because you would typically want search to work under different credentials than regular BDC usage by a user.

Once you have the specific instance, you can then find the entity, the filters, set filter values, and finally execute the Finder method using the following code –

Entity customerEntity = instance.GetEntities()["Customer"];
FilterCollection filterColl = customerEntity.GetFinderFilters();

(filterColl[0] as WildcardFilter).Value = "London";

IEntityInstanceEnumerator customerEnum = customerEntity.FindFiltered(filterColl, instance);

while(customerEnum.MoveNext())
{
	DataTable dt = (customerEnum.Current as DbEntityInstance).EntityAsDataTable;
	PrintDataRow(dt.Rows[0]);
}

PrintDataRow is simply a method that prints out the values of the DataRow in a single line.

The full final code looks like this.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
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;

namespace SampleBDCApp
{
	class Program
	{
		static void Main(string[] args)
		{
			string ssp = "SharedServices1";
			SqlSessionProvider.Instance().SetSharedResourceProviderToUse(ssp);

            NamedLobSystemDictionary lobSystems = ApplicationRegistry.GetLobSystems();

            foreach (LobSystem system in lobSystems.Values)
            {
				Console.WriteLine("Application: " + system.Name);
                NamedLobSystemInstanceDictionary instances =                     system.GetLobSystemInstances();

                foreach (LobSystemInstance instance in instances.Values)
                {
					Console.WriteLine(instance.Name);

					Entity customerEntity = instance.GetEntities()["Customer"];
					FilterCollection filterColl = customerEntity.GetFinderFilters() ;

                    (filterColl[0] as WildcardFilter).Value = "London";
                    IEntityInstanceEnumerator customerEnum =  customerEntity.FindFiltered(filterColl, instance);

                    while(customerEnum.MoveNext())
                    {
						DataTable dt = (customerEnum.Current as DbEntityInstance).EntityAsDataTable;
					PrintDataRow(dt.Rows[0]);
                    }
				}
			}
		}

        private static void PrintDataRow(DataRow dr)
        {
			foreach (DataColumn dataColumn in dr.Table.Columns)
            {
				Console.Write(dr[dataColumn] + "t");
			}
			Console.WriteLine();
		}
	}
}

.. and when you execute this, you get the following output –

Congratulations – you’ve just written your first application using BDC. You can now implement the above in a web service, and expose any underlying LOB using a consistent BDC API.

Here is an interesting exercise for you to try – Try using Office SSO + WindowsCredentials, so the end application’s identity matters when calling the web service, and that same identity goes all the way to the web service.

Have fun!