Console Application To Fetch SharePoint List Data Using REST API With CAML Query In C# Managed Code

Introduction

SharePoint 2013 has many restful APIs that can fetch data from lists. However, sometimes, we need to use a CAML query to filter out the data. SharePoint Rest API has the option to pass a CAML query as data to an API call. The below steps will help you to call a Rest API with CAML query filtering.

In addition, this article will help you to understand how to call REST API POST methods in console applications using C# managed code.

The below example calls a REST API to retrieve the data from the Department SharePoint list. The department list has a Title column, which refers to the department name. CAML query will be used to filter the Department list data by Title (Department name).

REST endpoint

/_api/web/lists/getbytitle('Department')/GetItems

CAML Query Structure

CAML query structure should be strictly followed as below.

<View>
  <Query>
    <CAML_Query>
    </CAML_Query>
  </Query>
</View>

Assembly References

1. Net: To make a request to a Uniform Resource Identifier (URI).
2. Web.Script.Serialization: For serialization and deserialization functionality
3. IO: For reading and writing streams.

Request Header for POST Request

The API endpoint is of type POST. Hence, the REST header should be the same as other post requests.

oWebRequest.Accept = "application/json;odata=verbose";
oWebRequest.ContentType = "application/json;odata=verbose";
oWebRequest.Headers.Add("X-RequestDigest", GetFormDigest());
oWebRequest.ContentLength = stringData.Length;

Steps

Please follow the below steps to create a console application.

1. Open Visual Studio.
2. Select the Console Application template and provide a suitable name.
3. Add required assemblies.
4. Add the below code to the Program.cs file.
5. Run the code.

   using System;
   using System.Collections.Generic;
   using System.IO;
   using System.Net;
   using System.Web.Script.Serialization;
   namespace ConsoleApp1
   {
       class Program
       {
           static void Main(string[] args)
           {
               string sConnStr = "<web_url>";
               Uri oUri = new Uri(sConnStr + "/_api/web/lists/getbytitle('Department')/GetItems");
               string sResult = string.Empty;
               string stringData = "{'query' : {'__metadata': { 'type': 'SP.CamlQuery' }, 'ViewXml':'<View><Query><Where><Eq><FieldRef Name=\"Title\"/><Value Type=\"Text\">HR</Value></Eq></Where></Query></View>'}}";
               HttpWebRequest oWebRequest = (HttpWebRequest)WebRequest.Create(oUri);
               oWebRequest.Credentials = CredentialCache.DefaultNetworkCredentials;
               oWebRequest.Method = "POST";
               oWebRequest.Accept = "application/json;odata=verbose";
               oWebRequest.ContentType = "application/json;odata=verbose";
               oWebRequest.Headers.Add("X-RequestDigest", GetFormDigest());
               oWebRequest.ContentLength = stringData.Length;
               using (StreamWriter writer = new StreamWriter(oWebRequest.GetRequestStream()))
               {
                   writer.Write(stringData);
                   writer.Flush();
               }
               using (WebResponse wresp = oWebRequest.GetResponse())
               using (StreamReader sr = new StreamReader(wresp.GetResponseStream()))
               {
                   sResult = sr.ReadToEnd();
               }
           }
           public static string GetFormDigest()
           {
               string sFormDigest = null;
               string sConnStr = "<web_url>";
               Uri oUri = new Uri(sConnStr + "/_api/contextinfo");
               HttpWebRequest oWebRequest = (HttpWebRequest)HttpWebRequest.Create(oUri);
               oWebRequest.UseDefaultCredentials = true;
               oWebRequest.Method = "POST";
               oWebRequest.Accept = "application/json;odata=verbose";
               oWebRequest.ContentLength = 0;
               oWebRequest.ContentType = "application/json";
               string sResult;
               using (WebResponse sWebResponse = oWebRequest.GetResponse())
               using (StreamReader sr = new StreamReader(sWebResponse.GetResponseStream()))
               {
                   sResult = sr.ReadToEnd();
               }
               var jss = new JavaScriptSerializer();
               var val = jss.Deserialize<Dictionary<string, object>>(sResult);
               var d = val["d"] as Dictionary<string, object>;
               var wi = d["GetContextWebInformation"] as Dictionary<string, object>;
               sFormDigest = wi["FormDigestValue"].ToString();
               return sFormDigest;
           }
       }
   }
   

Code Explanation

1. The complete data to be passed in the request will be.

   {
     "query": {
       "__metadata": {
         "type": "SP.CamlQuery"
       },
       "ViewXml": "<View><Query><Where><Eq><FieldRef Name=\"Title\"/><Value Type=\"Text\">HR</Value></Eq></Where></Query></View>"
     }
   }
   

2. Form Digest needs to be passed while calling the post method. GetFormDigest() returns the required request digest.

Summary

In this article, we have explored how to call SharePoint REST API with CAML query in C# console application. The CAML query can also be passed through the URL itself. However, this makes the API URL complicated and sometimes, restricts the length of the URL. The same code can be used in implementing custom APIs as well.

Happy Reading!