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.