SharePoint Online CAML Query Filter Example with CSOM

In this SharePoint tutorial, we will discuss how to use CAML query filter in SharePoint Online with CSOM (Client-side object model) or C#.Net with a few examples.

Also, we will check what is CAML in SharePoint Online? How to use CAML query builder in SharePoint Online? Then we will see an example of how to use CAML query in CSOM in SharePoint Online.

I have also create a complete video tutorial on https://www.youtube.com/watch?v=que6ww96MiQ

Scenario

Recently, one professional who enrolled for my premium SharePoint Online Developer training course ask me how to use the CAML filter condition in SharePoint Online.

Here I have two lists:

  • User Emails (Which is the Master List and it contains a few email addresses)
  • Registration List (If the user presented in the User Emails list, then the user can be registered to this Registration list, and if the user is not presented in the above list then the user cannot be registered here)

Here is both the lists.

sharepoint online caml query
sharepoint online caml query

And the other list you can see below:

sharepoint caml query
sharepoint caml query example

So here I have created a windows application using Visual Studio 2019 which will have the below controls:

  • Textbox (Here user will enter an email id)
  • Button (User will click on the Submit button)
  • Label (If the email not presented in the User Emails list, then it will say you can not register, else it will save the email into the Registration List and displays a successful message).

The form will look like below:

sharepoint caml query builder
sharepoint caml query builder

So here, we will use CAML query builder to create SharePoint CAML query filter conditions. And we will use here C#.Net code (Client object model (CSOM)).

Here, when a user enters an email address, we will use the CAML query with CSOM to check if the email address is presented in the User Emails list or not.

And if the user exists, then we will insert the item to the Registration list with CSOM code.

What is CAML Query in SharePoint?

What is CAML query in SharePoint? CAML stands for Collaborative Application Markup Language and we use CAML to define queries against SharePoint list data or library data. And then we can use the SharePoint CAML query with CSOM or JavaScript or Server Object model. CAML is an XML-based query language.

Here is a SharePoint caml query example.

<Query>
   <Where>
      <Eq>
         <FieldRef Name='Title' />
         <Value Type='Text'>dummy@gmail.com</Value>
      </Eq>
   </Where>
   <OrderBy>
      <FieldRef Name='Title' Ascending='True' />
   </OrderBy>
</Query>
<ViewFields>
   <FieldRef Name='Title' />
</ViewFields>
<QueryOptions />

As a SharePoint developer, you can write the above SharePoint CAML query or we can use the SharePoint CAML query builder to write the query.

SharePoint Online caml query builder

We can use the U2U CAML Query Builder for SharePoint 2013 to write the CAML query for SharePoint 2013, 2016, SharePoint 2019, or SharePoint Online. This tool will be helpful to create and test SharePoint CAML Queries.

This is a free standalone application that we can install in your desktop and it is build with the client object model code. You just need to install Microsoft .NET Framework 4.5 (x86 and x64) in your system.

We can use the same SharePoint caml query builder to connect with SharePoint Online as well as SharePoint 2013, SharePoint 2016, and SharePoint 2019 sites.

If you are still using SharePoint 2010, then you can download, U2U CAML Query Builder Solution Package for SharePoint 2010.

Double click on set up and then you can choose from one of the below options:

  • Connect to SharePoint Online: Choose this option, if you want to connect to a SharePoint Online site and then choose Custom credentials. Then provide the username and password.
  • Connect to SharePoint On Premises: Choose this option if you want to connect to a SharePoint on-premises site like SharePoint 2013, SharePoint 2016, or SharePoint 2019.
sharepoint online caml query builder
sharepoint online caml query builder

Once you connect to the SharePoint Online site, you can see all the list and libraries from the SharePoint Online site. Select the list or library for which you want to write the query.

Once you select the list, click on the New query button or Query with ViewFields button to build your CAML query. You can see a SharePoint online CAML query example which is created using caml query builder sharepoint online.

sharepoint online caml query example
sharepoint online caml query example

This is how we can use the caml query builder sharepoint online, and you can check out more on CAML Query Builder for SharePoint 2013/2016/2019/Online.

SharePoint Online CSOM CAML query filter example

Now, let us check out the SharePoint Online CSOM CAML query filter example.

Here I am using Visual Studio 2019 to create the windows application.

Open Visual Studio 2019 and then click on Create a new project like below:

