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 = File.ReadAllLines(path);
                DatabaseConfig.CheckPath(lines);
                string userName = DatabaseConfig.UserName;
                string password = DatabaseConfig.Password;

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

                Console.WriteLine(DatabaseConfig.ConnectionString);

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

                    // Process each query and corresponding Excel file
                    ProcessDataForList(devspContext, targetList, "Advertising", DatabaseConfig.QueryFiles[0], DatabaseConfig.ExcelFiles[0]);
                    ProcessDataForList(devspContext, targetList, "Donation", DatabaseConfig.QueryFiles[1], DatabaseConfig.ExcelFiles[1]);
                    ProcessDataForList(devspContext, targetList, "Organization", DatabaseConfig.QueryFiles[2], DatabaseConfig.ExcelFiles[2]);
                    ProcessDataForList(devspContext, targetList, "PurchaseOfTicket", DatabaseConfig.QueryFiles[3], DatabaseConfig.ExcelFiles[3]);
                }

                Console.WriteLine("Data uploaded to SharePoint successfully!");
            }
            catch (Exception ex)
            {
                HandleError(ex.Message);
            }
        }

        static void ProcessDataForList(ClientContext context, List targetList, string listName, string queryFilePath, string excelFilePath)
        {
            try
            {
                string query = File.ReadAllText(queryFilePath);
                DataTable excelData = ReadCsvFile(excelFilePath);
                DataTable databaseData = new DataTable();

                // Fetch data from the database
                GetDatafromSQL(DatabaseConfig.ConnectionString, databaseData, query);

                // Process data for the SharePoint list
                ProcessDataForSharePoint(context, targetList, listName, databaseData, excelData);
            }
            catch (Exception ex)
            {
                HandleError($"Error processing data for {listName}: {ex.Message}");
            }
        }

        static void ProcessDataForSharePoint(ClientContext context, List targetList, string listName, DataTable databaseData, DataTable excelData)
        {
            foreach (DataRow row in databaseData.Rows)
            {
                ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                ListItem newItem = targetList.AddItem(itemCreateInfo);

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

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

                newItem.Update();
            }

            context.ExecuteQuery();
        }

        static void 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($"Error fetching data from SQL: {ex.Message}");
            }
        }

        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($"Error reading CSV file: {ex.Message}");
            }

            return csvData;
        }

        static void EmailSender(string subject, string body)
        {
            try
            {
                MailMessage message = new MailMessage
                {
                    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($"Error sending email: {ex.Message}");
            }
        }

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

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

                switch (key)
                {
                    case "connectionString":
                        ConnectionString = value;
                        break;
                    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 =