Return SPListItems using CSOM and PowerShell without writing CAML


I recently tweeted about my triumph when trying to accomplish returning all SharePoint List Items without the use of CAML or LINQ. The reason I wanted to do this might be strange, but I’ll try to explain my process and methodology and why I got to a point of wanting to make SharePoint do things it didn’t want to do. 🙂

As a very advanced SharePoint Scripter, I do a TON of PowerShell. If you’ve read this blog, follow me on Twitter or talk to me in person you probably knew that. Having said that, there are times when I want to iterate through all list items – either looking for a match or just to return all values. It’s not super common but it happens. Well it’s a very easy thing to do with server side code (read: PowerShell and the Microsoft.SharePoint.PowerShell snap-in).

Getting List Items using standard PowerShell

$web = Get-SPWeb http://someSiteUrl
$list = $web.Lists["SomeList"]
$list.Items #do something with them here

As you see on line 3, there is an Items collection property on the List object. This is great, end of story – IF you’re using server-side PowerShell code. However, I’ve been focusing a lot lately on integrating PowerShell into the CSOM space – specifically to manage SharePoint Online with PowerShell. If you’ve written any CSOM code, you know that things are done a little differently. Here’s an example of how to return a list item using PowerShell on the client-side – note that I’ve omitted about 12 lines of code that must take place before this to load the Microsoft.SharePoint.Client* assemblies, create ClientContext, get the SPWeb, etc.:

Getting a List Item using PowerShell Client-Side Code

$list = $GLOBAL:Web.Lists.GetByTitle("BigListLotsOfItems")
$item = $list.GetItemById(5)
$GLOBAL:Context.Load($item)
$GLOBAL:Context.ExecuteQuery()
$item["Title"]

That’s great, but if I wanted to return all items – the Client Object Model won’t give me List.Items. In reading MSDN, there are C# examples of how to write CAML queries to return all list items – but the whole point is that I don’t want to write CAML. Here’s the C# example – which I could have certainly translated to PowerShell if I wanted to do so:

Using C# to get all List Items

// Starting with ClientContext, the constructor requires a URL to the
// server running SharePoint.
ClientContext context = new ClientContext("http://SiteUrl");

// Assume the web has a list named "Announcements".
List announcementsList = context.Web.Lists.GetByTitle("Announcements");

// This creates a CamlQuery that has a RowLimit of 100, and also specifies Scope="RecursiveAll"
// so that it grabs all list items, regardless of the folder they are in.
CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
ListItemCollection items = announcementsList.GetItems(query);

// Retrieve all items in the ListItemCollection from List.GetItems(Query).
context.Load(items);
context.ExecuteQuery();
foreach (ListItem listItem in items)
{
    // We have all the list item data. For example, Title.
    label1.Text = label1.Text + ", " + listItem["Title"];
}

So that’s great, but here’s how I went about getting all list items without writing CAML! I’ll break it down section by section, but if you’re impatient the whole solution is at the bottom. 🙂

Creating our Context

The first thing we need to do when working with SharePoint from the Client Side is create ClientContext. To do so using PowerShell, here is some example code:

$GLOBAL:Context = New-Object Microsoft.SharePoint.Client.ClientContext("http://someWebUrl")
$GLOBAL:Credentials = Get-Credential -UserName $EmailAddress -Message "Please enter your Office 365 Password"
$Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credentials.UserName,$Credentials.Password)
$GLOBAL:Web = $GLOBAL:Context.Web
$GLOBAL:Context.Load($GLOBAL:Web)
$GLOBAL:Context.ExecuteQuery()

Getting our SPWeb

Once we’ve created our ClientContext variable, we’ve already got the SPWeb. But just for transparency, here’s how you would/could access methods and properties of the SPWeb object:

$GLOBAL:Web.Title #returns Web.Title
$GLOBAL:Web.Description #returns Web.Description

Getting our SPList

Now, to get the List object – we’ll do the following:

$SPList = $GLOBAL:Web.Lists.GetByTitle("BigListLotsOfItems") #Note that BigListLotsOfItems is my List Title

Using the For loop to iterate through all items

And this is where the magic happens as they say. For anyone who has done any level of development or scripting, you know that the best way to loop while also allowing for modification of the array with which you’re looping is to use a For loop.

For ($i=0; $i -le $SPList.ItemCount; $i++)
{
}

