Exposing data from the BDC as a DataTable

Posted Wednesday, July 9, 2008 1:07 PM by CoreyRoth

Recently, I wanted to bind some data from the BDC to an ASP.NET control (i.e. a GridView, ListView, or DropDownList).  For what I needed a Business Data List web part just wouldn't cut it, so I decided to create something that I could bind to.  I also needed a quick and dirty way to get an XML representation of the data so that I could use it from InfoPath.  Normally, I avoid DataSets but for this purpose today, I am willing to tolerate it. 

First, we'll start with the process of how to retrieve data from the BDC through the API.  We'll start with the process to execute a BDC finder method.  The first thing you have to do is get a LobSystemInstance using the ApplicationRegistry object.  Application Registry is what the API guys came up with before marketing decided to call it the Business Data Catalog.  You pass it the name of the instance you want.  Note, this is the name of the instance in the XML file and not the application name you see in the BDC configuration pages.

// get a reference to the instance

LobSystemInstance instance = ApplicationRegistry.GetLobSystemInstanceByName("MyInstance");

 

// get a reference to the entity

Entity entity = instance.GetEntities()["MyEntity"];

Once you have the LobSystemInstance you need to get a reference to the entity you want.  Since we are creating a dataTable, we need to get a list of the Fields returned in the view and create a dataTable.  The Fields collection can be accessed after calling GetFinderView (use GetSpecificFinderView when calling the SpecificFinder method).  After that I call a simple method which manually creates a new dataTable given the fields I have provided.

// get a view so that the fields can be retrieved

FieldCollection fieldCollection = entity.GetFinderView().Fields;

 

// create a datatable based on the fields in the entity

DataTable dataTable = CreateDataTable(fieldCollection);

My CreateDataTable method is simple.  It just iterates through the field collection and adds a column to the table.

private DataTable CreateDataTable(FieldCollection fieldCollection)

{

    // create a new datatable

    DataTable dataTable = new DataTable();

 

    foreach (Field field in fieldCollection)

        dataTable.Columns.Add(field.Name);

 

    return dataTable;

}

To execute the BDC Finder method, you have to pass it a FilterCollection even if you aren't using any filters.  You then call the FindFiltered method which returns an IEntityInstanceEnumerator.

// get an empty filter collection

FilterCollection filterCollection = entity.GetFinderFilters();

 

// get an enumerator over IEntityInstances

IEntityInstanceEnumerator entityInstanceEnumerator = entity.FindFiltered(filterCollection, instance);

This gives you an enumerator, then you just need to use it and add rows to the dataTable.

// iterate through the entity instances

while (entityInstanceEnumerator.MoveNext())

{

    // add the row to the dataTable

    FillDataTable(dataTable, fieldCollection, entityInstanceEnumerator.Current);

}

The FillDataTable method just copies the values from the IEntityInstance into new dataRows.

private DataTable FillDataTable(DataTable dataTable, FieldCollection fieldCollection, IEntityInstance entityInstance)

{

    // create a new row based on the dataTable

    DataRow newDataRow = dataTable.NewRow();

 

    // add the data from each field into the datatable

    foreach (Field field in fieldCollection)

        newDataRow[field.Name] = entityInstance[field];

 

    // add the row to the table

    dataTable.Rows.Add(newDataRow);

 

    return dataTable;

}

After this we have a complete dataTable that can be used for binding or whatever.  You can do manual binding or could put this code in a class and have an ObjectDataSource call it.  As you can see in the example here, that I haven't included any exception handling or null checking.  Calling a SpecificFinder method is pretty similar except for a few changes. 

// get an entity instance given the id

IEntityInstance entityInstance = entity.FindSpecific(id, instance);

 

// get a view so that the fields can be retrieved

FieldCollection fieldCollection = entity.GetSpecificFinderView().Fields;

Instead of calling FindFiltered we call FindSpecifc passing in a value for the identity (i.e.: give me entity with id 37223).  The other change we make is that we get the FieldCollection from GetSpecificFinderView instead of GetFinderView.  Even if you don't want a dataTable, hopefully these examples will help you get the data out of the BDC into some form that you can use.

 

Comments

# Links (7/10/2008) « Steve Pietrek - Everything SharePoint

Pingback from  Links (7/10/2008) « Steve Pietrek - Everything SharePoint

# re: Exposing data from the BDC as a DataTable

Wednesday, August 6, 2008 1:48 PM by boneill

Great Tip! Saved me a lot of time trying to figure out how to get that data out!

one correction in your code:

// add the row to the dataTable

   FillDataTable(fieldCollection, entityInstanceEnumerator.Current);

should have the dataTable as well! Like:

FillDataTable(dataTable, fieldCollection, entityInstanceEnumerator.Current);

Also, I'll post this in the forums, but do you have any suggestions on using the business data type picker dialog box to open, so the user can select the entity??

# re: Exposing data from the BDC as a DataTable

Thursday, August 21, 2008 5:15 PM by CoreyRoth

Good catch.  I refactored the code some as I was putting it in this blog post, so I missed that.

I am not sure about how to implement the picker.  I would probably have to do some research / reflector work to figure it out.

# re: Exposing data from the BDC as a DataTable

Wednesday, July 7, 2010 2:07 AM by Damo

How do you do this in SharePoint 2010?

The function below is obsolete in 2010;

Microsoft.Office.Server.ApplicationRegistry.GetLobSystemInstanceByName("MyInstance");

Leave a Comment

(required) 
(required) 
(optional)
(required)