I write now have a code that updates and lists files from the SFTP site into a database table. Whenever there's a new file uploaded on a client SFTP site I am supposed to get an email notification. In order to do that I set a task scheduler on my local machine. Now, I get an email every day at a set time which is great but how do I only and only get an email when a new file has been uploaded on the sftp site or a new row/s has been added into a database table. I do not want to get an email every day, only email notify me when there's a new file.

Please point me in the right direction. What do I do?

    public static void Main()
    {
        string host = @"";
        string username = "";
        string password = @"";
     

        Dictionary<string, string[]> fileMetadata = new Dictionary<string, string[]>();


        using (SftpClient sftp = new SftpClient(host, username, password))
        {
            List<DateTime> sftpdates = new List<DateTime>();
            IEnumerable<SftpFile> files = null;
            try
            {
                sftp.Connect();
                files = sftp.ListDirectory(".");

                // Get SFTP dates 

                foreach (var file in files)

                {
                    Console.Write(file.Name   "\t");
                    Console.WriteLine(file.Attributes.LastWriteTime);
                    if (!file.Name.Equals(".") || !file.Name.Equals(".."))
                    {

                    }
                    sftpdates.Add(file.Attributes.LastWriteTime);
                }
                sftp.Disconnect();
                sftpdates.Sort();
                sftpdates.Reverse();
                Console.WriteLine("Latest "   sftpdates[0]);
            }
            catch (Exception e)
            {
                Console.WriteLine("An exception has been caught "   e.ToString());
            }


            // get lastestfile recieved stored in a DB and Compare the dates ( greater or not ) if yes ;
            var con = new SqlConnection(@"connection string here");
            con.Open();
            {
                SqlCommand getMaxdateDB = new SqlCommand
               ("select * from LatestFile ", con);
                SqlDataReader reader = getMaxdateDB.ExecuteReader();
                DateTime DatabaseDate = new DateTime();
                while (reader.Read())
                {
                    DatabaseDate = reader.GetDateTime(0);
                }
                reader.Close();

                if (sftpdates[0].CompareTo(DatabaseDate) > 0)
                {
                    Console.WriteLine("Newer file exists");
                }
                else if (sftpdates[0].CompareTo(DatabaseDate) == 0)

                {
                    Console.WriteLine("Latest dates are same");
                }
            }

            // if yes // send new records to db 
            {
                foreach (var file in files)
                {
                    SqlCommand getFile = new SqlCommand(
                            "select FILE_NAME, date_modified from NewFileUpload where File_Name='"
                              file.Name   "';", con);
                    SqlDataReader readfile = getFile.ExecuteReader();
                    bool row = readfile.HasRows;
                    string fileName;
                    DateTime date_modified = new DateTime();
                    try
                    {
                        fileName = readfile.GetString(0);
                        date_modified = readfile.GetDateTime(1);
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                    readfile.Close();
                    if (row == false)
                    {
                        Console.WriteLine("New file added...");
                        DateTime myDateTime = file.Attributes.LastWriteTime;
                        string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
                        SqlCommand cm = new SqlCommand("insert into NewFileUpload(File_Name, File_Size, Date_Modified) values "  
                    "('"   file.Name   "', '"   file.Attributes.Size   "', '"   sqlFormattedDate   "')", con);
                        cm.ExecuteNonQuery();
                        fileMetadata.Add(file.Name, new string[] { file.Attributes.Size.ToString(), sqlFormattedDate });

                    }
                }
                // send email
                // EMAIL LOGIC
                SendEmailMultiFiles(fileMetadata);

                {
                    SqlCommand UpdateLatestDate = new SqlCommand
                        ("truncate table LatestFile", con);
                    UpdateLatestDate.ExecuteNonQuery();
                    UpdateLatestDate = new SqlCommand
                   ("INSERT INTO LatestFile select max(Date_Modified) from NewFileUpload where File_Name not in ('..' ,'.')", con);
                    UpdateLatestDate.ExecuteNonQuery()


                }
            }


        }
        Console.ReadKey();
    }

    static void SendEmail(string filename, long filesize, DateTime datemodified)


    {

        //List <> NewUploadList = List  <> NewUploadList = new List<>(); /// might only send the date or file name
        // Key = filename
        // value = [filesize(string), datemodified/lastwrite(string)]
        Dictionary<string, string[]> fileMetadata = new Dictionary<string, string[]>();
    }

    static void SendEmailMultiFiles(Dictionary<string, string[]> fileMetadata
   

        //List <> NewUploadList = List  <> NewUploadList = new List<>(); /// might only send the date or file name
        Console.WriteLine("Email has been sent.");
        string SendMailbody = "File(s) added<br>";
        //  filename   " file size: "
        //  filesize   " date modified: "
        //  datemodified;
        int i = 1;
        foreach (var item in fileMetadata)
        {
            SendMailbody  = i     ") "   item.Key   " file size: "
                  item.Value[0]   " date modified: "
                  item.Value[1]   "<br>";
        }

        string mailto = "";
        string mailfrom = "";

        MailMessage mail = new MailMessage();
        mail.From = new System.Net.Mail.MailAddress(mailfrom);

        SmtpClient smtp = new SmtpClient();
        smtp.Port = 587;
        smtp.EnableSsl = true;
        smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
        smtp.UseDefaultCredentials = false;
        smtp.Credentials = new NetworkCredential("", "");
        smtp.Host = "smtp.gmail.com";
     
        mail.Subject = "New File Incoming";
        mail.IsBodyHtml = true;
        mail.Body = SendMailbody;
        smtp.Send(mail);



    }
}

}

CodePudding user response：

Just check the fileMetadata object before sending to the function.

If (fileMetadata.Count >= 1)
{
    SendEmailMultiFiles(fileMetadata);
}