U2U CAML Query Builder Feature

Building a simple query

This feature has been developed to ease the work of a SharePoint 2007 developer. It consists of an application page where you can build a caml query. The caml query itself is stored in a list from where you can further work with it.

CAML (Collaborative Application Markup Language) is an XML-based query language that helps you querying, building and customizing Web sites based on Windows SharePoint Services. The XML elements define various aspects of a WSS site. This tool will help you build your CAML Queries.

Open the list or document library for which you want to build a CAML query. Click on the Actions button and there you see a new feature: Build a CAML Query.

This brings you to a page where in the future you will be able to choose between different types of CAML queries but at the moment you can only choose to build a basic CAML query for the  SPQuery object. Click this hyperlink to open the basic CAML Query builder page. Select the fields you want to show…

Define a sort order….

Then there is the Filter On section where you can define one or more conditions. In case of a text field or a number, you get a textbox to fill out a value.

Building the Where clause

The value control changes in function of the data type of the field you choose. For example, if you choose to build a Where clause based on a choice field or a lookup field you will get a populated drop down list from which you can choose a value.

In the example below the selected field is of type User. In that case a standard SharePoint people picker is displayed to let you choose a user. For a yes/no field a checkbox is displayed. Leave it unchecked for false value, check if for a true value.

I also want to draw your attention on DateTime fields which cause a lot of problems to SharePoint developers. When selecting a column of type DateTime in the Filter On section, you can choose between fixed date, Today and Now.

Also fields based on business data can be queried now. But pay attention with custom field types. You can build queries using custom field types in the where clause, for those who derive from simple data types like text, number, yes/no etc. Those who derive from SPFieldMultiColumnValue still are an issue that need to be solved.

When filtering on a field of data type ModStat, a combobox with all possible values will be displayed (instead of a text box).

Building a Where clause with a DateTime value

If you choose Fixed Date, a calendar control will appear.

You can also specify an time value.

You can also opt to add or subtract a certain number of days from the selected date. In case of a fixed date, the new date will be calculated.

In case you choose Today,

an offset of days will be added to the query:

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>                            
                            

Add more than two filter to the Where clause

In the Filter On section fill out a first filter. If you want to add a second one, click the Add a column to the where clause hyperlink:

This adds a second filter to the Filter On section. You can add as many filters you need and indicate whether you want to Or or to And.

Click on the Preview button to see the actual query and the resulting data after running the query against the list:

You can save your query and it will be saved to a specific CAML Query List from where you can use and preview your queries.

Filter and sort by field ID

In the previous example the query was filtered by field name: 
<FieldRef Name='LastName' />

But you can also execute a query with a filter and a sort by ID. To achieve this, there is a new section added under the query Name section:

The default is query by name but you can choose the option Query by field ID. The user interface stays the same but the previous query looks like this while the resulting data is still the same (don't forget to press the Preview button again):

As you can see in this query all FieldRef elements in the Order By and the Where clause have an ID attribute, except the FieldRef elements in the ViewFields part. The weird thing is that when I replace the Name attribute with an ID attribute, only the ID field (and some other required fields) is returned. It seems ViewFields cannot handle the <FieldRef ID='[guid]' /> construct.

If you use this query against an SPQuery object, you have to:

• remove the <Query></Query> tags (I know this already caused a lot of confusion among the developers community but I need a root in an XML document)
• Assign the ViewFields node to the ViewFields property of the SPQuery object.
• Assign the Where and Order By node to the Query property of the SPQuery object.

 

The Preview section

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.

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 theLook 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:

Enhancements for the Calendar list

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 of type Calendar, you will have two 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.

Reviewing an existing query

When you click the Save button, the query will be saved to a dedicated list for the CAML Query Builder. The query, the view fields and the query options will be saved as an xml nodes. If you want to see the code snippets again, navigate to the Caml Query List which is a list that stores the CAML queries. 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.

 

 

 

This article comes from :http://www.u2u.be/res/tools/sharepointcamlquerybuilder.aspx