SharePoint 2013 Hosting – HostForLIFE.eu :: Console Application To Fetch SharePoint List Data Using REST API With CAML Query In C# Managed Code

SharePoint 2013 has many restful APIs to fetch the data from lists. However, sometimes, we need to use a CAML query to filter out the data. SharePoint Rest API has an 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 Department SharePoint list. 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> </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 same as other post requests,

Steps Please follow the below steps to create a console application:

1. Open Visual Studio.

2. Select Console Application template and provide a suitable name.

3. Add required assemblies.

4. Add the below code in 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 = null; 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; StreamWriter writer = new StreamWriter(oWebRequest.GetRequestStream()); writer.Write(stringData); writer.Flush(); 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 = null; oUri = new Uri(sConnStr + "/_api/contextinfo"); HttpWebRequest oWebRequest = HttpWebRequest.Create(oUri) as HttpWebRequest; oWebRequest.UseDefaultCredentials = true; oWebRequest.Method = "POST"; oWebRequest.Accept = "application/json;odata=verbose"; oWebRequest.ContentLength = 0; oWebRequest.ContentType = "application/json"; string sResult; WebResponse sWebReponse = oWebRequest.GetResponse(); using (StreamReader sr = new StreamReader(sWebReponse.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; } } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

   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 = null;                   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;                          StreamWriter writer = new StreamWriter(oWebRequest.GetRequestStream());                   writer.Write(stringData);                   writer.Flush();                          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 = null;                   oUri = new Uri(sConnStr + "/_api/contextinfo");                                      HttpWebRequest oWebRequest = HttpWebRequest.Create(oUri) as HttpWebRequest;                   oWebRequest.UseDefaultCredentials = true;                   oWebRequest.Method = "POST";                   oWebRequest.Accept = "application/json;odata=verbose";                   oWebRequest.ContentLength = 0;                   oWebRequest.ContentType = "application/json";                   string sResult;                   WebResponse sWebReponse = oWebRequest.GetResponse();                          using (StreamReader sr = new StreamReader(sWebReponse.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:
    

   1. “{‘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 need to be passed while calling 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.