Get Aggregate Value of a SharePoint Field using CSOM [Totals function]

This post is about directly applying aggregations on a SharePoint List, without specifying any grouping condition. If you want to group SharePoint List data before applying aggregation then, refer to this post, SharePoint Group By.

I must admit that this turned out to be little tricky than what I had imagined. The situation was fairly simple. I had a SharePoint List. I had applied couple of Totals functions, Standard Deviation & Variance, on one of its Number field in two separate custom views. I just wanted to retrieve those values from client side. That’s it! My initial assessment was there must be some function/property that I can easily query from the client. As it seemed initially, there’s no direct way of doing that. Fine, just another day at the office for a SharePoint developer ūüėČ

Read More »

SPList.Items.Add() v/s SPList.AddItem()

The result of both the methods are same. They both add a new SPListItem to a SharePoint List (SPList) and returns back the newly created item. The difference lies in the process of creating the new items. SPList.Items.Add() explicitly adds a new item to the SharePoint List whereas SPList.AddItem() adds it implicitly.

In order to add a new item, it is required to get the current Lists schema. It is done by calling GetItems. This call takes place internally, so we don’t have any control over it. However, this is where the said two functions differs as they both execute different queries to get their respective ListItems.

SPList.Items.Add()

In the first case,

var newItem = list.Items.Add();

ListItemCollection is initialized using the following query before creating a new item:

new SPQuery()
{
    ViewAttributes = "Scope=\"Recursive\""
});

SPList.AddItem() 

In this case,

var newItem = list.AddItem();

ListItemCollection is initialized using the following query:

new SPQuery()
{
    Query = "-1",
    ViewFields = ""
};

We can clearly see that this is a fake query as it is querying for a ListItem whose ID is -1! It means that the ListItemCollection will always be zero while adding a new item.


This difference gets reflected in the execution time for these queries. Expectedly, the second query, SPList.AddItem(), is much more faster especially while dealing with a List with large number of items.

Delete a ListItem from SharePoint ListView WebPart [XSLT]

The objective here is to delete a single or multiple ListItems from a single SharePoint List using a ListView WebPart i.e., to delete a ListItem from client side. In order to achieve this, we will be making an ajax call to the SharePoint WebService, Lists.asmx. SharePoint provides a number of WebServices with various functionalities. One of them is Lists.asmx. This service has many apis related with SharePoint List. We’re gonna call UpdateListItems api with the Delete command to delete a single or multiple ListItems accordingly. The url of this service is kept relative as, ../_vti_bin/lists.asmx.Following is the schema of this api.

POST /_vti_bin/Lists.asmx HTTP/1.1
Host: win-hk4iec2ge2r
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

<?xml version="1.0" encoding="utf-8"?>
 <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
 <UpdateListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
 <listName>string</listName>
 <updates>string</updates>
 </UpdateListItems>
 </soap:Body>
</soap:Envelope>

The approach is going to be pretty simple. We’ll be editing our custom XSLT as a normal HTML file with an exception of encoding the HTML schema of the api as we can not use any html tag inside an xml declaration. First we’ll define our HTML structure in the XSLT. Here, we will generate an HTML checkBox against the Title of all the items displayed. The IDs of these checkBoxes are the SharePoint ID of the corresponding ListItem they represent. This will help us to get the IDs of the ListItem that User wants to delete.



<table style="width:100%; padding-left: 0px">

<tr>

<td colspan="2" style="text-align: left; width: 40%"> 
 <input id="chkSelectAll" type="checkbox" name="checkbox" onclick="OnChangeCheckbox(this)" value="SelectAll" />
 <label id="SelectAll">Select All</label>
 </td>


<td style="text-align: left;">
 <button type="button" onclick="DeleteItems()" class="buttonv">Delete</button>
 </td>

 </tr>

</table>

Next declare the checkbox where the code for, for-each rows are written. As mentioned, the checkBoxes will appear against the title for each item in the view.


