U2U Blog

for developers and other creative minds

Updating a SharePoint list using the CAML Query Builder dlls

Another question I got today was how you could use the CAML Query Builder dls to add a new item to a SharePoint list.

Indeed, the CAML Query Builder tool consists of a user interface and several dlls:

  • U2U.SharePoint.CAML.dll: this dll contains a class named Builder that you can use to build the CAML queries you need
  • U2U.SharePoint.CAML.Server.dll: this dll contains a class named Helper that you can use to execute CAML queries through the SharePoint object model
  • U2U.SharePoint.CAML.Client.dll: this dll contains a class named Helper that you can use to execute CAML queries through the SharePoint web services

I think it is not commonly known that you can use the dlls without the user interface and that you can use them within your SharePoint projects.

In this post I will explain how you can use the U2U.SharePoint.CAML.Client dll to add a new list item to a SharePoint list, which means throught the SharePoint web services.

Lets say I have a U2U Contact list and I want to add a new colleague. This list contains the following columns: Last Name, First Name, Company, Business Phone and Email Address.

If you want to add a list item via the SharePoint web services, you need to execute the UpdateListItem method of the Lists web service. This method requires a CAML query that has the following syntax:

image

So the first step is to build your CAML query. Instantiate the U2U.SharePoint.CAML.Builder class and pass it the type of method that you want to build:

U2U.SharePoint.CAML.Builder camlBuilder = new U2U.SharePoint.CAML.Builder(U2U.SharePoint.CAML.Enumerations.CamlTypes.UpdateListItems);

This will create an xml document with the root node required by the update method. In this case it is <Batch>.

Then build you caml query with calls to the following methods:

camlBuilder.UpdateBatchNode(Caml.Constants.Batch.OnError, "Continue");

camlBuilder.AddMethodNode(1, "New");

camlBuilder.AddFieldNode(1, "Title", "DeRudder");

camlBuilder.AddFieldNode(1, "FirstName", "Kevin");

camlBuilder.AddFieldNode(1, "Email", "kevin@u2u.be");

Now you can execute the UpdateListItem metod on the U2U.SharePoint.CAML.Client dll:

U2U.SharePoint.CAML.Client.Helper helper = new U2U.SharePoint.CAML.Client.Helper("http://wss.u2ucourse.com", "U2U Contacts", camlBuilder.CamlDocument);

By using this constructor of the helper class you pass SharePoint URL, list name and CAML query document in one time. So calling the following method is sufficient to get your query correctly executed:

XmlNode result = helper.UpdateListItems();

This method calls the UpdateListItem method on the Lists web service and passes the CAML to it. The returned value is an xml node that contains a success message or an error message. If the xml contains the following <ErrorCode>0x00000000</ErrorCode>, it means your query executed successfully and you item is added to the list. If the ErrorCode element contains something else, inspect the xml string closely because the error message will be contained in it.

Please, let me know if you have question on other methods and I will post an explanation as soon as possible.

SharePoint PowerShell for Beginners - continued

In my first post on PowerShell for SharePoint developers I described how you could use the SPSite and SPWeb object to get information on its object model. In this post I will explain a few methods and properties of SPList.

Perhaps some of you know the featue I wrote last summer to view all properties of a SharePoint list. One of the things that you could inspect with this feature is the fields that are defined on the list. It shows you its name, internal name, id and data type. For more information see my post on the List Properties feature.

Today I will show you how to get this information by using PowerShell.

To get to the list you want to investigate, you need to perform some preliminary tasks:

Load the SharePoint assembly:

[System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.SharePoint”)

Open the SharePoint web:

$siteUrl = "your sharepoint site url"

$webName = "your webname"

$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)

$spWeb = $spSite.OpenWeb($webName)

Instantiate the SharePoint list: 

$listName = "your listname"

$spList = $spWeb.Lists[$listName]

The fields of a list are defined in its Fields collection property. You could execute the following but it will return you a long list of all fields with all of its properties.

$spList.Fields

View column properties of a SharePoint list

So if your interested in only a few properties you could first inspect the metadata of the field object. A field is of type SPField. As we are not able to create a new object of type SPField from a default constructor, we will get an instance of the first field of the list. To view the different properties defined on SPField, execute the following statement:

$spList.Fields[0] | gm -membertype property

Following screenshot shows you the result.

image

So, if you want to view a few properties like display name, internal name, id and data type of all fields on a list, use the following syntax:

$spList.Fields | ft Title, InteralName, Id, TypeAsString

This returns you the requested data in table format. If not all information like f.e. the Id is completely visible, you could customize the columns of the table or use the fl formatter, which stands for format-list.

