Untitled

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)
        {
            try
            {
                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);
                DataTable copyAdvertisingExcel = advertisingExcel.Copy();

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

                    // Assuming advertisingDT and advertisingExcel are available

                    foreach (DataRow row in advertisingDT.Rows)
                    {
                        ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                        ListItem newItem = targetList.AddItem(itemCreateInfo);

                        foreach (DataRow mappingRow in advertisingExcel.Rows)
                        {
                            string databaseColumn = mappingRow["DatabaseColumn"].ToString();
                            string sharePointColumn = mappingRow["SharePointColumn"].ToString();

                            if (advertisingDT.Columns.Contains(databaseColumn))
                            {
                                // Check if the column exists in advertisingDT
                                object columnValue = row[databaseColumn];
                                newItem[sharePointColumn] = columnValue;
                                Console.WriteLine(columnValue);
                            }
                            else
                            {
                                Console.WriteLine($"Column '{databaseColumn}' not found in advertisingDT.");
                            }
                        }

                        //newItem.Update();
                    }

                    //devspContext.ExecuteQuery();

                    //Console.WriteLine(dt.Tables);
                    /**/
                }
                    //devspContext.ExecuteQuery();
                Console.WriteLine("Data uploaded to SharePoint successfully!");
            }
            catch (Exception ex)
            {
                //HandleError(ex.Message);
            }
        }

        static DataTable GetDatafromSQL(string connString, DataTable tableName, string query)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(tableName);
                    }
                }
            }
            catch (Exception ex)
            {
                HandleError(ex.Message);
            }
            return tableName;
        }

        static DataTable ReadCsvFile(string filePath)
        {
            DataTable csvData = new DataTable();
            csvData.Columns.Add("DatabaseColumn");
            csvData.Columns.Add("SharePointColumn");

            try
            {
                using (StreamReader reader = new StreamReader(filePath))
                {
                    while (!reader.EndOfStream)
                    {
                        string[] fields = reader.ReadLine().Split(',');

                        if (fields.Length >= 2)
                        {
                            DataRow row = csvData.NewRow();
                            row["DatabaseColumn"] = fields[0].Trim();
                            row["SharePointColumn"] = fields[1].Trim();
                            csvData.Rows.Add(row);
                        }
                        else
                        {
                            Console.WriteLine($"Invalid line in CSV file: {string.Join(",", fields)}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                HandleError(ex.Message);
            }

            return csvData;
        }

        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)
            {
                HandleError(ex.Message);
            }
        }

        static void HandleError(string errorMessage)
        {
            EmailSender("Error in Code Execution", errorMessage);
            Console.WriteLine($"Error: {errorMessage}");
        }
    }

    // DatabaseConfig class and other existing code...
}

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

}