Summary: Learn how to use Windows PowerShell to get data from a SharePoint 2010 list.

  Hey, Scripting Guy! I need to be able to access data in a SharePoint 2010 list, but I do not have access to the server itself. All I can do is view the website and see the list in my browser. Can you help me?

— SA

Hello SA,

Microsoft Scripting Guy Ed Wilson here. Our guest blogger today has been with us before. Welcome back, Andy Schneider! Please see his previous blog post about changing the desktop monitor resolution with Windows PowerShell to learn more about Andy as well as to see his previous post. Today, Andy is going to answer SA’s question about SharePoint 2010.

Take it away Andy!

I have been working on a project where I need to programmatically access a list on a SharePoint site from a bunch of different servers. Although SharePoint 2010 comes with about 945,872 cmdlets out of the box (give or take a few), they are all used for administration on the SharePoint server itself.

However, there are some new DLLs designed for Microsoft .NET developers that write applications to access SharePoint sites. When I first read about this, I was super excited, because I knew that if there were .NET DLLs available for developers, I could probably use them in Windows PowerShell as well. I really thought they could help me simplify my code. As you will see in this article, the jury is still out on whether my code is simpler or not. By the way, my code is on TechNet Script Repository.

Just to give you an idea of what I am after in this article, the following image is a screenshot of a list I want to “get” in Windows PowerShell. There are really three columns I am interested in: Title, Command, and CommandType.

I am going to walk through my experience of trying to use these DLLs to pull data down from this SharePoint list. Basically what I was trying to do was convert some of the code in this MSDN article from C# to Windows PowerShell. The code snippet I am converting is actually pretty simple and can be seen in the following image.

To get started, the first thing I had to do was get the DLLs on my client computer. That was rather painless. You can get them from any SharePoint 2010 server (Foundation or Full). They are in the following directory:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI

Now that they were copied locally, I had to register them in Windows PowerShell. This is done using the Add-Type cmdlet, as shown in the following image.

Now is when it gets fun. Going back to the C# code, the first thing we need to do is create a new ClientContext object and a web object. That seems easy enough. You can see this in the following image.

Now, apparently we need to call this method called Load() and pass in $site. Seems manageable to me.

Oh, fun, it sounds like this Load method needs something else passed to it. Let us go find out what. In Windows PowerShell, you can get information on a method by not adding the () at the end. The () says “call this method.” Without the (), you just get information about the method itself. Check it out in the following image.

Seeing that overload definition for the first time made me throw my hands up in the air and decide to come back to it another day.

A day goes by…

Looking at this a little more closely, I know anything that has “T” in it usually has something to do with generics. I have also heard that generics and Windows PowerShell do not always play well together. So, if you are not a developer by trade like me, what are you going to do? I got on IM and chatted with a Windows PowerShell MVP that also happens to know a ton about SharePoint. Basically, in Windows PowerShell, you can create generic objects. However, you have to use some crazy reflection to be able to call methods that take generics as parameters. This Load method just so happens to be one of those methods. It quickly became clear that calling this method in Windows PowerShell directly was not the easiest way to go.

All hope is not lost. The Windows PowerShell team, in their tremendous wisdom, provided some capability for just such a problem. We can use a little bit of inline C# to get the bits we need, and then knock out the rest with Windows PowerShell. The cmdlet Add-Type to the rescue! The following image shows how to get that C# into your script.

I basically copied the C# code, changed a few names of methods, and put it into a Windows PowerShell here string. The one slightly tricky part was telling Add-Type to use the Sharepoint.Client DLLs as references. Notice the array I created called $assemblies that I passed to the –ReferencedAssemblies parameter. So now what? Running that code allows me to call my new static method, GetList().

The following image shows the rest of the code.

I will be the first to admit, this is definitely not as elegant as I would have liked. It turns out that the GetList() method returns a collection of items that Windows PowerShell does not know how to display, so you get an error if you execute write-host $items.

Let us take a quick look at the list I was pulling from on my own system again in the following image.

When I went to look at the first item in $items, there were no properties called Command or CommandType, and Title was giving me the title of the list, not the title of the actual list item in which I was really interested.

After messing around with Get-Member for a quite a while, I found that this information was stored in a Dictionary object in a property called FieldValues. Rather than dumping out a hash table, I decided it would be much easier for a user if I were to convert it to a collection of PSObjects.

To top it off, I also discovered that I could not use an index in any of my loops. In other words, I couldn’t just say $items[0] or $items[1]. I had to use the ForEach statement and iterate over all my collections. This actually made it quite difficult to construct my PSObjects properly. Notice on lines 51 and 52 in the previous screen shot that I had to use the ForEach statement to create regular arrays called $keys and $values so that I could get the count of each one and create the proper PSObject.

At the end of the day, I had indeed created a function in Windows PowerShell that uses these new client-side DLLs to access a SharePoint list. Would I recommend that you use them? Well, it depends. I think it is a great opportunity to learn how to use inline C#. There is a lot of great documentation on how to use these DLLs online as well if you get stuck trying to figure out something. All that being said, I think this is a great opportunity for someone to build a Windows PowerShell module. Considering what I learned during this whole process, I would definitely consider writing a binary module rather than using Windows PowerShell script. Who knows, maybe a new CodePlex Windows PowerShell project will show up in the next couple weeks.

SA, that is all there is to using Windows PowerShell to work with SharePoint lists. Thank you Andy for being our guest again today and sharing your time and knowledge. Guest Blogger Week will continue tomorrow when Boe Prox will join us.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy