Second Life of a Hungarian SharePoint Geek

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"))
  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";
  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"))
  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.

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.

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.

October 4, 2010

Validation issues with external lists (or have I missed something?)

Filed under: BCS, Bugs, External list, SP 2010 — Tags: , , , — Peter Holpar @ 00:58

Probably most of you have already seen one of those compelling demos of the new external list feature of SharePoint 2010 when it took only a few mouse click for the presenter to display business data stored for example in SQL database integrated into SharePoint as a list.

I should admit I became very enthusiastic over this exciting new possibility when first saw that on the SharePoint Conference last year. One year has elapsed since then and – although I’m still interested in this feature – I had to learn there are some rather annoying issues if you scrape the surface a bit.

One of the most serious issues I found is the question of validation. It’s usually trivial in the case of a traditional business application development, but it will give you a hard time in the case of external list.

In this post I illustrate this issue only with a few examples and the error stack traces belonging to them.

First, let’s see the well known Northwind Customers example. In this case the customers from the Northwind database are displayed in a SharePoint external list.

The image below shows the fields of the list. As you can see, all of the fields are Single line of text (the corresponding type in BDC is System.String), but you have no more information about them than whether they are optional of mandatory fields.

image

In contrast with System.String database fields are usually has length limitation. So let’s see what happens if we type content longer than the corresponding field in database allows that.

image

I would expect some user friendly warning message but instead of that we receive an unhandled exception.

image

Full stack trace is displayed below, having the most important text parts bolded.

Server Error in ‘/’ Application.
——————————————————————————–

String or binary data would be truncated.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +58
   System.Data.SqlClient.SqlDataReader.get_MetaData() +112
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6281668
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6282737
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +424
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +211
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +19
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +19
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStaticInternal(DbAccessProviderObjectFactory accessProviderObjectFactory, IConnectionManager connectionManager, IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, INamedPropertyDictionary lobSystemInstanceProperties, Object[]& args, IDbConnection connection, Boolean connectionExplicitlyManaged) +14939845
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStatic(IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, Object[] args, IExecutionContext context) +577

