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

Say you have a Reporting Services web part, and you want to change an indicator on the report from red to green with a single click. To do this, without relying on SharePoint Server Admins, build a Visual Studio 2010 C# Express Console Application following the Client Object Model.

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();

When done, compile and deploy code as an EXE file to a shared server location, and run it from a Windows desktop shortcut.