Untitled

//Created by Ahmad Masri in 11/24/2023
using Microsoft.SharePoint.Client;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Net.Mail;
using System.Security;

namespace FetchDataToSharePoint
{
    class Program
    {
        static void Main(string[] args)
        {
            string path = DatabaseConfig.Path;
            string[] lines = System.IO.File.ReadAllLines(path);
            DatabaseConfig.CheckPath(lines);
            string userName = DatabaseConfig.UserName;
            string password = DatabaseConfig.Password;

            string connectionString = DatabaseConfig.ConnectionString;
            string advertisingFilePath = "";
            string donationFilePath = "";
            string organizationFilePath = "";
            string purchaseOfTicketFilePath = "";
            string advertisingQuery = "";
            string donationQuery = "";
            string organizationQuery = "";
            string purchaseOfTicketQuery = "";
            string advertisingExcelPath = "";
            string donationExcelPath = "";
            string organizationExcelPath = "";
            string purchaseOfTicketExcelPath = "";
            DataTable advertisingExcel;
            DataTable donationExcel;
            DataTable organizationExcel;
            DataTable purchaseOfTicketExcel;


            SecureString securePassword = new SecureString();
            foreach (char c in password)
            {
                securePassword.AppendChar(c);
            }


            Console.WriteLine(connectionString);

            for (int i=0; i < DatabaseConfig.QueryFiles.Length;i++)
            {
                advertisingFilePath = DatabaseConfig.QueryFiles[0];
                donationFilePath = DatabaseConfig.QueryFiles[1];
                organizationFilePath = DatabaseConfig.QueryFiles[2];
                purchaseOfTicketFilePath = DatabaseConfig.QueryFiles[3];
            }

            advertisingQuery = System.IO.File.ReadAllText(advertisingFilePath);
            donationQuery = System.IO.File.ReadAllText(donationFilePath);
            organizationQuery = System.IO.File.ReadAllText(organizationFilePath);
            purchaseOfTicketQuery = System.IO.File.ReadAllText(purchaseOfTicketFilePath);

            for (int i = 0; i < DatabaseConfig.ExcelFiles.Length; i++)
            {
                advertisingExcelPath = DatabaseConfig.ExcelFiles[0];
                donationExcelPath = DatabaseConfig.ExcelFiles[1];
                organizationExcelPath = DatabaseConfig.ExcelFiles[2];
                purchaseOfTicketExcelPath = DatabaseConfig.ExcelFiles[3];
            }

            advertisingExcel = ReadCsvFile(advertisingExcelPath);
            donationExcel = ReadCsvFile(donationExcelPath);
            organizationExcel = ReadCsvFile(organizationExcelPath);
            purchaseOfTicketExcel = ReadCsvFile(purchaseOfTicketExcelPath);

            //Console.WriteLine(advertisingQuery + "\n" + donationQuery + "\n" + organizationQuery + "\n" + purchaseOfTicketQuery);
            using (var devspContext = new ClientContext(DatabaseConfig.Url))
            {
                devspContext.Credentials = new NetworkCredential(userName, securePassword, "MAIL");
                Web web = devspContext.Web;

                List targetList = web.Lists.GetByTitle(DatabaseConfig.Title);

                DataTable advertisingDT = new DataTable();
                advertisingDT = GetDatafromSQL(connectionString, advertisingDT, advertisingQuery);

                DataTable donationDT = new DataTable();
                donationDT = GetDatafromSQL(connectionString, donationDT, donationQuery);

                DataTable organizationDT = new DataTable();
                organizationDT = GetDatafromSQL(connectionString, organizationDT, organizationQuery);

                DataTable purchaseOfTicketDT = new DataTable();
                purchaseOfTicketDT = GetDatafromSQL(connectionString, purchaseOfTicketDT, purchaseOfTicketQuery);

                //Console.WriteLine(dt.Tables);
                foreach (DataRow dr in advertisingDT.Rows)
                {
                    foreach(DataRow ad in advertisingExcel.Rows) {
                        ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                        ListItem newItem = targetList.AddItem(itemCreateInfo);

                        // Assuming these are the correct column names in your SharePoint list
                        newItem[ad["SharePointColumn"].ToString()] = dr;
                        // Add other columns as needed
                        newItem.Update();
                    }
                    
                }

                //devspContext.ExecuteQuery();
            }
        }
        public static DataTable GetDatafromSQL(string connString, DataTable tableName, string query)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(tableName);
                }
            }
            return tableName;
        }
        public static DataTable ReadCsvFile(string filePath)
        {
            DataTable csvData = new DataTable();
            using (StreamReader reader = new StreamReader(filePath))
            {
                csvData.Columns.Add("DatabaseColumn");
                csvData.Columns.Add("SharePointColumn");

                while (!reader.EndOfStream)
                {
                    string[] fields = reader.ReadLine().Split(',');
                    // string[] fields = reader.ReadLine().Split(',');
                    DataRow row = csvData.NewRow();
                    for (int i = 0; i < fields.Length; i++)
                    {
                        if (i == 0) row["DatabaseColumn"] = fields[0];
                        if (i == 1) row["SharePointColumn"] = fields[1];
                    }
                    csvData.Rows.Add(row);

                }


            }

            return csvData;

        }
        public static void EmailSender(string subject, string body)
        {
            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;

                using (SmtpClient client = new SmtpClient(DatabaseConfig.Smtp))
                {
                    client.Credentials = CredentialCache.DefaultNetworkCredentials;
                    client.Send(message);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception caught in EmailSender(): {ex.Message}");
            }
        }

    }
}

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 Path = @"D:\CSRForm\CSRForm.txt";
        public static string ConnectionString = null;
        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 string[] ExcelFiles = null;

        public static void CheckPath(string[] lines)
        {
            foreach (string line in lines)
            {
                string[] parts = line.Split('=');
                string key = parts[0].Trim();
                string value = parts[1].Trim();
                if (key.StartsWith("connectionString"))
                {
                    ConnectionString = line.Remove(0, 17);
                }
                else
                {
                    
                    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":
                            QueryFiles = value.Split(',');
                            break;
                        case "excelFiles":
                            ExcelFiles = value.Split(',');
                            break;

                        default:
                            Console.WriteLine($"Unknown key: {key}");
                            break;
                    }
                }
            }
        }
    }

}