This version contains following enhancements:
- Code snippet generation
- DateTime enhancements
- Query options for SPQuery
- Enhancements for Calendar lists
You can download the new version from here.
You can read the documentation here.
Remark: The U2U Caml Query Builder previously was part of the U2U SharePoint Solution Package. If you installed this solution, you will have to retract and delete this solution before you can install the new solution. The other features like U2U List Properties and U2U Site Properties will be available soon in separate solutions.
Code Snippet Generation
With this feature you can build your CAML queries in a WYSIWYG way. When you click the Preview button, a Result section opens showing a text box with the query and a datagrid containing the rows resulting from the execution of the query. Until now the text box only contained the CAML but there has always been some confusing about which tags to add where. So I thought it would be nice to generate code snippets that show you the proper use of the generated CAML. You can copy the code snippet, which is a complete function, and paste it right away in your code! You only have to pass the SPList object on which you want to execute the query.
You can now choose if you want to generate code in C# or VB.NET or get the CAML back as before.
If you choose to have a code snippet in C# or VB.NET, you can choose if you want to get a DataTable returned or an SPListItemCollection. I'll show you the difference with the query I often use on the Employees table I have in my SharePoint site. The pure CAML looks like this:
If you ask to have a code snippet in C# returning an object of type DataTable, the code snippet looks like this:
If you ask to have a code snippet in VB.NET returning an object of type SPListItemCollection, the code snippet looks like this:
The preview page which is accessible from the item Edit Control block within the CAML Query dedicated list contains the same control.
One small note: if the Preview section doesn't open up when you clicked the Preview button, check if you filled out a name for the query at the top of the page.
Also this enhancement will be added to the windows version soon.
Date and Time enhancements
Until now I thought (and I'm sure I'm not the only one) that the time part of a date was always ignored by a CAML query when retrieving rows from a list using SPQuery or the GetListItems method of the Lists.asmx web service. I just recently read in a blog post that you CAN query time values so I decided to include it in the feature version of the U2U CAML Query Builder. A new version of the windows version will follow soon.
Let's say you have a table with date/time values and today we are the 5th June.
Open the CAML Builder via the Actions button on the list. Expand the filter on section and select the DateTime field. Choose an operator f.e. Greater than. Then you have 3 options:
If you choose Today, all dates greater than today's date will be retrieved:
If you choose Now, then the query will take the time part into account and also todays rows with a time value in the future will be retrieved:
Take a look at the CAML query: an extra attribute is added to the DateTime field: the IncludeTimeValue attribute instructs SharePoint whether to take care of the time value or not:
<Where><Gt>
<FieldRef Name='TestDate' />
<Value IncludeTimeValue='TRUE' Type='DateTime'>2008-06-05T11:11:42Z</Value>
</Gt></Where>
You can also work with a fixed date. In this case you can also work with a time part or not: if you leave the 12 AM unchanged, the IncludeTimeValue attribute will not be added in the CAML and the query will not take a time value in account.
If you change this, the feature will add the IncludeTimeValue attribute and the query will take the time value into account when retrieving the rows.
Query options for SPQuery
Euh? you will say... yes, query options for SPQuery. Most of the query options you can pass to the GetListItems method of the Lists.asmx web service can be applied to properties of the SPQuery object. But a lot of developers don't know all the different possibilities and properties of this SharePoint object so I decided to shed a light on it and add a section called Query Options. When you have made your choices and click the Preview button, the code snippet in C# and VB.NET will reflect these choices. The pure CAML will not because the QueryOptions node is not part of the CAML that can be used with SPQuery.
This is the complete Query Options section:
You can limit the rows returned in the result set by setting the RowLimit property.
But lets start with an easy one: IncludeMandatoryColumns. Check this option in the Query Options section.
In the View Fields section I only indicate FirstName, LastName and Phone. As EmployeeID and EmailAddress are defined as required in the SharePoint List Settings, they are also returned in the result set:
There are also some options to query folder. If you don't check the Folder option, only the root folder is queried. If you want to query also the sub folders you have to set the Look in all folders and sub folders option:
When clicking the Preview button this causes the query.ViewAttributes property to be set to "Scope = 'Recursive'".
You can also query a sub folder. In that case you have to check the Specify a folder option. The text box will become available and will contain the url of the root folder as initial value:
When clicking the Preview button this causes the query.Folder property to the specified folder.
To conclude an example of the Query option ExpandUserField:
If your list is of type Agenda, you can set the Meeting Instance ID to return only rows of a certain meeting instance:
When you click the Save button, the query will be saved to a dedicated list for the CAML Query Builder. The query options will be saved as an xml node. If you want to see the code snippets again, navigate to the Caml Query List which is a list that stores. This list does not show up on the Quick Launch but can be accessed via the View All Site Content button.
... and choose Preview CAML Query from the Edit Control Block.
A page opens where you can view the code snippets. The query is executed and the resulting rows are displayed in a datagrid.
Enhancements for Calendar lists
CAML queries for calendar lists are a bit more complicated, especially when working with recurring events. You can execute a normal CAML query on such a list and that's ok for normal events. But you will never get back the entries that are created based on a recurring event.
If your list is a Calendar, you will have to more options in the Query Options section:
If you check the Expand recurrences option also the instances created for a recurring event will be returned in the result set.
You can choose a fixed day from a date picker or you can choose Today. In that case the query will take the date on which the query is executed into account. You can also choose to see the recurring instances for a day, a week, a month or a year. The tool sets the CalendarDate property of the query and adds a DateRangesOverlap part to the Where clause.
The generated VB.NET code snippet then looks as follows:
In my example I have an event that occurs twice a week. These are the rows returned by the query when asking for a monthly overview as of the 1st June.
Note that each instance of a recurring event has the same ID as the recurring event that produced it. To learn more about the internals of a calendar list, read this excellent blog post.
Hope you like these extensions!