Populating the BDC Field of a SPListItem from client application
Recently I worked on a issue where our customer wants to create a list item from the client machine and wants to populate the BDC column of the List item. I believe we encounter this requirement very rarely and I have come up with some findings and as well some troubleshooting steps in this case. I have given the notes for all the issues I encountered.
Let’s first discuss what are the thoughts initially strikes us on this requirement.
1. We can make use of the OOB Lists.asmx web service to add an item to the list.
2. If we simply send the BDC field value like other column value then the item will be added but the field is not populated. It’s expected as the BDC field is going to Speak with the SharePoint through the Application definition file and it needs to be worked out with the Application registry API.
Note : While populating the BDC field we need to target the Identifier column of the Entity selected for the BDC field. Whenever you add any field from the Entity to show in the BDC column of the List the identifier column will be available as a hidden field. So using Application registry API’s we need to populate the BDC column through this field and not directly with the BDC column name. We can see more about this in the following code snippet.
3. As customer’s requirement is to populate the BDC field while creating list item from the client I just digged into the other OOB Office web services and found BDCFieldsResolver.asmx OOB web service which resolves the BDC Identifier column and gives it’s encoded value to populate.
4. But unfortunately the BDCFieldsResolver.asmx web service is giving the wrong encoded value of the identifier column. I found it wrong by comparing with the value given by the application registry API. When we use the Application registry API then the encoded value of the BDC column starts with “__bk….” But the “Resolve” method of BDCFiledsResolver.asmx web service is giving the encoded value starting with “__bg…” . Even hard coding the Encoded value of identifier column did not help in this case.
5. So the work around is to create a custom web service implementing the Application registry API to populate the BDC field and consume it in the client application.
Now let us have a deeper look on the application registry API : I have imported given the sample with the AdventureWorks ADF which gets the BDC data from the adventureworks database and sample shows the Product table used as the “Entity”
1. Set the SSP you are going to use
SqlSessionProvider.Instance().SetSharedResourceProviderToUse("SharedServices1");// set the SSP name where you have imported the ADF
2. Get the LOB system instance
NamedLobSystemInstanceDictionary sysInstances = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance AdvWorksIns = sysInstances["AdventureWorksSampleInstance"]; // Get the LOB instance where your entity is residing
3. Get the Entity which you selected for the BDC field
Entity prodEntity = AdvWorksIns.GetEntities()["Product"]; // Mention the Entity name
4. Once you got the entity then to get the data (Rows) of the entity you need to get the IEntityInstanceEnumerator to enumerate through each Entity instance. There are several ways to get the IEntityInstanceEnumerator
a. Entity enumeration using direct method execution against entities
b. Entity Enumeration using finder filters
In this sample we are using the Entity Enumeration using finder filters
FilterCollection iFilter = prodEntity.GetFinderFilters();
IEntityInstanceEnumerator Enum = prodEntity.FindFiltered(iFilter, AdvWorksIns);
5. Now we got the IEntityInstanceEnumerator and we are set to enumerate the entity instance. The following snippet shows that how you need to get the required encoded identifier field value . Point to remember here is that we should not target the BDC column name in the index of the SPListItem, we should populate the Internal field name of the Identifier column. In this sample the identifier column of the Product entity is “ProductID” and the internal name of the Identifier column in “Product_ID”
SPSite oSite = new SPSite(oSiteURL);
SPWeb oWeb = oSite.OpenWeb();
SPList oList = oWeb.Lists[oListName];
while (Enum.MoveNext())
{
IEntityInstance IEntInst = Enum.Current; // Get the current Entity instance
SPListItem Item = oList.Items.Add();
if (Enum.Current["ProductID"].ToString() == oBDCValue) // Check the Identifier column value with the value you want to populate
{
Item["Product_ID"] = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { Enum.Current["ProductID"].ToString() });
// a very important factor that mention the identifier columns internal name to populate the BDC field. Need to encode it as shown in this line. Here ["Product_ID"] is not the BDC column name it is the Internal identifier column name
Item["Title"] = oItemtitle;
Item.Update();
}
}
6. To test this first run this code in the server side and check whether the item is added successfully and the BDC field is populated. The following screenshot shows you that how the List item looks after adding the list item and populating the BDC field in this way. The Populated BDC field will show blank as we just targeted the hidden Identifier column Just do an Refresh of the BDC field so that it will get the BDC content from the database and will update the BDC field. (While refreshing it will send the Identifier column value which we populated to get the BDC data to display)
Note : The BDC column name is “BDCField” but the field we are populating is “Product_ID” which is the hidden Identifier column.
7. Following is the screenshot after refreshing the BDC field, Here I have populated the BDC field with ProductID = 2.
Note : Refreshing the BDC field will throw an error if the Identifier column type descriptor has been described as any other type than “System.String”. So whenever you populate the BDC field with Object model make sure that the Type of the Identifier column has been described as String in the application definition file.
8. Now create all the above steps as a web method in your custom web service and deploy it. When you are implementing this in the web service you need to remove the SqlSessionProvider.Instance().SetSharedResourceProviderToUse("SharedServices1") method call else it will throw an error stating that Cannot override the Shared Resource Provider context obtained from the Office Server. This API can be used only when an Office Server context is either internally unavailable or defined. Following is the final code snippet of the web method in the custom web service.
using Microsoft.Office.Server;
using Microsoft.Office.Server.Administration;
using Microsoft.Office.Server.ApplicationRegistry;
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.Office.Server.ApplicationRegistry.SystemSpecific;
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
[WebMethod]
public string AddItem(string oSiteURL,string oListName, string oBDCValue, string oItemtitle) // this method expects the Site URL, List Name, BDC(Identifier) field value, title of the item
{
try
{
NamedLobSystemInstanceDictionary sysInstances = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance AdvWorksIns = sysInstances["AdventureWorksSampleInstance"]; // Mention the LOB instance name where the entity resides
//Console.WriteLine("Getting an entity object and displaying its ID...");
Entity prodEntity = AdvWorksIns.GetEntities()["Product"]; // Mention the Entity name
FilterCollection iFilter = prodEntity.GetFinderFilters();
IEntityInstanceEnumerator Enum = prodEntity.FindFiltered(iFilter, AdvWorksIns);
SPSite oSite = new SPSite(oSiteURL);
SPWeb oWeb = oSite.OpenWeb();
SPList oList = oWeb.Lists[oListName];
while (Enum.MoveNext())
{
IEntityInstance IEntInst = Enum.Current;
SPListItem Item = oList.Items.Add();
if (Enum.Current["ProductID"].ToString() == oBDCValue)
{
Item["Product_ID"] = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { Enum.Current["ProductID"].ToString() }); // a very important factor that mention the identifier columns internal name to populate the BDC field. Need to encode it as shown in this line. In the enumerator we are going to populate only the value sent from the client application.
Item["Title"] = oItemtitle;
Item.Update();
}
}
return "True";
}
catch (Exception Ex)
{
return Ex.Message;
}
}
9. Consume this web service in your client application and you can add the List along with populating the BDC field.
Hope this helps in working with Object model related BDC issues.
Comments
Anonymous
May 27, 2009
PingBack from http://microsoft-sharepoint.simplynetdev.com/populating-the-bdc-field-of-a-splistitem-from-client-application/Anonymous
August 02, 2009
It would be awesome to see an example of this webservice being called from within a SharePoint event handler to update an existing item.Anonymous
November 16, 2009
Excellent article, works fine for me. All other related fields are also populated, But when I update all list items with latest data from BDC by clicking column header, I get this error "An error occurred while retrieving data from <BDC>. Administrators, see the server log for more information" But if I edit manually the same item without changing anything, item will be updated with successfully. Any idea ?Anonymous
August 30, 2010
This was really helpful. Thanks :)Anonymous
August 30, 2010
This was really helpful. Thanks :)Anonymous
October 21, 2010
Anyway to do the refresh programatically ? So you dont have to manually refresh the field after the upload.Anonymous
September 28, 2011
Hello! I saw you use hardcoded SharedServices name ("SharedServices1"), but there is an ability to get the name of SharedServices, which serves your web application. It's shown here - dotnetfollower.com/.../sharepoint-how-to-get-shared-service-name. Thanks!Anonymous
August 26, 2014
Hello! Had the same problems with encoded entities until I noticed that: EncodeEntityInstanceID( new object[] { "5" } ) returns "__bk..." EncodeEntityInstanceID( new object[] { 5 } ) returns "__bg...".