sharepoint 2019 csom caml query
sharepoint 2019 csom caml query

Then select Windows Forms App (.NET).

sharepoint 2019 csom caml query filter
sharepoint 2019 csom caml query filter

Then give a Project name, choose the location, and then provide a Solution name. Then click on the Create button.

sharepoint 2013 csom caml query
SharePoint 2013 csom caml query

This will create the solution.

Once the solution got created, first thing we need to add is the below two DLLs:

  • Microsoft.SharePoint.Client
  • Microsoft.SharePoint.Client.Runtime

If you have installed SharePoint Online Client Components SDK in your local system, then you can refer the above dlls from your system.

Or you can also refer the DLLs from the NuGet packages.

sharepoint online csom caml query
sharepoint online csom caml query

Then we need to design the form. Here from the Toolbox, I have added a textbox button and two labels.

And the form looks like below:

caml query builder sharepoint 2013 csom
caml query builder sharepoint 2013 csom

Then you can write the code on the button click and the complete code looks like below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.SharePoint.Client;
using Form = System.Windows.Forms.Form;

namespace SharePoint_Client_Demo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
           bool exists= IsEmailExists();
            if(exists==true)
            {
                InsertItem();
                lblResult.Text = "User registered successfully!";
            }
            else
            {
                lblResult.Text = "User email id not exists in the master list.";
            }
        }

        bool IsEmailExists()
        {
            bool isexists = false;
            using (ClientContext ctx = new ClientContext("https://tsinfotechnologies.sharepoint.com/sites/SPGuides/"))
            {
                try
                {
                    ctx.Credentials = new SharePointOnlineCredentials("bijay@tsinfotechnologies.onmicrosoft.com", GetSPOSecureStringPassword());
                    Web web = ctx.Web;
                    List list = web.Lists.GetByTitle("User Emails");
                    var q = new CamlQuery() { ViewXml = "<View><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+ txtEmailID.Text +"</Value></Eq></Where></Query><ViewFields><FieldRef Name='Title' /></ViewFields><QueryOptions /></View>" };
                    var r = list.GetItems(q);
                    ctx.Load(r);
                    ctx.ExecuteQuery();
                    if(r.Count>0)
                    {
                        isexists = true;
                    }

                }
                catch (Exception ex)
                {
                    isexists = false;
                }
                
            }
            return isexists;
        }

        void InsertItem()
        {
            using (ClientContext ctx = new ClientContext("https://tsinfotechnologies.sharepoint.com/sites/SPGuides/"))
            {
                try
                {
                    ctx.Credentials = new SharePointOnlineCredentials("bijay@tsinfotechnologies.onmicrosoft.com", GetSPOSecureStringPassword());
                    Web web = ctx.Web;
                    List list = web.Lists.GetByTitle("Registration List");
                    ListItemCreationInformation newlistitem = new ListItemCreationInformation();
                    ListItem item = list.AddItem(newlistitem);
                    item["Title"] = txtEmailID.Text;
                    item.Update();
                    ctx.ExecuteQuery();                   
                }
                catch (Exception ex)
                {
                   
                }

            }
        }

        private static SecureString GetSPOSecureStringPassword()
        {
            try
            {
                var secureString = new SecureString();
                foreach (char c in "**********")
                {
                    secureString.AppendChar(c);
                }
                return secureString;
            }
            catch
            {
                throw;
            }
        }
    }
}

Now, you can run the windows form application that will open the Windows form like below. You can see here, I enter an email id and it is displaying a message “User email id not exists in the master list”. Because the email id is not there in the master list.

caml query builder sharepoint online
caml query builder sharepoint online

Below screen, you can see I enter an email address and that is presented in the master list, so the email is added to the Registration list.

caml query builder sharepoint 2019
caml query builder sharepoint 2019

Also, if you will open the SharePoint Online list, you can see the email id entered in the SharePoint list.

sharepoint 2013 caml query examples
sharepoint 2013 caml query examples

This is how we can work with CAML query filter condition with CSOM in SharePoint Online.

You may like the following SharePoint tutorials:

In this SharePoint tutorial, we learned how to use SharePoint CAML query filter condition with CSOM. And also, we discussed:

  • What is SharePoint CAML query?
  • How to use caml query builder in SharePoint Online
  • How to create a windows application to work with CAML in SharePoint Online
  • SharePoint Online CSOM CAML query filter example
>