[RuntimeException: The query against the database caused an error.]
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStatic(IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, Object[] args, IExecutionContext context) +14941379
   Microsoft.SharePoint.BusinessData.Runtime.DataClassRuntime.ExecuteInternalWithAuthNFailureRetry(ISystemUtility systemUtility, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IParameterCollection nonReturnParameters, Object[] overrideArgs) +377
   Microsoft.SharePoint.BusinessData.Runtime.DataClassRuntime.ExecuteInternal(IDataClass thisDataClass, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, IParameterCollection nonReturnParameters, Object[]& overrideArgs) +19307891
   Microsoft.SharePoint.BusinessData.Runtime.EntityRuntime.ExecuteInternal(IDataClass thisDataClass, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, IParameterCollection nonReturnParameters, Object[]& overrideArgs, IFilterCollection filters) +122
   Microsoft.SharePoint.BusinessData.Runtime.AbstractEntityInstance.UpdateInternal(IMethodInstance updater, IEntityInstance instanceToUpdate, IDictionary`2 preValues) +316
   Microsoft.SharePoint.BusinessData.Runtime.AbstractEntityInstance.Update(Boolean compareHash) +1684
   Microsoft.SharePoint.SPListDataSource.UpdateEntityInstance(String bdcid, SPListItem itemToUpdate, IDictionary dictFieldValues) +212

[SPException: Failed to update a list item for this external list based on the Entity (External Content Type) 'Customer' in EntityNamespace 'http://sp2010/offline'. Details: The query against the database caused an error.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690873
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

You will get similar exceptions if you database table has unique key constraints of foreign key references and the user input contains a value that does not fulfill these constraints.

Now let’s see some examples where we work with Visual Studio and edit the BDC model file.

If you read the page BDC Custom Properties there are some promising properties.

Reference type fields (like String) are optional by default and value type fields (like int, double) are mandatory.

You can change this behavior and set for example a text as mandatory and a number to optional via the RequiredInForms property.

The following XML fragments of the DBC model file illustrate that concept.

  1. <TypeDescriptor TypeName="System.String" Name="Message">
  2.   <Properties>
  3.     <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
  4.   </Properties>
  5. </TypeDescriptor>

and similarly

  1. <TypeDescriptor TypeName="System.Int32" Name="Number">
  2.   <Properties>
  3.     <Property Name="RequiredInForms" Type="System.Boolean">false</Property>
  4.   </Properties>
  5. </TypeDescriptor>

If you prefer, you can use the BDC designer as well and set the property there in the Custom Properties.

image

If you want your value type to be optional, don’t forget to declare that accordingly (e.g. it should be nullable to accept empty values):

public int? Number { get; set; }

I’ve also played with the DecimalDigits property to limit the allowed number of decimal digits for a number and with the Size property to set the maximum length of a text, but without success.

Back to the validation issues.

If you have a field in your model that stores its value as int, then the input field control is smart enough to detect if you type a text into it. In this case you get the following validation error:

image

The value of this field is not a valid number.

But it is not smart enough to limit you only typing integers. So if you enter a non-integer number (like 22.22) or the text “NaN” (!) then you get different exceptions based on the method type. In the case of the edit item form (in the Updater method):

Server Error in ‘/’ Application.
——————————————————————————–

The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.SharePoint.WebPartPages.DataFormWebPartException: The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[DataFormWebPartException: The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690775
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

In the case of the new item form (in the Creator method):

Server Error in ‘/’ Application.
——————————————————————————–

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[FormatException: Input string was not in a correct format.]
   System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +10161267
   System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +207
   System.String.System.IConvertible.ToInt32(IFormatProvider provider) +61
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +307
   Microsoft.SharePoint.BdcClientUtil.ChangeType(Object value, Type destinationType, IFormatProvider culture) +478
   Microsoft.SharePoint.SPListDataSource.FillOneFieldValue(SPField fld, IView view, String bdcfldnm, Object objRawFieldValue, IFieldValueDictionary dictFields) +536

[SPException: Failed to convert value '22.22' to type 'System.Int32' for field 'Number' in Entity (External Content Type) 'Entity1', EntityNamespace 'BDCValidationTest.BDCModel'. Details: Input string was not in a correct format.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.InsertCallback(Int32 affectedRecords, Exception ex) +23690584
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +4052324
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +378
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

Another example, if you type a value that does not fit into the size of the .NET type you used in you model and entity. In this case I declared the type as byte, but entered the number 256:

Server Error in ‘/’ Application.
——————————————————————————–

Value was either too large or too small for an unsigned byte.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.OverflowException: Value was either too large or too small for an unsigned byte.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[OverflowException: Value was either too large or too small for an unsigned byte.]
   System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info) +4386590
   System.String.System.IConvertible.ToByte(IFormatProvider provider) +55
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +10246009
   Microsoft.SharePoint.BdcClientUtil.ChangeType(Object value, Type destinationType, IFormatProvider culture) +478
   Microsoft.SharePoint.SPListDataSource.FillOneFieldValue(SPField fld, IView view, String bdcfldnm, Object objRawFieldValue, IFieldValueDictionary dictFields) +536

[SPException: Failed to convert value '256' to type 'System.Byte' for field 'Byte' in Entity (External Content Type) 'Entity1', EntityNamespace 'BDCValidationTest.BDCModel'. Details: Value was either too large or too small for an unsigned byte.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690873
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

Finally, another strange behavior in SharePoint lists, not limited to the external lists only. If you have to specify the value of a numerical field, you can type a number containing any number of digit grouping symbols in any order (like “3,,,,2” that will be interpreted as 32). The only exception is that placing it into the first position (like “,32”) is not allowed.

After this experience, I’m thinking about how validation issues can be handled in the case of external lists to make them a reliable tool that can be used even for business applications, not only in presentation rooms.

October 2, 2010

Using call stack information to better understand SharePoint functionality

If you would like to get deeper understanding what happens in SharePoint and why,  probably .NET Reflector is your old friend. It helps you to investigate the code of the internal assemblies and to track call chains by clicking on the method name you would like to follow.

The functionality of Reflector is very nice, but it is sometimes simply more useful to see the calling process in action or check the values of the parameters the methods are called with. That is where call stack comes into the picture.

Recently, I study SharePoint external lists and try to understand the process better to be able to find a way to hook up some extensions to the base framework. In this process I find call stacks extremely useful.

By default, you can see something similar when looking to the Call Stack window in Visual Studio 2010.

image

This information helps you not too much as only your own methods are displayed, external calls are hidden.

Fortunately, you can turn on external code display as well as parameter values, as illustrated on the screenshot below.

image

As you can see there is a Go To Reflector menu either, but I really don’t suggest you to use that due to my bad experiences I wrote about earlier.

After enabling these options, Visual Studio displays the whole call stack, that is really what I needed this time. Now you shouldn’t follow method calls with Reflector step by step, you can check directly the method you think is important.

image

As you can see the parameter values are displayed with the method calls. The bad news is that it seems parameter values are displayed by calling the ToString() method on the parameter. At least I found no way to check the value of parameters having more complex types.

Last but not least you can get (almost) the same result from code. The following snippet shows how to do that using StackTrace, StackFrame, ParameterInfo and MethodBase classes. I wrote almost the same, as you can’t get the values of the parameters this way.

  1. using System;
  2. using System.Text;
  3. using System.Diagnostics;
  4. using System.Reflection;
  5.  
  6. namespace YourNameSpace
  7. {
  8.     public static class Helper
  9.     {
  10.         public static void OutputStackTrace()
  11.         {
  12.             StringBuilder sb = new StringBuilder();
  13.             StackTrace stackTrace = new StackTrace();
  14.             StackFrame[] stackFrames = stackTrace.GetFrames();
  15.             foreach (StackFrame stackFrame in stackFrames)
  16.             {
  17.                 MethodBase mb = stackFrame.GetMethod();
  18.                 sb.AppendFormat("{0}(", mb.Name);
  19.                 ParameterInfo[] pis = mb.GetParameters();
  20.  
  21.                 // aggregating parameters
  22.                 Array.ForEach<ParameterInfo>(pis,
  23.                     new Action<ParameterInfo>(pi => sb.AppendFormat("{0} {1}{2}",
  24.                         pi.ParameterType.Name,
  25.                         pi.Name,
  26.                         (pi.Position != pis.Length – 1) ? ", " : String.Empty)));
  27.  
  28.                 sb.AppendFormat(") – {0}\r\n", mb.DeclaringType.AssemblyQualifiedName);
  29.             }
  30.             String callStack = sb.ToString();
  31.             Debug.WriteLine(callStack);
  32.         }
  33.     }
  34. }

All you have to do is to include a call to this static method where you need the call stack information, and it is dumped to the Debug view of the Output window where you can copy it from for later use.

October 1, 2010

Creating the customization XSL for your external list

Filed under: BCS, External list, SP 2010 — Tags: , , — Peter Holpar @ 22:30

When you would like to customize the XSL of the external list either using SharePoint Designer 2010 or via deploying your custom XSL from a Visual Studio 2010 solution, it is always useful to know how the raw XML data returned by the BCS looks like.

In this case, you can use the very same technique that was showed us by Patrick:

Custom XSLT for the Search Core Results Web Part

You should inject the following content temporarily to the view (when using SPD) or you customization XSL (in case of VS):

  1. <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ddwrt2="urn:frontpage:internal">
  2.   <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
  3.   <xsl:template match="/">
  4.     <xmp>
  5.       <xsl:copy-of select="*"/>
  6.     </xmp>
  7.   </xsl:template>
  8. </xsl:stylesheet>

It will cause the raw XML content to be rendered on the page as shown below in the case of my file system example:

image

And this is the whole content of the XML in my case:

  1. <dsQueryResponse>
  2.   <Rows />
  3. </dsQueryResponse>
  4. <dsQueryResponse ViewStyleID="" BaseViewID="1" TemplateType="600" RowLimit="30">
  5.   <Rows>
  6.     <Row BdcIdentity="__bk08003400a300c5004400160047001600c50045005600d6000700c50035001600d6000700c600560064009600c60056003700c5006400f600c6004600560027001300" Icon="&lt;DIV&gt;&lt;a href='?path=\Folder1'&gt;&lt;img title='Folder1' alt='Folder1' src='http://sp2010/_layouts/images/folder.gif&#039; border='0'/&gt;&lt;/a&gt;&lt;/DIV&gt;" Name="Folder1" Size="0" Size.="0" Created="9/18/2010 9:20 PM" LastModified="9/18/2010 9:31 PM" IsFolder="Yes" />
  7.     <Row BdcIdentity="__bk08003400a300c5004400160047001600c50045005600d6000700c50035001600d6000700c600560064009600c60056003700c5006400f600c6004600560027002300" Icon="&lt;DIV&gt;&lt;a href='?path=\Folder2'&gt;&lt;img title='Folder2' alt='Folder2' src='http://sp2010/_layouts/images/folder.gif&#039; border='0'/&gt;&lt;/a&gt;&lt;/DIV&gt;" Name="Folder2" Size="0" Size.="0" Created="9/18/2010 9:31 PM" LastModified="9/18/2010 10:16 PM" IsFolder="Yes" />
  8.     <Row BdcIdentity="__bk89003400a300c5004400160047001600c50045005600d6000700c50035001600d6000700c600560064009600c60056003700c5004400f60036005700d6005600e6004700e2004600f60036008700" Icon="&lt;DIV&gt;&lt;a href='http://sp2010/_layouts/DownloadExternalData.aspx?EntityNamespace=FileSystem.FileSystemModel&amp;EntityName=FileSystemEntity&amp;LobSystemInstanceName=FileSystemModel&amp;StreamAccessorName=FileAccessor&amp;IsXmlEncodedStreamName=true&amp;ItemId=_BdcId_'&gt;&lt;img title='Document.docx' alt='Document.docx' src='http://sp2010/_layouts/images/icdocx.png&#039; border='0'/&gt;&lt;/a&gt;&lt;/DIV&gt;" Name="Document.docx" Size="12716" Size.="12716" Created="9/18/2010 9:25 PM" LastModified="9/18/2010 9:28 PM" IsFolder="No" />
  9.     <Row BdcIdentity="__bk09003400a300c5004400160047001600c50045005600d6000700c50035001600d6000700c600560064009600c60056003700c50044002700160077009600e6007600e2002600d6000700" Icon="&lt;DIV&gt;&lt;a href='http://sp2010/_layouts/DownloadExternalData.aspx?EntityNamespace=FileSystem.FileSystemModel&amp;EntityName=FileSystemEntity&amp;LobSystemInstanceName=FileSystemModel&amp;StreamAccessorName=FileAccessor&amp;IsXmlEncodedStreamName=true&amp;ItemId=_BdcId_'&gt;&lt;img title='Drawing.bmp' alt='Drawing.bmp' src='http://sp2010/_layouts/images/icbmp.gif&#039; border='0'/&gt;&lt;/a&gt;&lt;/DIV&gt;" Name="Drawing.bmp" Size="1282806" Size.="1282806" Created="9/18/2010 9:21 PM" LastModified="9/18/2010 9:30 PM" IsFolder="No" />
  10.     <Row BdcIdentity="__bkc9003400a300c5004400160047001600c50045005600d6000700c50035001600d6000700c600560064009600c60056003700c5007500f6002700b60037008600560056004700e2008700c60037008700" Icon="&lt;DIV&gt;&lt;a href='http://sp2010/_layouts/DownloadExternalData.aspx?EntityNamespace=FileSystem.FileSystemModel&amp;EntityName=FileSystemEntity&amp;LobSystemInstanceName=FileSystemModel&amp;StreamAccessorName=FileAccessor&amp;IsXmlEncodedStreamName=true&amp;ItemId=_BdcId_'&gt;&lt;img title='Worksheet.xlsx' alt='Worksheet.xlsx' src='http://sp2010/_layouts/images/icxlsx.png&#039; border='0'/&gt;&lt;/a&gt;&lt;/DIV&gt;" Name="Worksheet.xlsx" Size="8774" Size.="8774" Created="9/18/2010 9:24 PM" LastModified="9/18/2010 9:27 PM" IsFolder="No" />
  11.   </Rows>
  12. </dsQueryResponse>

You can copy the XML you received in the former step and save it for example into a text file. Then create the XSL that translates the content to the desired format, and replace the original XSL with this new one.

September 19, 2010

Publishing files stored in the file system through external list

Filed under: BCS, External list, SP 2010 — Tags: , , — Peter Holpar @ 01:35

Although it is rather common at companies that they store significant amount of the their files on SharePoint, there are always exceptions, files that are stored at the file system. We created special web parts and other alternative solutions even for SPS 2003 / WSS 2.0 and MOSS 2007 / WSS 3.0 to make these files accessible in a read-only way through the SharePoint portal user interface. As illustrated in this post, creating a similar solution is easily possible based on the external list feature of SharePoint 2010.

Unfortunately due to time and space limitations I can only describe the most interesting parts and ideas of the implementation and refer to another posts to help you better understand the process of customization.

Just to start at the beginning, if you are new to external lists that are built on .NET assemblies, I think reading and understanding the following articles will help at the first steps:

Creating .NET Assemblies That Aggregate Data from Multiple External Systems for Business Connectivity Services in SharePoint Server 2010

External list example demonstrating .NET connectivity assembly and custom field type

Notice (as usual): The goal of this sample only to illustrate the capabilities and the limitations of BCS and external lists. The code example is not intended to be used in production. If you would like to enhance it, you are free to use it at your own risk. You should definitely deal with error handling, concurrency and security issues I would not like to detail here due to lack of time.

For example, in this sample only makes it possible to display files from the local SharePoint front-end machine, that is typically not a file server role in a real-live scenario. Although the sample can be extended to make remote file server access available, describing the necessary steps is really beyond the scope of this post, similar to implementing the read-write access.

We want to create an instance of our external list on deployment, so added a new list instance item to our project having the following Elements.xml:

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  3.   <ListInstance Title="Files"
  4.                 OnQuickLaunch="TRUE"
  5.                 TemplateType="600"
  6.                 FeatureId="00bfea71-de22-43b2-a848-c05709900100"
  7.                 Url="Lists/Files"
  8.                 CustomSchema="Files\Schema.xml"
  9.                 Description="Filesystem demo external list">
  10.     <DataSource>
  11.       <Property Name="LobSystemInstance" Value="FileSystemModel" />
  12.       <Property Name="EntityNamespace" Value="FileSystem.FileSystemModel" />
  13.       <Property Name="Entity" Value="FileSystemEntity" />
  14.       <Property Name="SpecificFinder" Value="ReadItem" />
  15.     </DataSource>
  16.   </ListInstance>
  17. </Elements>

Notice the CustomScheama attribute of the ListInstance node. It makes possible to deploy a custom list schema file for our list as described by Stefan Stanev:

SharePoint 2010 – ListInstance CustomSchema attribute

It is a great help as it allows you to configure values automatically on deployment instead of setting those values (for example, item order, menu binding) that you should otherwise configure using either the view settings UI or SharePoint Designer.

  1. <List Title="Files" QuickLaunchUrl="Lists/Files/GetFilesByFolder.aspx" Direction="none" Url="Lists/Files" BaseType="0" Type="600" DontSaveInTemplate="TRUE" DisableGridEditing="TRUE" NoCrawl="TRUE" DisallowContentTypes="TRUE" BrowserFileHandling="Permissive" FolderCreation="FALSE" DisableAttachments="TRUE" Catalog="FALSE" SendToLocation="|" ImageUrl="/_layouts/images/itebl.png" xmlns:ows="Microsoft SharePoint" xmlns:spctf="http://schemas.microsoft.com/sharepoint/v3/contenttype/forms" xmlns="http://schemas.microsoft.com/sharepoint/">
  2.   <MetaData>
  3.     <ContentTypes>
  4.       <ContentType ID="0x01" Name="Item" Group="List Content Types" Description="Create a new list item." FeatureId="{695b6570-a48b-4a8e-8ea5-26ea7fc1d162}">
  5.         <Folder TargetName="Item" />
  6.         <FieldRefs>
  7.           <FieldRef ID="{c042a256-787d-4a6f-8a8a-cf6ab767f12d}" Name="ContentType" />
  8.           <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" Required="TRUE" ShowInNewForm="TRUE" ShowInEditForm="TRUE" />
  9.         </FieldRefs>
  10.       </ContentType>
  11.     </ContentTypes>
  12.     <Fields>
  13.       <Field DisplayName="BDC Identity" Hidden="FALSE" Name="BdcIdentity" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="BdcIdentity" Type="Text" />
  14.       <Field DisplayName="Path" Hidden="FALSE" Name="Path" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="Path" Type="Text" />
  15.       <Field DisplayName="Name" Hidden="FALSE" Name="Name" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="Name" Type="Text" />
  16.       <Field DisplayName="Icon" Hidden="FALSE" Name="Icon" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="Icon" Type="Text" />      
  17.       <Field DisplayName="Size" Hidden="FALSE" Name="Size" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="Size" Type="Integer" />
  18.       <Field DisplayName="Created" Hidden="FALSE" Name="Created" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="Created" Type="DateTime" />
  19.       <Field DisplayName="Last modified" Hidden="FALSE" Name="LastModified" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="LastModified" Type="DateTime" />
  20.     </Fields>
  21.     <Views>
  22.       <View DisplayName="GetFilesByFolder" DefaultView="TRUE" BaseViewID="1" Type="HTML" MobileView="TRUE" MobileDefaultView="TRUE" ImageUrl="/_layouts/images/generic.png" XslLink="FileSystem.xsl" WebPartZoneID="Main" WebPartOrder="0" Url="GetFilesByFolder.aspx" SetupPath="pages\viewpage.aspx">
  23.         <XslLink>FileSystem.xsl</XslLink>
  24.         <Method Name="GetFilesByFolder">
  25.           <!– don't forget to alter the value of the path parameter to match your local settings! –>
  26.           <Filter Name="path" Value="C:\Data\Temp\SampleFiles" />
  27.         </Method>
  28.         <Query>
  29.           <OrderBy>
  30.             <!– folders come first, then list of files –>
  31.             <FieldRef Name="IsFolder" Ascending="FALSE"/>
  32.             <!– order by name –>
  33.             <FieldRef Name="Name" />
  34.           </OrderBy>
  35.         </Query>
  36.         <ViewFields>
  37.           <FieldRef Name="Icon" />
  38.           <FieldRef Name="Name" ListItemMenu="TRUE" LinkToItem="TRUE" />
  39.           <FieldRef Name="Size" />
  40.           <FieldRef Name="Created" />
  41.           <FieldRef Name="LastModified" />
  42.         </ViewFields>
  43.         <RowLimit Paged="TRUE">30</RowLimit>
  44.         <Aggregations Value="Off" />
  45.       </View>
  46.     </Views>
  47.   </MetaData>
  48. </List>

Furthermore, the View node in the list schema contains a link to the XSL file (see the XslLink attribute and the subnode having the same name) used on the rendering of the external list, so it is just another great way for making customizations on deployment.

So we added a custom Schema.xml file (Understanding Schema.xml Files describes the content of this file) to the list instance and also added an XSL file called FileSystem.xsl to the project to a mapped folder called XSL in the Layouts folder to deploy the file to the {SharePointRoot}\Template\Layouts\XSL\ path that is the standard location of the XSL files used by external lists (XsltListViewWebPart). See a bit more details about XSL templates in the following post on Yaroslav Pentsarskyy’s blog.

Using custom XSL list rendering templates in SharePoint 2010

The FileSystem.xsl file looks like this:

  1. <xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal" xmlns:o="urn:schemas-microsoft-com:office:office">
  2.   <xsl:include href="/_layouts/xsl/main.xsl"/>
  3.   <xsl:include href="/_layouts/xsl/internal.xsl"/>
  4.   <xsl:param name="AllRows" select="/dsQueryResponse/Rows/Row[$EntityName = '' or (position() &gt;= $FirstRow and position() &lt;= $LastRow)]"/>
  5.   <xsl:param name="dvt_apos">'</xsl:param>
  6.   <xsl:variable name="BdcIdToken" select="'_BdcId_'" />
  7.   <xsl:template name="FieldRef_Text_body.Icon" ddwrt:dvt_mode="body" match ="FieldRef[@Name='Icon']" mode="Text_body" ddwrt:ghost="" xmlns:ddwrt2="urn:frontpage:internal">
  8.     <xsl:param name="thisNode" select="."/>
  9.     <xsl:variable name="text" select="$thisNode/@Icon" />
  10.     <xsl:choose>
  11.       <xsl:when test="contains($text, $BdcIdToken)">
  12.         <xsl:variable name="before" select="substring-before($text, $BdcIdToken)" />
  13.         <xsl:variable name="after" select="substring-after($text, $BdcIdToken)" />
  14.         <xsl:value-of select="concat($before, $thisNode/@BdcIdentity, $after)" disable-output-escaping ="yes"/>
  15.       </xsl:when>
  16.       <xsl:otherwise>
  17.         <xsl:value-of select="$text" disable-output-escaping ="yes"/>
  18.       </xsl:otherwise>
  19.     </xsl:choose>
  20.   </xsl:template>
  21.   <xsl:template name="FieldRef_body.Size" ddwrt:dvt_mode="body" match="FieldRef[@Name='Size']" mode="body" ddwrt:ghost="" xmlns:ddwrt2="urn:frontpage:internal">
  22.     <xsl:param name="thisNode" select="."/>
  23.     <xsl:if test="$thisNode/@IsFolder != 'Yes'">
  24.       <xsl:value-of select="format-number($thisNode/@Size, '###,###,###')"/>
  25.     </xsl:if>
  26.   </xsl:template>
  27. </xsl:stylesheet>

Of course, you can create these schema and .xsl files by editing XML directly if you want, but it is much easier to create the customizations through the SharePoint UI and SharePoint Designer, then exporting the result into a site template file (.wsp) that you can either import into Visual Studio 2010 or use its content directly. You can learn more about this process here:

Deploying an External List via Feature Using CAML

Based on my experience the method described in the post works only for subsites. For site collection roots I have not found the Save Site as Template option in Site Settings, so I made the customizations on a subsite.

One point I failed to configure this way was that my list does not appear on the Quick Launch, although I’ve set both the QuickLaunchUrl in the schema file and the correct value for the OnQuickLaunch attribute in the elements file.

I’ve also tried to set field attributes like ShowInDisplayForm in Schema.xml to alter the visibility of fields on form, but without success.

But let’s back to our business entity representing a file in the file system. The definition of our LOB system shown below:

  1. <LobSystem Name="FileSystemModel" Type="DotNetAssembly">
  2.   <LobSystemInstances>
  3.     <LobSystemInstance Name="FileSystemModel" />
  4.   </LobSystemInstances>
  5.   <Entities>
  6.     <Entity Name="FileSystemEntity" Namespace="FileSystem.FileSystemModel" EstimatedInstanceCount="1000" Version="1.0.0.35">
  7.       <Properties>
  8.         <Property Name="Class" Type="System.String">FileSystem.FileSystemModel.FileSystemEntityService, FileSystemModel</Property>
  9.       </Properties>
  10.       <Identifiers>
  11.         <Identifier Name="Path" TypeName="System.String" />
  12.       </Identifiers>

And this is the .NET class for the business entity:

  1. namespace FileSystem.FileSystemModel
  2. {
  3.     public class FileSystemEntity
  4.     {
  5.         public String Path { get; set; }
  6.         public String RootPath { get; set; }
  7.         public String Name { get; set; }
  8.  
  9.         public String Icon
  10.         {
  11.             get
  12.             {
  13.                 String icon = String.Empty;
  14.                 SPContext context = SPContext.Current;
  15.  
  16.                 if (context != null)
  17.                 {
  18.                     SPWeb web = context.Web;
  19.                     
  20.                     if (IsFolder)
  21.                     {
  22.                         String relativePath = String.IsNullOrEmpty(RootPath) ? String.Empty : Path.Substring(RootPath.Length);
  23.                         icon = String.Format("<DIV><a href='?path={0}'>" +
  24.                             "<img title='{1}' alt='{1}' src='{2}/_layouts/images/folder.gif' border='0'/></a></DIV>",
  25.                                                     relativePath,
  26.                                                     Name,
  27.                                                     SPContext.Current.Site.Url
  28.                                                     );
  29.                     }
  30.                     else
  31.                     {
  32.                         icon = String.Format("<DIV><a href='{2}/_layouts/DownloadExternalData.aspx" +
  33.                             "?EntityNamespace=FileSystem.FileSystemModel" +
  34.                             "&EntityName=FileSystemEntity" +
  35.                             "&LobSystemInstanceName=FileSystemModel" +
  36.                             "&StreamAccessorName=FileAccessor" +
  37.                             "&IsXmlEncodedStreamName=true&ItemId=_BdcId_'>" +
  38.                             "<img title='{1}' alt='{1}' src='{2}/_layouts/images/{0}' border='0'/></a></DIV>",
  39.                                                     SPUtility.MapToIcon(web, Name, String.Empty, IconSize.Size16),
  40.                                                     Name,
  41.                                                     SPContext.Current.Site.Url
  42.                                                     );
  43.                     }
  44.                 }
  45.                 return icon;
  46.             }
  47.         }
  48.  
  49.         // FileInfo.Length is type of long but
  50.         // Int64 (long) is not supported by external lists
  51.         public int? Size { get; set; }
  52.         public DateTime? Created { get; set; }
  53.         public DateTime? LastModified { get; set; }
  54.         public bool IsFolder { get; set; }
  55.     }
  56. }

Note the nullable properties. Null values will be rendered as empty cells by the external list.

You can ignore the relative complex content of the Icon property for now, you will understand its significance by the end of the post. One of the questions I had to decide the data type of the Size property. Since I wanted to be able to order the items by numerical values of the file size and not alphabetical order of the file size (for example 9 would be larger than 100 in this case) I decided to go with numerical values. There is however a limitation you should be aware of. The file size is handled by .NET as long (Int64) data type, but external lists do not support this data type, as stated in the following article:

Using the SharePoint List Object Model and the SharePoint Client Object Model with External Lists

“The following .NET Framework types are not supported by external lists: System.GUID, System.Object, System.URI, System.UInt64, and System.Int64. Therefore, if one of the fields of the external list are of the .NET Framework types listed here, these fields are omitted.”

So I selected Int32 for this example and hope nobody would like to publish and download files through SharePoint UI over this size limit.

To add some minor formatting to the file size, I’ve applied the following pattern to the numbers in the XSL:

<xsl:value-of select="format-number($thisNode/@Size, ‘###,###,###’)"/>

Also note in the XSL, that we disable output escaping for the Icon field. It causes the content of the field to be interpreted as HTML.

The definition of the Finder method in the model file:

  1. <Method Name="GetFilesByFolder" IsStatic="false">
  2.   <FilterDescriptors>
  3.     <FilterDescriptor Name="path" Type="Comparison" FilterField="path" />
  4.   </FilterDescriptors>
  5.   <Parameters>
  6.     <Parameter Name="path" Direction="In">
  7.       <TypeDescriptor Name="path" TypeName="System.String" AssociatedFilter="path" />
  8.     </Parameter>
  9.     <Parameter Name="returnParameter" Direction="Return">
  10.       <TypeDescriptor TypeName="System.Collections.Generic.IEnumerable`1[[FileSystem.FileSystemModel.FileSystemEntity, FileSystemModel]]" IsCollection="true" Name="FileSystemEntityList">
  11.         <TypeDescriptors>
  12.           <TypeDescriptor TypeName="FileSystem.FileSystemModel.FileSystemEntity, FileSystemModel" Name="FileSystemEntity">
  13.             <TypeDescriptors>
  14.               <TypeDescriptor TypeName="System.String" IdentifierName="Path" Name="Path" />
  15.               <TypeDescriptor TypeName="System.Boolean" Name="IsFolder" />
  16.               <TypeDescriptor TypeName="System.String" Name="Name" />
  17.               <TypeDescriptor TypeName="System.String" Name="Icon" />
  18.               <TypeDescriptor TypeName="System.Int32" Name="Size" />
  19.               <TypeDescriptor TypeName="System.DateTime" Name="Created">
  20.                 <Interpretation>
  21.                   <NormalizeDateTime LobDateTimeMode="Local" />
  22.                 </Interpretation>
  23.               </TypeDescriptor>
  24.               <TypeDescriptor TypeName="System.DateTime" Name="LastModified" DefaultDisplayName="Last modified">
  25.                 <Interpretation>
  26.                   <NormalizeDateTime LobDateTimeMode="Local" />
  27.                 </Interpretation>
  28.               </TypeDescriptor>
  29.             </TypeDescriptors>
  30.           </TypeDescriptor>
  31.         </TypeDescriptors>
  32.       </TypeDescriptor>
  33.     </Parameter>
  34.   </Parameters>
  35.   <MethodInstances>
  36.     <MethodInstance Name="GetFilesByFolder" Type="Finder" ReturnParameterName="returnParameter" ReturnTypeDescriptorPath="FileSystemEntityList" Default="false" DefaultDisplayName="GetFilesByFolder" />
  37.   </MethodInstances>
  38. </Method>

As you can see there is a parameter called path for this method. This parameter gets its default value from the Schema.xml file. Be sure to use a value that represents an existing folder on your SharePoint server you would like to display on the external list.

You can alter the root folder path value either in the schema file or at the Data Source Filters section of the view settings after the list is deployed.

image

image

The following method does the job for the finder:

  1. public IEnumerable<FileSystemEntity> GetFilesByFolder(string path)
  2. {
  3.     List<FileSystemEntity> entityList = new List<FileSystemEntity>();
  4.  
  5.     String relativePath = String.Empty;
  6.     String rootPath = path;
  7.  
  8.     HttpContext httpContext = HttpContext.Current;
  9.     if ((httpContext != null) && (httpContext.Request != null))
  10.     {
  11.         relativePath = httpContext.Request.QueryString["path"];
  12.     }
  13.  
  14.     if ((!String.IsNullOrEmpty(relativePath)) && (!relativePath.Contains("..")))
  15.     {
  16.         path = path + relativePath;
  17.     }
  18.  
  19.     if (Directory.Exists(path))
  20.     {
  21.         DirectoryInfo di = new DirectoryInfo(path);
  22.         FileInfo[] files = di.GetFiles();
  23.  
  24.         foreach (FileInfo file in files)
  25.         {
  26.             FileSystemEntity fileSystemEntity = GetFileSystemEntity(file, rootPath);
  27.             entityList.Add(fileSystemEntity);
  28.         }
  29.  
  30.         DirectoryInfo[] folders = di.GetDirectories();
  31.  
  32.         if (path != rootPath)
  33.         {
  34.             DirectoryInfo parentFolder = new DirectoryInfo(path).Parent;
  35.             FileSystemEntity fileSystemEntity = new FileSystemEntity
  36.             {
  37.                 IsFolder = true,
  38.                 Path = parentFolder.FullName,
  39.                 RootPath = rootPath,
  40.                 Name = "..",
  41.             };
  42.             entityList.Add(fileSystemEntity);
  43.         }
  44.  
  45.         foreach (DirectoryInfo folder in folders)
  46.         {
  47.             FileSystemEntity fileSystemEntity = GetFileSystemEntity(folder, rootPath);
  48.             entityList.Add(fileSystemEntity);
  49.         }
  50.  
  51.     }
  52.  
  53.     return entityList;
  54. }

We get the path query string parameter from the current HTTP context and append its value to the root path we configured for the view. I tried to limit the hacking options by eliminating values that contain “..” to disable users to change folders above the root.

If the folder is a subfolder of the root, we also add a “..” folder (without size and date properties) to the list to enable users to change to the parent folder.

Two helper methods to assemble the entity instances:

  1. private static FileSystemEntity GetFileSystemEntity(FileInfo fileInfo, String rootPath)
  2. {
  3.     FileSystemEntity fileSystemEntity = new FileSystemEntity
  4.     {
  5.         IsFolder = false,
  6.         Path = fileInfo.FullName,
  7.         RootPath = rootPath,
  8.         Name = fileInfo.Name,
  9.         // Int64 (long) is not supported by external lists
  10.         Size = (fileInfo.Length > Int32.MaxValue) ? Int32.MaxValue : (Int32)fileInfo.Length,
  11.         Created = fileInfo.CreationTime,
  12.         LastModified = fileInfo.LastWriteTime
  13.     };
  14.  
  15.     return fileSystemEntity;
  16. }
  17.  
  18. private static FileSystemEntity GetFileSystemEntity(DirectoryInfo directoryInfo, String rootPath)
  19. {
  20.     FileSystemEntity fileSystemEntity = new FileSystemEntity
  21.     {
  22.         IsFolder = true,
  23.         Path = directoryInfo.FullName,
  24.         RootPath = rootPath,
  25.         Name = directoryInfo.Name,
  26.         Size = 0,
  27.         Created = directoryInfo.CreationTime,
  28.         LastModified = directoryInfo.LastWriteTime
  29.     };
  30.  
  31.     return fileSystemEntity;
  32. }

Note, that for folders we do not set the size property.

The SpecificFinder method is defined in the model as illustrated below:

  1. <Method Name="ReadItem">
  2.   <Parameters>
  3.     <Parameter Direction="In" Name="path">
  4.       <TypeDescriptor TypeName="System.String" IdentifierName="Path" Name="Path" />
  5.     </Parameter>
  6.     <Parameter Direction="Return" Name="returnParameter">
  7.       <TypeDescriptor TypeName="FileSystem.FileSystemModel.FileSystemEntity, FileSystemModel" Name="FileSystemEntity">
  8.         <TypeDescriptors>
  9.           <TypeDescriptor TypeName="System.String" IdentifierName="Path" Name="Path" />
  10.           <TypeDescriptor TypeName="System.Boolean" Name="IsFolder" />
  11.           <TypeDescriptor TypeName="System.String" Name="Name" />
  12.           <TypeDescriptor TypeName="System.String" Name="Icon" />
  13.           <TypeDescriptor TypeName="System.Int32" Name="Size" />
  14.           <TypeDescriptor TypeName="System.DateTime" Name="Created">
  15.             <Interpretation>
  16.               <NormalizeDateTime LobDateTimeMode="Local" />
  17.             </Interpretation>
  18.           </TypeDescriptor>
  19.           <TypeDescriptor TypeName="System.DateTime" Name="LastModified" DefaultDisplayName="Last modified">
  20.             <Interpretation>
  21.               <NormalizeDateTime LobDateTimeMode="Local" />
  22.             </Interpretation>
  23.           </TypeDescriptor>
  24.         </TypeDescriptors>
  25.       </TypeDescriptor>
  26.     </Parameter>
  27.   </Parameters>
  28.   <MethodInstances>
  29.     <MethodInstance Type="SpecificFinder" ReturnParameterName="returnParameter" Default="true" Name="ReadItem" DefaultDisplayName="Read FileSystemEntity" />
  30.   </MethodInstances>
  31. </Method>

The corresponding method in the .NET class is really straightforward:

  1. public static FileSystemEntity ReadItem(String path)
  2. {
  3.     FileSystemEntity fileSystemEntity = null;
  4.     if (File.Exists(path))
  5.     {
  6.         FileInfo file = new FileInfo(path);
  7.         fileSystemEntity = GetFileSystemEntity(file, null);
  8.     }
  9.     else if (Directory.Exists(path))
  10.     {
  11.         DirectoryInfo folder = new DirectoryInfo(path);
  12.         fileSystemEntity = GetFileSystemEntity(folder, null);
  13.     }
  14.     else
  15.     {
  16.         // file deleted, create empty entity
  17.         fileSystemEntity = new FileSystemEntity();
  18.     }
  19.     return fileSystemEntity;
  20. }

The last method our model implements is the StreamAccessor.

  1. <Method Name="GetFile">               
  2.    <Parameters>
  3.      <Parameter Direction="In" Name="path">
  4.       <TypeDescriptor TypeName="System.String" IdentifierName="Path" Name="Path" />
  5.     </Parameter>
  6.      <Parameter Name="StreamData" Direction="Return">
  7.        <TypeDescriptor TypeName="System.IO.Stream" Name="FileContent" />
  8.     </Parameter>
  9.   </Parameters>
  10.    <MethodInstances>
  11.      <MethodInstance Name="FileAccessor" Type="StreamAccessor" ReturnParameterName="StreamData" ReturnTypeDescriptorName="FileContent">
  12.        <Properties>                    
  13.         <Property Name="FileNameField" Type="System.String">Name</Property>
  14.       </Properties>
  15.     </MethodInstance>
  16.   </MethodInstances>
  17. </Method>

We simply provide the read-only stream for the selected file:

  1. public static Stream GetFile(String path)
  2. {
  3.     return new FileStream(path, FileMode.Open);
  4. }

You might be a bit surprised that I’m implementing a StreamAccessor method for my external list. The following article describes, that a download link is displayed in the Business Data List Web Part for external content types implementing the StreamAccessor method, but “if an external list is created by using an external content type with a stream field, the list does not show the link to download the content.”

Accessing BLOB Data from External Systems Using Business Connectivity Services in SharePoint Server 2010

Fortunately that is true only for the default behavior of the external lists, but nobody stops you to learn from the example of the Business Data List Web Part and implement something similar.

This web part creates the link to the downloadable file through the DownloadExternalData.aspx, passing the properties of the BDC model as query string parameters. To identify the file (BDC entity) to download, we should also pass the ID of the BDC item.

The following code snippet illustrates how we assemble the link for the files in our code (note also, how we get the icon for the file extension using the SPUtility.MapToIcon method):

  1. icon = String.Format("<DIV><a href='{2}/_layouts/DownloadExternalData.aspx" +
  2.     "?EntityNamespace=FileSystem.FileSystemModel" +
  3.     "&EntityName=FileSystemEntity" +
  4.     "&LobSystemInstanceName=FileSystemModel" +
  5.     "&StreamAccessorName=FileAccessor" +
  6.     "&IsXmlEncodedStreamName=true&ItemId=_BdcId_'>" +
  7.     "<img title='{1}' alt='{1}' src='{2}/_layouts/images/{0}' border='0'/></a></DIV>",
  8.                             SPUtility.MapToIcon(web, Name, String.Empty, IconSize.Size16),
  9.                             Name,
  10.                             SPContext.Current.Site.Url
  11.                             );

We now the BDC model parameters, so they are hardcoded, but we don’t know the ID of the BDC entity. To reserve its place, we use the _BdcId_ token in the code, and replace it using the XSLT later:

  1. <xsl:when test="contains($text, $BdcIdToken)">
  2.   <xsl:variable name="before" select="substring-before($text, $BdcIdToken)" />
  3.   <xsl:variable name="after" select="substring-after($text, $BdcIdToken)" />
  4.   <xsl:value-of select="concat($before, $thisNode/@BdcIdentity, $after)" disable-output-escaping ="yes"/>
  5. </xsl:when>

For folders we simply create a link for the current page passing a path query string parameter that contains its relative path related to the root.

  1. String relativePath = String.IsNullOrEmpty(RootPath) ? String.Empty : Path.Substring(RootPath.Length);
  2. icon = String.Format("<DIV><a href='?path={0}'>" +
  3.     "<img title='{1}' alt='{1}' src='{2}/_layouts/images/folder.gif' border='0'/></a></DIV>",
  4.                             relativePath,
  5.                             Name,
  6.                             SPContext.Current.Site.Url
  7.                             );

Let’s see some screenshots about the working sample. On the first one you can see some folder and files. By default, the items are ordered by name, folders on the top, files below, as set by the Query/OrderBy nodes in the Schema.xml.

image

Note the icons. Clicking on them have different behavior based on the item type (folder vs. file). When clicking on file icons, a File Download dialog is displayed:

image

When you click on a folder icon, its relative path is included in the query string, and it causes the list to be refreshed with the content of the subfolder.

image

An extra folder icon is displayed in the subfolders. By clicking on the folder icon next to the “..” label you can navigate back to the parent folder.

You can also view an item either by clicking on its name or via selecting the View Item form the context menu. Note, that the menu is bound to the Name column (via <FieldRef Name="Name" ListItemMenu="TRUE" LinkToItem="TRUE" /> in Schema.xml), but it is also available for the first column (Icon) without explicitly specified. It seems to be a default behavior of external lists.

image 

The next screen is not from the current version but shows how the default view form would be displayed without customization:

image

In this case the form would be defined in the Schema.xml using the following settings:

  1. <Forms>
  2.   <Form Type="DisplayForm" Url="DispForm.aspx" SetupPath="pages\form.aspx" WebPartZoneID="Main" />      
  3. </Forms>

That display is definitely not what I would like to see.

Since the content of the display form is described by a BinarySerializedWebPart section when working with the site template exported earlier, it is not trivial to alter it in the Elements.xml that contains the form. Instead, we can easily alter the content using SharePoint Designer 2010 and extract the content again from the modified site.

image 

I opened the DispForm.aspx file of the external list for editing and removed the rows of the view I don’t need, the ones for fields Path, IsFolder and Icon. After saving the results, I saved the site as a template, exported the site template as .wsp file, renamed the file to .cab. This time I had to open the Elements.xml file in the MySiteTemplateNameModules folder and to look up the Module that describes the DispForm.aspx form of my list. Since my list is called Files, the Module node has the Name attribute value ListsFiles_pages. Since my “work” list was on a subsite to enable saving the site as a template, and my target site was a root site, I had to alter a few URLs and file locations before importing the content of this section into Elements.xml in the FileSystemForms module of my project.

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  3.   <Module Name="FileSystemForms" Url="Lists/Files" RootWebOnly="FALSE" SetupPath="pages">
  4.       <FileUrl="DispForm.aspx" Type="Ghostable" Path="form.aspx">
  5.       <BinarySerializedWebPart>
  6.         <GUIDMap>
  7.           <GUID Id="33ff2881_489d_4ce2_ac94_e81d64689d2a" ListUrl="Lists/Files" />
  8.         </GUIDMap>
  9.         <WebPart ID="{035cec7d-5f69-4dbf-a551-0b8203467c41}" WebPartIdProperty="" List="{$ListId:Lists/Files;}" Type="4"
  10.                  Flags="0" DisplayName="" Version="4" Url="Lists/Files/DispForm.aspx" WebPartOrder="1" WebPartZoneID="Main"
  11.                  IsIncluded="True" FrameState="0" WPTypeId="{feaafd58-2dc9-e199-be37-d6cdd7f84690}"
  12.                  SolutionId="{00000000-0000-0000-0000-000000000000}" Assembly="" Class="" Src=""
  13.                  AllUsers="B6Dt/kMAAAABAAAAAAAAAAIAAAAvX2xheW91dHMvaW1hZ2VzL2l0ZWJsLnBuZwAvRjFTaXRlL0xpc3RzL0ZpbGVzAP8BFCsAJQICAgMCAwEEAAICAhICFAEBAAIEBQtDb250cm9sTW9kZQspiAFNaWNyb3NvZnQuU2hhcmVQb2ludC5XZWJDb250cm9scy5TUENvbnRyb2xNb2RlLCBNaWNyb3NvZnQuU2hhcmVQb2ludCwgVmVyc2lvbj0xNC4wLjAuMCwgQ3VsdHVyZT1uZXV0cmFsLCBQdWJsaWNLZXlUb2tlbj03MWU5YmNlMTExZTk0MjljAQUIRm9ybVR5cGUCBAEAAAIWAoYBCyo0U3lzdGVtLldlYi5VSS5XZWJDb250cm9scy5XZWJQYXJ0cy5XZWJQYXJ0RXhwb3J0TW9kZQICggEFGi9fbGF5b3V0cy9pbWFnZXMvaXRlYmwucG5nAn0FEy9GMVNpdGUvTGlzdHMvRmlsZXMFCFBhZ2VUeXBlCyl3TWljcm9zb2Z0LlNoYXJlUG9pbnQuUEFHRVRZUEUsIE1pY3Jvc29mdC5TaGFyZVBvaW50LCBWZXJzaW9uPTE0LjAuMC4wLCBDdWx0dXJlPW5ldXRyYWwsIFB1YmxpY0tleVRva2VuPTcxZTliY2UxMTFlOTQyOWMEBQdMaXN0VXJsZQUGTGlzdElkKClYU3lzdGVtLkd1aWQsIG1zY29ybGliLCBWZXJzaW9uPTIuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49Yjc3YTVjNTYxOTM0ZTA4OSQzM2ZmMjg4MS00ODlkLTRjZTItYWM5NC1lODFkNjQ2ODlkMmEFD0xpc3REaXNwbGF5TmFtZWUClQEFJnszM0ZGMjg4MS00ODlELTRDRTItQUM5NC1FODFENjQ2ODlEMkF9BQ1YbWxEZWZpbml0aW9uBcUPDQo8VXNlckNvbnRyb2wgeDpDbGFzcz0iRm9ybVhtbFRvWGFtbC5Vc2VyQ29udHJvbDIiIHhtbG5zOng9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vd2luZngvMjAwNi94YW1sIiB4bWxuczpTaGFyZVBvaW50PSJNaWNyb3NvZnQuU2hhcmVQb2ludC5XZWJDb250cm9scyIgeG1sbnM6c3lzdGVtPSJjbHItbmFtZXNwYWNlOlN5c3RlbTthc3NlbWJseT1tc2NvcmxpYiI+PFN0YWNrUGFuZWwgeDpOYW1lPSJGb3JtIj4NCjxTdGFja1BhbmVsLlJlc291cmNlcz4NCjxzeXN0ZW06U3RyaW5nIHg6S2V5PSJGb3JtTW9kZSI+RGlzcGxheTwvc3lzdGVtOlN0cmluZz4NCjxzeXN0ZW06U3RyaW5nIHg6S2V5PSJGb3JtVHlwZSI+TGlzdEZvcm08L3N5c3RlbTpTdHJpbmc+DQo8L1N0YWNrUGFuZWwuUmVzb3VyY2VzPg0KPFN0YWNrUGFuZWwgeDpOYW1lPSJNYWluU2VjdGlvbnMiPjxHcmlkPjxHcmlkLkNvbHVtbkRlZmluaXRpb25zPg0KPENvbHVtbkRlZmluaXRpb24gU3R5bGU9IntTdGF0aWNSZXNvdXJjZSBtcy1mb3JtbGFiZWx9Ii8+DQo8Q29sdW1uRGVmaW5pdGlvbiBTdHlsZT0ie1N0YXRpY1Jlc291cmNlIG1zLWZvcm1ib2R5fSIvPg0KPC9HcmlkLkNvbHVtbkRlZmluaXRpb25zPjxHcmlkLlJvd0RlZmluaXRpb25zPg0KPFJvd0RlZmluaXRpb24gLz4NCjxSb3dEZWZpbml0aW9uIC8+DQo8Um93RGVmaW5pdGlvbiAvPg0KPFJvd0RlZmluaXRpb24gLz4NCjwvR3JpZC5Sb3dEZWZpbml0aW9ucz4NCjxTaGFyZVBvaW50OkZpZWxkTGFiZWwgR3JpZC5Db2x1bW49IjAiIEdyaWQuUm93PSIwIiBDb250cm9sTW9kZT0iRGlzcGxheSIgRmllbGROYW1lPSJOYW1lIiAvPg0KPENvbW1lbnQgRmllbGROYW1lPSJOYW1lIiBGaWVsZEludGVybmFsTmFtZT0iTmFtZSIgRmllbGRUeXBlPSJUZXh0IiAvPg0KPFNoYXJlUG9pbnQ6Rm9ybUZpZWxkIEdyaWQuQ29sdW1uPSIxIiBHcmlkLlJvdz0iMCIgQ29udHJvbE1vZGU9IkRpc3BsYXkiIEZpZWxkTmFtZT0iTmFtZSIgSW5jbHVkZURlc2NyaXB0aW9uPSJUcnVlIi8+DQo8U2hhcmVQb2ludDpGaWVsZExhYmVsIEdyaWQuQ29sdW1uPSIwIiBHcmlkLlJvdz0iMSIgQ29udHJvbE1vZGU9IkRpc3BsYXkiIEZpZWxkTmFtZT0iU2l6ZSIgLz4NCjxDb21tZW50IEZpZWxkTmFtZT0iU2l6ZSIgRmllbGRJbnRlcm5hbE5hbWU9IlNpemUiIEZpZWxkVHlwZT0iSW50ZWdlciIgLz4NCjxTaGFyZVBvaW50OkZvcm1GaWVsZCBHcmlkLkNvbHVtbj0iMSIgR3JpZC5Sb3c9IjEiIENvbnRyb2xNb2RlPSJEaXNwbGF5IiBGaWVsZE5hbWU9IlNpemUiIEluY2x1ZGVEZXNjcmlwdGlvbj0iVHJ1ZSIvPg0KPFNoYXJlUG9pbnQ6RmllbGRMYWJlbCBHcmlkLkNvbHVtbj0iMCIgR3JpZC5Sb3c9IjIiIENvbnRyb2xNb2RlPSJEaXNwbGF5IiBGaWVsZE5hbWU9IkNyZWF0ZWQiIC8+DQo8Q29tbWVudCBGaWVsZE5hbWU9IkNyZWF0ZWQiIEZpZWxkSW50ZXJuYWxOYW1lPSJDcmVhdGVkIiBGaWVsZFR5cGU9IkRhdGVUaW1lIiAvPg0KPFNoYXJlUG9pbnQ6Rm9ybUZpZWxkIEdyaWQuQ29sdW1uPSIxIiBHcmlkLlJvdz0iMiIgQ29udHJvbE1vZGU9IkRpc3BsYXkiIEZpZWxkTmFtZT0iQ3JlYXRlZCIgSW5jbHVkZURlc2NyaXB0aW9uPSJUcnVlIi8+DQo8U2hhcmVQb2ludDpGaWVsZExhYmVsIEdyaWQuQ29sdW1uPSIwIiBHcmlkLlJvdz0iMyIgQ29udHJvbE1vZGU9IkRpc3BsYXkiIEZpZWxkTmFtZT0iTGFzdE1vZGlmaWVkIiAvPg0KPENvbW1lbnQgRmllbGROYW1lPSJMYXN0IG1vZGlmaWVkIiBGaWVsZEludGVybmFsTmFtZT0iTGFzdE1vZGlmaWVkIiBGaWVsZFR5cGU9IkRhdGVUaW1lIiAvPg0KPFNoYXJlUG9pbnQ6Rm9ybUZpZWxkIEdyaWQuQ29sdW1uPSIxIiBHcmlkLlJvdz0iMyIgQ29udHJvbE1vZGU9IkRpc3BsYXkiIEZpZWxkTmFtZT0iTGFzdE1vZGlmaWVkIiBJbmNsdWRlRGVzY3JpcHRpb249IlRydWUiLz4NCjwvR3JpZD4NCjwvU3RhY2tQYW5lbD4NCjwvU3RhY2tQYW5lbD4NCjwvVXNlckNvbnRyb2w+AktkBRFQYXJhbWV0ZXJCaW5kaW5ncwXoAg0KPFBhcmFtZXRlckJpbmRpbmcgTmFtZT0iZHZ0X2Fwb3MiIExvY2F0aW9uPSJQb3N0YmFjaztDb25uZWN0aW9uIi8+DQogICAgICAgIDxQYXJhbWV0ZXJCaW5kaW5nIE5hbWU9IlVzZXJJRCIgTG9jYXRpb249IkNBTUxWYXJpYWJsZSIgRGVmYXVsdFZhbHVlPSJDdXJyZW50VXNlck5hbWUiLz4NCiAgICAgICAgPFBhcmFtZXRlckJpbmRpbmcgTmFtZT0iVG9kYXkiIExvY2F0aW9uPSJDQU1MVmFyaWFibGUiIERlZmF1bHRWYWx1ZT0iQ3VycmVudERhdGUiLz4NCiAgICAgICAgPFBhcmFtZXRlckJpbmRpbmcgTmFtZT0iTGlzdEl0ZW1JZCIgTG9jYXRpb249IlF1ZXJ5U3RyaW5nKElEKSIgRGVmYXVsdFZhbHVlPSIwIi8+DQogICAgICAgIA==" />
  14.       </BinarySerializedWebPart>
  15.     </File>
  16.   </Module>
  17. </Elements>

The next image shows the result.

image

If you forget to remove the Forms node from the schema file, both the form set in the Schema.xml and the one deployed using the module will be displayed, so be careful.

image

You can download the sample project here (including the sample file structure).

August 23, 2010

Creating external lists from code

Filed under: BCS, External list, SP 2010 — Tags: , , — Peter Holpar @ 20:30

You can create an external list based on an entity (external content type) defined in SharePoint Business Connectivity Services programmatically. All you need to do is to use the SPListDataSource class that describes the entity to be bound to the external list instance.

When creating an external list, you should use the Add method overload of the SPListCollection that has the SPListDataSource type parameter. You can find a sample for that from Frederik Prijck here.

A similar method replacing the strings with the string constants defined in the SPListDataSource.BDCProperties class:

  1. private void CreateExternalList(SPWeb web)
  2. {
  3.     SPListCollection lists = web.Lists;
  4.     SPListDataSource listDataSource = new SPListDataSource();
  5.     // set up the list data source
  6.     listDataSource.SetProperty(SPListDataSource.BDCProperties.Entity, "YourBdcEntity");
  7.     listDataSource.SetProperty(SPListDataSource.BDCProperties.EntityNamespace, "YourBdc.EntityNamespace");
  8.     listDataSource.SetProperty(SPListDataSource.BDCProperties.LobSystemInstance, "YourLobSystemInstancece");
  9.     listDataSource.SetProperty(SPListDataSource.BDCProperties.SpecificFinder, "ReadItem");
  10.     // create list
  11.     Guid extListGuid = lists.Add("External list title", "External list description", "extlist", listDataSource);
  12.     SPList extList = lists[extListGuid];
  13.     // set other list properties
  14.     extList.OnQuickLaunch = true;
  15.     extList.Update();
  16. }

As you can see the properties of the SPListDataSource class are not real .NET properties. Instead of calling the setter of the property, you can set the their values by calling the SetProperty method.

I felt initializing the SPListDataSource through the SetProperty method a bit cumbersome, so I’ve created an extension method for the SPListDataSource class:

  1. public static class Extensions
  2.     {
  3.         public static void Initialize(this SPListDataSource listDataSource, String entity, String entityNamespace, String lobSystemInstance, String specificFinder)
  4.         {
  5.             listDataSource.SetProperty(SPListDataSource.BDCProperties.Entity, entity);
  6.             listDataSource.SetProperty(SPListDataSource.BDCProperties.EntityNamespace, entityNamespace);
  7.             listDataSource.SetProperty(SPListDataSource.BDCProperties.LobSystemInstance, lobSystemInstance);
  8.             listDataSource.SetProperty(SPListDataSource.BDCProperties.SpecificFinder, specificFinder);
  9.         }
  10.     }

Using the new extension method makes the original code a bit more readable:

  1. private void CreateExternalListEx(SPWeb web)
  2. {
  3.     SPListCollection lists = web.Lists;
  4.     SPListDataSource listDataSource = new SPListDataSource();
  5.     // set up the list data source using the extension method
  6.     listDataSource.Initialize("YourBdcEntity", "YourBdc.EntityNamespace", "YourLobSystemInstancece", "ReadItem");
  7.     // create list
  8.     Guid extListGuid = lists.Add("External list title2", "External list description", "extlist2", listDataSource);
  9.     // set other list properties
  10.     SPList extList = lists[extListGuid];
  11.     extList.OnQuickLaunch = true;
  12.     extList.Update();
  13. }

Remark: I found that creating the external list from code takes considerably more time than creating one from the SharePoint UI. In my test environment it was about 30 secs vs. 5 secs that is quite a big difference. Probably I should launch Reflector to see that quicker method of external list creation.

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

Follow

Get every new post delivered to your Inbox.

Join 54 other followers