Add a column to a SharePoint list

The second task is to add a column to a SharePoint list. We will create a field with name "Extra column" and of type string. First of all, you need to know which method you need to execute on the list object and what are the arguments you need to specify.

Execute $spList.Fields.Add to get an overview of the different overloads of the Add mehtod:

image

As you can see, there are 2 overloads to add a new item to a SharePoint list. The overload we are going to use is the one that needs a column name, a field type and the required boolean. Execute the following:

$spList.Fields.Add("Extra column", "Microsoft.SharePoint.SPFieldType.Text", 0)

This returns you the following error:

image

As you can see in the error message returned by PowerShell, we didn't specify a correct value for the field type. If you tak a good look at the error message you will notice that PowerShell returns all possible values for that argument in the error message!

Specify boolean values as 0 and 1. If you pass the string "false", it is accepted as "different from false" and your field will be added as a required field.

Following statement will add the column to the list:

$spList.Fields.Add("Extra column", "Text", 0)

In addition, PowerShell returns you the internal name of the column you just added, i.e. Extra_x0020_column

Return to Internet Explorer and browse to the Settings Page of your list. You will see that the column is added to the list:

image

You can add it to the default view as you want.

 

In a next post I will explain how you can write a function that adds a column to a SharePoint list. You will be able to use this function for adding columns to each different type of list.

U2U Caml Query Builder (windows version) folder support

It has been a while that I worked on the Windows version of the U2U Caml Query Builder. In fact we were thinking about the replacement of it by the Feature version. But it seems a lot of developers are still working with the Windows version.

Recently I got a question about querying items in sub folders of a document library, for which support is missing in the current versions of the query tool.

If you use the object model you typically use an object of type SPQuery. In that case you can set properties of the object to query sub folder:

  • Set the ViewAttributes property to "Scope=\"Recursive\"" to query the root folder and sub folders of a certain document library
  • Set the Folder property to the name of a folder to search through the items in a certain sub folder.

Following a post of Mart Muller, you can also use SPSiteDataQuery to query through all folders and sub folders of a document library: http://blogs.tamtam.nl/mart/CommentView,guid,3fca3180-6dd5-40c1-9509-8b785d306e7b.aspx

If you need to pass your CAML queries through the web services of SharePoint, you need to include the necessary CAML into your query. As the windows version of the U2U Caml Query Builder supports both working with the object model as working with the web services, I modified the tool so that it can help you building your queries for sub folder support.

You can download the new windows version here.

I will not leave you without any word of explanation. So, for those who are interested, here is how you can build a CAML query with folder support.

When you start the windows version of the tool, you have to specify the URL to your SharePoint site. You can specify some credentials if necessary, and you can choose if you want to work through the object model or through the web services of SharePoint. So click the latter.

Login

Click the Connect button. In the left pane a treeview will be populated with the lists and document libraries of the SharePoint site of your choice.

You can build a good old query by selecting a list, but you can also build a query for use with web services, meaning you can get the necessary xml for the Query node, the ViewFields node and the QueryOptions node required by the GetListItems method of the Lists web service. If this is the case, right-click the list of your choice and select GetListItems from the context menu.

GetListItems

This shows you a tabbed control. In the View Fields tab you can select the fields you want to have included in the result.

ViewFields

Click the Query tab to build the query itself. Build your query like you always do: select the fields you want to include in the Order By clause; select the fields and set the criteria for the fields you want to include in the Where clause. Nothing new until now.

Query

The document library I use in this example has the following structure:

  • 1 document in the root folder with the name "SharePoint developers course.docx"
  • 1 document in the folder "SharePoint For Power Users" with the name "Chapter 1.rtf"
  • 1 document in the folder "SharePoint For Developers" with the name "Chapter 2.doc"

So I build a query to find my file starting with the name "c" (which is located in the sub folder).

To set some extra query options, click the Query Options tab and now pay attention! If you leave all settings like in the screen shot, and you press the Test button, you will get no result back. This is because the default is that only the root folder is queried.

No QueryOptions

If you want to query a certain sub folder, you can check the "Query sub folder" check box. The tool fills out the name of the document library because this is the root of the path, and you can add the name of a sub folder. You see in the screen shot that my document is returned in the result.

image

The query generated by the tool will look like the following:

image

If you want to look through all folders and sub folders, click the "Query all folders and sub folders" check box. This will include the <ViewAttributes> element into the <QueryOptions> element.  When the query executes against my document library, the 2 documents in the 2 sub folders start with "c" and are returned as result.

image

The CAML generated by the tool looks as follows:

image

In your code you can load the generated xml into an xml document and use it as follows:

image

