Get the Unique BCS record link and store in the backed for External List

Question

Hi All,

We have a requirement to send notifications whenever an item in BCS External List is updated based on some SQL Triggers. In that email notification we need to include a link to the BCS External List record which is updated.

Is there a way to identify the unique record {@BdcIdentity} from our BCS List and have it stored in the SQL Server for sending these notifications with links?

The notifications are sent from a powershell script which is called by SQL routine, hence it would be great to know if we can get this {@BdcIdentity} all the way back to the DB which is mapped to the external list.

Any response is appreciated.

Answer 1

Hello @Shalabh Gupta ,

The BDC Identity column resides in the SharePoint External List, not in SQL DB. Thus, its data is NOT transferred to SQL DB like the data in all the other fields.

You could try to the below script:

using Microsoft.SharePoint;  
using System;  
using System.Web;  
  
class whatif  
{  
    static void Main()  
    {  
        String siteUrl = "http://wn10101/StatusBook";  
  
        using (SPSite site = new SPSite(siteUrl))  
        {  
            site.AllowUnsafeUpdates = true;  
            SPSecurity.RunWithElevatedPrivileges(delegate()  
            {  
                using (SPServiceContextScope scope = new Microsoft.SharePoint.SPServiceContextScope(SPServiceContext.GetContext(site)))  
                using (SPWeb web = site.OpenWeb())  
                {  
                    SPList list = web.Lists["Manage Spread Steps"];  
  
                    // query items  
                    SPQuery query = new SPQuery();  
                    query.Query =  
                        @"<Where>  
                    <IsNull>  
                        <FieldRef Name='BDCIdentityKey' />  
                    </IsNull>  
                    </Where>  
                    <OrderBy>  
                        <FieldRef Name='Group'/>  
                    </OrderBy>";  
                    query.ViewFields =  
                        @"<ViewFields>  
                        <FieldRef Name='SpreadKey'/>  
                        <FieldRef Name='Topic'/>                    
                        <FieldRef Name='Group'/>  
                        <FieldRef Name='BdcIdentity'/>  
                        <FieldRef Name='BDCIdentityKey'/>  
                    </ViewFields>";  
  
                    // RowLimit has no effect  
                    query.RowLimit = 1;  
  
                    SPListItemCollection items = list.GetItems(query);  
  
                    foreach (SPListItem item in items)  
                    {  
                        item["BDCIdentityKey"] = item["BdcIdentity"].ToString();  
  
                        // Set all fields.  
                        item.Update();  
  
                        Console.WriteLine("SpreadKey: '{0}', Group: '{1}', BdcIdentity: {2}, BDCIdentityKey: {3}",   
                            item["SpreadKey"],  
                            item["Group"],  
                            item["BdcIdentity"],  
                            item["BDCIdentityKey"]);  
                    }  
                        Console.Write("Press Enter to continue!");  
                        Console.ReadLine();   
                }  
            });  
        }  
    }  
}  

Here is a similar case for your reference：

• Getting the BDC Identity value to SQL

Thanks,
Echo Du

==================

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Echo Du_MSFT

Thanks for the reply and sharing the c# code to get the unique-key from the backend.

Since we do not have a custom external list (just created from sharepoint designer), writing and hosting these scripts might not be possible at this moment. Is there a way to read some specific SP-Config database table where these keys are stored? If yes, we can then join those config tables with our BCS DB to achieve what we need.