This is a pretty standard implementation of the For loop, what I want to call out though is the use of Try/Catch/Finally. As a dabbling developer, I’m not sure if this is the intended use of Try/Catch/Finally – so if this is bad practice forgive me. 🙂

Essentially I start out by creating the ListItem variable, and then calling the Load method of the ClientContext variable. I do that before entering the Try/Catch/Finally. I then use Try {} to run the Context.ExecuteQuery method, and if that fails the loop will go into the Catch {} block – where I will increment the array by 1 ($i++) and then run the GetItemById(), Load() and ExecuteQuery() methods against the next $i in the loop.

$ListItem = $SPList.GetItemById($i)
$GLOBAL:Context.Load($ListItem)
    Try
    {
        $GLOBAL:Context.ExecuteQuery()
    }
    Catch
    {
        $i++
        $ListItem = $SPList.GetItemById($i)
        $GLOBAL:Context.Load($ListItem)
        $GLOBAL:Context.ExecuteQuery()
    }

Why did I do this? Well it’s really pretty simple. Since I have to use the GetItemById() method, I have to know the ListItemID of each ListItem. If someone has deleted an item, the ItemCount property won’t match the highest ID in the list – right? So if there are 5 items but someone deleted a few at one point or another – the highest ID might be 7 or 8, without incrementing by 1 on a failure, we’ll never get to 7 or 8…

And so now that we’ve discussed all of that, here’s the final example script.

The Final Solution

$GLOBAL:Context = New-Object Microsoft.SharePoint.Client.ClientContext("http://someWebUrl")
$GLOBAL:Credentials = Get-Credential -UserName $EmailAddress -Message "Please enter your Office 365 Password"
$Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credentials.UserName,$Credentials.Password)
$GLOBAL:Web = $GLOBAL:Context.Web
$GLOBAL:Context.Load($GLOBAL:Web)
$GLOBAL:Context.ExecuteQuery()
$SPList = $GLOBAL:Web.Lists.GetByTitle("BigListLotsOfItems")
For ($i=0; $i -le $SPList.ItemCount; $i++)
{
    $ListItem = $SPList.GetItemById($i)
    $GLOBAL:Context.Load($ListItem)
    Try
    {
        $GLOBAL:Context.ExecuteQuery()
    }
    Catch
    {
        $i++
        $ListItem = $SPList.GetItemById($i)
        $GLOBAL:Context.Load($ListItem)
        $GLOBAL:Context.ExecuteQuery()
    }
    Finally
    {
        #Return item here and do something with it.
    }
}

And that – my friends – is how you can get all List Items from a SharePoint list using PowerShell and CSOM. Hopefully this is helpful for someone else.

The future of InfoPath & SharePoint


Over the past several years I’ve grown very comfortable with InfoPath and SharePoint. In the 2007 days it was a great tool to provide advanced forms capabilities to the end user, with very little development knowledge or experience needed. Things like data connections to query and expose external data, conditional field formatting, user interface adjustments and conditional views were all configurable by anyone who was comfortable with Microsoft Office. I grew very accustomed to helping customers provide an elegant and intuitive interface for data entry. It was also common to tie the forms to a back-end workflow, which could take a business process from paper and sneakers to 1’s and 0’s, fully automated. It was beautiful. And it worked.

SharePoint 2010 took InfoPath even further, allowing us to quickly and *very* easily edit the default forms for a SharePoint list using InfoPath. Now we could do things like provide branding, conditional formatting and data connections in a standard list item form. Sure, it wasn’t the easiest thing to extend beyond the OOTB capabilities of InfoPath, and don’t get me started on the complete omission of Managed Metadata support; but it was a great starting point for custom forms. THAT was the pinnacle for InfoPath. That’s the best it would ever be and for some reason, that’s the last time the InfoPath team would get to develop any *new* features for InfoPath and SharePoint.

When SharePoint 2013 and Office 2013 were released, there were a lot of head scratchers – wondering “what’s next with InfoPath?” I was one of them, and I was increasingly concerned that the end of life was coming. Well, it’s here. InfoPath is officially going bye bye. Now we’re left wondering what’s next, and I suppose if you’re lucky enough to attend #SPConf14 you may just find out. Until that’s released however, we’re all left wondering.

