Second Life of a Hungarian SharePoint Geek

March 26, 2018

Querying SharePoint list items including their attachment in a single request from the JavaScript Client Object Model

Filed under: CAML, ECMAScript Client Object Model, JSCOM, SP 2013 — Tags: , , , — Peter Holpar @ 23:31

Assume there is a SharePoint list including a few hundreds of items, each item might have a few attachments, but there might be items without attachments as well. We need to display only some of the items (filtered dynamically based on specific conditions) for the users on a web page. We use already the JavaScript client object model on that page, so it would be ideal to keep this technology, instead of involving another one (like REST or SharePoint web services) just because this task.

I found several useful pages while looking for a solution for my problem, like this one about using CAML from the client object model, although some of the statements in the sections “Include attachment URLs” and “Limitations” turned to be a bit misleading later.

Few posts and forum answers suggested to query the items first using a CAML query, then read the attachments for each matching item in subsequent requests individually. That would not perform very well if there are a lot of matching item and is generally not a nice solution. Another suggestion I often found is to query the items with a CAML query (just like in the previous case), then query all of the attachments (they are stored in a folder called Attachments n the list with a separate subfolder, like 1, 2, etc. for each list items) in a second query, then match the attachments files to items based on the relative path of the attachments (attachment for the list item with ID 1 are in the folder /Attachments/1, etc.). If you have a lot of items having a few attachments for each item, that might have performance problem as well, and the implementation itself is challenging.

Vadim Gremyachev posted a solution using the managed client object model with a single request only on SharePoint Stack Exchange. In his solution, he uses the following line to create an execute the CAML query:

var items = list.GetItems(CamlQuery.CreateAllItemsQuery());

The static CreateAllItemsQuery method of the CamlQuery object is only an easy way to create a CAML query having the RecursiveAll view scope, it means, content of the subfolders (if there is any) would be searched through either.

public static CamlQuery CreateAllItemsQuery()
{
    return new CamlQuery { ViewXml = "<View Scope=\"RecursiveAll\">\r\n    <Query>\r\n    </Query>\r\n</View>" };
}

As we have a flat list, without any folders, I’ve slightly modified Vadim’s code first:

  1. var siteUrl = "http://YourSharePoint/site/subsite&quot;;
  2. using (var ctx = new ClientContext(siteUrl))
  3. {
  4.     var web = ctx.Web;
  5.     var list = web.Lists.GetByTitle("TitleOfYourList");
  6.     // Vadim's version fo a list with a folder struture
  7.     //var items = list.GetItems(CamlQuery.CreateAllItemsQuery());
  8.     // my version for a flat list without folders
  9.     var items = list.GetItems(new CamlQuery());
  10.     ctx.Load(items, icol => icol.Include(i => i["Title"], i => i.AttachmentFiles));
  11.     ctx.ExecuteQuery();
  12.  
  13.     //Print attachment Urls for list items
  14.     foreach (var item in items)
  15.     {
  16.         if (item.AttachmentFiles.Count > 0)
  17.         {
  18.             Console.WriteLine("{0} attachments:", item["Title"]);
  19.             foreach (var attachment in item.AttachmentFiles)
  20.             {
  21.                 Console.WriteLine(attachment.ServerRelativeUrl);
  22.             }
  23.         }
  24.         else
  25.         {
  26.             Console.WriteLine("No attachments were found for list item '{0}' ", item["Title"]);
  27.         }
  28.     }
  29. }

It resulted in the following request sent to the server, captured by Fiddler:

image

Note, that only the properties Title and AttachmentFiles were requested in the Include statement, and the ViewXml is empty.

We wanted to reproduce the same request using the JavaScript object model, but haven’t found any solution immediately ready to use, so first made a quick check, which alternative solutions are available in JavaScript, if we were not able to transform the managed code sample to JavaScript. Let’s see what we found.

I found a jQuery-based approach here, that utilizes the GetListItems method of the Lists SharePoint web service:

  1. $("table.ms-listviewtable tr:has(td.ms-vb2)").find("td:first").filter(function() {
  2.     return $(this).text().toLowerCase() == ID;
  3. }).html("<img src='" + url[1] + "' width=150 height=100 />");
  4.  
  5. $(document).ready(function() {
  6.     querySPWebServices();
  7. });
  8. function querySPWebServices() {
  9.     var soapEnv = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'&gt; \\par         <soapenv:Body> \\par         <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'&gt; \\par         <listName>Projects</listName> \\par         <viewFields> \\par         <ViewFields> \\par         <FieldRef Name='Title' /> \\par         <FieldRef Name='Body' /> \\par         <FieldRef Name='ID' /> \\par         <FieldRef Name='Attachments' /> \\par         </ViewFields> \\par         </viewFields> \\par         <query> \\par         <Query /> \\par         </query> \\par         <queryOptions> \\par         <QueryOptions> \\par         <IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls> \\par         </QueryOptions> \\par         </queryOptions> \\par         </GetListItems> \\par         </soapenv:Body> \\par         </soapenv:Envelope>";
  10.     $.ajax({
  11.         async: false,
  12.         url: "http://server.name.com/site/_vti_bin/lists.asmx&quot;,
  13.         type: "POST",
  14.         dataType: "xml",
  15.         data: soapEnv,
  16.         complete: processResult,
  17.         contentType: "text/xml; charset=\"utf-8\""
  18.     });
  19. };
  20.  
  21. function processResult(xData, status) {
  22.     $(xData.responseXML).find("z\\:row").each(function() {
  23.         if ($(this).attr("ows_Attachments") != 0) {
  24.             var url = $(this).attr("ows_Attachments").replace(/#/g, "").split(';');
  25.             var ID = $(this).attr("ows_ID");
  26.             $("table.ms-listviewtable tr:has(td.ms-vb2)").find("td:first").filter(function() {
  27.                 return $(this).text().toLowerCase() == ID;
  28.             }).html("<img src='" + url[1] + "' width=150 height=100 />");
  29.         };
  30.     });
  31. };

Another solution invoking the GetListItems method as well, but this time using SPServices can be found here:

  1. var p = $().SPServices({
  2.     operation: "GetListItems",
  3.     listName: "listname",
  4.     CAMLQueryOptions: "<QueryOptions>" +
  5.             "<IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls>" +
  6.         "</QueryOptions>"
  7. });
  8.   
  9. p.done(function() {
  10.   
  11.     $(p.responseXML).).SPFilterNode("z:row").each(function() {
  12.       
  13.         var attachments = [];
  14.   
  15.         var att = $(this).attr("ows_Attachments");
  16.         if(att !== "0") {
  17.             attachments = att.split(";#"); // Now you'll have an array of attachment URLs
  18.         } else {
  19.             // att will be "0", indicating that there are no attachments
  20.         }
  21.       
  22.     });
  23.   
  24. });

You could query list items with their attachments via the REST interface as well:

http://YourSharePoint/site/subsite/_api/Web/Lists/GetByTitle('TitleOfYourList')/Items/?$expand=AttachmentFiles

One can use CAML in conjunction with REST by using POST requests and the GetItems method as described here, but as far as I see, the $expand=AttachmentFiles plays not well with the GetItems method. Instead of CAML, you can add the condition, ordering and row limit attributes of your query via $filter, $orderby and $top to the REST query as described here.

After this short overview, let’s try to rewrite our C# code above in JavaScript.