Have fun!

U2U Email Snippet Outlook Add-In

This is a nice example of what an OBA can be. At U2U we are constantly looking to enhance the productivity of the people working in the administration. Small enhancements like the Email Snippet Outlook Add-In are very helpful to respond quicker to requests from customers via email. And it can also be used for dozens of other scenarios.

EmailSnippets1

Read more about the tool and download a free version here.

People ranked by social distance are DummyResults

It's great to write a book. I am these days fully immersed writing the chapter explaining the XSL that is used to render the search results in the different Search Web Parts such as the Search Core Results Web Part and the People Search Core Results Web Part. Sometimes you can't prevent a good laugh while, and that's quite sad, you are all alone in your hotel room cranking out words in Word 2007.

Take this one. In the Search Center, execute a people search and make sure they are sorted by social distance. Now replace the XSL with the following XSL to view the raw XML that will be transformed:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">   <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>     <xsl:template match="/">       <xmp><xsl:copy-of select="*"/></xmp>     </xsl:template> </xsl:stylesheet>

Ok. What is the outcome? Well, this one:

<All_Results>     <DummyResult>       <Colleague />     </DummyResult>     <Result>       <id>9</id>       <accountname>LITWAREINC\PeterM</accountname>       <userprofile_guid>59C5FEB9-26F0-48AA-95E3-E3CBB826E849</userprofile_guid>       <preferredname>Peter Mullen</preferredname>       <jobtitle>Product Compete Manager</jobtitle>       <department></department>       <workphone>(425) 555-0144</workphone>       <officenumber>S1044</officenumber>       <aboutme></aboutme>       <pictureurl>http://moss:32901/personal/peterm/Shared Pictures/Profile  Pictures/PeterM.jpg</pictureurl>       <workemail>PeterM@litwareinc.com</workemail>       <website></website>       <url>http://moss:32901/Person.aspx?guid=59C5FEB9-26F0-48AA-95E3-E3CBB826E849</url> <urlEncoded>http%3A%2F%2Fmoss%3A32901%2FPerson%2Easpx%3Fguid%3D59C5FEB9%2D26F0%2D48AA%2D95E3%2DE3CBB826E849</urlEncoded>       <hithighlightedsummary>I assist our Product, Sales and <c0>Marketing</c0> teams by  providing actionable competitive insights.</hithighlightedsummary>       <hithighlightedproperties>         <HHTitle />         <HHUrl>http://moss:32901/Person.aspx?guid=59C5FEB9-26F0-48AA-95E3- 3CBB826E849</HHUrl>       </hithighlightedproperties>       <responsibility>Competitive Research</responsibility>       <skills>Orion;Neptune</skills>       <sipaddress></sipaddress>       <imageurl imageurldescription="File with extension:  spx">/_layouts/images/html16.gif</imageurl>       <Colleague />     </Result>     <DummyResult>       <ColleagueOfColleague />     </DummyResult>     <Result>       <id>1</id>       <accountname>LITWAREINC\DaveR</accountname>       <userprofile_guid>DA38BB36-8F42-4E65-A534-ACD6A955F156</userprofile_guid>       <preferredname>Dave Richards</preferredname>       <!-- remaining part is left out for brevity-->       <ColleagueOfColleague />     </Result>     <DummyResult>       <Everyone />     </DummyResult>     <Result>       <id>2</id>       <accountname>LITWAREINC\SanjayJ</accountname>       <userprofile_guid>19049F1B-B894-4671-BF8A-61C18FD9CABF</userprofile_guid>       <preferredname>Sanjay Jacob</preferredname>       <!-- remaining part is left out for brevity-->       <EveryOne />     </Result> </All_Results> 


Notice the DummyResult elements. Who came up with this element in the product team? smile_regular I want to meet this guy!

F03xx63

 

If you can't laugh with it, don't mind! Maybe it is a sign that I need a trip to Antartica when I start having fun all alone in my room with things like this. Yes, two more days and I am done for the rest of the year.

Wait... MS Press people, Mr. Barker... I'll still work, but no more teaching :).

Have fun folks!

 

A look back at TechEd and ITForum Barcelona

Participating at TechEd and ITForum has long been a tradition at U2U. I think it is now for the 9th time in a row that we have a booth at TechEd. It was again nice to have all of the U2U colleagues there for a week and besides having them learn as much as possible, also get to know them better during the social events. I do not spend a lot of time at the office, so there is plenty of catch-up for me on the socializing aspect during that week. Thanks again all you guys and girls for again a wonderful week and a great business year @ U2U. It was not the same without my friend David who died in the summer. I still miss him a lot these days but I know he certainly would be very proud about the Belgians doing a great job in Barcelona.

