SharePoint Console Application Sample- CAML Query

The following code is a sample to read SharePoint List data and convert to JSON format.Add Web Service Reference and Newton Json.The code is also an illustration of CAML query joins.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Client;
using System.Web.Script.Serialization;
　
 
 
namespace ConsoleApplicationReadSPList
{
using ServiceReference1;
public class SPAnnouncement
{
public string Title;
}
public class SPEmailGroup
{
public string Email;
public string MailGroup;
}
public class SPEmailDetails
{
public string Email;
public string Region;
public string Section;
}
 
class Program
{
static void Main(string[] args)
{
 
string websiteUrl = "http://w15-sp";
ServiceReference1.MFLDataContext context = new ServiceReference1.MFLDataContext(new Uri(websiteUrl + "/_vti_bin/listdata.svc"));
context.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Starting with ClientContext, the constructor requires a URL to the
// server running SharePoint.
ClientContext clientcontext = new ClientContext(websiteUrl);
// Assume the web has a list named "Announcements".
List announcementsList = clientcontext.Web.Lists.GetByTitle("Announcements");
// This creates a CamlQuery that has a RowLimit of 100, and also specifies Scope="RecursiveAll"
// so that it grabs all list items, regardless of the folder they are in.
CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
ListItemCollection items = announcementsList.GetItems(query);
// Retrieve all items in the ListItemCollection from List.GetItems(Query).
clientcontext.Load(items);
clientcontext.ExecuteQuery();
foreach (ListItem listItem in items)
{
// We have all the list item data. For example, Title.
Console.WriteLine(listItem["Title"]);
var obj = new SPAnnouncement
{
Title = listItem["Title"].ToString()
};
var json = new JavaScriptSerializer().Serialize(obj);
Console.WriteLine(json);
 
}
//Reading another list with two values
// Assume the web has a list named "Announcements".
List emailList = clientcontext.Web.Lists.GetByTitle("EmailGroup");
// This creates a CamlQuery that has a RowLimit of 100, and also specifies Scope="RecursiveAll"
// so that it grabs all list items, regardless of the folder they are in.
CamlQuery query2 = CamlQuery.CreateAllItemsQuery(100);
ListItemCollection items2 = emailList.GetItems(query2);
// Retrieve all items in the ListItemCollection from List.GetItems(Query).
clientcontext.Load(items2);
clientcontext.ExecuteQuery();
foreach (ListItem listItem2 in items2)
{
// We have all the list item data. For example, Title.
Console.WriteLine(listItem2["Email"]);
var mail = listItem2["MailGroup"] as FieldLookupValue;
var obj2 = new SPEmailGroup
{
Email = listItem2["Email"].ToString(),
MailGroup = mail.LookupValue
};
var json2 = new JavaScriptSerializer().Serialize(obj2);
Console.WriteLine(json2);
}
//Getting only selected columns
// Retrieve all items in the ListItemCollection from List.GetItems(Query).
 
StringBuilder caml = new StringBuilder();
caml.Append("<View><Query>");
caml.Append("<FieldRef Name='Region'/>");
caml.Append("<Joins>");
caml.Append("<Join Type='INNER' ListAlias='Section'>");
caml.Append("<Eq><FieldRef Name='MailGroup' RefType='MailGroup' />");
caml.Append("<FieldRef List='Section' Name='MailGroup' />");
caml.Append("</Eq>");
caml.Append("</Join>");
caml.Append("</Joins>");
caml.Append("<ProjectedFields>");
caml.Append("<Field Name='MailGroup' Type='Lookup' List='Section' ShowField='Region' />");
caml.Append("</ProjectedFields>");
caml.Append("<ViewFields>");
caml.Append("<FieldRef Name='Modified' /><FieldRef Name='Region' /><FieldRef Name='Section' />");
caml.Append("</ViewFields>");
caml.Append("</Query><RowLimit>100</RowLimit></View>");
CamlQuery query3 = new CamlQuery();
query3.ViewXml = caml.ToString();
ListItemCollection items3 = emailList.GetItems(query3);
clientcontext.Load(items3);
clientcontext.ExecuteQuery();
foreach (ListItem listItem3 in items3)
{
// We have all the list item data. For example, Title.
Console.WriteLine(listItem3["Email"]);
 
var obj3 = new SPEmailDetails
{
Email = listItem3["Email"].ToString(),
Region = listItem3["Region"].ToString(),
Section = listItem3["Section"].ToString()
 
};
var json3 = new JavaScriptSerializer().Serialize(obj3);
Console.WriteLine(json3);
}
Console.Read();
}
}
}