First, I came up with a query like the one below. I requested the very same properties, that we had in the managed client object version: the Title and AttachmentFiles. Note, that I omitted all of the filters I needed in the case of real life list from the CAML query due to the readability as this part is not relevant now in solving the main problem. In contrast to the managed client object code above, I restricted the fields in the CAML query as well via the ViewFields element. That was a mistake, as it turned out later, but I’m a strong believer, that mistakes are there to teach us something new.

  1. var list = web.get_lists().getByTitle("TitleOfYourList");
  2.  
  3. var camlQuery = new SP.CamlQuery();
  4. var query = '<View><ViewFields><FieldRef Name="Title"/><FieldRef Name="AttachmentFiles"/></ViewFields></View>';
  5. camlQuery.set_viewXml(query);
  6.  
  7. var items = list.getItems(camlQuery);
  8. ctx.load(items, 'Include(Title, AttachmentFiles)');

The result was not satisfying, the request failed (screenshot from Fiddler):

image

ErrorMessage: Value does not fall within the expected range.

ErrorTypeName: System.ArgumentException

ErrorStackTrace:

Microsoft.SharePoint.SPFieldMap.GetColumnNumber(String strFieldName, Boolean bThrow)
   at Microsoft.SharePoint.SPListItemCollection.GetColumnNumber(String groupName, Boolean bThrowException)
   at Microsoft.SharePoint.SPListItem.GetValue(SPField fld, Int32 columnNumber, Boolean bRaw, Boolean bThrowException)
   at Microsoft.SharePoint.SPListItem.get_Attachments()
   at Microsoft.SharePoint.SPAttachmentCollection_Client.<GetEnumerator>d__0.MoveNext()
   at Microsoft.SharePoint.Client.ServerStub.<EnumerateChildItems>d__36.MoveNext()
   at Microsoft.SharePoint.Client.ServerStub.WriteChildItems(JsonWriter writer, Object obj, ClientObjectQuery objectQuery, ProxyContext proxyContext)
   at Microsoft.SharePoint.Client.ServerStub.WriteAsJson(JsonWriter writer, Object obj, ClientObjectQuery objectQuery, ProxyContext proxyContext)
   at Microsoft.SharePoint.Client.ServerStub.WriteAsJsonWithMonitoredScope(JsonWriter writer, Object value, ClientObjectQuery objectQuery, ProxyContext proxyContext)
   at Microsoft.SharePoint.ServerStub.SPListItemServerStub.WriteOnePropertyValueAsJson(JsonWriter writer, Object target, ClientQueryProperty field, ProxyContext proxyContext)
   at Microsoft.SharePoint.Client.ServerStub.WriteAsJson(JsonWriter writer, Object obj, ClientObjectQuery objectQuery, ProxyContext proxyContext)
   at Microsoft.SharePoint.Client.ServerStub.WriteChildItems(JsonWriter writer, Object obj, ClientObjectQuery objectQuery, ProxyContext proxyContext)
   at Microsoft.SharePoint.Client.ServerStub.WriteAsJson(JsonWriter writer, Object obj, ClientObjectQuery objectQuery, ProxyContext proxyContext)
   at Microsoft.SharePoint.Client.ServerStub.WriteAsJsonWithMonitoredScope(JsonWriter writer, Object value, ClientObjectQuery objectQuery, ProxyContext proxyContext)
   at Microsoft.SharePoint.Client.ClientMethodsProcessor.WriteQueryResults(Object obj, ClientObjectQuery objQuery)
   at Microsoft.SharePoint.Client.ClientMethodsProcessor.ProcessQuery(XmlElement xe)
   at Microsoft.SharePoint.Client.ClientMethodsProcessor.ProcessOne(XmlElement xe)
   at Microsoft.SharePoint.Client.ClientMethodsProcessor.ProcessStatements(XmlNode xe)
   at Microsoft.SharePoint.Client.ClientMethodsProcessor.Process()

You can see the getter method of the Attachments property of the SPListItem object in the fourth line of the stack trace. Let’s have a quick view, how this method works:

  1. public SPAttachmentCollection get_Attachments()
  2. {
  3.    if (this.m_Attachments == null)
  4.    {
  5.        string pbstrUrlPrefix = null;
  6.        object[,] objArrAttachments = null;
  7.        uint pdwRowCount = 0;
  8.        bool bDoQuery = false;
  9.        if (!this.HasExternalDataSource && (this.ID != 0))
  10.        {
  11.            object obj3;
  12.            object obj2 = this.GetValue("Attachments");
  13.            if (obj2 != null)
  14.            {
  15.                bDoQuery = obj2.ToString() != "0";
  16.            }
  17.            SPSecurity.SetListInHttpContext(HttpContext.Current, this.m_Items.List.InternalName);
  18.            SPWeb web = this.Web;
  19.            web.Request.GetAttachmentsInfo(web.Url, this.m_Items.List.InternalName, Convert.ToInt32(this.ID), bDoQuery, out pbstrUrlPrefix, out pdwRowCount, out obj3);
  20.            if (bDoQuery)
  21.            {
  22.                objArrAttachments = (object[,]) obj3;
  23.            }
  24.        }
  25.        this.m_Attachments = new SPAttachmentCollection(this, pbstrUrlPrefix, pdwRowCount, objArrAttachments);
  26.    }
  27.    return this.m_Attachments;
  28. }

As we’ve learnt from Karine’s post, the Attachments field is a flag indicating if a list item has any attachments or not. Obviously, the getter method depends on this property (see the line object obj2 = this.GetValue("Attachments"); in code above), and we should request this property explicitly in our code.

That is the new version of my query:

  1. var camlQuery = new SP.CamlQuery();
  2. var query = '<View><ViewFields><FieldRef Name="Title"/><FieldRef Name="Attachments"/><FieldRef Name="AttachmentFiles"/></ViewFields></View>';
  3. camlQuery.set_viewXml(query);
  4.  
  5. var items = list.getItems(camlQuery);
  6. ctx.load(items, 'Include(Title, Attachments, AttachmentFiles)');

The request was captured by Fiddler as shown bellow. See the properties included in the Include statement, as well the ViewXml:

image

This version works already as expected, but comparing the request with the one captured at C#-based version (see above) I found, that one can omit the Attachments field from the Include statement without any problem.

  1. var camlQuery = new SP.CamlQuery();
  2. var query = '<View><ViewFields><FieldRef Name="Title"/><FieldRef Name="Attachments"/><FieldRef Name="AttachmentFiles"/></ViewFields></View>';
  3. camlQuery.set_viewXml(query);
  4.  
  5. var items = list.getItems(camlQuery);
  6. ctx.load(items, 'Include(Title, AttachmentFiles)');

Furthermore, there is no problem, if you omit the ViewFields element entirely from the CAML query (in our case, we needed later other parts of the CAML query, like the Where element to build up the filters, but it is irrelevant now). That is actually the same query we had with the managed client object model sample at the beginning of this post.

  1. var list = web.get_lists().getByTitle("TitleOfYourList");
  2.  
  3. var camlQuery = new SP.CamlQuery();
  4.  
  5. var items = list.getItems(camlQuery);
  6. ctx.load(items, 'Include(Title, AttachmentFiles)');

Note: Using of the ViewFields element might have an effect on the performance of the query on the server side (especially if you have a lot of columns in your list and you need only a few of them) and so on the response time as well, but after the query has been completed on the server, it seems to be irrelevant regarding to the network bandwidth usage.

