U2U Blog

for developers and other creative minds

Iterating through the columns of a list with Powershell and the SharePoint Lists.asmx web service

Yesterday I got a question on how to iterate through the columns of a list by using Powershell and the SharePoint Lists.asmx web service.

If you read previous post and saved the necessary environment variable declarations in your profile, the only thing you should do to start is:

$listservice = New-Object Lists

$listservice.Credentials=[System.Net.CredentialCache]::DefaultCredentials

Otherwise you have to go back to my last post and execute the first steps to declare the environment variables and compile and load the lists dll.

You can get access to the list by executing the GetList method on the Lists.amx web service. The required argument is the name of the list:

$list = $listservice.GetList("Employees")

You can view the title of the list:

$list.Title

Or the Guid:

$list.ID

image

You can get access to the fields (or columns) defined on your list by storing the field collection into a variable:

$fieldsnode = $listservice.GetList("Employees").Fields

But the return value of this Fields collection is an xml node. You can view the content of the field collection by executing the $fieldsnode.get_OuterXml() method but this returns you more than a page full of xml. But Powershell is smart enough to be able to handle the child nodes as objects. You can  view the different fields by executing f.e. the $fieldsnode.get_ChildNodes().

And now we come to iterating through the field collection. A field exposes properties like Name (which is the InternalName when working with the SharePoint object model), the ID, the DisplayName, Type and a lot of other properties. You can iterate through the field collection and retrieve the internal name as follows:

$fieldsnode.get_ChildNodes() | foreach-object { $_.Name }

image

 

Nick this one is for you! Thanks for reading my blog.

Book of the month: Inside Index and Search Engine

My "boss" Patrick Tisseghems second book is available now. He wrote this juwel with his friend Lars Fastrup. If you are interested in Index and Search for MOSS 2007, this is a must-have!

InsideSearch

His book counts 640 pages and is a architectural guidance for deploying SharePoint Server 2007 and optimizing search.

Optimize the user search experience in your workplace by customizing and extending the enterprise search capabilities in SharePoint Server 2007. Recognized SharePoint experts deliver the straightforward guidance and pragmatic coding samples you need to enable quick access to critical information.
Discover how to:
• Configure and manage the SharePoint Server 2007 index file and search engines
• Plan a search deployment strategy that balances performance, scalability, and security considerations
• Set up and customize search functionality and UI controls
• Monitor and analyze search-usage metrics to optimize the end-user experience
• Use search APIs to prepare and execute a query, and to process the results
• Program administrative functions using the search administration object model
• Develop and install custom IFilters and protocol handlers
• Use the Business Data Catalog to structure and index critical line-of-business data
• Program search operations using Windows SharePoint Services 3.0

Enjoy reading!

Update a SharePoint List remotely with PowerShell

Yesterday I got a comment on my blog post Update a SharePoint List with PowerShell asking if it is possible to remotely update a SharePoint list. This means that you have to execute the UpdateListItem method of the Lists.asmx web service of SharePoint.

It is possible to work with the SharePoint web services from within SharePoint. There are already some good posts around:

- Calling a Web Service from PowerShell which explains the steps to take before you can consume a Web Service

- Consuming SharePoint Web Services from PowerShell which explains how to get a list from the Lists Web Service.

In this post I'll explain the steps how to execute the UpdateListItem method.

First load the Visual Studio tools into your Powershell environment:

$env:VSINSTALLDIR="C:\Program Files\Microsoft Visual Studio 8"

$env:VCINSTALLDIR="C:\Program Files\Microsoft Visual Studio 8\VC"

$env:DevEnvDir="$env:VSINSTALLDIR\Common7\IDE"

$env:FrameworkSDKDir="$env:VSINSTALLDIR\SDK\v2.0"

$FrameworkPath=$([System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory())

$env:FrameworkDir=$(split-path $FrameworkPath -Parent)

$env:FrameworkVersion=$(split-path $FrameworkPath -Leaf)

$env:PATH="$env:VSINSTALLDIR\Common7\IDE;$env:VCINSTALLDIR\BIN;$env:VSINSTALLDIR\Common7\Tools;$env:VSINSTALLDIR\Common7\Tools\bin;$env:VCINSTALLDIR\PlatformSDK\bin; $env:FrameworkSDKDir\bin;$env:FrameworkDir\$env:FrameworkVersion;$env:VCINSTALLDIR\VCPackages;$env:PATH"

$env:INCLUDE="$env:VCINSTALLDIR\ATLMFC\INCLUDE; $env:VCINSTALLDIR\INCLUDE;$env:VCINSTALLDIR\PlatformSDK\include;$env:FrameworkSDKDir\include;$env:INCLUDE"

$env:LIB="$env:VCINSTALLDIR\ATLMFC\LIB; $env:VCINSTALLDIR\LIB;$env:VCINSTALLDIR\PlatformSDK\lib;$env:FrameworkSDKDir\lib;$env:LIB"

$env:LIBPATH="$FrameworkPath; $env:VCINSTALLDIR\ATLMFC\LIB"

As explained in other posts, you can put this in your profile so that these tools are loaded each time you run Powershell.

Then execute the following commands to compile the Lists web service:

PS>wsdl http://[site url]/_vti_bin/Lists.asmx

PS>csc /t:library Lists.cs

PS>[Reflection.Assembly]::LoadFrom("Lists.dll")

Once this is done you can place the following statement in your profile so that it is executed each time you run Powershell:

[void][Reflection.Assembly]::LoadFrom("Lists.dll")

Instantiate the lists web service:

PS> $listservice = New-Object Lists

You have to be authenticated to contact the web service:

$listservice.Credentials=[System.Net.CredentialCache]::DefaultCredentials

My example uses a computers list:

powershell updatelistitems before

There is a mistake in the title of the second item, which need to be corrected.

The method you need to use to update a list item is the UpdateListItems. This method asks for 2 arguments: the name of the list and a batch query. First a string is build with the CAML needed to update the list item and then the UpdateListItems method is executed:

PS>$query = "<Batch OnError='Continue'><Method ID='1' Cmd='Update'><Field Name='ID'>2</Field><Field Name='Title'>Laptop 2</Field></Method></Batch>"

PS>$listservice.UpdateListItems("Computers", $query)

In fact the second argument of the UpdateListItems method is an xml node but if the query string contains valid xml, it is implicitly converted into an xml node before it is passed to the UpdateListItems method. If you want to explicitly use an xml node you can write the following:

PS>[xml]$query = "<Batch OnError='Continue'><Method ID='1' Cmd='Update'><Field Name='ID'>2</Field><Field Name='Title'>Laptop 2</Field></Method></Batch>"

And here is the result of the udpate:

powershell updatelistitems after

For more information about working with xml nodes, i refer to the post of Dan Sullivan.

As you can see I can use CAML in a lot of different ways :)