I’m open to change, and I’m definitely hoping Microsoft knocks this decision out of the park. However, I am extremely doubtful there will be anything that comes to close to the ease of which InfoPath could be used to extend SharePoint’s data entry forms. Sure, there are those who are great at writing custom code and building HTML/JS forms – I’m not one of them. Most people who USED InfoPath aren’t either. InfoPath shined in its ability to give the power user some freedom and power – I just don’t see anything new on the horizon that will be as easy to use. Maybe I’m wrong, I hope I’m wrong – but until I see it I won’t believe it.

The ultimate question I have at this point is: does Microsoft even make an attempt at a replacement? Or do they leave the power-user-forms-building to 3rd party ISVs such as Nintex, Dell, etc.? That remains to be seen, but I for one am excited to hear what’s next for InfoPath & SharePoint. I guess we’ll have to wait and see.

Using PowerShell to manage SharePoint Online


Anybody who knows me or has ever read a single post of mine knows I’m a big PowerShell geek. I like it, I love it and I always want some more of it. Bad song lyrics aside, I quite literally use PowerShell every day for one task or another. Over the past 6 months or so I’ve been quietly working on creating some Cmdlets/Functions that will allow me to run PowerShell against Office 365 SharePoint Online. This might sound easy – but spoiler alert – it’s not. That’s not to say it’s difficult, if you’re a developer who is comfortable with CSOM, you’ll be just fine. However, I didn’t have those skills when I took off on this adventure; so needless to say it’s been a bit of a learning experience, combined with a lot of trial & error.

There are really a few key steps which must be taken prior to getting SharePoint Online and PowerShell to talk to one another.

  1. Set up the SharePoint Online Management Shell Windows PowerShell environment
  2. Run Connect-SPOService to connect to a SharePoint Online Administration Center

Once you’ve completed those two simple tasks, you’ve got the baseline environmental requirements in place to start using PowerShell with SharePoint Online. However, if I was writing a blogpost to tell you how to run the 30 built-in cmdlets you get by loading the Microsoft.Online.SharePoint.PowerShell module; well, you’d be left wanting more.

To really start doing the fun developer stuff, you will want to make sure you load the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime assemblies into your session. I’ve written a nice little function for this, which looks like:

function Add-SPOAssemblies {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") | Out-Null
}

Then, to run it – just use:

Add-SPOAssemblies

Pretty simple, and it just loads those two assemblies so you can access all the goodies that come with them.

Once you’ve gotten there, you can start to do things like create ClientContext against an SPWeb:

$webUrl = "SPOWebUrl"
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)
$creds = Get-Credential -Message "Please enter your Office 365 Administrative credentials"
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($creds.UserName,$creds.Password)
$spoweb = $ctx.Web
$ctx.Load($spoweb)
$ctx.ExecuteQuery()

So that’s cool, but it hasn’t done anything yet! Well try this:

$spoweb.Title

Ohhhh… So now that’s cool! Once you’ve gotten this far, i’m sure you can start to see the possibilities.

In case you want to wrap that up into a nice function, here’s a simple example that I think works well:

function New-SPOServiceContext {
[CmdletBinding()]
Param(
[System.String]$EmailAddress,
[System.String]$SPOWebUrl
)
try {
$assemblies=Add-SPOAssemblies
}
catch {
Write-Error "Unable to load Microsoft.SharePoint.Client assemblies"
}
if($assemblies){
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SPOWebUrl)
$creds = Get-Credential -Message "Please enter your Office 365 Administrative credentials" -UserName $EmailAddress
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($creds.UserName,$creds.Password)
$spoweb = $ctx.Web
$ctx.Load($spoweb)
$ctx.ExecuteQuery()
}
}

And of course to run it, use:

New-SPOServiceContext -EmailAddress your.email@your-company.com -SPOWebUrl https://something.sharepoint.com

“But wait, at the beginning you said this was challenging – this was way too easy!?”

Well, I wasn’t used to having to create a context object, then a credential object, followed by a web variable, and then I have to load and execute a query?! All new to me, but again – I haven’t done very much (read: any) development on the client side.

I could share loads of other cool things I’ve done, but you’ll have to wait while I clean up and finish some of the functions I’ve been working on. Until then, happy PowerShelling!

Upgrade multiple SharePoint content databases using PowerShell and XML


As a SharePoint Consultant, a lot of the work I do is around upgrades and migrations. I’ve done quite a few of these, and I’ve done them from 2003 to 2007, 2007 to 2010, and now 2010 to 2013. I’ve done several migrations where a 3rd party tool was used to migrate the content, but a lot of the time I end up using the standard database attach approach to move the content databases from the old farm to the new farm. In doing so, obviously PowerShell is required – but more times than not there are multiple content databases. Manually testing and mounting each one can be extremely time consuming, especially if you’re going to end up deploying the content to multiple farms (Dev, QA, DR, Production). What I’ve decided to do is actually build some PowerShell and XML that can be used both to run Test-SPContentDatabase as well as Mount-SPContentDatabase to allow you to do these in bulk.