That is the full version of the code (to be exact, one of the working ones), including displaying the information (this time in the console only) returned by the server:

  1. var ctx = SP.ClientContext.get_current();
  2. var web = ctx.get_web();
  3.  
  4. var list = web.get_lists().getByTitle("TitleOfYourList");
  5.  
  6. var camlQuery = new SP.CamlQuery();
  7.  
  8. var items = list.getItems(camlQuery);
  9. ctx.load(items, 'Include(Title, Attachments, AttachmentFiles)');
  10.  
  11. ctx.executeQueryAsync(
  12.     function () {
  13.         var enumerator = items.getEnumerator();
  14.         while (enumerator.moveNext()) {
  15.             var item = enumerator.get_current();
  16.             console.log(item.get_item('Title'));
  17.             if (item.get_item('Attachments')) {
  18.                 var attEnumerator = item.get_attachmentFiles().getEnumerator();
  19.                 while (attEnumerator.moveNext()) {
  20.                     var attachment = attEnumerator.get_current();
  21.                     console.log(attachment.get_serverRelativeUrl()); // or get_fileName()
  22.                 }
  23.             }
  24.         }
  25.     },
  26.     function (sender, args) {
  27.         console.log('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
  28.     }
  29. );

That means, we fulfilled our goal by returning the items and their attachments in a single request/response via the JavaScript client object model.

Note, that after the response returned, we first check if there are any attachments for the item by reading the value of item.get_item(‘Attachments’), then accessing the attachments via the item.get_attachmentFiles() method. You could, however, check the existence of the attachments simply by using the get_attachmentFiles() method, without the Attachments field, like this:

var af = item.get_attachmentFiles();
if (af) {
    var attEnumerator = af.getEnumerator();

After extending the CAML query with the filters I need in the Where element, the query returned only the filtered items without any negative effect of the existing functionality, so that part of the task was completed either.

Lessons learned: If you need to read the attachments of your items, you must include the AttachmentFiles field in your Include statement. Including the Attachments field in the Include statement is optional, you need it only if you would like to use it explicitly (see the condition if (item.get_item(‘Attachments’)) in the last code snippet), otherwise you can omit it. If you would like to restrict the fields via the ViewFields element of your CAML query, you should include both of the Attachments and AttachmentFiles fields, but if you don’t restrict the columns by the usage of a ViewFields element, it works as well.

One final note: As so many times before, Fiddler was an invaluable tool to understand how the various versions work, or actually how they don’t work, by comparing the requests sent by the codes down to the wire.

Advertisements

March 23, 2018

How to check if a specific file exists in a folder structure of a SharePoint document library using the client object model

Filed under: CAML, Managed Client OM, SP 2013 — Tags: , , — Peter Holpar @ 22:25

Recently we had to create a utility function that makes it us possible to check if a file having a specific name exists anywhere within a folder structure of a SharePoint document library.

As long as you know not only the title of the document library, but its server relative URL as well, it requires only a single round-trip to the server:

  1. private bool FileExists(ClientContext clientContext, String docLibTitle, String fileName, String rootFolderServerRelativeUrl, String folderPath)
  2. {
  3.     var folderServerRelativeUrl = string.Format("{0}/{1}", rootFolderServerRelativeUrl, folderPath);
  4.     // or use a helper method to combine URL parts
  5.     //var folderServerRelativeUrl = JoinUrlParts(rootFolderServerRelativeUrl, folderPath);
  6.  
  7.     List DocumentsList = clientContext.Web.Lists.GetByTitle(docLibTitle);
  8.  
  9.     CamlQuery camlQuery = new CamlQuery();
  10.     camlQuery.ViewXml = @"<View Scope='Recursive'>
  11.                         <Query>
  12.                             <Where>
  13.                                 <Eq>
  14.                                     <FieldRef Name='FileLeafRef'></FieldRef>
  15.                                     <Value Type='Text'>" + fileName + @"</Value>
  16.                                 </Eq>
  17.                             </Where>
  18.                         </Query>
  19.                 </View>";
  20.     camlQuery.FolderServerRelativeUrl = folderServerRelativeUrl;
  21.     ListItemCollection listItems = DocumentsList.GetItems(camlQuery);
  22.     clientContext.Load(listItems);
  23.     clientContext.ExecuteQuery();
  24.  
  25.     return listItems.Count > 0;
  26. }

Usage:

  1. var webUrl = "http://YourSharePoint/site/subsite&quot;;
  2. string docLibTitle = "Documents";
  3. var rootFolderServerRelativeUrl = "/site/subsite/Shared Documents";
  4. var folderPath = "folder/subfolder";
  5.  
  6. ClientContext clientContext = new ClientContext(webUrl);
  7. var fileName = "document.docx";
  8.  
  9. bool fileFound = FileExists(clientContext, docLibTitle, fileName, rootFolderServerRelativeUrl, folderPath);

Note: If the folderServerRelativeUrl points to a location not within the document library (rootFolderServerRelativeUrl is wrong), the CAML query will ignore the FolderServerRelativeUrl and the entire library will be searched for a matching file. If  the folderPath part is wrong (not existing folder) then no matching item will be found, the query will return always false. Although the SPFolder server-side object model provides an Exists property to check if the folder at the given oath exists, there is no such property for the Folder object in the client object model. As a workaround, you can detect such mistakes by including these two lines of code in the FileExists method before invoking the ExecuteQuery method:

Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(folderServerRelativeUrl);
clientContext.Load(folder);

If either part of the folderServerRelativeUrl is wrong, a File not found exception will be thrown on calling the ExecuteQuery method.

The helper method mentioned in the code is useful if you would not like to bother with leading and trailing slashes in the URL and in the folder path:

  1. public static string JoinUrlParts(params string[] urlParts)
  2. {
  3.     return string.Join("/", urlParts.Where(up => !string.IsNullOrEmpty(up)).ToList().Select(up => up.Trim('/')).ToArray());            
  4. }

If you, however, know only the title of the document library but not its server relative URL you need two round-trips:

  1. private bool FileExists(ClientContext clientContext, String docLibTitle, String fileName, String folderPath)
  2. {
  3.     List docLib = clientContext.Web.Lists.GetByTitle(docLibTitle);
  4.     Folder rootFolder = docLib.RootFolder;
  5.     clientContext.Load(rootFolder, f => f.ServerRelativeUrl);
  6.     clientContext.ExecuteQuery();
  7.  
  8.     string rootFolderServerRelativeUrl = rootFolder.ServerRelativeUrl;
  9.     var folderServerRelativeUrl = string.Format("{0}/{1}", rootFolderServerRelativeUrl, folderPath);
  10.     // or use a helper method to combine URL parts
  11.     //var folderServerRelativeUrl = JoinUrlParts(rootFolderServerRelativeUrl, folderPath);
  12.  
  13.     Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(folderServerRelativeUrl);
  14.     clientContext.Load(folder);
  15.  
  16.     CamlQuery camlQuery = new CamlQuery();
  17.     camlQuery.ViewXml = @"<View Scope='Recursive'>
  18.                         <Query>
  19.                             <Where>
  20.                                 <Eq>
  21.                                     <FieldRef Name='FileLeafRef'></FieldRef>
  22.                                     <Value Type='Text'>" + fileName + @"</Value>
  23.                                 </Eq>
  24.                             </Where>
  25.                         </Query>
  26.                 </View>";
  27.     camlQuery.FolderServerRelativeUrl = folderServerRelativeUrl;
  28.     ListItemCollection listItems = docLib.GetItems(camlQuery);
  29.     clientContext.Load(listItems);
  30.     clientContext.ExecuteQuery();
  31.  
  32.     return listItems.Count > 0;
  33. }

Note: This version already includes the two-liner to check the existence of the folder path. If you don’t need that, remove it.

Usage of this version:

  1. var webUrl = "http://YourSharePoint/site/subsite&quot;;
  2. var docLibTitle = "Documents";
  3. var folderPath = "folder/subfolder";
  4. ClientContext clientContext = new ClientContext(webUrl);
  5. var fileName = "document.docx";
  6.  
  7. bool fileFound = FileExists(clientContext, docLibTitle, fileName, folderPath);

November 14, 2011

Creating custom validation rules in our list field iterators

Filed under: CAML, Custom forms, SP 2010, Validation — Tags: , , , — Peter Holpar @ 13:36

Last week we saw how to hide specific field controls or how to display them as read-only even on the edit form. In the current post I introduce a simple technique one can apply to create custom validation for SharePoint forms without InfoPath.

First, what are the main problems with the out of the box SharePoint form validation capability?

As written in the first post of this series about custom list field iterators, SharePoint 2010 made a large step to the right direction compared to its predecessor, WSS 3.0. One can define field level and form level validation rules using a syntax similar to the one used with calculated columns, based on values of other fields of the current item.

However, you can define only a single validation rule for your form having a single validation error message, and a single validation rule and error message for the fields. Furthermore, there is another serious limitation, namely one can validate only the basic field types (like text, date, numeric) and the same is true to the fields one can refer to in the validation rule.

It means, you are out of luck if you have to work with validation of lookup or user fields, managed metadata or even your custom field types.

In the validation rule you can refer to the fields of the current item, you cannot build validation rules based on field values of other, related items, custom user properties, not to mention accessing other systems, like CRM, 3rd party web services, etc.

Moreover, the validation error messages are static text, one can not built them up dynamically based on field values, for example.

I found that in real world applications customers need usually much more validation power.  All of these inflexibilities listed above lead me to create some kind of custom solution for validation. You can read another approach built on custom field types here, but in this post I illustrate the list field iterator based solution.

As you might know the BaseFieldControl class, the base class of all field controls implements the IValidator interface. In practice it means, you can use these field controls similar to standard ASP.NET validator controls.

But before working with them, we first have to get a reference to the field controls. So we extend our BaseListFieldIterator class – introduced in the previous part – with the GetFieldControlByName method. There might be multiple forms on the page, so just to be sure that the field control is within our list field iterator, we call the GetIteratorByFieldControl method and compare the ClientID values.

The SetValidationError method is to set the validation error for the specified field.

  1. protected BaseFieldControl GetFieldControlByName(String fieldNameToFind)
  2. {
  3.     foreach (Control control in _formContext.FieldControlCollection)
  4.     {
  5.         if (control is BaseFieldControl)
  6.         {
  7.             BaseFieldControl baseField = (BaseFieldControl)control;
  8.             String fieldName = baseField.FieldName;
  9.             if ((fieldName == fieldNameToFind) &&
  10.                 (GetIteratorByFieldControl(baseField).ClientID == ClientID))
  11.             {
  12.                 return baseField;
  13.             }
  14.         }
  15.     }
  16.     return null;
  17. }
  18.  
  19. protected Microsoft.SharePoint.WebControls.ListFieldIterator GetIteratorByFieldControl(BaseFieldControl fieldControl)
  20. {
  21.     return (Microsoft.SharePoint.WebControls.ListFieldIterator)fieldControl.Parent.Parent.Parent.Parent.Parent;
  22. }
  23.  
  24. protected void SetValidationError(String fieldName, String errorMessage)
  25. {
  26.     BaseFieldControl fieldControl = GetFieldControlByName(fieldName);
  27.     fieldControl.ErrorMessage = errorMessage;
  28.     fieldControl.IsValid = false;
  29. }

Next we have to add the following code to our custom ListFieldIterator class.

  1. protected override void OnLoad(EventArgs e)
  2. {
  3.     if (Page.IsPostBack)
  4.     {
  5.         Page.Validate();
  6.         this.Validate();
  7.     }
  8. }
  9.  
  10. public void Validate()
  11. {
  12.     if (base.ControlMode != SPControlMode.Display)
  13.     {
  14.         // here comes the validation logic
  15.     }
  16. }

We will extend the Validate method to fulfill our fictitious business needs.

The validation messages are stored as constants:

  1. public static class ValidationMessages
  2. {
  3.     public static readonly String TitleNotUnique = "Task title should be unique";
  4.     public static readonly String UserBusy = "This user already has another task for this time";
  5.     public static readonly String NoDateForAssignement = "A user can be assigned only if the start and due dates are specified";
  6.     public static readonly String PredecessorsNotCompleted = "At least one of the predecessors is not yet completed";
  7.     public static readonly String PredecessorSelfReference = "A task cannot be its own predecessor";
  8.     public static readonly String DueDateEarlierThanStartDate = "Due date should not be earlier than start date ";
  9.     public static readonly String CompletedTaskPercentage = "For a completed task the % complete should be 100";
  10. }

Despite of the constant values used in the sample, it is easy to create dynamic messages as well. For example, you can define a message like "Task title ‘{0}’ is not unique", then substitute the current value when setting the validation error.

The first rule is to allow only unique task titles.

  1. TextField titleField = GetFieldControlByName(SampleTaskListFields.Title) as TextField;
  2.  
  3. if (titleField != null)
  4. {
  5.     String title = titleField.Value as String;
  6.  
  7.     if (!String.IsNullOrEmpty(title))
  8.     {
  9.         if (ItemWithSameTitleExists(title))
  10.         {
  11.             SetValidationError(SampleTaskListFields.Title, ValidationMessages.TitleNotUnique);
  12.         }
  13.     }
  14. }

We check the uniqueness through a CAML query, and use the GetFieldRefs helper method to build the value of the ViewFields property of the SPQuery instance.

  1. protected bool ItemWithSameTitleExists(string title)
  2. {
  3.  
  4.     SPQuery query = new SPQuery();
  5.  
  6.     query.ViewFields = GetFieldRefs(SampleTaskListFields.Title);
  7.     // we should not check the ID in this case, as altering the title is not allowed
  8.     // title will be validated only on new item creation
  9.     // if title would be editable for existing items, then we should check
  10.     // whether the ID is not the one of the current (edited) item
  11.     // note, that CAML Eq for a Text field type is case insensitive
  12.     query.Query = String.Format("<Where><Eq><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Eq></Where>",
  13.         SampleTaskListFields.Title, title);
  14.  
  15.     bool result = List.GetItems(query).Count > 0;
  16.  
  17.     return result;
  18. }
  19.  
  20. private String GetFieldRefs(params String[] fieldNames)
  21. {
  22.     String fieldRefs = String.Concat(fieldNames.ToList().
  23.         ConvertAll(fieldName => String.Format("<FieldRef Name='{0}'/>", fieldName)));
  24.  
  25.     return fieldRefs;
  26. }

The next image shows the validator in action.

image

The comparison is case insensitive.

image

Yes, you are right it is nothing more than a custom implementation for the built-in Enforce unique values feature, but this one does not require indexed columns, and you have the chance to handle issues like leading / trailing spaces. For example, if you enable Enforce unique values, it won’t prohibit users entering ‘ Sample task 1  ‘ without any difficulties. If you don’t like this, you can trim the title parameter value and/or use Contains instead of Eq in the CAML query.

After this basic validation, let’s see a more tricky one. This one can be solved using standard field validation, although in this case it is more complicated “thanks” to the read-only Start date field when the task is not in the “Not Started” status.

  1. DateTimeField startDateField = GetFieldControlByName(SampleTaskListFields.StartDate) as DateTimeField;
  2. DateTimeField dueDateField = GetFieldControlByName(SampleTaskListFields.DueDate) as DateTimeField;
  3.  
  4. // if the task is not in 'Not Started' status, then we display the start date
  5. // as read-only and value would be null
  6. // in this case we should get the real value, stored in the item
  7. DateTime? startDateValue = (startDateField.ControlMode == SPControlMode.Display) ? (DateTime?)Item[SampleTaskListFields.StartDate] : startDateField.Value as DateTime?;
  8. DateTime? dueDateValue = dueDateField.Value as DateTime?;
  9.  
  10. if ((startDateValue.HasValue) && (dueDateValue.HasValue))
  11. {
  12.     if (dueDateValue.Value < startDateValue.Value)
  13.     {
  14.         SetValidationError(SampleTaskListFields.DueDate, ValidationMessages.DueDateEarlierThanStartDate);
  15.     }
  16. }

The illustration of the validation error:

image

This validation is again a simple one. We require 100% for % Complete when Status is Completed.

  1. DropDownChoiceField statusField = GetFieldControlByName(SampleTaskListFields.Status) as DropDownChoiceField;
  2. NumberField percComplField = GetFieldControlByName(SampleTaskListFields.PercentComplete) as NumberField;
  3.  
  4. bool checkForPredecessorStatus = false;
  5.  
  6. if ((statusField != null) && (percComplField != null))
  7. {
  8.     String statusFieldValue = statusField.Value as String;
  9.     Double? percComplValue = percComplField.Value as Double?;
  10.  
  11.     if (statusFieldValue == TaskStates.Completed)
  12.     {
  13.         checkForPredecessorStatus = true;
  14.         if ((!percComplValue.HasValue) || (percComplValue.Value != 1))
  15.         {
  16.             SetValidationError(SampleTaskListFields.PercentComplete, ValidationMessages.CompletedTaskPercentage);
  17.         }
  18.     }
  19. }

The screenshot of the validation:

image

Before you leave the post unread thinking this technique provides no additional value for validation, let’s see some more advanced example.

Using the standard Tasks list you can set an existing task as its own predecessor. That is not very nice, and in this case I found no simple OOB SharePoint tool to prohibit that.

Using this technique, you can achieve the result as shown below:

  1. MultipleLookupField predecessorsField = GetFieldControlByName(SampleTaskListFields.Predecessors) as MultipleLookupField;
  2.  
  3. if (predecessorsField != null)
  4. {
  5.     SPFieldLookupValueCollection predecessorsValue = predecessorsField.Value as SPFieldLookupValueCollection;
  6.  
  7.     if (predecessorsValue != null)
  8.     {
  9.         // a task can reference itself only in edit mode
  10.         if (ControlMode == SPControlMode.Edit)
  11.         {
  12.             if (!predecessorsValue.TrueForAll(predecessor => predecessor.LookupId != ItemId))
  13.             {
  14.                 SetValidationError(SampleTaskListFields.Predecessors, ValidationMessages.PredecessorSelfReference);
  15.             }
  16.         }
  17.         if (checkForPredecessorStatus)
  18.         {
  19.             if (!IsAllTasksCompleted(predecessorsValue.ConvertAll(predecessor => predecessor.LookupId)))
  20.             {
  21.                 SetValidationError(SampleTaskListFields.Status, ValidationMessages.PredecessorsNotCompleted);
  22.             }
  23.         }
  24.     }
  25. }

And in action:

image

The code above contains a check of predecessors when the task is to be set Completed. (The value of checkForPredecessorStatus computed earlier when validating % Complete.) We don’t want to allow that if there is at least on uncompleted predecessor. For the check we use the IsAllTasksCompleted method, passing the task ID of all of the predecessors as parameter.

  1. private bool IsAllTasksCompleted(List<int> taskIds)
  2. {
  3.     SPQuery query = new SPQuery();
  4.     query.ViewFields = GetFieldRefs(
  5.         SampleTaskListFields.Id,
  6.         SampleTaskListFields.Status);
  7.  
  8.     query.Query = String.Format("<Where><Neq><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Neq></Where>",
  9.                     SampleTaskListFields.Status, TaskStates.Completed);
  10.  
  11.     bool result = true;
  12.     
  13.     SPListItemCollection tasksNotCompleted = List.GetItems(query);
  14.  
  15.     foreach (SPListItem task in tasksNotCompleted)
  16.     {
  17.         if (taskIds.Contains((int)task[SampleTaskListFields.Id]))
  18.         {
  19.             result = false;
  20.             break;
  21.         }
  22.     }
  23.  
  24.     return result;
  25. }

The result of the validation is shown here:

image

Next, we want to allow to assign a user to the task if both start and due dates are set.

  1. UserField userField = GetFieldControlByName(SampleTaskListFields.AssignedTo) as UserField;
  2.  
  3. if (userField != null)
  4. {
  5.     String userFieldValue = userField.Value as String;
  6.  
  7.     if (!String.IsNullOrEmpty(userFieldValue))
  8.     {
  9.  
  10.         if ((startDateValue.HasValue) && (dueDateValue.HasValue))
  11.         {
  12.             if (startDateValue.Value <= dueDateValue.Value)
  13.             {
  14.                 SPFieldUserValue userValue = new SPFieldUserValue(Web, userFieldValue);
  15.                 int? taskId = (base.ControlMode == SPControlMode.Edit) ? ItemId : (int?)null;
  16.                 if ((userValue.LookupId != -1) && (UserIsBusy(userValue.LookupId, taskId, startDateValue.Value, dueDateValue.Value)))
  17.                 {
  18.                     SetValidationError(SampleTaskListFields.AssignedTo, ValidationMessages.UserBusy);
  19.                 }
  20.             }
  21.         }
  22.         else
  23.         {
  24.             SetValidationError(SampleTaskListFields.AssignedTo, ValidationMessages.NoDateForAssignement);
  25.         }
  26.     }
  27. }

On the screenshot below, I “forgot” to set the start date, that is not allowed in this case.

image

Finally, using the method described in this post, we allow to assign the user for the task, if (s)he has no other assignment for that time interval. In this case we should ignore the task being edited when doing the validation.

  1. protected bool UserIsBusy(int userId, int? taskId, DateTime startDate, DateTime dueDate)
  2. {
  3.  
  4.     SPQuery query = new SPQuery();
  5.     query.ViewFields = GetFieldRefs(
  6.         SampleTaskListFields.Id,
  7.         SampleTaskListFields.AssignedTo,
  8.         SampleTaskListFields.StartDate,
  9.         SampleTaskListFields.DueDate);
  10.  
  11.     // NOTE: you can add filter for Status Neq 'Completed' as well if you wish
  12.     // I have not included that for the sake of simplicity
  13.     if (taskId.HasValue)
  14.     {
  15.         // it is editing an existing task, so we should exclude the task itself
  16.         query.Query = String.Format("<Where><And><And><Neq><FieldRef Name='{0}'/><Value Type='Integer'>{1}</Value></Neq><Eq><FieldRef Name='{2}' LookupId='TRUE' /><Value Type='Lookup'>{3}</Value></Eq></And>{4}</And></Where>",
  17.                         SampleTaskListFields.Id, taskId.Value,
  18.                         SampleTaskListFields.AssignedTo, userId,
  19.                         BuildDateRangeOverlapFilter(startDate, dueDate));
  20.     }
  21.     else
  22.     {
  23.         // it is a new task, we don't have to check te task ID
  24.         query.Query = String.Format("<Where><And><Eq><FieldRef Name='{0}' LookupId='TRUE' /><Value Type='Lookup'>{1}</Value></Eq>{2}</And></Where>",
  25.                         SampleTaskListFields.AssignedTo, userId,
  26.                         BuildDateRangeOverlapFilter(startDate, dueDate));
  27.     }
  28.  
  29.     bool result = List.GetItems(query).Count > 0;
  30.  
  31.     return result;
  32. }
  33.  
  34. protected String BuildDateRangeOverlapFilter(DateTime startDate, DateTime endDate)
  35. {
  36.  
  37.     StringBuilder sb = new StringBuilder();
  38.     sb.Append(String.Format("<And>{0}{1}</And>",
  39.         BuildSimpleDateFilter(SampleTaskListFields.StartDate, endDate, "Leq"),
  40.         BuildSimpleDateFilter(SampleTaskListFields.DueDate, startDate, "Geq")));
  41.  
  42.     return sb.ToString();
  43.  
  44. }
  45.  
  46. protected String BuildSimpleDateFilter(String dateFieldName, DateTime filterDate, String relation)
  47. {
  48.     String datePattern = "yyyy-MM-ddT00:00:00Z";
  49.  
  50.     return String.Format("<{0}><FieldRef Name='{1}'/><Value Type='DateTime'>{2}</Value></{0}>", relation, dateFieldName, filterDate.ToString(datePattern));
  51.  
  52. }

And that is the outcome of the validation in this case:

image

Note: Other validation possibilities for Person or Group field type are to check the number of users set (you can allow a single user or multiple ones using the standard tools, but you can not expect exactly three of them), or to check if the specified users are from a specific Active Directory group.

Although the technique illustrated in this post require coding and not so straightforward as specifying a simple formula on the UI, I hope you can use it  effectively if the customer requirements demand something more sophisticated.

You can download the sample application from here.

November 11, 2011

How to find overlapping items for a date range

Filed under: CAML, SP 2010 — Tags: , — Peter Holpar @ 13:33

It is a common requirement to find list items (like tasks) that have an overlapping duration with a specified data range.

Several years ago I wrote a post about the DateRangesOverlap CAML element and about its strange behavior returning items outside of the specified month range. As I suggested there, we can (and should) create the right query using basic CAML elements.

It is easy to create a complex CAML query to filter items for overlapping date ranges, however, there is a way to create a simple query as well. The simplest way and the best description in the topic is the one I found on stackoverflow. It says, the date ranges are overlapping when it is true for both of them, that their start date is no later than the end date of the other date range. That is very clear if you think about that just a bit.

Based on that concept I’ve created two helper methods:

  1. protected String BuildDateRangeOverlapFilter(DateTime startDate, DateTime endDate)
  2. {
  3.     StringBuilder sb = new StringBuilder();
  4.     sb.Append(String.Format("<Where><And>{0}{1}</And></Where>",
  5.         BuildSimpleDateFilter("StartDate", endDate, "Leq"),
  6.         BuildSimpleDateFilter("DueDate", startDate, "Geq")));
  7.  
  8.     return sb.ToString();
  9. }
  10.  
  11.  
  12. protected String BuildSimpleDateFilter(String dateFieldName, DateTime filterDate, String relation)
  13. {
  14.     String filter = String.Format("<{0}><FieldRef Name='{1}'/><Value Type='DateTime'>{2}</Value></{0}>",
  15.         relation, dateFieldName, SPUtility.CreateISO8601DateTimeFromSystemDateTime(filterDate));
  16.  
  17.     return filter;
  18. }

And here is an example about the usage:

  1. DateTime startDate = DateTime.Today.AddDays(11);
  2. DateTime endDate = DateTime.Today.AddDays(15);
  3.  
  4. SPList taskList = web.Lists["Tasks"];
  5.  
  6. SPQuery query = new SPQuery();
  7. query.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='StartDate'/><FieldRef Name='DueDate'/>";
  8. query.Query = BuildDateRangeOverlapFilter(startDate, endDate);
  9.  
  10. SPListItemCollection matches = taskList.GetItems(query);
  11.  
  12. foreach (SPListItem match in matches)
  13. {
  14.     Console.WriteLine(match["Title"]);
  15. }

Note 1: The start date of the date ranges must be less than or equal to the end date, but I think that is an acceptable restriction.

Note 2: Before you ask it, I’ve not tested this solution with recurring events, but I assume it does not work in that case. Sorry!

Note 3: To convert my date stored as DateTime to String I used earlier the DateTime.ToString(String format) method with format pattern "yyyy-MM-ddT00:00:00Z", later I found that more-or-less matches the UniversalSortableDateTimePattern, so switched to parameter “u”. If you check the source code of the SPUtility.CreateISO8601DateTimeFromSystemDateTime method used in this sample (for example with Reflector), you can see that instead of simply calling DateTime.ToString with the right pattern, it takes the individual parts of the DateTime (like year, month, day, etc.), and creates the result by appending these component to each other using a StringBuilder. At first it was quite strange for me, but then thought, it must be the fastest way of conversion, as it does not have the overhead of pattern recognition and lot of conditions found in DateTime.ToString. However, if you should work with time zone information, then it might be not ideal for you. Although ISO 8601 supports time zones, AFAIS it is not implemented in CreateISO8601DateTimeFromSystemDateTime, for example, by providing an overload with a DateTimeOffset parameter. I don’t know if CAML supports at all this kind of time zone information provided in the date string. I admit it is not a crucial question most of the cases, however it might produce a surprise if not taken into account. You can read a bit more about that here.

October 5, 2011

Creating a view that filters list items based on user permissions using CAML

Filed under: Bugs, CAML, SP 2010, Views — Tags: , , , — Peter Holpar @ 23:17

Recently I got a task that was about creating a SharePoint view that displays only list items that the logged-in user has editor permissions for. Sounds a bit tough at first, but if you have ever heard about the PermMask field (not very well documented, but you can read more about that here and here) then it gives you some hope.

So I’ve created a test list (called SecTest) and created two items. One of my test users has admin rights, the second one has editor permission for the first item and read permission for the second one. The list inherits the permissions of the parent site, the permission inheritance is broken for the second item.

First I’ve tried to use SharePoint designer to customize the existing All Items view of the list.

Added the PermMask to the FieldRefs:

<FieldRef Name="PermMask" />

And created the filter part for the Query:

<Where>
    <Eq>
        <FieldRef Name=’PermMask’ />
        <Value Type=’Computed’>somevalue</Value>
    </Eq>
</Where>

(I’ve tried different permission mask values, all with the same result.)

Saving the view in SPD resulted the following warning:

image

Pressing the Details I’ve got the following message:

soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.0x80131904

image

(I’ve tried to use Text instead of Computed as the Type attribute of the Value in the Where section of the CAML expression, but it did not help.)

Checking the view in the browser produced an error as well.

I’ve found the following related error message in the SharePoint log (one for the saving in SPD and another one for opening the view in IE):

System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value ‘0xb008431061’ to data type int.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlDataReader.HasMoreRows()     at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)     at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)     at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadl…    
…ock)    
Unable to execute query: Error 0x80131904    
Unknown SPRequest error occurred. More information: 0x80131904    
<nativehr>0x80131904</nativehr><nativestack></nativestack>    
<nativehr>0x80131904</nativehr><nativestack></nativestack>    

