Second Life of a Hungarian SharePoint Geek

August 21, 2013

March 13, 2012

Implementing a simple secure store provider

Filed under: BCS, Security, SP 2010 — Tags: , , — Peter Holpar @ 23:27

In my recent post I described two alternatives for accessing external data from the OWS process. One of the options – the RevertToSelf authentication – is not the best choice for a production environment. The other options – the Impersonate Windows Identity authentication mode – requires Secure Store Service (SSS) service, that is a SharePoint Server 2010 feature. It means you might have no ideal solution if you have a SharePoint Foundation 2010 environment.

Although the out-of-the-box Secure Store Implementation is the Microsoft.Office.SecureStoreService.Server.SecureStoreProvider class (Microsoft.Office.SecureStoreService assembly), that is really SharePoint Server only, the main interfaces located in the Microsoft.BusinessData.Infrastructure.SecureStore namespace(Microsoft.BusinessData assembly), that is available in SharePoint Foundation as well.

It means we can implement a custom Secure Store Implementation for SharePoint Foundation.

Below I show you a very basic sample for that. Of course, you can create more sophisticated versions based on the same concepts.

In Visual Studio 2010 we should create a new Class Library project. The target framework should be set to .NET 3.5 and platform target as x64. Since we have to deploy our assembly to GAC, we need a key file as well, to sign the strong-named assembly.

First I include the code for the helper classes.

The SimpleTargetApplicationDefinition class holds information about our target application.

  1. namespace SimpleSecureStoreProvider
  2. {
  3.     public class SimpleTargetApplicationDefinition : ITargetApplicationDefinition
  4.     {
  5.         public string ContactEmail { get; internal set; }
  6.  
  7.         public Uri CredentialManagementUrl { get; internal set; }
  8.  
  9.         public string FriendlyName { get; internal set; }
  10.  
  11.         public string Name { get; internal set; }
  12.  
  13.         public TargetApplicationType Type { get; internal set; }
  14.     }
  15. }

The SimpleTargetApplicationField describes a field of the target application.

  1. namespace SimpleSecureStoreProvider
  2. {
  3.     public class SimpleTargetApplicationField : ITargetApplicationField
  4.     {
  5.         public SecureStoreCredentialType CredentialType { get; internal set; }
  6.  
  7.         public bool IsMasked { get; internal set; }
  8.  
  9.         public string Name { get; internal set; }
  10.     }
  11. }

The SimpleSecureStoreCredential class contains a piece of the credential information.

  1. namespace SimpleSecureStoreProvider
  2. {
  3.     public class SimpleSecureStoreCredential : ISecureStoreCredential
  4.     {
  5.         bool disposed;
  6.  
  7.         public SecureString Credential { get; internal set; }
  8.  
  9.         public SecureStoreCredentialType CredentialType { get; internal set; }
  10.  
  11.         public void Dispose()
  12.         {
  13.             this.Dispose(true);
  14.         }
  15.  
  16.         private void Dispose(bool disposing)
  17.         {
  18.             if (!disposed)
  19.             {
  20.                 if (Credential != null)
  21.                 {
  22.                     Credential.Dispose();
  23.                 }
  24.                 disposed = true;
  25.             }
  26.         }
  27.  
  28.     }
  29. }

The main part of the code is the provider itself, that is SimpleSecureStoreProvider class. The skeleton of the class is like this:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Collections.ObjectModel;
  6. using Microsoft.BusinessData.Infrastructure.SecureStore;
  7. using System.Diagnostics;
  8. using System.Security;
  9.  
  10. namespace SimpleSecureStoreProvider
  11. {
  12.     public class SimpleSecureStoreProvider : ISecureStoreProviderExtended, ISecureStoreProvider
  13.     {
  14.     
  15.         …
  16.  
  17.     }
  18. }

We should return the application definition in the GetTargetApplication method. In this sample we return always the same data, regardless of the appId parameter.  As you can see, the sample is an example for a Group Target Application Type. In the GetTargetApplications method we return a single target application. I’ve included Trace commands to help us to monitor which methods are invoked in runtime.

  1. public ITargetApplicationDefinition GetTargetApplication(string appId)
  2. {
  3.     Trace.TraceInformation("GetTargetApplication {0} ({1})", appId, Process.GetCurrentProcess().Id);
  4.     return new SimpleTargetApplicationDefinition
  5.     {
  6.         ContactEmail = "user@company.com",
  7.         CredentialManagementUrl = null,
  8.         FriendlyName = "TargetApp",
  9.         Name = "AppDef",
  10.         Type = TargetApplicationType.Group
  11.     };
  12. }
  13.  
  14. public ReadOnlyCollection<ITargetApplicationDefinition> GetTargetApplications()
  15. {
  16.     Trace.TraceInformation("GetTargetApplications ({1})", Process.GetCurrentProcess().Id);
  17.     return new ReadOnlyCollection<ITargetApplicationDefinition>(new List<ITargetApplicationDefinition>() { GetTargetApplication("app") });
  18. }

In this application we have two fields, one for the user name and one for the password, as shown in the GetTargetApplicationFields method.

  1. public ReadOnlyCollection<ITargetApplicationField> GetTargetApplicationFields(string appId)
  2. {
  3.     Trace.TraceInformation("GetTargetApplicationFields {0} ({1})", appId, Process.GetCurrentProcess().Id);
  4.     List<ITargetApplicationField> fields = new List<ITargetApplicationField>();
  5.     fields.Add(new SimpleTargetApplicationField
  6.     {
  7.         CredentialType = SecureStoreCredentialType.WindowsUserName,
  8.         IsMasked = false,
  9.         Name = "UserName"
  10.     });
  11.     fields.Add(new SimpleTargetApplicationField
  12.     {
  13.         CredentialType = SecureStoreCredentialType.WindowsPassword,
  14.         IsMasked = false,
  15.         Name = "Password"
  16.     });
  17.  
  18.     return new ReadOnlyCollection<ITargetApplicationField>(fields);
  19. }

In the GetCredentials and GetRestrictedCredentials methods we should return credentials for the application. In this case I have a user name and password hardcoded, but these should be stored in a bit more secure and configurable way.

  1. public SecureStoreCredentialCollection GetCredentials(string appId)
  2. {
  3.     Trace.TraceInformation("GetCredentials {0} ({1})", appId, Process.GetCurrentProcess().Id);
  4.  
  5.     List<ISecureStoreCredential> creds = new List<ISecureStoreCredential>();
  6.  
  7.     creds.Add(new SimpleSecureStoreCredential
  8.     {
  9.         Credential = GetSecureString(@"domain\user"),
  10.         CredentialType = SecureStoreCredentialType.UserName
  11.     });
  12.     creds.Add(new SimpleSecureStoreCredential
  13.     {
  14.         Credential = GetSecureString("password"),
  15.         CredentialType = SecureStoreCredentialType.Password
  16.     });
  17.     return new SecureStoreCredentialCollection(creds);
  18. }
  19.  
  20. public SecureStoreCredentialCollection GetRestrictedCredentials(string appId)
  21. {
  22.     Trace.TraceInformation("GetRestrictedCredentials {0} ({1})", appId, Process.GetCurrentProcess().Id);
  23.  
  24.     return GetCredentials(appId);
  25. }