<div>


 <input type="checkbox" name="checkbox" value="{@ID}" id="{@ID}" onclick="ClearParent(this)"/>
 <a class="list_link" title="{@ListId_x003a_Title}" href="viewList.aspx?ListId={@ListId_x003a_ID}">
 <xsl:value-of select="@ListId_x003a_Title" disable-output-escaping="yes" />
 </a>
 
 <span class="submittedby"></span>
 
 <span class="summary">
 <xsl:value-of select="@ListId_x003a_ShortSummary" disable-output-escaping="yes" />
 </span>
 

</div>

Now all the JavaScript required to complete this operation.

        
        //This function gets the ID of all the ListItems that the user has selected and mark them for Deletion.
        function GetItemsToBeRemoved(){
          var counter = 0;
          var checkedIds = new Array();
          var cbs = document.getElementsByTagName('input');
          for(var i=0; i < cbs.length; i++) {               if(cbs[i].type == 'checkbox') {                   if(cbs[i].value != "SelectAll"){                       if(cbs[i].checked){                         checkedIds[counter++] = cbs[i].id;                       }                   }               }           }           return checkedIds;         }         //This is the function which is called on the Delete Button Click event. Only "" without encoding.         function DeleteItems(){           var checkedIds = GetItemsToBeRemoved();           if(checkedIds.length > 0){
            GenerateRequest(checkedIds);
          }
          else{
            alert('Plz select atleast 1 item to delete.');
          }
        }
        
        //Create and send the SOAP request. Again HTML tags are encoded. Since we'll be generating a Batch request for deletion, we have to generate this request in a for-loop.
        function GenerateRequest(checkedIds){

            var soapEnv = "<?xml version='1.0' encoding='utf-8'?><soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><UpdateListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>SpListName</listName><updates><Batch>";
            var methodEnv1 = "<Method ID='1' Cmd='Delete'><Field Name='ID'>";
            var methodEnv2 = "</Field></Method>";
            var soapEnv2 = "</Batch></updates></UpdateListItems></soap:Body></soap:Envelope>";

 
            //Foreach selected item, create a delete request. 
            for (var i = 0; i < checkedIds.length; i++){
                soapEnv+=methodEnv1 + checkedIds[i] + methodEnv2;
            };

            soapEnv+=soapEnv2;

            //Finally send a single batch request for multiple items. 
            $.ajax({
                    url: "../_vti_bin/lists.asmx",
                    beforeSend: function(xhr) { xhr.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems");
                },
                type: "POST",
                dataType: "xml",
                data: soapEnv,
                complete: CheckForErrors,
                contentType: "text/xml; charset=\"utf-8\""
            });
        }

        //This function checks if any error has been encountered or not. If no error is found, then it re-Loads the current page to refresh the View.
        function CheckForErrors(xData, status) {

            //alert("Update Status : " + status );
            //alert("Update Status : " + xData.responseText );

            if(status == "success"){
                location.reload(true);
            }

        }

        //helper functions
        
        //Disable the delete button if there are no items to be displayed i.e., the SharePoint List is empty. Call this function on document.ready.
        function ValidateNoRecords(){
            var isDataFound = false;
            var cbs = document.getElementsByTagName('input');
            for(var i=0; i < cbs.length; i++) {
                if(cbs[i].type == 'checkbox') {
                    if(cbs[i].value != "SelectAll"){
                        isDataFound = true;
                        break;
                    }
                }
            }

            if(!isDataFound){
                var id = "#chkSelectAll";
                $(id).closest('tr').hide()
            }

        }

        //Checks whether to display the Delete button or not.
        $(document).ready(function(){
            ValidateNoRecords();
        })

***

Create a SharePoint Timer Job

Create a Blank SharePoint project in Visual Studio.Enter your site url and validate.

Add a new class say, TestTimer.

All timers must inherit from the class, Microsoft.SharePoint.Administration.SPJobDefinition. Following is the code sample for our TestTimer class.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Administration;