FYI, the hexadecimal value causing the conversion error (see the bolded section above, 0xb008431061 = 756052856929 in decimal) corresponds the permission mask of the Read permission level (see the value of the PermMaskfield in the Roles table of the content database, where in the record where Title is Read, that is SELECT PermMask FROM [YourContentDB].[dbo].[Roles] WHERE Title = ‘Read’)

Next, I’ve tried the same from code using a test console application:

  1. SPList list = web.Lists["SecTest"];
  2. SPQuery query = new SPQuery();
  3. // tried with and without these lines
  4. //query.IncludeAllUserPermissions = true;
  5. //query.IncludePermissions = true;
  6. query.Query = "<Where><Eq><FieldRef Name='PermMask' /><Value Type='Computed'>0xb008431061</Value></Eq></Where>";
  7. query.ViewFields = "<ViewFields><FieldRef Name='PermMask' /></ViewFields>";
  8.  
  9. SPListItemCollection items = list.GetItems(query);
  10.  
  11. // exception is thrown at the next line
  12. foreach (SPItem item in items)
  13. {
  14.     Console.WriteLine(item["PermMask"]);
  15. }

The code produced the same error.

I’ve started SQL Server Profiler to create a trace of the SQL side during the code execution. The trace captured the following errors:

Exception
Error: 245, Severity: 16, State: 1
User Error Message
Conversion failed when converting the nvarchar value ‘0xb008431061’ to data type int.