The GetSecureString method helps to convert a string into SecureString.

  1. private SecureString GetSecureString(string value)
  2. {
  3.     SecureString result = new SecureString();
  4.     if (value != null)
  5.     {
  6.         value.ToCharArray().ToList().ForEach(c => result.AppendChar(c));
  7.     }
  8.  
  9.     return result;
  10. }

The methods below are not implemented in this sample:

  1. public void DeleteCredentials(string appId)
  2. {
  3.     Trace.TraceInformation("DeleteCredentials {0} ({1})", appId, Process.GetCurrentProcess().Id);
  4.  
  5.     throw new NotImplementedException();
  6. }
  7.  
  8. public SecureStoreCredentialCollection GetCredentialsUsingTicket(string ticket, string appId)
  9. {
  10.     Trace.TraceInformation("GetCredentialsUsingTicket {0} ({1})", appId, Process.GetCurrentProcess().Id);
  11.  
  12.     throw new NotImplementedException();
  13. }
  14.  
  15. public string IssueTicket()
  16. {
  17.     Trace.TraceInformation("IssueTicket ({1})", Process.GetCurrentProcess().Id);
  18.  
  19.     throw new NotImplementedException();
  20. }
  21.  
  22. public ISecureStoreProviderInformation ProviderInformation
  23. {
  24.     get
  25.     {
  26.         Trace.TraceInformation("ProviderInformation ({1})", Process.GetCurrentProcess().Id);
  27.        
  28.         throw new NotImplementedException();
  29.     }
  30. }

After building the assembly and deploying it to the GAC, we should configure our BCS external system instance. Most important is to set the right Secure Store Implementation value (in my case it is SimpleSecureStoreProvider.SimpleSecureStoreProvider, simplesecurestoreprovider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9fc83bb193b5ea3b). The value of the Secure Store Target Application Id is irrelevant in this version.

image

If everything works as expected, we should be able to access the external data (in this case the Northwind database through an external list) using the credentials returned by our custom secure store implementation.

image

March 12, 2012

Solving the external data access security issue in the case of the OWS process

In the recent two posts I wrote about a security related problem we found when tried to access an external data source from the SharePoint 2010 Timer service process.

After presenting a workaround in the first part, last time I promised a real solution for the issue.

The key to the solution seems to be the information one can found on this MSDN page.

As it states, “The user security token is not available in every context. Without the security token, PassThrough security will not work.

As you may know, PassThrough is the standard and usually recommended authentication method to an external data source, but in this case we should switch to an alternative one.

The trivial solution would be to use the RevertToSelf authentication. Since this type of authentication is not recommended in a production environment, it is disabled by default. Before using it, you should enable it, for example with the help of PowerShell (see an example here).

After you enabled RevertToSelf, you can find the equivalent BDC Identity option in the list of the available authentication modes:

image

(To access the settings above, you should select the External Systems view at the administration of the Business Data Connectivity Service, then click the name of the external system you would like to manage, and then click the name of the external system instance.)

