Thursday, April 30, 2015

ListItemCollection as a DataTable in SharePoint 2013 using CSOM

ListItemCollection as a DataTable in SharePoint 2013 using CSOM
internal DataTable GetDataTableFromListItemCollection()
        {
            string strWhere = string.Empty;
            DataTable dtGetReqForm = new DataTable();
            using (var clientContext = new ClientContext("sharepoint host url"))
            {
                try
                {
                    Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("ReqList");
                    clientContext.Load(spList);
                    clientContext.ExecuteQuery();

                    if (spList != null && spList.ItemCount > 0)
                    {
                        Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
                        camlQuery.ViewXml =
                        @"<View>" +
                        "<Query> " +
                            "<Where>" +
                                "<And>" +
                                        "<IsNotNull><FieldRef Name='ID' /></IsNotNull>" +
                                        "<Eq><FieldRef Name='ReqNo' /><Value Type='Text'>123</Value></Eq>" +
                                "</And>" +
                            "</Where>" +
                        "</Query> " +
                        "<ViewFields>" +
                            "<FieldRef Name='ID' />" +
                        "</ViewFields>" +
                        "</View>";

                        SPClient.ListItemCollection listItems = spList.GetItems(camlQuery);
                        clientContext.Load(listItems);
                        clientContext.ExecuteQuery();

                        if (listItems != null && listItems.Count > 0)
                        {
                            foreach (var field in listItems[0].FieldValues.Keys)
                            {
                                dtGetReqForm.Columns.Add(field);
                            }

                            foreach (var item in listItems)
                            {
                                DataRow dr = dtGetReqForm.NewRow();

                                foreach (var obj in item.FieldValues)
                                {
                                    if (obj.Value != null)
                                    {
                                        string key = obj.Key;
                                        string type = obj.Value.GetType().FullName;

                                        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;
                                    }
                                }
                                dtGetReqForm.Rows.Add(dr);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    if (clientContext != null)
                        clientContext.Dispose();
                }
            }
            return dtGetReqForm;

        }
ThankYou.

Wednesday, April 22, 2015

Merge Two DataTables Into one DataTable in C#

Merge Two DataTables Into one DataTable in C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("EmpID", typeof(int));
            dt1.Columns.Add("ColX", typeof(int));
            dt1.Columns.Add("ColY", typeof(int));

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("EmpID", typeof(int));
            dt2.Columns.Add("ColZ", typeof(int));

            DataRow row;
            for (int i = 1; i <= 2; i++)
            {
                row = dt1.NewRow();
                row["EmpID"] = i;
                row["ColX"] = 10 + i;
                row["ColY"] = 20 + i;
                dt1.Rows.Add(row);
            }
            for (int i = 1; i <= 5; i++)
            {
                row = dt1.NewRow();
                row = dt2.NewRow();
                row["EmpID"] = i;
                row["ColZ"] = 30 + i;
                dt2.Rows.Add(row);
            }
            var lst = from table1 in dt1.AsEnumerable()
                      join table2 in dt2.AsEnumerable() on (int)table1["EmpID"] equals (int)table2["EmpID"]
                      select new
                     {
                         EmpID = (int)table1["EmpID"],
                         ColX = (int)table1["ColX"],
                         ColY = (int)table1["ColY"],
                         ColZ = (int)table2["ColZ"]
                     };
            DataTable targetTable = new DataTable();
            var dt1Columns = dt1.Columns.OfType<DataColumn>()
                .Where(dc => dc.ColumnName != "ColX")
                .Select(dc => new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
            var dt2Columns = dt2.Columns.OfType<DataColumn>()
                .Where(dc => dc.ColumnName != "EmpID")
                .Select(dc => new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
            targetTable.Columns.AddRange(dt1Columns.ToArray());
            targetTable.Columns.AddRange(dt2Columns.ToArray());         
            foreach (var item in lst)
            {
                DataRow dr = targetTable.NewRow();
                dr["EmpID"] = item.EmpID;
                dr["ColY"] = item.ColY;
                dr["ColZ"] = item.ColZ;
                targetTable.Rows.Add(dr);
            }
        }
    }
}