static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("Enter Sharepoint domain url");
                string targetSiteUrl = Console.ReadLine();
                if (string.IsNullOrEmpty(targetSiteUrl))
                {
                    Console.WriteLine("SP_Ctx <url>"); return;
                }

                //if (args.Length < 1) { Console.WriteLine("SP_Ctx <url>"); return; }
                //string targetSite = args[0];

                string targetSite = targetSiteUrl;
                using (ClientContext ctx = ClaimClientContext.GetAuthenticatedContext(targetSite))
                {
                    if (ctx != null)
                    {
                        ctx.Load(ctx.Web);  // Query for Web
                        ctx.ExecuteQuery(); // Execute
                        Console.WriteLine(ctx.Web.Title);
                    }
                }
                Console.ReadLine();
            }
            catch (Exception e)
            {
                throw e;
            }
            //var rtfaCookie = ExtractRTFACookies();
        }
示例#2
0
 private void cmdValidateSharePointSite_Click(object sender, EventArgs e)
 {
     try {
         update_message("Validating the SharePoint site. " + DateTime.Now.ToLongTimeString());
         _clientContext = new ClientContext(get_site_url());
         if (chkUseClaims.Checked)
         {
             _clientContext = ClaimClientContext.GetAuthenticatedContext(get_site_url());
         }
         _site = _clientContext.Web;
         ListCollection lists = _site.Lists;
         _clientContext.Load(lists);
         _clientContext.ExecuteQuery();
         cmbLists.Items.Clear();
         List <string> sorted_lists = new List <string>();
         foreach (List list in lists)
         {
             sorted_lists.Add(list.Title);
         }
         sorted_lists.Sort();
         cmbLists.Items.AddRange(sorted_lists.ToArray <string>());
         if (lists.Count > 0)
         {
             cmbLists.SelectedIndex = 0;
         }
         update_message("SharePoint site is ready. " + DateTime.Now.ToLongTimeString());
     }
     catch (Exception ex) {
         MessageBox.Show(ex.ToString());
     }
 }
        static async void CreateCampaignFromSharepoint()
        {
            using (ClientContext clientContext = ClaimClientContext.GetAuthenticatedContext(targetSharepointSite))
            {
                // Using https://code.msdn.microsoft.com/Remote-Authentication-in-b7b6f43c for authentication
                Web       site  = clientContext.Web;
                List      list  = site.Lists.GetByTitle(targetSharepointList);
                CamlQuery query = new CamlQuery();
                query.ViewXml = "<View/>";
                ListItemCollection items = list.GetItems(query);

                // Load the SharePoint list
                clientContext.Load(list);
                clientContext.Load(items);
                clientContext.ExecuteQuery();

                // Loop through the list to go through all the items which are newly added
                foreach (ListItem item in items)
                {
                    if (item["IsProcessed"].ToString() != "Yes")
                    {
                        string name = item["Title"].ToString();
                        string notificationTitle   = item["NotificationTitle"].ToString();
                        string notificationMessage = item["Message"].ToString();
                        string category            = item["Category"].ToString();
                        string actionURL           = ((FieldUrlValue)item["URL"]).Url;

                        // Send an HTTP request to create AzME campaign
                        int campaignId = CreateAzMECampaign
                                             (name, notificationTitle, notificationMessage, category, actionURL).Result;

                        if (campaignId != -1)
                        {
                            // If creating campaign is successful then set this to true
                            item["IsProcessed"] = "Yes";

                            // Now try to activate the campaign also
                            await ActivateAzMECampaign(campaignId);
                        }
                        else
                        {
                            item["IsProcessed"] = "Error";
                        }
                        item.Update();
                    }
                }
                clientContext.ExecuteQuery();
            }
        }