After you selected BDC Identity authentication mode, you can use this code to access the external system:

  1. using (SPSite site = new SPSite("http://sp2010&quot;))
  2. {
  3.     using (SPWeb web = site.OpenWeb())
  4.     {
  5.         Guid siteId = site.ID;
  6.         Guid webId = web.ID;
  7.  
  8.         SPSecurity.RunWithElevatedPrivileges(delegate()
  9.         {
  10.             using (SPSite siteImp = new SPSite(siteId))
  11.             {
  12.                 // access external list here
  13.             }
  14.         });
  15.     }
  16. }

The other, and recommended option for authentication is to use the Secure Store Service (SSS).

Note: Secure Store Service is not included in SharePoint Foundation 2010, so this option is unfortunately limited for SharePoint Server 2010 Standard and Enterprise versions.

Create a target application in SSS,

image

then set the credentials of an account with permissions for the external system.

Next, configure your external system to Impersonate Windows Identity, set the name of the Secure Store Target Application Id as created in the previous step, and set  Secure Store Implementation as Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c.

image

After you configured your external system instance as described above, you can use this code to access the external data from the timer process job / event receiver:

  1. using (SPServiceContextScope scope = new SPServiceContextScope(SPServiceContext.GetContext(web.Site)))
  2. {
  3.     // access external list within this block
  4. }

Important to note, that based on the MSDN article mentioned at the beginning of this post, workflows and sandboxed solutions might suffer from the same security problem, so if you have such issues accessing an external data source, the solutions described above might help you in these cases as well.

February 19, 2012

Efforts taken to fool (or fix?) external data access security from the OWS timer process code

Based on the feedback I received to my last post, I think it would be useful to provide a bit more details about the different impersonation / context change methods I’ve tried to applied in my code when experimenting with external data access from OWS timer process. I will include a few helper methods in this post as well, for example one that can be used to trace out the current process context and identity details:

  1. private void TraceUserNames(string msg, SPWeb web)
  2. {
  3.     string httpContextUser = ((HttpContext.Current != null) && (HttpContext.Current.User != null) && (HttpContext.Current.User.Identity != null)) ?
  4.         HttpContext.Current.User.Identity.Name : string.Empty;
  5.     string windowsIdentity = (WindowsIdentity.GetCurrent() != null) ? WindowsIdentity.GetCurrent().Name : string.Empty;
  6.     string spUserName = ((web != null)) ? web.CurrentUser.LoginName : string.Empty;
  7.     Trace.TraceInformation("MailTestEventReceiver.ItemAdded, {3}. httpContextName: '{0}', windowsIdentity: '{1}', spUserName: '{2}'",
  8.        httpContextUser, windowsIdentity, spUserName, msg);
  9. }

You should include

using System.Diagnostics;

for the Trace commands. Using a method like this one is always suggested when you need to get information about what context your the process is running in.

The following code access the external list using the default context of the process:

  1. SPWeb web = properties.Web;
  2. HttpContext originalContext = HttpContext.Current;
  3.  
  4. TraceUserNames("Before impersonation", web);
  5. TestExtList(web);

In this case, TestExtList was a simple method to test external list access from code:

  1. private void TestExtList(SPWeb web)
  2. {
  3.     try
  4.     {
  5.         SPList extList = web.Lists[Global.ExtList];
  6.         SPListItemCollection items = extList.GetItems(extList.DefaultView);
  7.         Trace.TraceInformation("MailTestEventReceiver.TestExtList item count: {0}", items.Count);
  8.     }
  9.     catch (Exception ex)
  10.     {
  11.         Trace.TraceInformation("ERROR in MailTestEventReceiver.TestExtList: '{0}', '{1}'", ex.Message, ex.StackTrace);
  12.     }
  13. }

When uploading documents from the UI, the result of the TraceUserNames method:

Before impersonation. httpContextName: ”, windowsIdentity: ‘domain\user’, spUserName: ‘domain\user’

However, when the code is triggered by an incoming mail, the result is pretty different:

Before impersonation. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

That is true, even if the E-mail security policy at Incoming e-mail settings is set to Accept e-mail messages based on document library permissions (A).

image

So this setting seems to have no effect on the context identity.

The differences I found between this one and Accept e-mail messages from any sender (B).

  1. In the first case (A) a user is looked up based on the e-mail address, and if no one is found or this user has no write permission, the mail won’t be delivered to the document library. In the case (B) the mail is delivered without this kind of verification.
  2. In the case (A) Created By / Modified By fields are set to the user determined by the e-mail address. In the case (B) theses fields are set to SHAREPOINT\system. This latter one correlates with the SharePoint user identity I’ve found for the incoming mail process (see above).

Note: It means that no really security check / authorization happens here. The SMTP mails required no authentication, and it is not so complex to fake a mail with a sender mail address that has write access to the library.

As you may remember from the former post, accessing the external data from code was successful when the document was uploaded from the browser, however it failed with the exception below, when sent as a mail attachment to the library.

Access denied by Business Data Connectivity.
at Microsoft.SharePoint.SPListDataSource.GetEntityInstanceEnumerator(XmlNode xnMethodAndFilters)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstancesInternal(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

To fix this, I first tried to use “simple” elevated permissions:

  1. Guid siteId = properties.SiteId;
  2. Guid webId = properties.Web.ID;
  3.  
  4. SPSecurity.RunWithElevatedPrivileges(delegate()
  5.     {
  6.         using (SPSite siteImp = new SPSite(siteId))
  7.         {
  8.             using (SPWeb webImp = siteImp.OpenWeb(webId))
  9.             {
  10.                 TraceUserNames("Using elevated privileges", webImp);
  11.                 TestExtList(webImp);
  12.             }
  13.         }
  14.     });

The output of the TraceUserNames method was:

Using elevated privileges. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

The exception on external data access was in this case:

Attempted to perform an unauthorized operation.
at Microsoft.SharePoint.SPListDataSource.CheckUserIsAuthorized(SPBasePermissions perms)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

My next thought was that I should probably apply some kind of impersonation to reproduce the context I found when uploading the document from the browser (see the first TraceUserNames output above).

To get the user I should to impersonate, I read the value of the Modified By field (as discussed earlier, this field contains the sender in scenario A).

  1. SPListItem item = properties.ListItem;
  2. Object editorFieldValueRaw = item[SPBuiltInFieldId.Editor];
  3. SPFieldUserValue editorFieldValue = (editorFieldValueRaw is String) ?
  4.     new SPFieldUserValue(web, (String)editorFieldValueRaw) :
  5.     (SPFieldUserValue)editorFieldValueRaw;
  6. SPUser editor = editorFieldValue.User;
  7. Trace.TraceInformation("User to impersonate: '{0}'", editor.LoginName);

The following code used to impersonate the SharePoint user:

  1. using (SPSite siteImp = new SPSite(siteId, editor.UserToken))
  2. {
  3.     using (SPWeb webImp = siteImp.OpenWeb(webId))
  4.     {
  5.         TraceUserNames("After impersonation", webImp);
  6.         TestExtList(webImp);
  7.     }
  8. }

The output of the TraceUserNames method was:

After impersonation. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘domain\user’

I’ve received the original Access Denied exception when tried to get the number of item in the external list.

Next, I’ve injected a HttpContext as described in this and this posts.

  1. IPrincipal impersonationPrincipal = new WindowsPrincipal(new WindowsIdentity(GetUpn(editor)));
  2. HttpRequest request =
  3. new HttpRequest(string.Empty, properties.WebUrl, string.Empty);
  4.  
  5. HttpContext originalContext = HttpContext.Current;
  6.  
  7. HttpResponse response = new HttpResponse(
  8.      new System.IO.StreamWriter(new System.IO.MemoryStream()));
  9.  
  10. HttpContext impersonatedContext = new HttpContext(request, response);
  11. // these lines required to inject SPContext as well
  12. // if you don't need that it can be deleted
  13. impersonatedContext.User = impersonationPrincipal;
  14. if (web != null)
  15. {
  16.     impersonatedContext.Items["HttpHandlerSPWeb"] = web;
  17. }
  18. HttpContext.Current = impersonatedContext;
  19.  
  20. TraceUserNames("Dummy HTTP context", web);
  21. TestExtList(web);
  22.  
  23. HttpContext.Current = originalContext;

The output of the TraceUserNames method was:

Dummy HTTP context. httpContextName: ‘domain\user’, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

Again, I’ve received the original Access Denied exception.

Finally, I’ve applied Windows impersonation (after granting the Act as part of the operating system user right to the SharePoint 2010 Timer service identity):

  1. WindowsImpersonationContext impersonationContext = null;
  2. try
  3. {
  4.     WindowsIdentity userIdentity = new WindowsIdentity(GetUpn(editor));
  5.     impersonationContext = userIdentity.Impersonate();
  6.  
  7.     using (SPSite siteImp = new SPSite(siteId))
  8.     {
  9.         using (SPWeb webImp = siteImp.OpenWeb(webId))
  10.         {
  11.             TraceUserNames("After Windows impersonation", webImp);
  12.             TestExtList(webImp);
  13.         }
  14.     }
  15.  
  16. }
  17. catch (Exception ex)
  18. {
  19.     Trace.TraceInformation("ERROR in MailTestEventReceiver.ItemAdded impersonation: '{0}', '{1}'", ex.Message, ex.StackTrace);
  20. }
  21. finally
  22. {
  23.     if (impersonationContext != null)
  24.     {
  25.         impersonationContext.Undo();
  26.     }
  27. }

The GetUpn method used in the former code (using System.DirectoryServices.ActiveDirectory namespace is required):

  1. private static string GetUpn(SPUser spUser)
  2. {
  3.     string[] userName = spUser.LoginName.Split('\\');
  4.     Domain domain = Domain.GetCurrentDomain();
  5.     string upn = userName[1] + "@" + domain.Name;
  6.     Trace.TraceInformation("MailTestEventReceiver.GetUpn result: '{0}'", upn);
  7.     return upn;
  8. }

The output of the TraceUserNames method was:

After Windows impersonation. httpContextName: ”, windowsIdentity: ‘domain\user’, spUserName: ‘SHAREPOINT\system’

Again, I’ve received the original Access Denied exception.

As you can see, none of this methods resulted the same output as the one from the user interface-based upload, however mixing the methods (Windows + SharePoint impersonation) the output was the same. However, it did not help to avoid the exception, so the problem seemed to be a little more complex.

After introducing a workaround in the last post, in the next post I will show you a solution for this issue.

February 14, 2012

Accessing external data from SharePoint timer jobs or from event receivers triggered by incoming mail

Recently we had an issue with accessing external list data. One of my colleagues wrote an ItemAdded event receiver for a document library, that updates the document metadata based on a CAML query run against an external list. The source of the external list is a simple database table. For this post assume it is the Meetings table in the Northwind database.

Everything was OK while we uploaded the documents “manually” – that means from the web UI. However, enabling incoming mail for the document library and configuring it to save mail attachments to the library caused us headache.

Documents sent as e-mail attachments were not handled as expected, instead, generated an exception on the external list query.

We received the following error message on a system configured for Kerberos:

Access Denied for User ”, which may be an impersonation by ‘domain\owsTimerAccount’.

In another configuration without Kerberos, using “simple” Windows authentication:

Access denied by Business Data Connectivity.

The stack trace was the same for the both cases:

Microsoft.SharePoint.SPListDataSource.GetEntityInstanceEnumerator(XmlNode xnMethodAndFilters)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstancesInternal(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

Of course, the owsTimerAccount user has permissions for BCS, the external list and for the external database.

I’ve tried the same code with elevated permissions, SharePoint and Windows impersonation, but that has no effect, except elevated permissions, where I’ve received just another exception:

Attempted to perform an unauthorized operation.

and the stack trace is a bit different as well:

at Microsoft.SharePoint.SPListDataSource.CheckUserIsAuthorized(SPBasePermissions perms)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

Note: If you would like to apply Windows impersonation, the original identity your process running with (in this case the account configured for the SharePoint 2010 Timer service) should have the Act as part of the operating system user right.

At this point we started to suspect that the source of the issue is somehow related with the SPTimerV4 context. I’ve created a timer job to access our external list just to validate this theory and found the very same results.

Fortunately, this forum thread led us to the solution: instead of running a CAML query against an external list, we should access and filter the external data directly using the BCS API.

The code examples below show the implementation steps for a timer job. If you need the solution for an event receiver, simply replace the name and signature of the Execute method to the one of your event receiver method.

Trace methods in code are included simply to verify the flow of the process.

First, we should add a reference to the Microsoft.BusinessData.dll assembly located at GAC.

Next, add the following namespaces to the code of your SPJobDefinition class:

  1. using System.Diagnostics;
  2. using Microsoft.SharePoint;
  3. using Microsoft.SharePoint.Administration;
  4. using Microsoft.SharePoint.BusinessData.SharedService;
  5. using Microsoft.BusinessData.MetadataModel;
  6. using Microsoft.BusinessData.MetadataModel.Collections;
  7. using Microsoft.BusinessData.Runtime;

I’ve defined the following “constants” in my code. These values should be altered to reflect the properties of your business data.

  1. private static readonly String _nameSpace = "http://sp2010&quot;;
  2. private static readonly String _externalList = "ExtCalendar";
  3. private static readonly String _externalCT = "Nortwind Calendar";
  4. private static readonly String _finderView = "Read List";
  5. private static readonly String _textFieldName = "cLocation";
  6. private static readonly String _dateFieldName = "dStart";

In the Execute method the test method is started using elevated permissions (site URL should be replaced):

  1. public override void Execute(Guid targetInstanceId)
  2. {
  3.     Trace.TraceInformation("Starting BcsTestJob execution");
  4.  
  5.     try
  6.     {
  7.         using (SPSite site = new SPSite("http://sp2010&quot;))
  8.         {
  9.             using (SPWeb web = site.OpenWeb())
  10.             {
  11.                 Guid siteId = site.ID;
  12.                 Guid webId = web.ID;
  13.  
  14.                 SPSecurity.RunWithElevatedPrivileges(delegate()
  15.                 {
  16.                     using (SPSite siteImp = new SPSite(siteId))
  17.                     {
  18.                         TestExtList(site, "New York", new DateTime(2010, 9, 21));
  19.                     }
  20.                 });
  21.             }
  22.         }
  23.     }
  24.     catch (Exception ex)
  25.     {
  26.         Trace.TraceInformation("ERROR in BcsTestJob.Execute: '{0}', '{1}'", ex.Message, ex.StackTrace);
  27.     }
  28.     Trace.TraceInformation("BcsTestJob finished");
  29. }

In the TestExtList method we access the business data and filter the results. Of course, in this case we can’t use CAML.

  1. private void TestExtList(SPSite site, string textValue, DateTime dateValue)
  2. {
  3.     try
  4.     {
  5.  
  6.         using (SPServiceContextScope scope = new Microsoft.SharePoint.SPServiceContextScope(SPServiceContext.GetContext(site)))
  7.         {
  8.             BdcService service = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);
  9.             IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
  10.             IEntity entity = catalog.GetEntity(_nameSpace, _externalCT);
  11.             ILobSystemInstance LobSysteminstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;
  12.  
  13.             IFieldCollection fieldCollection = entity.GetFinderView(_finderView).Fields;
  14.  
  15.             IMethodInstance methodInstance = entity.GetMethodInstance(_finderView, MethodInstanceType.Finder);
  16.             IEntityInstanceEnumerator ientityInstanceEnumerator = entity.FindFiltered(methodInstance.GetFilters(), LobSysteminstance);
  17.  
  18.             int counter = 0;
  19.             while (ientityInstanceEnumerator.MoveNext())
  20.             {
  21.                 IEntityInstance entityInstance = ientityInstanceEnumerator.Current;
  22.                 if (((String)entityInstance[_textFieldName] == textValue) && ((DateTime)entityInstance[_dateFieldName] == dateValue))
  23.                 {
  24.                     counter++;
  25.                 }
  26.             }
  27.  
  28.             Trace.TraceInformation("BcsTestJob.TestExtList item count: {0}", counter);
  29.         }
  30.  
  31.     }
  32.     catch (Exception ex)
  33.     {
  34.         Trace.TraceInformation("ERROR in BcsTestJob.TestExtList: '{0}', '{1}'", ex.Message, ex.StackTrace);
  35.     }
  36. }

Applying this approach no exception is thrown. The external data can be accessed from the event receiver as well from the timer job, both of them running in the OWS Timer process context.

March 3, 2011

A more developer-friendly way of programmatically creating SharePoint 2010 external content types

Filed under: BCS, Reflection, SP 2010 — Tags: , , — Peter Holpar @ 00:51

Todd Baginski held a presentation (SPC 405 – Business Connectivity Services Runtime and Object Model Deep Dive) on 2009 Microsoft SharePoint Conference about creating ECTs from code. You can read more about it and find the original code on his blog.

The code in Todd’s post is really valuable when learning the object model, but I found that creating ECTs this way is very error-prone. If you need to alter the model, you have serious chance to make something wrong.

In this post I show you the main issues with that approach and try to offer you an alternative way to achieve the same result in a more developer-friendly manner.

First, I don’t like specifying type descriptor parameters as string values. It is longer than necessary and easy to mistype.

  1. TypeDescriptor returnRootCollectionTypeDescriptor2 =
  2.     customersParameter.CreateRootTypeDescriptor("Customers", true,
  3.     "System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
  4.     "Customers", null, null, TypeDescriptorFlags.IsCollection, null, catalog);

As a first step, one could compute the string from the original type and store it in string variables as shown here:

  1. String int32TypeName = typeof(Int32).ToString(); // will be "System.Int32"
  2. String stringTypeName = typeof(String).ToString(); // will be "System.String"
  3. String iDataReaderTypeName = typeof(IDataReader).AssemblyQualifiedName; // will be "System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
  4. String iDataRecordTypeName = typeof(IDataRecord).AssemblyQualifiedName; // will be "System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

Later, these string values could be used as parameters of type descriptor creation.

The ideal solution would be to use the Type itself as the parameter of these methods. We will see how to achieve that soon.

Second, the parameters of the methods are rather redundant (see the name and lobName parameters that are usually the same value), contain several unspecified (null) parameter values, and the calls to create different type descriptors share most of their parameter values.

  1. returnRootElementTypeDescriptor.ChildTypeDescriptors.Create("FirstName", true,
  2.     "System.String", "FirstName", null, null, TypeDescriptorFlags.None, null);
  3. returnRootElementTypeDescriptor.ChildTypeDescriptors.Create("LastName", true,
  4.     "System.String", "LastName", null, null, TypeDescriptorFlags.None, null);
  5. returnRootElementTypeDescriptor.ChildTypeDescriptors.Create("Phone", true,
  6.     "System.String", "Phone", null, null, TypeDescriptorFlags.None, null);

It would be nice to create a base parameter set, without the need to specify default (null) values, and specify only the unique values on method calls.

Third, the calls to create type descriptors are sometimes rather complex and contains redundant parameter values again.

  1. customerIDParameter.CreateRootTypeDescriptor("CustomerId", true, "System.Int32", "CustomerId",
  2.     new IdentifierReference("CustomerId",
  3.     new EntityReference("AdventureWorks", "Customer", catalog), catalog),
  4.     null, TypeDescriptorFlags.None, null, catalog);

Wouldn’t it be great to get the entity and identifier references automatically from the entities and identifiers of the model already specified?

I’ve tried to achieve the above goals by applying a few extension methods and creating a custom type to hold type descriptor creation parameters.

The following two extension methods show how to get the IdentifierReference and the EntityReference by specifying the Identifier and the Entity:

  1. public static IdentifierReference GetReference(this Identifier identifier)
  2. {
  3.     // to protect method against possible direct call with null value
  4.     // should not happen when called like a "standard" extension method
  5.     if (identifier == null)
  6.     {
  7.         throw new ArgumentNullException("identifier");
  8.     }
  9.  
  10.     Entity entity = identifier.Entity;
  11.     EntityReference entityReference = entity.GetReference();
  12.     IdentifierReference result = new IdentifierReference(identifier.Name, entityReference, entity.GetCatalog());
  13.  
  14.     return result;
  15. }
  16.  
  17. public static EntityReference GetReference(this Entity entity)
  18. {
  19.     // to protect method against possible direct call with null value
  20.     // should not happen when called like a "standard" extension method
  21.     if (entity == null)
  22.     {
  23.         throw new ArgumentNullException("entity");
  24.     }
  25.  
  26.     // IMPORTANT! name and namespace parameters are in opposite order
  27.     // in the case of Entity.Create method and EntityReference constructor
  28.     EntityReference result = new EntityReference(entity.Namespace, entity.Name, entity.GetCatalog());
  29.  
  30.     return result;
  31. }

Just a side note. As you can read in the comments, name and namespace parameters are in opposite order in the case of Entity.Create method and EntityReference constructor. I find this design to be quite misleading.

Both methods above need a reference to the parent AdministrationMetadataCatalog. Instead of passing this as a parameter, I applied a Reflection call to get its value from the Entity that contains it as a non-public field. This extension method might be nicer as an extension property, but unfortunately there is not yet such thing in .NET.

  1. // unfortunately, there is no (yet) "extension property" in .NET
  2. // so we must create a "get" extension method instead
  3. public static AdministrationMetadataCatalog GetCatalog(this Entity entity)
  4. {
  5.     // to protect method against possible direct call with null value
  6.     // should not happen when called like a "standard" extension method
  7.     if (entity == null)
  8.     {
  9.         throw new ArgumentNullException("entity");
  10.     }
  11.  
  12.     AdministrationMetadataCatalog result = null;
  13.  
  14.     MethodCollection methods = entity.Methods;
  15.  
  16.     // a new MethodColletion is created on each Entity creation (see internal Entity constructor)
  17.     // so it shouldn't be null
  18.     if (methods != null)
  19.     {
  20.         Type methodCollectionType = typeof(MethodCollection);
  21.         FieldInfo fi_metadataCatalog = methodCollectionType.GetField("metadataCatalog", BindingFlags.NonPublic | BindingFlags.Instance);
  22.         result = (AdministrationMetadataCatalog)fi_metadataCatalog.GetValue(methods);
  23.     }
  24.  
  25.     return result;
  26. }

You can use these methods as illustrated here:

  1. Entity customerEntity = Entity.Create("Customer", "AdventureWorks", true,
  2.     new Version("1.0.0.0"), 1000, CacheUsage.Default, lobSystem, customerModel, catalog);
  3. EntityReference customerEntityRef = customerEntity.GetReference();
  4.  
  5. // create the identifier
  6. Identifier customerIdentifier = customerEntity.CreateIdentifier("CustomerID", true, typeof(Int32));
  7. IdentifierReference customerIdentifierRef = customerIdentifier.GetReference();

Next, I’ve created a class to hold type descriptor creation parameters. I’ve planned this class to support inheriting values from another instance of the class through its constructor. Only values explicitly set must be inherited, but default values must not. It requires the class to remember which values were specified explicitly. (Remark: in this version of code this behavior has not too much importance. I planned a merge functionality either that needs this feature in a possible later version.) The class is able to resolve name and lobName parameter values from each other so one should only specify one of them if they are the same.

  1. public class TypeDescriptorParams
  2. {
  3.     private String _name;
  4.     private bool _isNameSpecified = false;
  5.     public String Name { get
  6.     {
  7.         return _name;
  8.     }
  9.         set
  10.         {
  11.             _isNameSpecified = true;
  12.             _name = value;
  13.         }
  14.     }
  15.  
  16.     private bool _isCached;
  17.     private bool _isIsCachedSpecified = false;
  18.     public bool IsCached
  19.     {
  20.         get
  21.         {
  22.             return _isCached;
  23.         }
  24.         set
  25.         {
  26.             _isIsCachedSpecified = true;
  27.             _isCached = value;
  28.         }
  29.     }
  30.  
  31.     private Type _type;
  32.     private bool _isTypeSpecified = false;
  33.     public Type Type
  34.     {
  35.         get
  36.         {
  37.             return _type;
  38.         }
  39.         set
  40.         {
  41.             _isTypeSpecified = true;
  42.             _type = value;
  43.         }
  44.     }
  45.  
  46.     private String _lobName;
  47.     private bool _isLobNameSpecified = false;
  48.     public String LobName
  49.     {
  50.         get
  51.         {
  52.             return _lobName;
  53.         }
  54.         set
  55.         {
  56.             _isLobNameSpecified = true;
  57.             _lobName = value;
  58.         }
  59.     }
  60.  
  61.     private IdentifierReference _identifierReference;
  62.     private bool _isIdentifierReferenceSpecified = false;
  63.     public IdentifierReference IdentifierReference
  64.     {
  65.         get
  66.         {
  67.             return _identifierReference;
  68.         }
  69.         set
  70.         {
  71.             _isIdentifierReferenceSpecified = true;
  72.             _identifierReference = value;
  73.         }
  74.     }
  75.  
  76.     private FilterDescriptor _filterDescriptor;
  77.     private bool _isFilterDescriptorSpecified = false;
  78.     public FilterDescriptor FilterDescriptor
  79.     {
  80.         get
  81.         {
  82.             return _filterDescriptor;
  83.         }
  84.         set
  85.         {
  86.             _isFilterDescriptorSpecified = true;
  87.             _filterDescriptor = value;
  88.         }
  89.     }
  90.  
  91.     private TypeDescriptorFlags _flags;
  92.     private bool _isFlagsSpecified = false;
  93.     public TypeDescriptorFlags Flags
  94.     {
  95.         get
  96.         {
  97.             return _flags;
  98.         }
  99.         set
  100.         {
  101.             _isFlagsSpecified = true;
  102.             _flags = value;
  103.         }
  104.     }        
  105.  
  106.     private AssociationReference _associationReference;
  107.     private bool _isAssociationReferenceSpecified = false;
  108.     public AssociationReference AssociationReference
  109.     {
  110.         get
  111.         {
  112.             return _associationReference;
  113.         }
  114.         set
  115.         {
  116.             _isAssociationReferenceSpecified = true;
  117.             _associationReference = value;
  118.         }
  119.     }
  120.  
  121.     private AdministrationMetadataCatalog _metadataCatalog;
  122.     private bool _isMetadataCatalogSpecified = false;
  123.     public AdministrationMetadataCatalog MetadataCatalog
  124.     {
  125.         get
  126.         {
  127.             return _metadataCatalog;
  128.         }
  129.         set
  130.         {
  131.             _isMetadataCatalogSpecified = true;
  132.             _metadataCatalog = value;
  133.         }
  134.     }
  135.  
  136.     public TypeDescriptorParams()
  137.     {
  138.     }
  139.  
  140.     public TypeDescriptorParams(TypeDescriptorParams explicitParams)
  141.     {
  142.         if (explicitParams._isNameSpecified) this.Name = explicitParams.Name;
  143.         if (explicitParams._isIsCachedSpecified) this.IsCached = explicitParams.IsCached;
  144.         if (explicitParams._isTypeSpecified) this.Type = explicitParams.Type;
  145.         if (explicitParams._isLobNameSpecified) this.LobName = explicitParams.LobName;
  146.         if (explicitParams._isIdentifierReferenceSpecified) this.IdentifierReference = explicitParams.IdentifierReference;
  147.         if (explicitParams._isFilterDescriptorSpecified) this.FilterDescriptor = explicitParams.FilterDescriptor;
  148.         if (explicitParams._isFlagsSpecified) this.Flags = explicitParams.Flags;
  149.         if (explicitParams._isAssociationReferenceSpecified) this.AssociationReference = explicitParams.AssociationReference;
  150.         if (explicitParams._isMetadataCatalogSpecified) this.MetadataCatalog = explicitParams.MetadataCatalog;
  151.     }
  152.  
  153.     public void ResolveNames()
  154.     {
  155.         // names default to the other name type
  156.         if ((this._isNameSpecified) && (!this._isLobNameSpecified))
  157.         {
  158.             this.LobName = this.Name;
  159.         }
  160.  
  161.         if ((!this._isNameSpecified) && (this._isLobNameSpecified))
  162.         {
  163.             this.Name = this.LobName;
  164.         }
  165.     }
  166. }

You can see that we specify the Type and not the String-based type name of the descriptor. It will be resolved later to the name.

Having this class, I created the following extension methods to bridge my code to the standard BCS API calls:

  1. public static TypeDescriptor CreateChildTypeDescriptor(this TypeDescriptor typeDescriptor, TypeDescriptorParams typeDescrParams)
  2. {
  3.     // to protect method against possible direct call with null value
  4.     // should not happen when called like a "standard" extension method
  5.     if (typeDescriptor == null)
  6.     {
  7.         throw new ArgumentNullException("typeDescriptor");
  8.     }
  9.  
  10.     typeDescrParams.ResolveNames();
  11.  
  12.     TypeDescriptor result = typeDescriptor.ChildTypeDescriptors.Create(
  13.         typeDescrParams.Name,
  14.         typeDescrParams.IsCached,
  15.         ResolveForBcs(typeDescrParams.Type),
  16.         typeDescrParams.LobName,
  17.         typeDescrParams.IdentifierReference,
  18.         typeDescrParams.FilterDescriptor,
  19.         typeDescrParams.Flags,
  20.         typeDescrParams.AssociationReference);
  21.  
  22.     return result;
  23. }
  24.  
  25. public static TypeDescriptor CreateRootTypeDescriptor(this Parameter parameter, TypeDescriptorParams typeDescrParams)
  26. {
  27.     // to protect method against possible direct call with null value
  28.     // should not happen when called like a "standard" extension method
  29.     if (parameter == null)
  30.     {
  31.         throw new ArgumentNullException("parameter");
  32.     }
  33.  
  34.     typeDescrParams.ResolveNames();
  35.  
  36.     TypeDescriptor result = parameter.CreateRootTypeDescriptor(
  37.         typeDescrParams.Name,
  38.         typeDescrParams.IsCached,
  39.         ResolveForBcs(typeDescrParams.Type),
  40.         typeDescrParams.LobName,
  41.         typeDescrParams.IdentifierReference,
  42.         typeDescrParams.FilterDescriptor,
  43.         typeDescrParams.Flags,
  44.         typeDescrParams.AssociationReference,
  45.         typeDescrParams.MetadataCatalog);
  46.  
  47.     return result;
  48. }
  49.  
  50. public static Identifier CreateIdentifier(this Entity entity, String name, bool isCached, Type type)
  51. {
  52.     // to protect method against possible direct call with null value
  53.     // should not happen when called like a "standard" extension method
  54.     if (entity == null)
  55.     {
  56.         throw new ArgumentNullException("entity");
  57.     }
  58.  
  59.     Identifier result =
  60.         entity.Identifiers.Create(name, isCached, ResolveForBcs(type));
  61.  
  62.     return result;
  63. }

The following helper method is used by the above methods to resolve the Type to the type name BCS calls require. For CLR library classes we need only the short name of the type, otherwise the qualified name.

  1. private static string ResolveForBcs(Type type)
  2. {
  3.     String typeName = null;
  4.     
  5.     if (type != null)
  6.     {
  7.         typeName = (type.Module.ScopeName == "CommonLanguageRuntimeLibrary") ? type.ToString() : type.AssemblyQualifiedName;
  8.     }
  9.  
  10.     return typeName;
  11. }

Using these concepts, one can write more compact code, no need to specify null and common values. The calls to create type descriptors are easier to read and maintain.

  1. TypeDescriptorParams standardStringType =
  2.     new TypeDescriptorParams
  3.     {
  4.         IsCached = true,
  5.         Type = typeof(Int32),
  6.         Flags = TypeDescriptorFlags.None,
  7.     };
  8.  
  9. TypeDescriptorParams standardIntType =
  10.     new TypeDescriptorParams(standardStringType)
  11.     {
  12.         Type = typeof(Int32),
  13.     };
  14.  
  15. // create the TypeDescriptor for the CustomerID parameter
  16. CustomerIDParameter.CreateRootTypeDescriptor(new TypeDescriptorParams(standardIntType)
  17. {
  18.     Name = "CustomerID",
  19.     IdentifierReference = customerIdentifierRef,
  20.     MetadataCatalog = catalog
  21. });

Repetitive code blocks requires only specifying unique parameter values:

  1. returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
  2. {
  3.     Name = "FirstName"
  4. });
  5.  
  6. returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
  7. {
  8.     Name = "LastName"
  9. });
  10.  
  11. returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
  12. {
  13.     Name = "Phone"
  14. });

This methods help you to write and alter the code easier, but there is still chance to have errors. To find these errors before activating your model, you can use the Validate method of your Entity, and check the returning ActivationError[] array if necessary.

  1. ActivationError[] errors = customerEntity.Validate();
  2.  
  3. result = (errors.Length == 0);
  4.  
  5. if (result)
  6. {
  7.     customerEntity.Activate();
  8.     Console.WriteLine("Model created");
  9. }
  10. else
  11. {
  12.     Console.WriteLine("Validation errors:");
  13.     Array.ForEach(errors,
  14.         error => Console.WriteLine(error));
  15. }

Of course, these examples only provide you some idea about what can be done to create a more maintainable code. There are a lot of things to do yet, for example, BCS connection properties are still string based, that I think not really safe as well:

  1. // set the connection properties
  2. lobSystemInstance.Properties.Add("AuthenticationMode", "PassThrough");
  3. lobSystemInstance.Properties.Add("DatabaseAccessProvider", "SqlServer");
  4. lobSystemInstance.Properties.Add("RdbConnection Data Source", "sp2010");
  5. lobSystemInstance.Properties.Add("RdbConnection Initial Catalog", "AdventureWorksLT");
  6. lobSystemInstance.Properties.Add("RdbConnection Integrated Security", "SSPI");
  7. lobSystemInstance.Properties.Add("RdbConnection Pooling", "true");

You can find the complete solution here, including code from my former posts about checking existence of a list from managed client object model and creating external list from code client side code.

February 9, 2011

How to query external lists on the server side using CAML and alter the external data?

Filed under: BCS, CAML, External list, SP 2010 — Tags: , , , — Peter Holpar @ 08:38

In my former post I’ve wrote about how to query SharePoint 2010 BCS external lists using client side code. Now I try to show how to do it on the server side.

For this example I used the external links sample demonstrated in a this post. Although the download for that post contains only a single sample link having SharePoint in its title, you need to create more such links to see the real effect of the code below. I used that code in a server side console application, but after minor modifications it can be used in web context as well.

  1. String siteUrl = "http://sp2010&quot;;
  2.  
  3. using (SPSite site = new SPSite(siteUrl))
  4. {
  5.     using (SPWeb web = site.OpenWeb())
  6.     {
  7.  
  8.         SPList list = web.Lists["External Links"];
  9.  
  10.         // query items
  11.         SPQuery query = new SPQuery();
  12.         query.Query =
  13.             @"<Where>
  14.                 <Contains>
  15.                     <FieldRef Name='Title' />
  16.                     <Value Type='Text'>SharePoint</Value>
  17.                 </Contains>
  18.               </Where>
  19.               <OrderBy>
  20.                    <FieldRef Name='Title'/>
  21.               </OrderBy>";
  22.         query.ViewFields =
  23.             @"<ViewFields>
  24.                    <FieldRef Name='Title'/>
  25.               </ViewFields>";
  26.         // RowLimit seems to have no effect
  27.         query.RowLimit = 1;
  28.  
  29.         SPListItemCollection items = list.GetItems(query);
  30.  
  31.         foreach (SPListItem item in items)
  32.         {
  33.             Console.WriteLine("Title: '{0}'", item["Title"]);
  34.         }
  35.  
  36.     }
  37. }

As you can see, the code is just like for a standard SharePoint list. You have to include the fields you use in the code in the ViewFields node of the CAML query as FieldRef. Similar to the client side example, specifying the RowLimit seems to have no effect, that is very sad, but true.

If your model allows that (it is not read-only and has the necessary methods), you can use code to alter (create, update, delete) your external data through the external list as well. In our case, the following code snippet will create a new link in the file system:

  1. newItem["Link"] = new SPFieldUrlValue { Url = "http://www.company.com&quot;, Description = "Link item from code" };
  2. newItem.Update();
  3. list.Update();

In this case we had to fill only the Link field with the link title and URL, as external list item Title field will be determined in our .NET assembly BCS connector class based on the link title (see the original post for details). It means that the fields you have to fill depend on the nature of the connector and the properties of the external system.

How to query external lists on the client side using CAML?

Filed under: BCS, CAML, Managed Client OM, SP 2010 — Tags: , , , — Peter Holpar @ 08:05

One of the great features of SharePoint 2010 BCS External List concept is that it enables you to publish and access external data similar way as in the case of standard SharePoint list (I wrote similar, because there are significant exceptions as well). It includes the programmatic access either.

Despite of the similarities, I have found forums questions that show it is not always trivial to achieve your goals. In this post I show you a code example that illustrates how one can filter the external data using managed client object model through a  CAML query. For this example I’ve created an external list on my server that connects to the Customers table of the well-known Northwind database. You can find several posts about how to create the external content type (ECT) for you external list, for example here.

Once you have your external list created (called Northwind Customers in my case), you can try this code from your client project:

  1. String siteUrl = "http://sp2010&quot;;
  2.  
  3. ClientContext clientContext = new ClientContext(siteUrl);
  4.  
  5. List list = clientContext.Web.Lists
  6.     .GetByTitle("Northwind Customers");
  7. CamlQuery camlQuery = new CamlQuery();
  8. // When using a CAML query for external list
  9. // one should include all referenced fields in ViewFields
  10. // otherwise a "The given key was not present in the dictionary" exception is thrown
  11. // RowLimit seems to have no effect            
  12. camlQuery.ViewXml =
  13.     @"<View>
  14.         <Query>
  15.           <Where>
  16.             <Eq>
  17.               <FieldRef Name='City'/>
  18.               <Value Type='Text'>London</Value>
  19.             </Eq>
  20.           </Where>
  21.           <OrderBy>
  22.             <FieldRef Name='ContactName'/>
  23.           </OrderBy>
  24.         </Query>
  25.         <ViewFields>
  26.           <FieldRef Name='CustomerID'/>
  27.           <FieldRef Name='ContactName'/>
  28.           <FieldRef Name='CompanyName'/>
  29.           <FieldRef Name='City'/>
  30.         </ViewFields>
  31.         <RowLimit>1</RowLimit>
  32.     </View>";
  33.  
  34. // include referenced field here as well
  35. ListItemCollection listItems = list.GetItems(camlQuery);
  36. clientContext.Load(
  37.      listItems,
  38.      items => items
  39.          .Include(
  40.             item => item["CustomerID"],
  41.             item => item["CompanyName"],
  42.             item => item["ContactName"],
  43.             item => item["City"]));
  44. clientContext.ExecuteQuery();
  45.  
  46. // display the result
  47. foreach (ListItem listItem in listItems)
  48. {
  49.     Console.WriteLine("CustomerID: '{0}', CompanyName: '{1}', ContactName: '{2}'",
  50.         listItem.FieldValues["CustomerID"],
  51.         listItem.FieldValues["CompanyName"],
  52.         listItem.FieldValues["ContactName"]);
  53. }
  54.  
  55. Console.Write("Press Enter to continue!");
  56. Console.ReadLine();

The code filters customers that have London specified in the City field and order them by the value of the ContactName field.

Important things to note:

  • You must include all fields you use either to filter, order in CAML query, or to display later in your code into both the ViewFields node of CAML query as FieldRef and load into the ClientContext as illustrated above. Otherwise you will receive a "The given key was not present in the dictionary" exception when referencing the field later.
  • Specifying the RowLimit seems to have no effect, all of the matching items are returned by the query (I found similar info in this forum thread). That is not very nice when working with external lists having large item count.

February 1, 2011

How to get a BCS metadata catalog without specifying a URL?

Filed under: BCS, SP 2010 — Tags: , — Peter Holpar @ 23:06

It was the original question in this MSDN forum thread.

As you probably know, we can get a reference for a BCS catalog from client side code (Microsoft.SharePoint.BusinessData.Administration.Client) like this:

AdministrationMetadataCatalog catalog = AdministrationMetadataCatalog.GetCatalog(siteUrl);

On the server side (Microsoft.SharePoint.BusinessData.Administration) there are two different options. If the process runs in the SharePoint context, you can use a code that does not require the URL:

BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
AdministrationMetadataCatalog catalog = service.GetAdministrationMetadataCatalog(SPServiceContext.Current);

However if your code runs out of SharePoint context, you need a URL again to get the SPServiceContext reference:

SPServiceContext.GetContext(siteUrl)

In this specific question we had to solve the puzzle for the server side, out of SharePoint server context variation, to be able to use the code from an STSADM command.

Although my first reply (one has to provide some kind of context for the BdcSerivce to be able to return a catalog to work with) remained true, it turned out one can get a service context without specifying a site URL, and it can be done using the following code snippet:

  1. private AdministrationMetadataCatalog GetBdcCatalog()
  2. {
  3.     AdministrationMetadataCatalog catalog = null;
  4.  
  5.     try
  6.     {
  7.         BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>();
  8.         SPServiceApplication serviceApp = bdcService.Applications.FirstOrDefault();
  9.  
  10.         if (serviceApp != null)
  11.         {
  12.             SPServiceContext context = SPServiceContext.GetContext(serviceApp.ServiceApplicationProxyGroup, SPSiteSubscriptionIdentifier.Default);
  13.             catalog = bdcService.GetAdministrationMetadataCatalog(context);
  14.         }
  15.     }
  16.     catch (Exception ex)
  17.     {
  18.         // do error handling
  19.     }
  20.  
  21.     return catalog;
  22.  
  23. }

A bit tricky, but seems to work as expected.

January 26, 2011

Creating external lists using the managed client object model

Filed under: BCS, External list, Managed Client OM, SP 2010 — Tags: , , , — Peter Holpar @ 23:06

Last year I wrote a post about how to create external lists in SharePoint 2010 using server side code. Now I show you how to achieve the same result from the client side.

The solution does not differ too much, there are only two minor differences:

  1. External data source property names are not exposed as public fields (see the members of the SPListDataSource.BDCProperties class on the server side).
  2. When adding the new external list to the SPListCollection, you have to use the Add method that has three String parameters (title, description and url) and a dataSource parameter of SPListDataSource type. The result of the call is the ID (Guid) of the new list.
    On the client side we have use the Add method of the ListCollection class that has a single parameters parameter of type ListCreationInformation. The ListCreationInformation class represents all the information (list title, description, URL, data source properties, and a few more info) we used on the server side. The return type of the Add method is List. Another Important difference is that in this case the data source properties are not represented as a SharePoint-specific type, like SPListDataSource on the server side, but they are collected into a DataSourceProperties property that is a “standard” .NET  IDictionary<string, string> type. The additional information in ListCreationInformation contains for example a QuickLaunchOption property of enum type  QuickLaunchOption (possible values Off, On and DefaultValue).

After this introduction let’s see the code.

First, I’ve created an extension method in a static helper class to make my life easier and to avoid mistyping parameter names:

  1. // the listDataSource parameter is only a fake one to enable attaching the static method to ListDataSource type
  2. public static IDictionary<String, String> Initialize(this ListDataSource listDataSource, String entity, String entityNamespace, String lobSystemInstance, String specificFinder)
  3. {
  4.     // BDCProperties (SPListDataSource internal class) are not available from client code, we should use string literals
  5.     Dictionary<String, String> result = new Dictionary<String, String>();
  6.     result.Add("Entity", entity);
  7.     result.Add("EntityNamespace", entityNamespace);
  8.     result.Add("LobSystemInstance", lobSystemInstance);
  9.     result.Add("SpecificFinder", specificFinder);
  10.  
  11.     return result;
  12. }

As you can see, the listDataSource parameter is not used in the class. My purpose was only to bind the functionality to the ListDataSource class and keep the working of the server-side and client-side codes as parallel as it is possible.

The following code demonstrates the creation of the external list from the managed client API using the extension method we created above.

  1. ClientContext clientContext = new ClientContext(siteUrl);
  2. Web web = clientContext.Web;
  3.  
  4. ListCollection lists = web.Lists;
  5.  
  6. ListDataSource listDataSource = new ListDataSource();
  7. // set up the list data source properties using the extension method (see Extensions.cs)
  8. IDictionary<String, String> listDataSourceProp = listDataSource.Initialize("YourBdcEntity", "YourBdc.EntityNamespace", "YourLobSystemInstancece", "ReadItem");
  9.  
  10. // initialize create list info
  11. // IMPORTANT! The DataSourceProperties is not of type ListDataSource,
  12. // as one may expect, but a simple IDictionary<String, String>
  13. ListCreationInformation listCreateInfo = new ListCreationInformation
  14. {
  15.     Title = listTitle,
  16.     Description = "List description",
  17.     DataSourceProperties = listDataSourceProp,
  18.     Url = "listurl",
  19.     QuickLaunchOption = QuickLaunchOptions.On
  20. };
  21.  
  22. // create list
  23. List newList = lists.Add(listCreateInfo);
  24.  
  25. clientContext.ExecuteQuery();

I made some test if I can to change the data source binding of a list after creation.

The List class contains a DataSource property of type ListDataSource, but it is read-only so you cannot make a standard list to  an external one, or change the data source parameters simply like:

newList.DataSource = listDataSource;

The above code lines produces a compile time error.

I’ve also tested what happens if I change the properties of the data source one-by-one like shown here:

  1. clientContext.Load(newList, list => list.DataSource);
  2. clientContext.ExecuteQuery();
  3.  
  4. newList.DataSource.Properties["SpecificFinder"] = "AnotherSpecificFinder";
  5. clientContext.ExecuteQuery();

Using this code I’ve experienced neither compile-time nor run-time error, but when I checked the value of the property later using this code:

  1. clientContext.Load(existingList, list => list.DataSource);
  2. clientContext.ExecuteQuery();
  3. Console.WriteLine(existingList.DataSource.Properties["SpecificFinder"]);

I found that the value of the property has not been changed. So it seems to be a good idea to plan and set the properties of your external lists in advance.

Older Posts »

Theme: Shocking Blue Green. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 53 other followers