Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
3.7 kB
1
Indexable
Never
using System;
using System.Data;
using System.Net;
using System.Net.Mail;
using System.Security;

namespace FetchDataToSharePoint
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string configFilePath = @"D:\CSRForm\CSRForm.txt";
                ConfigFileData configFileData = DatabaseConfig.ReadConfigFile(configFilePath);
                string userName = configFileData.UserName;
                SecureString securePassword = new SecureString();
                foreach (char c in configFileData.Password)
                {
                    securePassword.AppendChar(c);
                }

                UploadToSharePoint(userName, securePassword);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
                // Optionally, send an email with the error details
                EmailSender("Error in FetchDataToSharePoint", ex.ToString());
            }
        }

        public static void UploadToSharePoint(string userName, SecureString securePassword)
        {
            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 dt = GetDatafromSQL();
                
                foreach (DataRow dr in dt.Rows)
                {
                    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                    ListItem newItem = targetList.AddItem(itemCreateInfo);

                    // Assuming these are the correct column names in your SharePoint list
                    newItem["CSCNumber"] = dr["CSCNumber"];
                    // Add other columns as needed

                    newItem.Update();
                }

                devspContext.ExecuteQuery();
            }
        }

        public static DataTable GetDatafromSQL()
        {
            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}";

            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dataTable);
                }
            }

            return dataTable;
        }

        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}");
            }
        }
    }
}
Leave a Comment