示例#4
0
        private static void InsertIntoList(DataTable listTable, string contactListName)
        {
            string siteUrl       = ConfigurationManager.AppSettings["SharePointURL"];
            bool   isBatchUpdate = Convert.ToBoolean(ConfigurationManager.AppSettings["IsBatchUpdate"]);

            using (Microsoft.SharePoint.Client.ClientContext context = ClaimClientContext.GetAuthenticatedContext(siteUrl))
            {
                string strListName = ConfigurationManager.AppSettings["TargetSharePointListName"];
                int    counter     = 0;
                Console.WriteLine(string.Format("\nContent type updation would be performed for url - {0}\n", siteUrl));
                Web site = context.Web;
                context.Load(site);
                context.Load(site.Lists, lists => lists.Include(list => list.Title, // For each list, retrieve Title and Id.
                                                                list => list.Id));
                context.ExecuteQuery();
                Microsoft.SharePoint.Client.List GlossaryList = site.Lists.GetByTitle(strListName);
                for (int iRow = 0; iRow < listTable.Rows.Count; iRow++)
                {
                    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                    ListItem oListItem = GlossaryList.AddItem(itemCreateInfo);
                    //the column name below is the absolute name of the column which can be seen in List Settings-->List columns
                    //Modify the below rows as per your target list columns
                    oListItem["Title"] = Convert.ToString(listTable.Rows[iRow][0]); //List Title
                    oListItem["r9dw"]  = Convert.ToString(listTable.Rows[iRow][2]); //Definition
                    oListItem["gpqr"]  = Convert.ToString(listTable.Rows[iRow][3]); //TermCategory
                    counter++;
                    oListItem.Update();
                    if (isBatchUpdate == true)
                    {
                        if ((counter % 10) == 0) //for batch batch by batch updation in multiples of 10
                        {
                            context.ExecuteQuery();
                            counter = 0; //reset the counter
                        }
                    }
                    else
                    {
                        context.ExecuteQuery(); //Either use this if you want to update every item one by one
                    }
                }
            }
        }
示例#5
0
        static void Main(string[] args)
        {
            if (args.Length < 1)
            {
                Console.WriteLine("SP_Ctx <url>"); return;
            }

            string targetSite = args[0];

            using (ClientContext ctx = ClaimClientContext.GetAuthenticatedContext(targetSite))
            {
                if (ctx != null)
                {
                    ctx.Load(ctx.Web);  // Query for Web
                    ctx.ExecuteQuery(); // Execute
                    Console.WriteLine(ctx.Web.Title);
                }
            }
            Console.ReadLine();
        }
示例#6
0
        static void Main(string[] args)
        {
            //if (args.Length < 1) { Console.WriteLine("SP_Ctx <url>"); return; }
            //string targetSite = args[0];
            string targetSite = "https://sharepointOnlineURLRoot/sites/siteName";

            using (ClientContext ctx = ClaimClientContext.GetAuthenticatedContext(targetSite))
            {
                if (ctx != null)
                {
                    ctx.Load(ctx.Web);  // Query for Web
                    ctx.ExecuteQuery(); // Execute
                    Console.WriteLine(ctx.Web.Title);
                }
            }
            Console.WriteLine("");
            Console.WriteLine("");
            CookieCollection authCookie =
                ClaimClientContext.GetAuthenticatedCookies(targetSite, 925, 525);

            listWS.Lists list = new listWS.Lists();
            list.Url = "https://sharepointOnlineURLRoot/sites/siteName/_vti_bin/Lists.asmx"
                       //list.Timeout = 15000; //in milliseconds
                       list.CookieContainer = new CookieContainer();
            list.CookieContainer.Add(authCookie);
            string listName = "Shared Documents";
            string viewName = "";
            //string listName = "{1A4A3C5D-360E-45EB-B9ED-E8653981CAC0}";
            //string viewName = "{5A4AF2C5-8A9F-427F-B8AA-BC59E3BE8AA0}";
            string rowLimit = "5";

            // Instantiate an XmlDocument object
            System.Xml.XmlDocument xmlDoc       = new System.Xml.XmlDocument();
            System.Xml.XmlElement  query        = xmlDoc.CreateElement("Query");
            System.Xml.XmlElement  viewFields   = xmlDoc.CreateElement("ViewFields");
            System.Xml.XmlElement  queryOptions = xmlDoc.CreateElement("QueryOptions");

            //*Use CAML query*/
            query.InnerXml = "<Where><Gt><FieldRef Name=\"ID\" />" +
                             "<Value Type=\"Counter\">0</Value></Gt></Where>";
            viewFields.InnerXml = "<FieldRef Name=\"Title\" />";
            //queryOptions.InnerXml = "";
            queryOptions.InnerXml =
                "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" +
                "<DateInUtc>TRUE</DateInUtc>";
            System.Xml.XmlNode nodes =
                list.GetListItems(
                    listName,
                    viewName,
                    query,
                    viewFields,
                    rowLimit,
                    null,
                    string.Empty);
            string ixml = list.GetList(listName).InnerXml;

            Console.WriteLine(
                "Retrieving title of all the items in SharePoint Online" +
                "sites 'Shared Documents' using Lists webservice");
            Console.WriteLine(
                "===========================================" +
                "=============================================");
            foreach (System.Xml.XmlNode node in nodes)
            {
                if (node.Name == "rs:data")
                {
                    for (int i = 0; i < node.ChildNodes.Count; i++)
                    {
                        if (node.ChildNodes[i].Name == "z:row")
                        {
                            Console.WriteLine(
                                node.ChildNodes[i].Attributes["ows_Title"].Value);
                        }
                    }
                }
            }
            Console.ReadLine();
        }