When writing this script, I had a few things in mind that I wanted to accomplish:

  1. Provide 100% of the input to the script in the form of XML, allowing the script to be 100% separate from the parameters and input.
  2. Provide options to use Test OR Mount, depending on where I’m at in the migration. (It’s always recommended to run Test-SPContentDatabase BEFORE Mount-SPContentDatabase!)
  3. Provide readable output and progress indication to know where the script is in it’s execution.

To accomplish these 3 goals, I made sure to:

  • Include ALL of the available options when running Test-SPContentDatabase AND Mount-SPContentDatabase in the XML and Script (empty values are simply ignored).
  • Include SWITCH parameters for Test and Mount – to allow the user to specify their intention at runtime.
  • Include Write-Progress and Write-Output to provide clear output to the executing user.

Now that I’ve explained my intentions with writing this code, I’ll share it!

Here is an example of how you could use the XML:

<?xml version="1.0" encoding="utf-8" ?>
<WebApps>
	<WebApp Url="http://portal.adventureworks.com">
		<Databases>
			<Database Name="SP2010_Pub_Content_1" WarningSiteCount="0" MaxSiteCount="1" AssignNewDatabaseId="false" ChangeSyncKnowledge="false" ClearChangeLog="false" DatabaseCredentials="" DatabaseServer="SPSQL\Publishing" NoB2BSiteUpgrade="false" SkipIntegrityChecks="false"/>
			<Database Name="SP2010_Pub_Content_2" WarningSiteCount="5" MaxSiteCount="10" AssignNewDatabaseId="false" ChangeSyncKnowledge="false" ClearChangeLog="false" DatabaseCredentials="" DatabaseServer="SPSQL\Publishing" NoB2BSiteUpgrade="false" SkipIntegrityChecks="false"/>
		</Databases>
	</WebApp>
	<WebApp Url="http://collab.adventureworks.com">
		<Databases>
			<Database Name="SP2010_Collab_Content_1" WarningSiteCount="25" MaxSiteCount="30" AssignNewDatabaseId="false" ChangeSyncKnowledge="false" ClearChangeLog="false" DatabaseCredentials="" DatabaseServer="SPSQL\Collab" NoB2BSiteUpgrade="false" SkipIntegrityChecks="false"/>
			<Database Name="SP2010_Collab_Content_2" WarningSiteCount="50" MaxSiteCount="100" AssignNewDatabaseId="false" ChangeSyncKnowledge="false" ClearChangeLog="false" DatabaseCredentials="" DatabaseServer="SPSQL\Collab" NoB2BSiteUpgrade="false" SkipIntegrityChecks="false"/>
		</Databases>
	</WebApp>
</WebApps>

Here is the PowerShell code:

<#
.Synopsis
    This script leverages an XML file as the input - and based on the XML contents will attach one or more 
	content databases to one or more SharePoint 2010/2013 Web Applications.
.Description
    This PowerShell Script uses Get-Content, ForEach-Object and Mount-SPContentDatabase cmdlets along with 
	some other various code snippets to iterate through one or more web applications.
    While attaching content databases to a SharePoint 2010/2013 farm, it will use Write-Progress to provide an update in the PowerShell window.
.Example
    Mount-SPContentDatabasesFromXml.ps1 -XmlInput C:\ContentDatabases.xml
   
    This example will mount all content databases to all web applications specified in an XML file at C:\ContentDatabases.xml.
   
    Example XML syntax:
    <?xml version="1.0" encoding="utf-8" ?>
    <WebApps>
        <WebApp Url="http://igs">
            <Databases>
                <Database Name="SP2010_Content_1" WarningLevel="5" MaxSites="10"/>
                <Database Name="SP2010_Content_2" WarningLevel="50" MaxSites="100"/>
            </Databases>
        </WebApp>
    </WebApps>
.Notes
    Name: Mount-SPContentDatabasesFromXml.ps1
    Author: Ryan Dennis
    Last Edit: 9/16/2013
    Keywords: Mount-SPContentDatabase, ForEach-Object, Get-Content, SharePoint Upgrade/Migration
