|
|
|
|
Building a CAML Query for the SharePoint Object Model
|
|
|
|
|
When this application is installed on the machine where SharePoint is installed, you can opt to build
a CAML query using the SharePoint object model. This query can then typically be used on an SPQuery object
that is executed against a list.
|
|
|
|
|
|
|
|
|
|
Building a Simple Query
|
|
|
When the U2U Caml Query Builder is connected to a SharePoint site (after you have entered a valid URL and clicked the
Connect button in the previous screen), a new form is displayed and a treeview is populated with all lists and document
libraries in the current site.
You can click on one of the lists in the preview. A new section is displayed in the form. The section contains a
listbox populated with all the fields of the selected list.
Select a field from the list. You can choose to use this field in the Order By clause or in the Where clause.
|
|
|
|
|
Order By
|
|
|
If you want to use the selected field in the Order By clause, click the first check box. A combo box appears
where you can decide whether you want to sort in ascending order (which is the default) or descending order.
This causes the user interface to add the field to the right treeview to give you a graphical impression of the query and
to build a CAML query.
|
|
|
|
|
Where
|
|
|
If you want to use the selected field in the Where clause, click the second check box.
This action causes a number of controls to appear:
- A dropdown list with the possible operators.
- A text box or dropdown list depending on the data type of the selected field.
Select an operator (f.e. Equals, Is Null or Starts With) and fill out a value on which you want to filter. If the
selected field is a choice field or lookup field, a dropdown list will displayed containing all possible values
retrieved from the SharePoint site. You will only be able to select one of these values. The data type of the
selected field is visible in a non-editable control.
Click the small arrow button next to the value control to add the field to the where clause. The selected field
will be added to the treeview at the right and the query will be build automatically.
|
|
|
|
|
Complex Where
|
|
|
You can also specify more than one filter condition. Select the field for which you want to add a second where condition. Click the
Where checkbox. Immediately a combobox appears where you can select “And” or “Or”. Fill out or select a value and click the
> button. Notice that a Where node is added to the CAML query.
You can continue like this for as many conditions you need to specify.
Take a look at the query: CAML has a specific notation for such complex queries.
<Query>
<OrderBy>
<FieldRef Name='LastName' Ascending='False' />
</OrderBy>
<Where>
<Or>
<Or>
<Or>
<BeginsWith>
<FieldRef Name='LastName' />
<Value Type='Text'>E</Value>
</BeginsWith>
<BeginsWith>
<FieldRef Name='LastName' />
<Value Type='Text'>R</Value>
</BeginsWith>
</Or>
<BeginsWith>
<FieldRef Name='LastName' />
<Value Type='Text'>T</Value>
</BeginsWith>
</Or>
<BeginsWith>
<FieldRef Name='LastName' />
<Value Type='Text'>Z</Value>
</BeginsWith>
</Or>
</Where>
</Query>
|
|
|
|
|
Using Dynamic Parameters
|
|
|
If you want to make the value for the filter dynamic, you can insert in the value
field the name of a parameter enclosed between []. The name of the parameter is free,
just use the brackets so that the application knows that you want to go dynamic.
ps. In that case you will have to use the U2U.SharePoint.CAML.dll within your code and
this to be able to translate the parameters into the values you (or your users) provide
dynamically. For a detailed description about using the dll from within your code, If refer to the
Using your Query in Code section.
In the test pane, you can then give a value to this parameter. Move to the second
tab and click parameters. The Get Parameters button can be used to fill up the
datagrid with your parameters.
|
|
|
|
|
Working with DateTime values
|
|
|
Until now, when selecting a field of type DateTime, the only possibility was to
select a certain date. When testing the query, this date was always used "as is". Now
supplementary controls have been added to do calculations with dates.
Add or subtract days, months or years from the selected date
You can select the first option to add or subtract a certain number of days, months or years from
the date selected in the date picker.
As you can see, the value in the CAML query is enclosed within brackets. When testing
the query, the information between the "[" and "]" characters are translated
into a real date in the Builder class of the U2U.SharePoint.CAML.dll before the
query is passed to the U2U.SharePoint.CAML.Server.dll (when using the object model)
or the U2U.SharePoint.CAML.Client.dll (when using the SharePoint Web Services).
This means that if you want to use this type of queries into your code, you will
need to integrate the U2U.SharePoint.CAML.dll into your project.
Add or subtract days, months or years from today
You can select the second option if you need to calculate a date from today.
Add a parameter
If you need more complicated calculations you can choose the third option.
|
|
|
|
|
Query by Name or By ID
|
|
|
In most of the cases list items are retrieved using the <FieldRef Name="x" />
syntax. But there are cases that you need to query by ID. In that case you can set the
Query by ID option in the upper right corner of the form.
Your query will look like the following:
Keep in mind that when you already built a query the default way and then choose to build
the query by ID, your query will be cleared and you will have to start over again.
|
|
|
|
|
Using your Query in Code
|
|
|
You can copy/paste the CAML query from the user interface into your code. Don't forget to
remove the <Query> and </Query> tags when working with
SPQuery, otherwise your query will be ignored and all list
items will be returned.
You can also use the dlls that are part of this application for retrieving information
from SharePoint. In that case I refer to the
Using the CAML Query Builder DLLs in your code
|
|
|
|
|
Continue with Building a CAML Query for the SharePoint
Lists Web Service.
|