The source of the error was the following parameterized query:

exec sp_executesql N’DECLARE @DocParentIdForRF uniqueidentifier SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId = @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @FLN;   SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (INDEX=Perms_Url) WHERE SiteId=@SITEID AND DelTransId = 0x AND ScopeId IN (SELECT tp_ScopeId FROM Lists WITH (NOLOCK) WHERE tp_WebId=@L7 AND tp_ID = @L2 UNION SELECT t1.ScopeId FROM UserData INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[DoclibRowId]) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.SiteId=UserData.tp_SiteId) AND (t1.SiteId = @SITEID) AND (t1.ParentId = UserData.tp_ParentId) AND (t1.Id = UserData.tp_DocId) AND (UserData.tp_ListId = @L2) WHERE ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND (UserData.tp_RowOrdinal=0) AND ((UserData.[tp_ID] = N”0xb008431061”) AND t1.SiteId=@SITEID AND (t1.ParentId=@DocParentIdForRF))) OPTION (MAXDOP 1)’,N’@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@FDN nvarchar(4000),@FLN nvarchar(4000),@LISTID uniqueidentifier,@RequestGuid uniqueidentifier,@L7 uniqueidentifier’,@LFFP=’00000000-0000-0000-0000-000000000000′,@SITEID=’D2F4F094-9626-4B84-87D3-260B6B9213EF’,@L2=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@FDN=N’Lists’,@FLN=N’SecTest’,@LISTID=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@RequestGuid=’E75EC6C1-5CDE-4368-B55C-A9AAA661E539′,@L7=’3ED0DC82-1AC6-4A29-A177-56DA059B2EF8′

