Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
8.1 kB
3
Indexable
Never
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);

                    DataTable mainDT;

                    //Console.WriteLine(dt.Tables);
                    /**/
                    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                    ListItem newItem = targetList.AddItem(itemCreateInfo);
                    
                }
                    //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...
}
Leave a Comment