I discovered an interesting error recently while working with Microsoft’s Collaborative Application Markup Language (CAML) that, surprisingly, had received no ink. Partly what surprises me about this is that the error may require you to rewrite large sections of your code if you haven't previously considered this SharePoint limitation. I'll start with some context, but first of all the error is:

“Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries,”

So let's back up and describe what CAML is for everyone who isn’t yet experiencing this problem.

CAML is an XML based query language that is similar to, and serves many of the same purposes as SQL and DDL. However, CAML is used primarily to interact with SharePoint lists. You can retrieve data using something like:

Which is equilivant to WHERE Title = ’hello’ in a SQL statement. At this point I’m sure you’re asking why exchange 80 characters for 21? I suppose the answer is related to how CAML is primarily used in web services requests. Regardless, the nested too deeply error occurs when you try to insert large amounts of data into a list.

The insert syntax is actually all batch based, so if you have 100 list items to insert into a list, you build them all into one big CAML statement and then send it across the wire with a call to Lists.UpdateListItems(). Your CAML statement will look something like the following:

The problem comes when you want to insert something like 19,642 list items into a list. SharePoint complains with “Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries,” and suddenly you’re stuck with an enormous CAML statement that you need to break up into smaller batches. How small? Well, for me 300 worked and 500 didn’t, so I set my batch size to 300.

This means that if you are currently experiencing this problem, then have fun rewriting your code to use smaller batches. And, if you're lucky enough to be reading this before you experience the error, then prepare yourself now for this possibility.

Or, if performance isn’t an issue and you don’t want to or can’t rewrite the code that built your CAML then feel free to use this code to batch up CAML batches into smaller batches.

Useful MSDN References

Incidentally, these MSDN references might be useful if you’re interested in more info:

Introduction to Collaborative Application Markup Language (CAML)

CAML Reference: Query Schema

CAML Reference: Batch Element

I’ve just started reading the blog of Scott Guthrie who is a general manager at Microsoft and is currently writing about the new C# 3.0 (code named Orcas). The post I felt strongly about describes three new language features including:

• Automatic Properties
• Object Initializers, and
• Collection Initializers