示例#7
0
        /// <summary>
        /// Update Pipeline list
        /// </summary>
        /// <param name="clientContextWeb">client context url</param>
        /// <param name="backupListTarget">list that is exported and deleted</param>
        /// <param name="excelIndexTarget">list that needs to be backedup</param>
        /// <param name=sharepointIndexTarget">doc library for backup of excel file</param>
        public UpdatePipelineList(string clientContextWeb, string backupListTarget, string excelIndexTarget, string sharepointIndexTarget)
        {
            System.IO.Directory.CreateDirectory(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @"\Export\");

            string folder = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @"\Export\";
            string filter = "*.xlsx";

            string[] files = Directory.GetFiles(folder, filter);

            string pipelinefile = "Pipeline.xlsx";
            string dhcfile      = "DHCUpdate.xlsx";

            Regex regexPipeline = FindFilesPatternToRegex.Convert("*pipeline*.xlsx");
            Regex regexDHC      = FindFilesPatternToRegex.Convert("*dhc*.xlsx");

            foreach (string file in files)
            {
                //Console.WriteLine("Inside File check: {0}", file);
                if (regexPipeline.IsMatch(file.ToLower()))
                {
                    pipelinefile = file;
                }
                else if (regexDHC.IsMatch(file.ToLower()))
                {
                    dhcfile = file;
                }
            }

            Console.WriteLine("------Update Pipeline ----------------");
            //Console.WriteLine("Folder      : {0}", folder);
            Console.WriteLine("Pipelinefile: {0}", pipelinefile);
            Console.WriteLine("DHCfile     : {0}", dhcfile);
            Console.WriteLine("--------------------------------------");
            log.Debug(string.Format("------   Update Pipeline Files   ------"));
            log.Debug(string.Format("Pipelinefile: {0}", pipelinefile));
            log.Debug(string.Format("DHCfile     : {0}", dhcfile));
            log.Debug(string.Format("---------------------------------------"));

            FileStream stream, streamDHC;

            try
            {
                //update for reading files
                stream = System.IO.File.Open(pipelinefile, FileMode.Open, FileAccess.Read);

                //update for reading files
                streamDHC = System.IO.File.Open(dhcfile, FileMode.Open, FileAccess.Read);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Please close the excel file and press enter");
                Console.ReadLine();
                //update for reading files
                stream = System.IO.File.Open(pipelinefile, FileMode.Open, FileAccess.Read);

                //update for reading files
                streamDHC = System.IO.File.Open(dhcfile, FileMode.Open, FileAccess.Read);
            }



            IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            reader.IsFirstRowAsColumnNames = true;

            DataSet ds = reader.AsDataSet();

            IExcelDataReader readerDHC = ExcelReaderFactory.CreateOpenXmlReader(streamDHC);

            readerDHC.IsFirstRowAsColumnNames = true;

            DataSet dsDHC = readerDHC.AsDataSet();

            DataRowSharepointMappingCollection mapping    = MyRetriever.GetTheCollection();
            DataRowSharepointMappingCollection mappingDHC = MyRetriever.GetTheCollection("DataRowDHCMappingsSection");



            DataTable  dt       = ds.Tables[0];
            DataColumn dcParent = dt.Columns["Opportunity Name"];

            //using (var clientContext = new ClientContext(clientContextWeb))
            using (ClientContext clientContext = ClaimClientContext.GetAuthenticatedContext(clientContextWeb))
            {
                Web web = clientContext.Web;

                //------------------------------------
                // GetItems for PipeLine list
                //------------------------------------
                // DEV NOTES:
                //     Aug. 4, 2017 - Removed 2000  limitation from CamlQueries   (MAT)
                //
                List               oldList  = web.Lists.GetByTitle(backupListTarget);
                CamlQuery          query    = CamlQuery.CreateAllItemsQuery();
                ListItemCollection oldItems = oldList.GetItems(query);

                clientContext.Load(oldItems);

                var listFields = oldList.Fields;
                clientContext.Load(listFields, fields => fields.Include(field => field.Title, field => field.InternalName, field => field.ReadOnlyField, field => field.StaticName));

                clientContext.ExecuteQuery();

                //--------------------------------------------------
                // GetItems from LOB (LineOfBusiness list here.....
                //--------------------------------------------------
                // DEV NOTES:
                //     Aug. 4, 2017 - Removed 1000 limitation from CamlQueries   (MAT)
                //
                List               LOBList  = web.Lists.GetByTitle("LOB-MPP-Map");
                CamlQuery          LOBquery = CamlQuery.CreateAllItemsQuery();
                ListItemCollection LOBitems = LOBList.GetItems(LOBquery);

                clientContext.Load(LOBitems);

                var LOBFields = LOBList.Fields;
                clientContext.Load(LOBFields, fields => fields.Include(field => field.Title, field => field.InternalName));

                clientContext.ExecuteQuery();

                //UpdateLOBFields( clientContext, oldList, oldItems, LOBitems);
                // Console.WriteLine("Finished return from LOB update");
                //oldList.Update();
                //Console.WriteLine("Finished return from oldList update");
                //clientContext.ExecuteQuery();
                //-------------------------
                //Stop here for now.
                //-------------------------
                // Console.ReadLine();
                // System.Environment.Exit(0);

                //log.Debug(string.Format("Opening List: {0}", backupListTarget));
                //log.Debug("Internal fields");
                //log.Debug("-------------------------");
                //foreach (Field f in listFields)
                //{
                //    log.Debug(string.Format("Title: {0},   Internal Name: {1}", f.Title, f.InternalName));
                //    log.Debug(string.Format("Static Name: {0}", f.StaticName));
                //    log.Debug("-----------------");
                //    //log.Debug(f.InternalName);
                //}

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    //log.Debug("-------  Inside For  -----------------");
                    //log.Debug(dr[excelIndexTarget].ToString());

                    var my_itemlist = oldItems.ToList();

                    // ---------------BEGIN MY COMMENT SECTION --------------------------
                    //Console.WriteLine("Sales Opportunity Id: {0}", dr["Sales Opportunity Id"].ToString());
                    //Console.WriteLine(" My_itemlist count: {0}", my_itemlist.Count);

                    ////---------MAT ----DEBUG TEST--------------------
                    ////--  List out item list for verification ---
                    //// ---------------------------------------------
                    //if (my_itemlist.Count() == 0)
                    //{
                    //    Console.WriteLine("My List count in 0");
                    //}
                    //else
                    //{
                    //    log.Debug("-- Item List ------");
                    //    foreach (ListItem targetListItem in my_itemlist)
                    //    {
                    //        log.Debug(string.Format("Title: {0}, HPOppID: {1}", targetListItem["Title"], targetListItem["HPOppID"].ToString()));
                    //        Console.WriteLine(targetListItem["Title"]);
                    //    }
                    //}
                    //log.Debug(string.Format(".....MAT - Listing COMPLETED HERE"));

                    //Console.WriteLine("  --------  MAT list completed here  ---------------");
                    // ---------------END MY COMMENT SECTION --------------------------

                    var page = from ListItem itemlist in oldItems.ToList()
                               // var page = from ListItem itemlist in my_itemlist
                               //where itemlist["HPOppID"].ToString() == dr["Sales Opportunity Id"].ToString()
                               where itemlist["HPOppID"].ToString() == dr[excelIndexTarget.ToString()].ToString()
                               select itemlist;

                    //Console.WriteLine("Page Count is: {0}", page.Count());
                    //this is an update
                    if (page.Count() == 1)
                    {
                        Console.ForegroundColor = ConsoleColor.Blue;
                        //Console.WriteLine(string.Format("UPDATE RECORD:  Name:{0}  ID:{1}", dr["Opportunity Name"].ToString(), dr["Sales Opportunity Id"].ToString()));
                        //log.Debug(string.Format("UPDATE: Name:{0}  ID:{1}", dr["Opportunity Name"].ToString(), dr["Sales Opportunity Id"].ToString()));
                        Console.WriteLine(string.Format("UPDATE RECORD:  Name:{0}  ID:{1}", dr["Opportunity Name"].ToString(), dr[excelIndexTarget.ToString()].ToString()));
                        log.Debug(string.Format("UPDATE: Name:{0}  ID:{1}", dr["Opportunity Name"].ToString(), dr[excelIndexTarget.ToString()].ToString()));


                        ListItem item = page.FirstOrDefault();

                        //iterate the mapping between sharepoint list items and excel spreadsheet items
                        foreach (DataRowSharepointMapping map in mapping)
                        {
                            UpdateField(item, map.SharePointColumn, map.DataRowColumn, dr, sharepointIndexTarget);
                        }
                        CompareSalesStage(item, dsDHC, mappingDHC, excelIndexTarget, sharepointIndexTarget);

                        //Console.WriteLine("- Before Item update.");
                        // just update the item
                        item.Update();

                        //Console.WriteLine("- Before List update.");
                        //update the list
                        oldList.Update();


                        countupdate++;
                    }
                    // This is a new record
                    //else if (page.Count() == 0 && !string.IsNullOrEmpty(dr["Sales Opportunity Id"].ToString()))   ----MAT
                    else if (page.Count() == 0 && !string.IsNullOrEmpty(dr[excelIndexTarget.ToString()].ToString()))
                    {
                        Console.ForegroundColor = ConsoleColor.Yellow;
                        //Console.WriteLine(string.Format("-------  Inside ELSE NEW RECORD-----------------"));
                        //Console.WriteLine(string.Format("NEW RECORD: Name:{0}  ID:{1}", dr["Opportunity Name"].ToString(), dr["Sales Opportunity Id"].ToString()));
                        Console.WriteLine(string.Format("NEW RECORD: Name:{0}  ID:{1}", dr["Opportunity Name"].ToString(), dr[excelIndexTarget.ToString()].ToString()));
                        //log.Debug("-------  Inside ELSE NEW RECORD-----------------");

                        ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                        ListItem oListItem = oldList.AddItem(itemCreateInfo);
                        // -- iterate the mapping between sharepoint list items and excel spreadsheet items
                        foreach (DataRowSharepointMapping map in mapping)
                        {
                            UpdateField(oListItem, map.SharePointColumn, map.DataRowColumn, dr, sharepointIndexTarget);
                        }
                        CompareSalesStage(oListItem, dsDHC, mappingDHC, excelIndexTarget, sharepointIndexTarget);

                        // -- just update the item
                        //Console.WriteLine("- Before Item update.");
                        oListItem.Update();
                        // -- update the list
                        //Console.WriteLine("- Before List update.");
                        oldList.Update();

                        countnew++;
                    }

                    else
                    {
                        //-----------------------------------------------------
                        // DUPLICATE RECORDS CHECK  (MAT) - 8/31/17
                        //-----------------------------------------------------
                        //log.Debug(string.Format("DUPLICATE RECORD TEST inside PAGE: {0}", page.ToList().Count()));
                        if (page.ToList().Count() > 1)
                        {
                            Console.ForegroundColor = ConsoleColor.DarkGray;
                            log.Debug(string.Format("DUPLICATE RECORD (NO ACTION TAKEN): Name:{0}  ID:{1} --- ", dr["Opportunity Name"].ToString(), dr[excelIndexTarget.ToString()].ToString()));
                            Console.WriteLine(string.Format("DUPLICATE RECORD (NO ACTION TAKEN): Name:{0}  ID:{1} -- ", dr["Opportunity Name"].ToString(), dr[excelIndexTarget.ToString()].ToString()));
                            dupcount++;
                        }

                        //-------------------------------------------------------

                        //Console.ForegroundColor = ConsoleColor.Red;
                        //Console.WriteLine("ERROR");
                    }

                    //  Not sure about this one. (MAT)
                    clientContext.ExecuteQuery();
                }
                Console.ForegroundColor = ConsoleColor.Green;
                Console.WriteLine(string.Format("We updated: {0} records and added {1} records", countupdate.ToString(), countnew.ToString()));
                Console.WriteLine(string.Format("Duplicates found: {0} records", dupcount.ToString()));
                log.Debug(string.Format("------------------------------------------------------------"));
                log.Debug(string.Format("We updated: {0} records and added {1} records", countupdate.ToString(), countnew.ToString()));
                log.Debug(string.Format("Duplicates found: {0} records", dupcount.ToString()));
                Console.WriteLine("Completed first set of updates. \n");
                Console.WriteLine("Starting LOB checks........ \n");
                log.Debug(string.Format("------------------------------------------------------------"));
                log.Debug(string.Format("Starting LOB Checks........"));
                UpdateLOBFields(clientContext, oldList, oldItems, LOBitems, sharepointIndexTarget);
                //Console.WriteLine("Finished return from LOB update...");
                //oldList.Update();
                clientContext.ExecuteQuery();
                Console.WriteLine("Finished Line Of Business updates... \n");
            }
        }
 private void ConnectToPWA()
 {
     projContext = ClaimClientContext.GetAuthenticatedContext <ProjectContext>(txtPWASite.Text);
 }
