Search This Blog

Thursday, May 9, 2019

ClientContext -Getting Date from List and Passing the Caml Query


static string startDateFx = Convert.ToDateTime("05/01/2019").ToString("yyyy-MM-ddTHH:mm:ssZ");  //DateTime.Now.AddDays(-20).ToString("yyyy-MM-ddTHH:mm:ssZ");
        static string endDatFx = Convert.ToDateTime("05/09/2019").ToString("yyyy-MM-ddTHH:mm:ssZ");//DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ssZ");
  using (SP.ClientContext clientContext = new SP.ClientContext(siteUrl))
            {

                try
                {
                    SP.List configList = clientContext.Web.Lists.GetByTitle(configListName);
                    SP.View ConfiglistView = configList.Views.GetByTitle("All Items");
                    ConfiglistView.RowLimit = 2147483647;
                    clientContext.Load(ConfiglistView);
                    clientContext.ExecuteQuery();
                    SP.CamlQuery query = new SP.CamlQuery();
                    SP.ListItemCollection configItems = configList.GetItems(query);
                    clientContext.Load(ConfiglistView);
                    clientContext.Load(configItems);
                    clientContext.ExecuteQuery();
                    if (configItems != null && configItems.Count > 0)
                    {                      
                        foreach (SP.ListItem item in configItems)
                        {
                            startDateFx = (DateTime.SpecifyKind(DateTime.Parse(item["StartDate"].ToString()), DateTimeKind.Utc)).ToLocalTime().ToString("yyyy-MM-ddTHH:mm:ssZ");
                            endDatFx = (DateTime.SpecifyKind(DateTime.Parse(item["EndDate"].ToString()), DateTimeKind.Utc)).ToLocalTime().ToString("yyyy-MM-ddTHH:mm:ssZ");                          
                        }
                        GetListReport(clientContext, ListName, xlReportPath, DateTime.Now.ToString("dd MMM yyyy") + fileName, startDateFx, endDatFx);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine();
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("Authentication failed." + ex.Message);
                    Console.ForegroundColor = ConsoleColor.Gray;
                }
            }

//sample one

  SP.CamlQuery query = new SP.CamlQuery();
            //  query.ViewXml = "<View Scope='RecursiveAll'><Query>" + listView.ListViewXml + "</Query></View>";
            query.ViewXml = @"<View>" +
"<Query>" + "<Where><And>" +
    "<Geq>" +
        "<FieldRef Name='ClosureDate'/>" +
        "<Value Type='DateTime' IncludeTimeValue='FALSE'>" + startDateFx + "</Value>" +
    "</Geq>" +
    "<Leq>" +
        "<FieldRef Name='ClosureDate'/>" +
        "<Value Type='DateTime' IncludeTimeValue='FALSE'>" + endDatFx + "</Value>" +
    "</Leq>" +
    "</And></Where>" +
"</Query>" +
"</View>";