Create Data Table while using SharePoint 2010 Client Context with .Net Code
I am using .net code to access SharePoint List.
I created a method ExecuteSPQuery() which returns Data Table.
For that I wrote below code.
public static DataTable ExecuteSPQuery()
{
DataTable dtData = new DataTable();
Microsoft.SharePoint.Client.ListItemCollection listCollection = null;
ClientContext context = new ClientContext(“SITE_URL”);
try
{
Site oWebsite = context.Site;
context.AuthenticationMode = ClientAuthenticationMode.Anonymous;
List list = context.Web.Lists.GetByTitle(“listName”);
CamlQuery query = new CamlQuery();
query.ViewXml = “<View><Where><Eq><FieldRef Name=’_ModerationStatus’ /><Value Type=’ModStat’>Approved</Value></Eq></Where></View>”;
listCollection = list.GetItems(query);
context.Load(listCollection);
context.ExecuteQuery();
for (int iCntr1 = 0; iCntr1 < listCollection.Count; iCntr1++)
{
foreach (var field in listCollection[0].FieldValues.Keys)
{
dtData.Columns.Add(field);
}
foreach (var item in listCollection)
{
DataRow dr = dtData.NewRow();
foreach (var obj in item.FieldValues)
{
if (obj.Value != null)
{
string key = obj.Key;
string type = obj.Value.GetType().FullName;
if (type == “System.String” || type == “System.Double” || type == “System.Int32”)
{
dr[obj.Key] = obj.Value;
}
if (type == “Microsoft.SharePoint.Client.FieldLookupValue”)
{
dr[obj.Key] = ((FieldLookupValue)obj.Value).LookupValue;
}
else if (type == “Microsoft.SharePoint.Client.FieldUserValue”)
{
dr[obj.Key] = ((FieldUserValue)obj.Value).LookupValue;
}
else if (type == “Microsoft.SharePoint.Client.FieldUserValue[]”)
{
FieldUserValue[] multValue = (FieldUserValue[])obj.Value;
foreach (FieldUserValue fieldUserValue in multValue)
{
dr[obj.Key] += (fieldUserValue).LookupValue;
}
}
else if (type == “System.DateTime”)
{
if (obj.Value.ToString().Length > 0)
{
var date = obj.Value.ToString().Split(‘ ‘);
if (date[0].Length > 0)
{
dr[obj.Key] = date[0];
}
}
}
else
{
dr[obj.Key] = obj.Value;
}
}
else
{
dr[obj.Key] = null;
}
}
dtData.Rows.Add(dr);
}
}
}
catch (Exception ex)
{
//Log Exception
}
finally
{
if (context != null)
context.Dispose();
}
return dtData;
}