示例#9
0
        /// <summary>
        /// Backups and exports lists
        /// </summary>
        /// <param name="clientContextWeb">client context url</param>
        /// <param name="backupListTarget">list that is exported and deleted</param>
        /// <param name="backupListSource">list that needs to be backedup</param>
        /// <param name="pipelineBackupDocLib">doc library for backup of excel file</param>
        public BackupHelper(string clientContextWeb, string backupListTarget, string backupListSource, string pipelineBackupDocLib)
        {
            try
            {
                //using (var clientContext = new ClientContext(clientContextWeb))
                using (ClientContext clientContext = ClaimClientContext.GetAuthenticatedContext(clientContextWeb))
                {
                    try
                    {
                        Web web = clientContext.Web;

                        List oldList = web.Lists.GetByTitle(backupListTarget);
                        List newList = web.Lists.GetByTitle(backupListSource);

                        // This creates a CamlQuery that has a RowLimit of 100, and also specifies Scope="RecursiveAll"
                        // so that it grabs all list items, regardless of the folder they are in.
                        // DEV NOTES:
                        //     Aug. 4, 2017 - Removed 2000  limitation from CamlQueries   (MAT)
                        //
                        CamlQuery          query    = CamlQuery.CreateAllItemsQuery();
                        CamlQuery          newquery = CamlQuery.CreateAllItemsQuery();
                        ListItemCollection oldItems = oldList.GetItems(query);
                        ListItemCollection newItems = newList.GetItems(newquery);
                        int counter = 0;

                        var listFields = newList.Fields;
                        clientContext.Load(listFields, fields => fields.Include(field => field.Title, field => field.InternalName, field => field.ReadOnlyField));


                        clientContext.Load(oldItems);
                        clientContext.Load(newItems);
                        clientContext.ExecuteQuery();

                        // Retrieve all items in the ListItemCollection from List.GetItems(Query).
                        DataTable dt = new DataTable("ExportData");


                        foreach (Field f in listFields)
                        {
                            //Console.WriteLine("Checking Field: {0}", f.Title);
                            try
                            {
                                //   if (Array.IndexOf(stringArray, f.Title) >= 0)
                                //    {
                                if (Array.IndexOf(badTitleArray, f.InternalName) < 0 && !f.ReadOnlyField)
                                {
                                    //Console.WriteLine("Adding Field: {0}", f.Title);
                                    dt.Columns.Add(f.Title);
                                }
                            }
                            catch (Exception ex)
                            {
                                log.Error("error creating table", ex);
                            }
                        }


                        foreach (ListItem item in oldItems)
                        {
                            DataRow dr = dt.NewRow();

                            foreach (Field f in listFields)
                            {
                                if (Array.IndexOf(badTitleArray, f.InternalName) < 0 && !f.ReadOnlyField)
                                {
                                    try
                                    {
                                        dr[f.Title] = item[f.InternalName];
                                    }
                                    catch (Exception ex)
                                    {
                                        //log.Debug("found a problem with field");
                                        log.Debug(string.Format("ERROR: Problem with field ({0}).", f.InternalName.ToString()));
                                        Console.WriteLine("Error with: {0} \n", f.InternalName.ToString());
                                        Console.WriteLine("Error message {0}", ex.Message);
                                    }
                                }
                            }

                            dt.Rows.Add(dr);

                            // WORKING DELETE OBJECTS
                            oldItems.GetById(item.Id).DeleteObject();
                            if (counter > 50)
                            {
                                try
                                {
                                    clientContext.ExecuteQuery();
                                    counter = 0;
                                }
                                catch (Exception ex)
                                {
                                    log.Error("caught and exception in delete object", ex);
                                    Console.ForegroundColor = ConsoleColor.Red;
                                    Console.WriteLine(string.Format("We had an issue:{0}", ex.ToString()));
                                    Console.ForegroundColor = ConsoleColor.Yellow;
                                    Console.WriteLine("Connectivity was lost please try again");
                                    throw ex;
                                }
                            }
                            else
                            {
                                counter++;
                            }
                        }
                        //finish up the final documents
                        counter = 0;
                        try
                        {
                            clientContext.ExecuteQuery();
                        }
                        catch (Exception ex)
                        {
                            log.Error("caught and exception while cleaning up objects", ex);
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine(string.Format("We had an issue:{0}", ex.ToString()));
                            Console.WriteLine("Connectivity was lost please try again");
                            throw ex;
                        }

                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine("Items are all deleted we are about to save our file");

                        // save excel file
                        XLWorkbook wb = new XLWorkbook();
                        wb.Worksheets.Add(dt, "WorksheetName");
                        string timestamp = DateTime.Now.ToString("yyyyMMddhhmm");
                        string fileName  = "backup" + timestamp + ".xlsx";
                        wb.SaveAs(fileName);

                        using (var fs = new FileStream(fileName, FileMode.Open))
                        {
                            var fi   = new FileInfo(fileName);
                            var list = clientContext.Web.Lists.GetByTitle(pipelineBackupDocLib);
                            clientContext.Load(list.RootFolder);
                            clientContext.ExecuteQuery();
                            var fileUrl = String.Format("{0}/{1}", list.RootFolder.ServerRelativeUrl, fi.Name);

                            Microsoft.SharePoint.Client.File.SaveBinaryDirect(clientContext, fileUrl, fs, true);
                        }


                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine("The file was successfully saved and we are about to copy lists");



                        foreach (ListItem item in newItems)
                        {
                            var           itemCreateInfo = new ListItemCreationInformation();
                            var           newListItem    = oldList.AddItem(itemCreateInfo);
                            StringBuilder sbLog          = new StringBuilder();
                            foreach (Field f in listFields)
                            {
                                //   if (Array.IndexOf(stringArray, f.Title) >= 0)
                                //    {
                                if (Array.IndexOf(badTitleArray, f.InternalName) < 0 && !f.ReadOnlyField)
                                {
                                    if (f.InternalName.ToLower() == "title")
                                    {
                                        sbLog.Append(string.Format("Field Name:{0}", f.InternalName));
                                    }

                                    newListItem[f.InternalName] = item[f.InternalName];
                                }
                                //}
                            }


                            newListItem.Update();
                            if (counter > 50)
                            {
                                try
                                {
                                    clientContext.ExecuteQuery();
                                }
                                catch (Exception ex)
                                {
                                    log.Error("caught an update exception", ex);
                                    log.Debug(string.Format("ERROR: {0} with update.", newListItem["HPOppID"].ToString()));
                                    Console.ForegroundColor = ConsoleColor.Red;
                                    Console.WriteLine(string.Format("We had an issue:{0}", ex.ToString()));
                                    Console.WriteLine("Connectivity was lost please try again");
                                    // throw ex;
                                }
                            }
                            else
                            {
                                counter++;
                                //log.Debug(string.Format("added item:{0}", sbLog.ToString()));
                            }
                        }

                        //finish up the final documents
                        counter = 0;
                        try
                        {
                            clientContext.ExecuteQuery();
                        }
                        catch (Exception ex)
                        {
                            log.Error("caught and exception", ex);
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine(string.Format("We had an issue:{0}", ex.ToString()));
                            Console.WriteLine("Connectivity was lost please try again");
                            log.Error(string.Format("Connectivity was lost. Please try again:{0}", ex.ToString()));
                            // throw ex;
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.WriteLine(string.Format("We had an issue Please record, exit and try again:{0}", ex.ToString()));
                        log.Error(string.Format("We had an issue Please record, exit and try again:{0}", ex.ToString()));
                        //Console.ReadLine();
                    }
                }
            }
            catch (MissingMethodException ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("[MAT] Missing Method exception:  {0}: ", ex.Message);

                log.Error("Client context was lost", ex);
                //Console.ReadLine();
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Client context was lost:  Pleasse record and press enter");
                log.Error("Client context was lost", ex);
                //Console.ReadLine();
            }


            log.Debug("------------------------------------------------------------");
            log.Debug("Backup Completed.");
            log.Debug("------------------------------------------------------------");

            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("Successfully completed Backup.");
            Console.ForegroundColor = ConsoleColor.White;
            //Console.ReadLine();
        }