Untitled

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;

namespace FetchDataToSharePoint
{
    internal class DatabaseConfig
    {
        public static string ConnectionString = @"connectionString=Data Source=AZUREWEBDB;Initial Catalog=CardsStatements;User Id=spuser;Password=spuser@123;";
        public static string Url = null;
        public static string UserName = null;
        public static string Password = null;
        public static string EmailSender = null;
        public static string EmailReceiver = null;
        public static string EmailReceiver1 = null;
        public static string Smtp = null;
        public static string Title = null;
        //public static string[] Queryfiles = null;
        public static void CheckPath(string[] lines, String[] Queryfiles)
        {
            foreach (string line in lines)
            {
                string[] parts = line.Split('=');
                string key = parts[0].Trim();
                if (parts.Length == 2 && !(key.StartsWith("queryFiles")))
                {
                    string value = parts[1].Trim();
                    switch (key)
                    {
                        case "username":
                            UserName = value;
                            break;
                        case "password":
                            Password = value;
                            break;
                        case "url":
                            Url = value;
                            break;
                        case "emailSender":
                            EmailSender = value;
                            break;
                        case "emailReceiver":
                            EmailReceiver = value;
                            break;
                        case "emailReceiver1":
                            EmailReceiver1 = value;
                            break;
                        case "smtp":
                            Smtp = value;
                            break;
                        case "title":
                            Title = value;
                            break;
                        case "queryFiles":

                        default:
                            Console.WriteLine($"Unknown value: {value}");
                            break;
                    }
                }
                else if (key.StartsWith("queryFiles"))
                {
                    string[] queries = parts[1].Split(',');
                    Queryfiles = new string[queries.Length];
                    /*for (int i=0;i < queries.Length;i++)
                    {
                        Queryfiles = new string[queries.Length];
                        Queryfiles[i] = queries[i];
                        continue;
                        //Console.WriteLine(queries[i]);
                    }*/
                    Queryfiles = (string[])queries.Clone();
                    for (int i = 0; i < Queryfiles.Length; i++)
                    {
                        Queryfiles[i] = queries[i];
                        Console.WriteLine(Queryfiles[1]);
                    }
                }
            }
        }
        public static string ReadQueryFromTXTFile(string queryfile)
        {
            using (StreamReader sr = new StreamReader(queryfile))
            {
                return sr.ReadToEnd();
            }
        }
    

    }
}

//Created by Ahmad Masri in 11/24/2023
using System;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Security;

using Microsoft.SharePoint.Client;

using System.Net.Mail;

namespace FetchDataToSharePoint
{
    class Program
    {
        static void Main(string[] args)
        {
            string Path = @"D:\CSRForm\CSRForm.txt";
            string[] lines = System.IO.File.ReadAllLines(Path);
            ConfigFileData queryfiles = null;
            DatabaseConfig.CheckPath(lines, queryfiles);
            string userName = DatabaseConfig.UserName;
            string password = DatabaseConfig.Password;
            SecureString securePassword = new SecureString();
            foreach (char c in password)
            {
                securePassword.AppendChar(c);
            }
            Console.WriteLine(queryfiles);

        }
        public static void UploadToSharePoint(string userName, SecureString securePassword) //fetches all retreived data to sharepoint
        {
            using (var devspContext = new ClientContext(DatabaseConfig.Url))
            {
                // SharePoint Online Credentials    
                devspContext.Credentials = new NetworkCredential(userName, securePassword, "MAIL");
                Web web = devspContext.Web;
                //devspContext.Load(web);
                //devspContext.AuthenticationMode = ClientAuthenticationMode.Default;
                //devspContext.ExecuteQuery();

                List targetList = web.Lists.GetByTitle(DatabaseConfig.Title);
                DataTable dt = new DataTable();
                dt = GetDatafromSQL();
                Console.WriteLine(dt);
                ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                ListItem newItem = targetList.AddItem(itemCreateInfo);
                foreach (DataRow dr in dt.Rows) // Loop over the rows.  
                {
                    newItem["CSCNumber"] = dr["CSCNumber"];
                    
                    newItem.Update();
                    Console.WriteLine();
                    //devspContext.Load(newItem);
                    //devspContext.ExecuteQuery();

                }
                //devspContext.Load(productList);
                devspContext.ExecuteQuery();
            }
        }

        public static DataTable GetDatafromSQL() //get data and inserts them into the datatable
        {
            DataTable dataTable = new DataTable();
            string connString = DatabaseConfig.ConnectionString;
            string tableName = "Statments";
            string columns = "Id, CSCNumber, Institution, Cycle, BranchCode";
            string query = $"SELECT TOP 30 {columns} FROM {tableName}";

            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dataTable);
            conn.Close();
            da.Dispose();
            return dataTable;
        }
        public static void EmailSender(string subject, string body) //email sender for exception handling (error will be sent by email)
        {
            try
            {
                MailMessage message = new MailMessage();
                message.From = new MailAddress(DatabaseConfig.EmailSender);
                message.To.Add(DatabaseConfig.EmailReceiver);
                message.To.Add(DatabaseConfig.EmailReceiver1);
                message.Subject = subject;
                message.Body = body;
                SmtpClient client = new SmtpClient(DatabaseConfig.Smtp);
                client.Credentials = CredentialCache.DefaultNetworkCredentials;

                try
                {
                    client.Send(message);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception caught in CreateBccTestMessage(): {0}",
                        ex.Message.ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception caught in CreateBccTestMessage(): {0}",
                        e.Message.ToString());
            }


        }


    }
}