Querying Data with the CAML Query Builder V2
It has been a couple of months now since we have released the first version of the CAML Query Builder. Thanks a lot for the very positive feedback we have received from you all. Do remember that this is side-project of our CAML girl and not a fully commercial product. So we do our best to get everything working and support you with any questions but sometimes you have to be patient. Sorry for that.
Today, you can download version 2 giving you the following additional features on top of what you find in version 1. You can learn about the v1 features by reading my first posting on the CAML Query Builder tool over here.
So, what is new in v2?
- You can now choose whether you work in object model or in Web services mode. It means that you now can use the app on the SharePoint machine itself and on a client accessing the SharePoint content via the Web services.
- When working with the object model, you can only query for content. If you opt for Web services mode, you can query but also now update content in a SharePoint list using CAML.
- You now also have the possibility to make use of parameters and give these parameters a value at runtime when using the CAML Server Helper to execute the saved CAML query string.
The new version is downloadable using the following link: http://www.u2u.info/SharePoint/U2U%20Community%20Tools/CamlCreatorV2.zip.
Update: Always go the library itself to download the latest version available.
And don't forget that you find on http://www.u2u.info/SharePoint some other goodies to play with.
Now before you start looking at the tutor, read my previous posting and the article on using the SmartPart.
Just like before, I will illustrate the use of the CAML Query Builder and the new features using a small example. I will make use of the same SharePoint list as before showing the AdventureWorks products.
U2UCamlCreator.exe starts the query builder application. You can set the credentials and also now choose the mode you want to work in.
Getting only the fields you need
V1 of the CAML Query Builder retrieved always all of the fields from the list. This version here allows you to select the fields you want to get returned from the SharePoint site. Just right-click on the list you want to work with in the treeview. Note that this only works now for the Web services mode.
Using the View Fields tab and the listboxes you can then define the fields you want to work with. Clicking on the Query tab allows you to define the query then as with the V1 version.
And of course, you can test it again.
Dynamic Parameters
If you want to have the filtering value dynamic, you can now insert in the value field the name of a parameter enclosed between []. If you follow my steps, you can click now in the treeview displaying the results of your actions on the Model node.
The name of the parameter is free, just use the brackets so that the CAML girl knows that you want to go dynamic.
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.
And of course, you can test again your query.
Executing the Query in your Code
The CAML builder allows you to store the query as a file. Just use the File | Save File menu item. Once it is saved, we can use the server component of the CAML builder to have this query executed within our code. Just like the previous tutor, I have a small ASP.NET user control that I am going to host in the smartpart. Except now I have a text box where we can enter a search string.
Follow the steps discussed in the previous tutor to set the reference and then create an object of the CAML Helper.
You need a reference to the following assemblies:
- U2U.SharePoint.CAML.dll
- U2U.SharePoint.CAML.Server.dll
Since we are having one parameter, you need to first create an array of CAMLParameter objects and add to it one CAMLParameter object.
Just like before, the ExecuteQuery method returns you a ADO.NET DataTable you can immediately bind to your DataGrid.
U2U.SharePoint.CAML.Server.CAMLHelper helper =
new U2U.SharePoint.CAML.Server.CAMLHelper
(Server.MapPath("~/UserControls/products_v2.caml"));
U2U.SharePoint.CAML.CamlParameter[] pars = new U2U.SharePoint.CAML.CamlParameter[1];
pars[0] = new U2U.SharePoint.CAML.CamlParameter("Model",TextBox1.Text);
DataTable dt = helper.ExecuteQuery(pars);
DataGrid1.DataSource = dt;
DataGrid1.DataBind();
The result can be displayed in the Web part:
You can also use the CAML Query Builder now to construct your query string to do update/new/delete operations via CAML. I will give a sample and some steps to follow hopefully later this week. It is getting late here in Oslo and tomorrow is again working day :-). 2 days in Oslo and then going all the way from Northern Europe to South-Africa for 2 more days on Portals.
Have fun with it! You can send questions, feedback, bug reports either to me (patrick@u2u.be) or directly to our CAML girl (karine@u2u.be).