Get the BDC Identity value from a SharePoint 2010 External List to SQL Server

Why bother? Well, because without the BDC Identity value, it is nearly impossible to hyperlink directly to the Edit form of an External List from anywhere on a SharePoint web site.

Say you have a Reporting Services web part, and you want to position users just one click away from of changing a value from red to green in the report. If you could access the BDC Identity value from SQL and SSRS, you could add an Go to URL Action to an element that opens the External List to a specific Edit form with a URL string that contains the BDC Identity in its &Source value.

This task needs to be done by people in the department that create the data – not by techs or Server Administrators. This is SharePoint, so they expect something simple or out-of-the-box to do the job.

Simply using a URL query string with FilterFields on some other value in the external list will open the external list and filter on the value, but the UX requires two clicks and leaves two windows to close after each procedure. And if there is a way to copy the BDC Identity to SQL using jQuery, ECMAScript, or a Workflow, it did not reveal itself.

As it turns out, a low cost and low demand way to accomplish this task can be to build a Visual Studio 2010 C# Express Console Application that follows the Client Object Model. It can be deployed to a shared drive on a server as an EXE file that can then be run by subject matter experts from a desktop shortcut.

The following Program.cs, for example, uses the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime dll’s to open the SharePoint site and find the External List by name.

using System;
using Microsoft.SharePoint.Client;

class Program
{
   public static void Main()
    {

    String siteUrl = "http://whatever/whatever";

	    ClientContext clientContext = new ClientContext(siteUrl);

	    List list = clientContext.Web.Lists
	        .GetByTitle("name of external list");
	    CamlQuery camlQuery = new CamlQuery();

	    // RowLimit will have no effect
	    camlQuery.ViewXml =

It uses this XML section to return all empty fields in a column of SQL origin with a string datatype called “CopyOfBdcIdentity.”

	        @"<View>
	            <Query>
	              <Where>
	                <IsNull>
	                    <FieldRef Name='CopyOfBdcIdentity' />
	                </IsNull>
	              </Where>
	              <OrderBy>
                      <FieldRef Name='Group'/>
	              </OrderBy>
	            </Query>
	            <ViewFields>
                        <FieldRef Name='ID'/>
	                <FieldRef Name='Title'/>
	                <FieldRef Name='BdcIdentity'/>
	                <FieldRef Name='CopyOfBdcIdentity'/>
	            </ViewFields>
	            <RowLimit>1</RowLimit>
	        </View>";

It then loops through the results, copies the BDC Identity value as a string to the CopyOfBdcIdentity in each item, and reports on the results.

	    // include referenced field here as well
	    ListItemCollection listItems = list.GetItems(camlQuery);
	    clientContext.Load(
	         listItems,
	         items => items
	             .Include(
	                item => item["ID"],
	                item => item["Title"],
	                item => item["BdcIdentity"],
	                item => item["CopyOfBdcIdentity"]));
	    clientContext.ExecuteQuery();

	    // loop through the result
	    foreach (ListItem listItem in listItems)
	    {
            listItem["CopyOfBdcIdentity"] = listItem["BdcIdentity"].ToString();

            // update all fields
            listItem.Update();

            // display the result
            Console.WriteLine("SpreadKey: '{0}', Group: '{1}'",
	            listItem.FieldValues["ID"],
	            listItem.FieldValues["Title"]);
	    }

	    // invoke ExecuteQuery a second time
            clientContext.ExecuteQuery();

	    Console.Write("Update complete, press Enter to continue!");
	    Console.ReadLine();
   }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s