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);
            }
        }
    }
}

Featured Post

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management https://docs.microsoft.com/en-us/azure/api-management/api-...

Popular posts