namespace SharePointTimerSample
{
    class TestTimer : SPJobDefinition
    {
        public TestTimer()
            : base()
        {
        }

        public TestTimer(string jobName, SPService service, SPServer server, SPJobLockType targetType)
            : base(jobName, service, server, targetType)
        {
        }

        public TestTimer(string jobName, SPWebApplication webApplication)
            : base(jobName, webApplication, null, SPJobLockType.Job)
        {
            this.Title = "My Test Timer Job";
        }

        protected override bool HasAdditionalUpdateAccess()
        {
            return true;
        }

        public override void Execute(Guid contentDbId)
        {
            //Write your main code here
        }
    }
}

The main functionality of our Timer job has to be laid down in the “Execute” method. It is this method, which will get called periodically by the timer after the completion of its given time interval.

Now, add a new Feature to your project.

Set the scope of the timer as per your requirement. Here, we’re going to set its scope to the site level. You can also set the name of your timer job here. Plz note that this is the name that will be displayed under the SiteCollection features option and is not the name of your wsp. The wsp’s name is going to be the name of your project.

Add a EventReceiver to our newly created Feature.

It is at the EventReceiver, we define the activities that needs to be performed while activating or deactivating the timer job. Even the time interval is also defined here.


using System;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Permissions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Security;

namespace SharePointTimerSample.Features.Feature1
{
    /// 
    /// This class handles events raised during feature activation, deactivation, installation, uninstallation, and upgrade.
    /// 
    /// 
    /// The GUID attached to this class may be used during packaging and should not be modified.
    /// 

    [Guid("e61e62e9-5b30-4c8b-ad03-402056f835e3")]
    public class Feature1EventReceiver : SPFeatureReceiver
    {
        // Uncomment the method below to handle the event raised after a feature has been activated.

        public override void FeatureActivated(SPFeatureReceiverProperties properties)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                SPSite site = properties.Feature.Parent as SPSite;
                int userid = site.RootWeb.CurrentUser.ID;

                // make sure the job isn't already registered  
                site.WebApplication.JobDefinitions.Where(t => t.Name.Equals("List_JOB_NAME")).ToList().ForEach(j => j.Delete());

                // install the job  
                TestTimer listLoggerJob = new TestTimer("List_JOB_NAME", site.WebApplication);

                //SPDailySchedule schedule = new SPDailySchedule();
                //schedule.BeginHour = 17;
                //schedule.BeginMinute = 0;
                //schedule.BeginSecond = 0;
                //schedule.EndSecond = 59;
                //schedule.EndMinute = 59;
                //schedule.EndHour = 0;
 
                SPMinuteSchedule schedule = new SPMinuteSchedule();
                schedule.BeginSecond = 0;
                schedule.EndSecond = 59;
                schedule.Interval = 5;
                
                listLoggerJob.Schedule = schedule;
                listLoggerJob.Update();
            });
        }


        // Uncomment the method below to handle the event raised before a feature is deactivated.

        public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                SPSite site = properties.Feature.Parent as SPSite;

                // delete the job  
                site.WebApplication.JobDefinitions.Where(t => t.Name.Equals("List_JOB_NAME")).ToList().ForEach(j => j.Delete());
            });
        }


        // Uncomment the method below to handle the event raised after a feature has been installed.

        //public override void FeatureInstalled(SPFeatureReceiverProperties properties)
        //{
        //}


        // Uncomment the method below to handle the event raised before a feature is uninstalled.

        //public override void FeatureUninstalling(SPFeatureReceiverProperties properties)
        //{
        //}

        // Uncomment the method below to handle the event raised when a feature is upgrading.

        //public override void FeatureUpgrading(SPFeatureReceiverProperties properties, string upgradeActionName, System.Collections.Generic.IDictionary parameters)
        //{
        //}
    }
}

Finally, select your Feature in the Solution Explorer, and change, “Activate On Default” to False and “Always Force Install” to True.