All three features are purely syntactic sugar: they add nothing of real value to the language (unlike generics in C# 2.0 for example). One features is wonderful, while two are terrible.

The Sweetest Syntactic Sugar Ever

Automatic properties are fabulous. I’ve always grumbled about the number of lines required to create simple properties. Anything that can turn:

Into:

Gets my vote.

Sickeningly Sweet

Perhaps I’m a purest, but with more language conveniences comes: a steeper learning curve because of a larger language, and decreased readability because of the numerous ways of doing the same thing. Picture reading this in someone else’s code:

Who would write this crap? Everyone else, now that Microsoft lets them. I vote for the verbose, the readable, the single approach:

Hmmm, and by the way did you count the number of lines?

Conclusion

So do the differences between my thoughts on automatic properties contradict with my thoughts on object initializers and collection initializers? They don’t. Basically a little sugar is good, but too much ruins the dish.

The topic of parameter passing in C# keeps coming up, and so I thought this post might be useful. It's been my most popular post on my rapid application development blog, so I thought I'd repost here. Please kick the post if you like it.

So there I was, merrily browsing the Internet when I came across this fantastic site on topics such as:

• Implementing a singleton pattern in C#,
• Type initializers,
• Static constructors,
• Delegates and events,
• And as my title suggests: Parameter passing in C#.

In short, all those things you rarely need to know to get your job done, but that separate mediocre developers from good ones. The site is by Jon Skeet and the articles are informative, well researched, well explained, well written. Here's the C# part of his site:

http://www.yoda.arachsys.com/csharp/

The article that caught my attention made sense to me, but being a very visual person I couldn't help but think that some pictures could really help illustrate the points. So without further ado, I illustrated the article. You probably don't need to read the article to understand this post - but you should:

http://www.yoda.arachsys.com/csharp/parameters.html.

Note: you can click the images to get a clearer view.

1. Value Types

Notice that the values live inside the box which will not be the case for reference types. Also, the assignment operation copies the value inside of the box, this is important to compare with reference types.

Quiz: What is the result of the WriteLine statement?

Answer: 5

2. Reference Types

Variables that hold reference types actually hold a reference to a location in memory (on the heap). So assignment operations copy the address. Notice this is still consistent with diagram #1, the copy operation copies the value inside of the box.

Quiz: What is the result of the WriteLine statement?

Answer: hello world

3. Immutable Reference Types

Immutable reference types like strings behave just like regular reference types except they don't provide a way to change their value.

Quiz: What is the result of the WriteLine statement?

Answer: hello

4. Value Types Passed by Value

Passing a variable to a function by value is equivilant to instantiating a new variable and assigning it to the first (well, ignoring scope issues and such). Notice that the diagram below is nearly identical to diagram #1.

Quiz: What is the result of the WriteLine statement?

Answer: 5, same as #1

5. Reference Types Passed by Value

In #4 I said passing a variable to a function by value is equivilant to instantiating a new variable and assigning it to the first. Is that still true of reference types? Yup. And did you notice there's an implicit assignment statement when passing by value? As you'll see shortly there won't be when passing by reference.

Quiz: What is the result of the WriteLine statement?

Answer: hello world

6. Value Types Passed by Reference

Passing by reference doesn't involve an implicity copy, instead it instantiates the inner variable to the address in memory of the outer variable. Then all references to the inner variable are implicitly dereferenced for you and voila, magically you're changing the value of the outer variable.

Quiz: What is the result of the WriteLine statement?

Answer: 10, and notice how different the diagram and results are than #1 and #4.

7. Reference Types Passed by Reference

Really this is no different than value types passed by reference (#6), except calling sb.Append() from an inner variable is dereferenced once to get to the outer variable and again because the outer variable is itself a pointer.

By the way, when you get to the section in Jon's article called:

"Sidenote: what is the difference between passing a value object by reference and a reference object by value?"

Please read it carefully, it's an extremely good point. It can be sumed up by comparing the final assignment statement above (Reference Types Passed by Reference) to the final assignment statement in in diagram #5 (Reference Types Passed by Value). It's a subtle, but important difference.

Oh and the quiz, what is the value of the Console.WriteLine in #7?

Answer: NullReferenceExceptinon – Object reference not set to an instance of an object

Still confused? Then I didn't do my job right, since this is the point in the article when I thought pictures would help. So please post your thoughts whether it makes sense or not.

- Lee

In the previous post in this series (Multi-Value Columns in SharePoint Designer - Solution #1), I described a problem where SharePoint Designer can’t send e-mail to multiple recipients if those recipients exist inside of a multi-value column in a SharePoint list. The simple hacky solution I described was to temporarily turn the column into a single value column just for SharePoint Designer. But this approach has problems, and there is a better way: developing custom activities in Visual Studio for use in SharePoint Designer.

In this post I will describe how to develop a custom activity in Visual Studio that will also solve this problem and I will also describe how to install it on a SharePoint server so that SharePoint Designer clients can automatically download and use it. Before I do let me back up and tell you why, from my perspective, this approach is probably not the best way to go.

SharePoint Designer is a Microsoft Office product that replaces FrontPage, integrates tightly with SharePoint, and allows non-developers (aka “knowledge workers” in the Microsoft lingo) to create simple workflows without writing any code.

The problem is that simple workflows and multi-value columns are like oil and water: not so compatible. If you’re using multi-value columns then your knowledge workers should admit defeat and let developers create the workflows in Visual Studio using the Windows Workflow Foundation (which, incidentally, will be the topic for third article in this series).

Still, you may have a good reason for continuing to develop workflows in SharePoint Designer, and I’ve already gone through the pain of writing and installing custom activities, so hopefully this post will make life easier for someone somewhere.

Creating the Custom Activity in Visual Studio

The code to create the custom activity in Visual Studio is the most interesting part of this solution. Make sure to check out the GetEmailAddressesFromUsernames() method if you have time to review the code. Here is the procedure assuming this is your first time working with Windows Workflow Foundation in Visual Studio.

1. The first step is to download and install Visual Studio 2005 extensions for .NET Framework 3.0 (Windows Workflow Foundation) which will add workflow options to Visual Studio.

2. If you aren’t running on Windows Server 2003, then you probably need to install the Windows Workflow Foundation DLL’s. You’ll know there’s a problem if, after creating the project in Visual Studio, your SharePoint references are invalid. I picked these DLL’s up from my Windows Server 2003 machine (actually a VMWare virtual machine) from the following location:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI

You should install these to the Global Assembly Cache (GAC) to make Visual Studio happier, although you may still need to reference them for your project. The easy way to install to the GAC is just to copy them to somewhere on your local (non-W2K3) machine and then drag them all over to C:\Windows\assembly.

3. After completing step #1 when you open Visual Studio you should get a new tree node under “Visual C#” (or Visual Basic) called “Workflow.” Select that, then “Workflow Activity Library” then call the project something like “MultiRecipientMail”.

4. Visual Studio should automatically create a blank activity called Activity1.cs. If you want a more reasonable name then delete it and “Add” a “New Item” of type “Activity” called something like “MultiRecipientMailActivity.” Don’t make the same painful mistake as me: name your activity something other than the name of the project or anything in the namespace.

5. At this point you could drag and drop a “Replicator” activity on the design surface and put a “Send Mail” activity inside, but I’ll cover that in my next post. For now I think code is the clearer way to go. Hit F7 or right click and “View Code” and paste the following which I’ll try to comment in-line rather than in this article.

(for the code see the original article Multi Value Columns Solution #2 - Custom Activities in SPD on BlogSpot)

You’ll need to substitute your own public key token from step 9 in the “Assembly=” statement.

12. Next you need to tell SharePoint designer clients to trust this new dll. To do so open the Web.Config in the virtual directory for your site (e.g. C:\Inetpub\wwwroot\wss\VirtualDirectories\34089\ web.config). Find the section called “” and add the following new line (substitute your own public token):

(again substitute your own public key token)

13. Restart IIS by opening a command prompt and typing “iisreset.” SharePoint will pick up the new MultiRecipientMail.ACTIONS file, find the dll in the GAC, and provide the new information to any SharePoint Designer clients that access the site.

14. Open SharePoint Designer and either navigate to the “Workflows\Multi Recipient Email.xoml” project from my previous article or create a new workflow project based on a list with a multi-value column. You should get a message like the following while SharePoint Designer downloads the new dll:

15. If everything worked correctly you should now be able to 1. click Actions; 2. More Actions; 3. Select the “Custom Actions” category from the dropdown (this category came from the .ACTIONS file in Step 11); and 4. Select the “Multi Recipient E-mail” action. If it doesn’t show up in the Workflow Designer page after clicking "add", then you probably referenced it incorrectly in the Web.Config.

16. Now you should be able to 1. click the Fx button next to “To;” 2. Select your multi value column (e.g. Peers To Review from my previous article); 3. Hit Ok; and 4. Fill in the remaining fields with values.

17. Now if you head back to Sharepoint, select the dropdown of a list item in the list associated with your workflow, click “Workflows”, select the workflow you created in SharePoint Designer, and click “Start” you should receive an e-mail at each address in your multi-value column.

Well … at least it worked for me. :) I did kitchen test this article, but please leave comments if it doesn't work for you.

Redeploying

If you want to make any changes to the code the steps to redeploy are:

1. Compile (you don’t need to sign again, that’s one time only)

2. Copy the dll to your W2K3 server

3. Re-install the dll to the GAC. You can just re-copy it over to c:\windows\assembly if you like.

4. Restart iis with an “iisreset” from the command line (no need to change the .ACTIONS or Web.Config files)

5. Finally, if you need to make changes to your workflow, you may need to restart SharePoint Designer to download the new dll (I usually have to).

You’re Done! Easy huh?

So you should now have a reusable component that non-developers can use in SharePoint Designer to send e-mail to multiple recipients as determined by a multi-value column in a SharePoint list. Of course if you decide that developing the workflow in Visual Studio is better, then the third article in this series may be for you.

Recently I’ve been working with Microsoft Office SharePoint Server (MOSS) 2007. Since this is my first post on the topic I’d love to start at a high level about what it is and how it works, but let’s get to the interesting stuff: what doesn’t work well and how to get around it.

Specifically, this will be the first in a series regarding a deficiency in the workflow component of SharePoint that doesn’t allow you to send e-mail to multiple recipients from a multi-value column of a list using SharePoint Designer 2007 (SPD).

This first post will describe the problem, provide lots of screenshots so it can double as a fast introduction to workflows in SharePoint for the uninitiated, and then provide a quick hacky solution.

The Multi-Value Column Problem

Here’s how it should work. You create a list (e.g. Performance Review) and add a column (e.g. “Peers To Review”) and select a type of “Person or Group” with “Allow multiple selections” set to Yes.

(click any image to enlarge)

Now if you add a blank workflow in SharePoint Designer (from the SharePoint Content tab):

And you select the list you created (Performance Review):

Then you should be able to

1. Add a “Send an Email” action
2. View the message’s properties
3. Select the recipient
4. Add a function by clicking “Workflow Lookup”
5. Select the Current Item (aka the current list item, which is like a row in a spreadsheet and in my example this would be the current performance review that the workflow is being run on)
6. Then select the new column “Peers to Review”

But wait. Where’s your column? And here is the problem. The UI of SharePoint Designer filters out all columns that are marked with “Allow multiple selections.”

Simple Hacky Solution #1

Believe it or not the Workflow Engine knows how to send e-mail to a column with “Allow multiple selections” but it’s the SharePoint Designer UI that doesn’t. This means that a simple solution to the problem is this:

1. Turn off “Allow multiple selections” for your column (note the warning “This will remove all person values except the first one”)
2. Close and reopen the workflow in SharePoint Designer and follow steps above and magically your column will appear in the dropdown
   
   
3. Finish creating your workflow
4. Turn back on “Allow multiple selections”
5. Create an item for the list and run the workflow you created in SharePoint Designer

And voila, it works! Both (or all) recipients have received an e-mail.

Sadly, this simple technique isn’t an acceptable solution for the long term. What happens if you want to change your workflow down the road? You may need to turn “Allow multiple selections” back off, thus deleting your multi-user data. A better solution is to create a custom action in Visual Studio and get it to plug into SharePoint Designer. And that will be the topic of my next post:

Multi-Value Columns in SharePoint Designer - Solution #2

---

Note 1: this series is largely in response to the several users having similar problems at: http://forums.microsoft.com/MSDN/showpost.aspx?postid=1443799&siteid=1

Note 2: If you’re interested in the ERD for the topics in this SharePoint post this diagram might be useful to get you up to speed:

Note 3: This is double posted at http://rapidapplicationdevelopment.blogspot.com/2007/04/multi-value-columns-in-sharepoint.html

It seems as though as software developers mature they develop consistency in their approach to just about every aspect of their work, regardless if there is a good reason for adopting a particular practice or not.

For instance, in data modeling I developed the habit of always naming my tables in the plural – Employees instead of Employee, and such. There’s no reason for this convention, other than perhaps I copied what I saw from the Northwind database.

But it’s important to question these practices from time to time, and after over seven years of doing things the same way I have decided to make a change. And for the second time now (see my post The Importance of a Logical Data Model), it was a colleague: Steve Dempsey who initiated the change. So why would one opt for singular names over plural ones?

Developers might chose singular names because they are shorter and require less typing, but this argument never held for me because of tools like intellisense and code generation (not to mention touch typing). But Steve is extremely adamant about singular names for a different reason: because of relationship readability.

For instance, in Sharepoint, workflows relate to events. Specifically, a workflow (singular) is initiated by one and only one event, and an event (singular) can initiate multiple workflows, as is expressed below:

The objective of modeling is thus to express the relationship of a single entity (a workflow, an event, or whatever) to zero or one or many of another entity. So why not just name your entities appropriately in the first place: by making them singular?

Of course now the problem is getting an old dog to remember his new trick. Or is it tricks?

---

note: I am now double posting my blog entries, this post is also available on Blogspot.