I’ve found that removing the bolded condition above helps to run the query without any exception.

In my case the result was (or would be?)  the next one:

image

The ScopeId field identifies the entity the permission is set on. See the Perms table in the content DB to decode it. In my case the first row is related to the root web, the second on is to the second item in the list, for which we set individual permissions. The Acl field stores the access control list for the entity in a binary format.

It seems that if you include the PermMask field in the CAML query, SharePoint tries to read up the related permission settings but fails due to a bug.

I’ve tried to locate the source of the issue, but was not fully successful. From the stack trace it seems that the ExecuteQueryInternal method of the internal SPSqlClient class (Microsoft.SharePoint namespace, Microsoft.SharePoint assembly) is called to run the SQL query, this method calls the ExecuteReader method of the Microsoft.SharePoint.Utilities.SqlSession, but in the ReadInternal method of System.Data.SqlClient.SqlDataReader the exception is thrown. However I’ve not found the method where the query is incorrectly assembled. I assume it is related to the SetCommand method of the SPSqlClient class but I’ve not yet found the caller of the method. The signature of the method (IntPtr queryText, uint lenQueryText, bool isStoredProc) and the Marshal.PtrToStringUni call within the method suggest that the caller might be even an unmanaged code. See the similar SetCommand method of the SPSqlClientClass class (Microsoft.SharePoint.Library namespace, Microsoft.SharePoint.Library assembly):