.Link
    http://www.sharepointryan.com
    ryan@sharepointryan.com
       
#Requires -Version 2.0
#>
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)][System.String]$XmlInput,
[Parameter(Mandatory=$false,ParameterSetName="Test")][Switch]$Test,
[Parameter(Mandatory=$false,ParameterSetName="Mount")][Switch]$Mount
)
# Look for the SharePoint Snapin and add it if not already added #
if ((Get-PSSnapin Microsoft.SharePoint.PowerShell) -eq $null)
{
    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
}
$FarmVersion = (Get-SPFarm).BuildVersion
if ($FarmVersion.Major -eq '15') {$FarmVersion = "2013"} 
elseif ($FarmVersion.Major -eq '14') {$FarmVersion = "2010"} 
else {$FarmVersion = ""}

# Load XML document #
[System.Xml.XmlDocument]$xml = Get-Content $($XmlInput)
if ($xml -eq $null)
{
    throw "XML input not found"
}

# Iterate through each WebApps.WebApp in the XML document and mount databases #
$i=0
$dbCount = $xml.GetElementsByTagName("Database").Count
$xml.WebApps.WebApp | ForEach-Object {
$waUrl = $_.Url

    $_.Databases.Database | ForEach-Object {
        $dbName = $_.Name
		if (!([string]::IsNullOrEmpty($_.WarningSiteCount)))
		{$paramWarningSites = @{WarningSiteCount = $_.WarningSiteCount}}
		else{$paramWarningSites = ""}
		
		if (!([string]::IsNullOrEmpty($_.MaxSiteCount)))
		{$paramMaxSites = @{MaxSiteCount = $_.MaxSiteCount}}
		else{$paramMaxSites = ""}
		
		if (!([string]::IsNullOrEmpty($_.AssignNewDatabaseId)))
		{$paramNewDbId = @{AssignNewDatabaseId = $true}}
		else{$paramNewDbId = ""}
		
		if (!([string]::IsNullOrEmpty($_.ChangeSyncKnowledge)))
		{$paramChangeSync = @{ChangeSyncKnowledge = $true}}
		else{$paramChangeSync = ""}
		
		if (!([string]::IsNullOrEmpty($_.ClearChangeLog)))
		{$paramClearLog = @{ClearChangeLog = $true}}
		else{$paramClearLog = ""}
		
		if (!([string]::IsNullOrEmpty($_.DatabaseCredentials)))
		{$paramDbCreds = (Get-Credential -Message "Enter Database Credentials")}
		else{$paramDbCreds = ""}
		
		if (!([string]::IsNullOrEmpty($_.DatabaseServer)))
		{
			if($Test){
				$paramDbServer = @{ServerInstance = $_.DatabaseServer}
			}
			if($Mount){
				$paramDbServer = @{DatabaseServer = $_.DatabaseServer}
			}
		}
		else{$paramDbServer = ""}
				
		if (!([string]::IsNullOrEmpty($_.NoB2BSiteUpgrade)))
		{$paramNoB2B = @{NoB2BSiteUpgrade = $true}}
		else{$paramNoB2B = ""}
		
		if (!([string]::IsNullOrEmpty($_.SkipIntegrityChecks)))
		{$paramSkipChecks = @{SkipIntegrityChecks = $true}}
		else{$paramSkipChecks = ""}
		
        try
        {
            $i++
            # If Mount switch param is included, mount the databases #
            if($Mount){
				Write-Progress -Activity "Mounting $dbName to web application $($waUrl)" -PercentComplete ($i/$dbCount*100) -Status "Mounting content databases to SharePoint $FarmVersion"
				Mount-SPContentDatabase -Name $dbName -WebApplication $waUrl @paramNewDbId @paramChangeSync @paramClearLog @paramDbCreds @paramDbServer @paramMaxSites @paramNoB2B @paramSkipChecks @paramWarningSites
        	}
			# If Test switch param is included, test the databases #
			if($Test){
				Write-Progress -Activity "Testing $dbName" -PercentComplete ($i/$dbCount*100) -Status "Testing content databases with SharePoint $FarmVersion"
				Write-Output "::Performing operation Test-SPContentDatabase on database: $dbName`n"
				Test-SPContentDatabase -Name $dbName -WebApplication $waUrl @paramDbCreds @paramDbServer
			}
		}
        catch
        {
            Write-Error $_
        }
    } #endDbForEach
} #endForEach