That’s it. Next, deploy your solution and activate the feature from the “Site Collection features” option of your site. [As we set the scope to Site level.]

Get Available PowerShell Commands for SharePoint on Client-Side

Needless to say, that on Client Side, we get a limited scope of running various SharePoint commands. As a result there aren’t much cmdlets for the Client Side. So how do you know what are the available commands that might come handy while executing cmdlets remotely? Simply run the following command in PowerShell or SharePoint Online Management Shell.
get-command | Where-Object { $_.ModuleName -eq "Microsoft.Online.SharePoint.PowerShell" }

A sample list of available cmdlets are displayed in the following screenShot.

Share a SharePoint File with different Office365 users using Client Object Model C#

In my previous post, Get a list of all the Shared users for a ListItem/Document of SharePoint List, I had described how to get the list of all the SharePoint users with whom a given Document Library item, has been shared. Now, in this post, I am going to Share an existing ListItem with a different SP user using CSOM.This works in the following 2 simple steps:

  1. Get the user(principal) with whom the item has to be shared.
  2. Next, get/create the RoleDefinition which will have various Permission Rights for the user.

The 2nd point is important. SP has already defined various RoleDefinitions that we can use for our purpose like, Administrator, ContentEditior, Read, etc. So we can either use any one of them or can even create our own custom RoleDefinition. I am going to demonstrate both the functionalities here.

First get the User and the ListItem which is to be shared


ClientContext ctx = new ClientContext("targetURL");
ctx.Credentials = new SharePointOnlineCredentials(userName, passWord);

ctx.Load(ctx.Web);
SP.ListCollection listCol = web.Lists;
ctx.Load(listCol);

SP.List list = listCollection.GetByTitle("ListTitle");

SP.CamlQuery camlQuery = new SP.CamlQuery();
camlQuery.ViewXml = "";

SP.ListItemCollection listItemCollection = list.GetItems(camlQuery);
ctx.Load(listItemCollection);

ctx.ExecuteQuery();

//get the user you want to share the current item
User currentUser = ctx.Web.EnsureWeb("memberLoginName")
SP.ListItem listItem = listItemCollection.FirstOrDefault(t => t.DisplayName == "WordFile");
ctx.Load(listItem,
    li => li.HasUniqueRoleAssignments,
    li => li.RoleAssignments.Include(
        p => p.Member,
        p => p.RoleDefinitionBindings.Include(
            s => s.Name)));
ctx.Load(currentUser);
            
ctx.ExecuteQuery(); 

Share with User with Existing RoleDefinition

RoleDefinitionCollection roleDefCol = ctx.Web.RoleDefinitions;

ctx.Load(roleDefCol, r => r.Include(p => p.Name));
ctx.ExecuteQuery();

RoleDefinitionBindingCollection rDefBindCol = new RoleDefinitionBindingCollection(ctx);

//Could be Edit, Read, Contribute, etc.
string roleDefName = "Edit";
RoleDefinition rDef = roleDefCol.FirstOrDefault(p => p.Name == roleDefName);

if(rDef != null)
{
    //Add the current binding to the seleted listItems RoleAssignments
    rDefBindCol.Add(rDef);
}

ctx.ExecuteQuery();

Console.WriteLine("SharePoint Role Assigned successfully.");

Share with User with new Custom RoleDefinition

Site collSite = ctx.Site;

// Set up permissions.
BasePermissions permissions = new BasePermissions();
permissions.Set(PermissionKind.ViewListItems);
permissions.Set(PermissionKind.AddListItems);
permissions.Set(PermissionKind.EditListItems);
permissions.Set(PermissionKind.DeleteListItems);

// Create a new role definition.
RoleDefinitionCreationInformation rdcInfo = new RoleDefinitionCreationInformation();
rdcInfo.Name = "Manage List Items";
rdcInfo.Description = "Allows a user to manage this list items";
rdcInfo.BasePermissions = permissions;
RoleDefinition roleDef = collSite.RootWeb.RoleDefinitions.Add(rdcInfo);