[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType=MethodCodeType.Runtime)]
public virtual extern void SetCommand([In] IntPtr pstrQueryText, [In] uint cchQueryText, [In] bool bStoredProcedureSyntax);

Very likely that I have to find an alternative approach to create my permission based view. I have some further ideas, but more about them in a possible post later.

February 9, 2011

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Important things to note:

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

May 14, 2010

Declaratively adding a lookup field to a list schema using the name of the referenced list

Filed under: CAML, SharePoint — Tags: , — Peter Holpar @ 01:09

This topic is really not a new one. I read already debates several times about if it is possible or not, see for example this post in Josh Gaffey’s blog:

Add SharePoint lookup column declaratively through CAML XML

or this post from Chris O’Brien’s blog:

Creating lookup columns as a feature

There are a lot of comments pro and cons in these posts.

Since I’ve got this question again on the MSDN forum (see create lookup column in list template without guid of parent list), I decided to check the issue myself.

In the past I deployed lists with lookup fields using the name of the referenced list formerly using a custom deployment tool, now I had to create a declarative feature using CAML definitions.

In my test I was to create a lookup field to the Title field of the standard Tasks list.

Based on the guide of Josh in the above post I first try to create a lookup site column.

To achieve this, I’ve created a simple feature that included the following fields.xml file:

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  3.     <Field ID="{95d89725-eb97-428b-bc79-ee02ca8b724c}"
  4.         Name="TestField"
  5.         SourceID="http://schemas.microsoft.com/sharepoint/v3"
  6.         StaticName="TestField"
  7.         Group="Test site columns"
  8.         Type="Lookup"
  9.         DisplayName="Test field"
  10.         List="Lists/Tasks"
  11.         FieldRef="ID"
  12.         ShowField="Title" />
  13. </Elements>

The feature.xml file looks like this:

  1. <?xml version="1.0" encoding="utf-8"?>
  2.   <FeatureId="2AABD552-10B1-4561-8A19-2311D39C9A2E"
  3.           Title="List name based lookup field demo feature (site column)"
  4.           Description="The sole purpose of this feature is to demonstrate how to create list name based lookup field in WSS 3.0 using a site column"
  5.           Version="1.0.0.0"
  6.           Hidden="FALSE"
  7.           Scope="Site"
  8.           xmlns="http://schemas.microsoft.com/sharepoint/">
  9.   <ElementManifests>
  10.     <ElementManifest Location="fields.xml"/>
  11.   </ElementManifests>
  12. </Feature>

Activating the feature resulted in the following in the site columns list:

image

At first sight that was OK, but after checking the details it turned out to be not perfect:

image

As you can see on the image above, the place of the parent list was empty.

Checking the field from code showed that the LookupList property of the site column was “Lists/Tasks”, so it was not resolved to the list GUID on feature activation.

When I tried to add the site column to a list, I’ve received the following exception:

Exception from HRESULT: 0x80040E07   at Microsoft.SharePoint.Library.SPRequestInternalClass.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)

  at Microsoft.SharePoint.Library.SPRequest.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)

I’ve also tried to create a custom list definition in the same feature, but when I tried to create a new instance based on that definition, I received the following exception:

Cannot complete this action.
Please try again.   at Microsoft.SharePoint.Library.SPRequestInternalClass.CreateListFromFormPost(String bstrUrl, String& pbstrGuid, String& pbstrNextUrl)
   at Microsoft.SharePoint.Library.SPRequest.CreateListFromFormPost(String bstrUrl, String& pbstrGuid, String& pbstrNextUrl)

Although some comments suggested to remove the ShowField attribute to get it work, in my experience it did not help. It only made the case even worse, as now the column information was also empty (as one could expect logically):

image

So I have to find an alternative way. I decided to try it with a custom list definition that contains the field definition itself.

If you are new to creating custom list definitions, you can read more about that here:

How to: Create a Custom List Definition

I’ve created a feature that contains the custom list definition. The main modifications of the simple custom list schema are the followings:

  1. <ContentTypes>
  2.     <ContentTypeID="0x01"
  3.     Name="Item"
  4.     Group="Item group"
  5.     Description="Item description"
  6.     Version="0">
  7.     <FieldRefs>
  8.       <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" Required="TRUE" ShowInNewForm="TRUE" ShowInEditForm="TRUE"/>
  9.       <!– Title –>
  10.       <FieldRef ID="{95d89725-eb97-428b-bc79-ee02ca8b7225}" Name="TestField" Required="FALSE" ShowInNewForm="TRUE" ShowInEditForm="TRUE"/>
  11.       <!– Test field –>
  12.     </FieldRefs>
  13.     <XmlDocuments>
  14.       <XmlDocument NamespaceURI="http://schemas.microsoft.com/sharepoint/v3/contenttype/forms">
  15.         <FormTemplates xmlns="http://schemas.microsoft.com/sharepoint/v3/contenttype/forms">
  16.           <Display>ListForm</Display>
  17.           <Edit>ListForm</Edit>
  18.           <New>ListForm</New>
  19.         </FormTemplates>
  20.       </XmlDocument>
  21.     </XmlDocuments>
  22.   </ContentType>
  23.   <ContentTypeRef ID="0x0120" />
  24. </ContentTypes>
  25. <Fields>
  26.   <Field Type="Lookup"
  27.          DisplayName="Test field"
  28.          Required="FALSE"
  29.          List="Lists/Tasks"
  30.          ShowField="Title"
  31.          UnlimitedLengthInDocumentLibrary="FALSE"
  32.          ID="{95d89725-eb97-428b-bc79-ee02ca8b7225}"
  33.          SourceID="http://schemas.microsoft.com/sharepoint/v3"
  34.          StaticName="TestField"
  35.          Name="TestField" />
  36. </Fields>

and a reference for the lookup field was added to the ViewFields:

  1. <ViewFields>
  2.   <FieldRef Name="Attachments">
  3.   </FieldRef>
  4.   <FieldRef Name="LinkTitle">
  5.   </FieldRef>
  6.   <FieldRef Name="TestField">
  7.   </FieldRef>
  8. </ViewFields>

Next, I’ve installed and activated the feature:

stsadm -o installfeature -name ListNameBasedLookUp
stsadm -o activatefeature -name ListNameBasedLookUp -url http://yoursite

The custom list definition appeared on the available list types:

image

I’ve created a new instance called LookUpList1:

image

The new list instance contained the lookup field:

image

And the definition of the field was correct. It pointed to the right list (Tasks) and to the right field (Title):

image

The field was added to the default view as expected:

image

In the meantime I’ve created to item in the Tasks list:

image

When I created a new item, the values of the lookup field came from the specified source list and field:

image

And last but not least, the item is saved correctly to the list:

image

So my experience matches to Scott’s comment in Josh’s post:

“To make this method work, the field definition must exist in a list definition since the wiring up to the GUID happens when you the provision the list, rather than when you add a column to an existing content type/list.”

At the end some note:

If you want to alter the feature XML files and would like to avoid caching issues, I suggest you to alter the feature and field GUIDs each time you deploy (deactivate/uninstall/alter/install/activate/IISRESET) the feature. Ignoring this hint may cause you a lot of headache. Believe me, I tell you that from my own experience!

Another side note, that the experience may depend on the version number of SharePoint in my environment I tested it with the December 2009 Cumulative Update (12.0.0.6524).

You can find the full sample feature here.

December 5, 2009

Creating view to display list items created in the current month

Filed under: CAML, SharePoint — Tags: , — Peter Holpar @ 00:50

The task sounds rather simple at first: we should create a view that filters the items and displays only the ones that were created in the current month.

Unfortunately, the user interface gives only a very limited support to create views, so this task could be only implemented by creating two computed columns, first one for the first day of the month the item was created, and the second one for the last day. Then we could use these columns to compare with the current date (Today), when creating the view. I don’t like this solution, especially because of the need for the computed columns.

Instead of this, it is worth to create the view from code, for example, by cloning an existing view (like „All Items”), then by setting the Query property of the SPView class. Here we need a CAML query that makes it possible to filter for date intervals. That is exactly what DateRangesOverlap does. Since in this case the beginning and end dates are the same value, the value of the Created field, it must be included twice in the query.

Let’s see the final result:

SPView view = list.Views["All Items"].Clone("Current Month", 0, false, 
false);
view.Query = @"<Where><DateRangesOverlap><FieldRef
Name='Created' /><FieldRef Name='Created' /><Value
Type='DateTime'><Month
/></Value></DateRangesOverlap></Where>"
;
view.Update();

Nothing is perfect. DateRangesOverlap has a little issue when filtering for a given month. You can read about that in my former post: Using Month element in DateRangesOverlap can return items not in the specified month.

Getting the list of new items from code

Filed under: CAML, SharePoint — Tags: , — Peter Holpar @ 00:26

You have probably already noticed the little ‘NEW’ icon next to the items recently created in SharePoint. This icon appears by default next to the items not older than two days, but you can configure the time interval if you wish. This can be done by altering the days-to-show-new-icon parameter for the web application:

stsadm.exe -o setproperty -pn days-to-show-new-icon -pv 0 -url [Your Virtual Server’s URL]

This example sets the value to zero, that means the icon does not appear at all beside the items, even when they are just created.

Sometimes it may be useful to get the list of these kind of „latest items” from code. The key of this job is to know how to get the above-mentioned parameter from code, all the others are simple CAML query. So let’s see, how to do that!

First, we have to get a reference for the web application. If it is the application of the current web, it is so straightforward:

SPWebApplication webApp = web.Site.WebApplication;

Otherwise, it is a bit more difficult, you should know the name of the web application:

SPWebApplication webApp = 
SPWebService.ContentService.WebApplications["WebAppName"];

From the web application it is rather easy to get the value of the parameter:

int days = webApp.DaysToShowNewIndicator;

That is the number of days. Based on that there are two approaches to get the items created in this interval. In the first one, we get the exact time, that will be used for comparison, then build the CAML query using that exact value:

DateTime fromDate = DateTime.Now.AddDays(-days);
String fromDateString =
SPUtility.CreateISO8601DateTimeFromSystemDateTime(fromDate);
String query =
String.Format("<Where><Gt><FieldRef Name='Created'
IncludeTimeValue='TRUE' /><Value
Type='DateTime'>{0}</Value></Gt></Where>"
, fromDateString);

The second approach is even more simple. In this case we use the number of days as an offset in the query:


String query = String.Format("<Where><Gt><FieldRef
Name='Created' IncludeTimeValue='TRUE' /><Value
Type='DateTime'><Today OffsetDays='{0}'
/></Value></Gt></Where>"
, -days);

Note, that since the item creation happened in the past, we should use negative values in both cases.

Older Posts »

Blog at WordPress.com.