This year, David Gristwood kept me very busy during the week delivering 6 sessions of which three of them with my buddy Ted Pattison. It was great to talk to all of you. I enjoyed myself a lot during the delivery of the sessions and I hope that there was a lot you have learned. The interactive sessions on the best practices for building SharePoint solutions and the dos and don'ts for making SharePoint available in the company were great. I really enjoyed the last session where Ted played Jerry Springer with the microphone. Nice memories!

Last week, I also delivered my first IT Forum session talking about best practices for deploying OBA applications. Although too early probably for most people (not me for once :)), the delivery went great.

What's up for me now? Well, it is book writing time all the way until the end of January. Lars and I start making good progress with the chapters on the new forthcoming MS Press book titled 'Inside the Index and Search Engines: Microsoft® Office SharePoint® Server 2007'. I like the new cover layout for the upcoming MS Press books. We have two months to complete all of the chapters and as it seems, it is going to be a nice collection of my and Lars's experience on what you can accomplish with search inside MOSS 2007.

Last week I also met Mike Fitzmaurice. For those of you readers who have not seen Mike lately, he has gone through a though diet and looks fabulous. The new Fitz has arrived :). Fitz and I are preparing for our trip (expedition) to Antartica in 3 weeks. I am getting quite nervous about this, but it I know it is going to be the experience of my life.

Approximate search in Linq

Many applications have a search functionality. In such a case, we would like an approximate (or fuzzy) search, which returns all items that are similar (in stead of identical) to the item we're looking for. In the past days, I developped a library which implements this for Linq (Language INtergated Query) to objects. So, all you .Net 3.5 programmers out there that need approximate search, download this dll from our website and get started!

Basically, the library adds two methods to LINQ: ProximitySort and ProximitySearch. The first one sorts all objects in the collection based on how similar they are to a given object, so it returns a collection. The second one returns only the best match. Because of this limitation, it can apply some optimisations. In short, ProximitySearch is conceptually similar to ProximitySort(...).First(), only more performant.

Both methods are based on string comparison: they just apply the ToString method on every object in the collection, and then calculate a distance between this string and the string of the SearchTerm object. The distance used is the Lhevenstein distance, a variant of the edit distance. These distances are language independant, they work on names, product numbers,... . If you want to compare objects on some other string than the one returned by ToString, there is an extra overload which allows you to specify a string selector.

Let's show some sample code.

"A few minutes ago somebody called with a complaint on the order he made on 10/5/2007, his name was 'Peterson' or something like that..."

var allShoppers = from c in Customers
                  where c.Orders.OrderDate = "10/5/2007"
                  select c;
var peterson = allShoppers.ProximitySearch("Peterson", c => c.CustomerName);

First we retrieve all people who bought something on that particular day (allShoppers), then we filter out the single person who's customername looks most like 'Peterson'.

 

"On our website, customers hate that huge drop down box which contains all our product names, but when we let them select a product category first, the complain that they don't know in which category to look... What can we do?"

Product searchProduct = new Product();
searchProduct.Name = textbox.Text;
var similarProd = Products.ProximitySort(searchProduct).Take(10);

If Product has a ToString method that shows the productname, and textbox.Text contains the name of the product the user is searching for, then the similarProd collection will contain all the products who's name resembles the one the user typed. Show them in a drop down box and let the user select from that limited box.

Have fun with the linq extensions! If I find some time next week (I'll be at TechEd) I'll post a follow-up article with some best practises and timings.

Some cool Excel tips

One of the chapters that we give in the SharePoint courses is the Excel Services chapter. Before we publish an Excel report to SharePoint, I show some cool things in Excel and from time to time there is a student who knows some tips as well, like today.

Apparently these tips where available in the older versions of MS Excel but I didn't know them.

First one is selecting a table

1

How did I do it:

Use my mouse :-), because the shortcuts that I knew weren't very handy (with page downs and the end button ...). From time to time I end up on row twenty thousand ...

In the new version of Excel, when you select a table, Excel is going to wait for a small second before continue selecting. Normally that should be enough for you, off course this is depending on the distance between brains and your mouse-finger.

How am I going it to do:

She used a shortcut. She selected the first cell of the table and use the 'CTRL + *' key combination. Simple but very handy.

Dragging the selection to the last row

If you use a function and you want to copy it down, or in this demo you want to drag numbers down to the last row you can use a very helpful technique as well.

How did I do it:

Use my mouse (again), select the little square and drag it to the last cell

2

How am I going to do it:

Instead of dragging it, I'm going to double-click on that little square:

3