// Create a new RoleDefinitionBindingCollection object.
RoleDefinitionBindingCollection collRDB = new RoleDefinitionBindingCollection(ctx);
// Add the role to the collection.
collRDB.Add(roleDef);

// Break permissions so its permissions can be managed directly.
listItem.BreakRoleInheritance(true, false);

// Get the RoleAssignmentCollection for the target listItem.
RoleAssignmentCollection collRoleAssign = listItem.RoleAssignments;
// Add the user to the target listItem and assign the user to the new //RoleDefinitionBindingCollection.
RoleAssignment rollAssign = collRoleAssign.Add(currentUser, collRDB);

ctx.ExecuteQuery();

Console.WriteLine("Custom Role Assigned successfully."); 

Get a list of all the Shared users for a ListItem/Document of SharePoint List using Client Object Model C#

In SharePoint, users have the option to share a document or a folder with another user with either Read or Edit permissions. So you can have a document or may be a folder that you have shared with a person, or many person, or even a group. So now the endevaour is to get a list of all the users with whom a given document is shared with their corresponding permission levels.

To do this, we’re take the help of Role Assignment class.
SharePoint stores this information in the ListItem’s RoleAssignments property. So have a look at the following code:


ClientContext ctx = new ClientContext(targetURL);
ctx.Credentials = new SharePointOnlineCredentials(userName, passWord);

ctx.Load(ctx.Web);
SP.ListCollection listCol = web.Lists;
ctx.Load(listCol);

SP.List list = listCollection.GetByTitle("ListTitle");

SP.CamlQuery camlQuery = new SP.CamlQuery();
camlQuery.ViewXml = "";

SP.ListItemCollection listItemCollection = list.GetItems(camlQuery);
ctx.Load(listItemCollection);

//Execute Query 
ctx.ExecuteQuery();

 
SP.ListItem listItem = listItemCollection.FirstOrDefault(t => t.DisplayName == "WordFileName");

ctx.Load(listItem,
    li => li.HasUniqueRoleAssignments,
    li => li.RoleAssignments.Include(
        p => p.Member,
        p => p.RoleDefinitionBindings.Include(
            s => s.Name)));
ctx.ExecuteQuery();

if (listItem.HasUniqueRoleAssignments)
{
    //Get all the RoleAssignments for this document
    foreach (RoleAssignment rAssignment in listItem.RoleAssignments)
    {
        //A single RA can have multiple bindings
        foreach (RoleDefinition rDef in rAssignment.RoleDefinitionBindings)
        {
            Console.WriteLine(String.Concat("Role assigned to the member, ", rAssignment.Member.LoginName, " is ", rDef.Name));
        }
    }
}
else
    Console.WriteLine("The current ListItem has no unique roles assigned to it.");

Here, what I am doing is that for a given SharePoint site, I am connecting to a Document Library named, “ListTitle”. Then I am trying to get an item, “WordFileName”¬† from that list as this item has got some unique roles.

The important things to note down here is:

  1. A single item can have multiple Role Assignments i.e., different types of roles can be assigned to it. Say, a given user, A, might be assigned with “Edit” permission while another user, B, can have only “Read” permissions. Each of this variations will come down as a single Role Assignment(RA).
  2. A single Role Assignment can have multiple bindings.

Following are the roles defined for the user, that you can get for an item:

  • Full Control
  • Design
  • Edit
  • Contribute
  • Read
  • Limited Access
  • View Only
  • Records Center Web Service Submitters

The above List may vary if there are some custom Role Definitions.

You might want to have a look about the Limited Access permission here, https://realmpksharepoint.wordpress.com/2014/07/29/you-cannot-grant-a-user-the-limited-access-permission-level/

Similarly, here’s the link to the blog where I have described how, through code, we can share Office365 file,¬†https://realmpksharepoint.wordpress.com/2014/08/04/share-a-sharepoint-file-with-different-office365-users-using-client-object-model-c/
That’s it.