Create Datatable using .Net Client Context in SharePoint 2010

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;

    }

Comments

Popular