To end this article, something that I really like. If you have a lot of rows in a table and you scroll down, you loose the headers of the table:

4

You can fix this with having a fixed row, but I don't like that. In excel 2007 there is again great functionality. Actually it is like something very small but it is sooo very nice:

5

By selecting a column in the table and scrolling down, the names of the headers will be placed on top.

So, if you have more of these things, please let them know. It will help a lot of people.

K.

Update a SharePoint List with PowerShell

Some time ago I wrote an introductary post on Powershell and SharePoint 2007. Based on that post I recently got a question on how to run a WMI query to determine the IP address of a server and place the result in an IP field of a custom SharePoint list.

WMI stands for Windows Management Instrumentation and is the management infrasturcture for the Windows operating system. It also has an object model that you can use within a .NET application, but also from within PowerShell. A good starting point for using WMI within Powershell is the following tutorial:

http://www.powershellpro.com/powershell-tutorial-introduction/powershell-tutorial-scripting/

To test my sample code of this blog post I created a custom list with name "Servers" and some additional fields like IPAddress and CPUSpeed. 

When opening the PowerShell command prompt, the first thing you have to do is load the SharePoint assembly:

[System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.SharePoint”)

To get an instance of a certain SharePoint list, you could use Powershell code like the following: 

$siteUrl = "your sharepoint site url"

$webName = "your webname"

$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)

$spWeb = $spSite.OpenWeb($webName)

$spList = $spWeb.Lists["Servers"]

For querying WMI with PowerShell you use the get-wmiobject method. To get information about the computer processor you need to query the WMI namespace Win32_Processor. This namespace returns information about architecture, availability, name, cpu status, processor speed, devices, manufactures, etc.

$computername = "your computer name"

$processor = get-wmiobject Win32_Processor -computername $computername

To get information about the IP Adress you need to query the WMI namespace Win32_NetworkAdapterConfiguration. This namespace returns information about DHCP, the IP Address, DNS domain and service name of the queried server. 

$configs = get-wmiobject Win32_NetworkAdapterConfiguration -computername $computername

As for the calculation of the CPU speed, you can use the CurrentClockSpeed or the MaxClockSpeed property of the processor instance. As the data type of these properties are both unsigned integers, it will need some formatting before storing it in the SharePoint list:

"{0:0.##}" -f ($processor.MaxClockSpeed/1000) + " GHz"

As the CPU speed is also part of the processor name, you can also strip it out from the $processor.Name string.

Adding a new item to a SharePoint list using PowerShell is not so different from using the SharePoint object model from within a .NET application:

$spitem = $spList.Items.Add()

$spitem["Title"] = $config.HostName

$spitem["IPAddress"] = $config.IPAddress

$spitem["CPUSpeed"] = $cpuspeed

$spitem.Update()

When using the above syntax, pay attention that you use the internal name of the field. F.e. if you see in the SharePoint list view a column with title "CPU Speed", chances are great that the internal name of this field is "CPU_x0020_Speed".

Now putting it all together, the sample code looks like the following. You can place it within a foreach loop to loop through a list of all servers.

$siteUrl = "http://yoursharepointsite"

$webName = "yourweb" 

$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)

$spWeb = $spSite.OpenWeb($webName)

$listName = "your list name"

$spList = $spWeb.Lists[$listName]

$computername = "yourcomputername"

$processor = get-wmiobject Win32_Processor -computername $computername

$configs = get-wmiobject Win32_NetworkAdapterConfiguration -computername $computername

foreach ($config in $configs)

{

   $spitem = $spList.Items.Add()

   $spitem["Title"] = $config.ServiceName

   $ipaddress = $config.IPAddress | out-string

   $spitem["IPAddress"] = $config.IPAddress + "" 

   $spitem["CPUSpeed"] = "{0:0.##}" -f ($processor.MaxClockSpeed/1000) + " GHz"

   $spitem.Update()

}

 This is a screenshot of my test list after execution of previous code:

image

I hope this example is helpful. In case of questions, don't hesitate to post a comment.

Extending the Holiday Approval Workflow

Waaaaw, I didn't think that my previous post, Create e custom Workflow using SharePoint Designer, was going to be that famous :-), because a lot of questions already came up.

1. Can we directly put it in the calendar

Well in the action where you send the email you can add an action to add it directly in a calendar. You could even connect this calendar with MS Outlook.

setcalendar

2. Can you change the pages created by SharePoint Designer

Well even that is possible. In our workflow, two pages are created. A page to allow a user to start the workflow manually and the page where the Human Resource Manager needs to complete his task.

screen1

Open these pages and customize them to your own needs.

Keep the questions coming: kevin@u2u.be

K.