Using Business Data Catalog Data in SharePoint with Custom Web Parts
It has been awhile since we have posted, but we have been so busy with all the great stuff that is actually working in Beta 2 TR of MOSS. We have been immersed in Business Data Catalog, KPI, Excel Web Access and Forms Server. There is just so much great stuff it is hard to figure out where to start.
BDC is what I consider the most powerful new feature coming out with MOSS 2007, the ability to easily discover and access backend disparate back end systems, just opens up the world of possibilities.
I’ve been playing with it to connect to a mid-size backend SQL Server database, and I wanted to give a run down of what I found, and what things we did to use the data in SharePoint in a little more friendlier rendering.
Application Definition
The application definition is the XML file, that describes the metadata model for the backend system that you are connecting to. It translates the backend meta data, into the different Entities, Methods etc, that you will be accessing with Business Data Catalog, the Adventures example that Microsoft includes in the SDK is a great example of defining the metadata for a connection.
Here is a link to the AdventureWorks Sample
http://msdn2.microsoft.com/en-us/library/ms519241.aspx
There are some good tools out there already for helping you to create these application definition language, I used both
BDCGenerator - http://weblog.vb-tech.com/nick/archive/2006/09/25/1796.aspx
Todd Baginski’s MOSS BDC MetaData Manager - http://www.sharepointblogs.com/tbaginski/archive/2006/07/16/9259.aspx
The Meta Data manager was really great, and was a great kick start in defining the Application Definition. I got the definitions defined, and I was pulling Entities from the BDC and using the BDC list web part to display them, and I was grabbing all these great data. I was able to define some Filters etc, and I got this output.
The information is great, and the ability to filter add actions etc, is just great, but I want to aggregate the data from a few different BDC connections, and display it in a chart format to give the user a more graphical representation of the data. We have built a few different UI pieces to render SharePoint data, so I just changed where the data was being grabbed from, and I was able to get it into a SPDataGrid control.
Now, this is cool because we have the data in the a web control and we can customize add to it, add filtering or grouping or aggregating features right into it. So the next step was to filter, group the data and through it at our charting solution and POW!
We are currently fleshing this out, so we can make it a little more modular and flexible, but with how our UI renders the data, all we need to through at our different displays is a .NET System.Data.DataTable and we have a ton of control over the rendering.
This is the code that I used to take the data out of the BDC, and put it into a DataTable, and from here you can do almost anything you want with it.
The first method builds a datatable that matches the BDC Entity, and the second bit of code populates it with data. You will need to play your Entity and Instance name in the 2 places in the code, or you could use the object model, and store those in a list and pull out of there if you close, or in properties in the web part.
|
// First build the DataTable definition to make the BDC fields from the Entity
private static DataTable BuildDataTable(FieldCollection fieldCollection)
{
DataTable dt = new DataTable();
// Go through each field, and get the columns, and build a field
foreach (Field f in fieldCollection)
{
DataColumn dc = new DataColumn(f.Name, Type.GetType(f.TypeDescriptor.TypeName));
dt.Columns.Add(dc);
}
return dt;
}
|
|
// Now after building the table populate you it with Data
NamedLobSystemInstanceDictionary sysInstances = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance myIns = sysInstances["NAMEOFYOURBDCINSTANCE"];
Entity myEntity = myIns.GetEntities()["NAMEOFYOURBDCENTITY"];
FilterCollection fc = myEntity.GetFinderFilters();
IEntityInstanceEnumerator prodEntityInstanceEnumerator = myEntity.FindFiltered(fc, myIns);
// Build a datatable
dtResults BuildDataTable(myEntity.GetFinderView().Fields);
while (prodEntityInstanceEnumerator.MoveNext())
{
IEntityInstance IE = prodEntityInstanceEnumerator.Current;
DataRow dr = dtResults.NewRow();
foreach (Field f in prodEntity.GetFinderView().Fields)
{
if (IE[f] != null)
{
dr[f.Name] = IE[f];
}
}
// Add the Rows to the Data Table
dtResults.Rows.Add(dr);
}
|
As you can see the possibilities once you get the data from the BDC with custom web parts, is phenomenal. The performance has been pretty good so far, as we are trying to test it with more and more data. There are two tips that are incredibly helpful from the SDK if you didn’t notice when building custom BDC web parts, I thought I would list them here.
The name space for the BDC in the Object Model is “Application Registry”, that was the originally name and it can be very confusing at first, if you don’t know that, looking through the SDK for the BDC information, the second tip from the FAQ in the SDK that was helpful, is the BdcMetaData.xsd file.
This is a great file if you are building the application definition xml files by hand, in Visual Studio, you can use this XSD as the schema for your XML and you can use intellisence while you are editing the files. I started off with the Adventure Works example and the MetaData Manager, then I went to editing the files in the Visual Studio, so this file was very helpful. The location of it is,
<Root>\Program Files\Microsoft Office Server\12.0\Bin
As the SDK notes.
I’m very excited about BDC, and how easily it is to access and discover data in the back end system, I have more ideas about consuming and doing more with the BDC data once it is in SharePoint, and how it interacts w/ the Excel Services. That is going to be my next thing to tackle. I hope this was helpful.
-=
Morgan
