Check Azure SQL Table for emails to send mvc C#

Question

I am trying to figure out the best way to check a Table in SQL Azure that has information about sending emails. My users will schedule what date the email is supposed to be sent, and Every day around 8 AM I would like to check the table to see what emails need to send and automatically send them using my users information.

I want this to be a background task in case the user doesn't sign into our software on the date the email is supposed to send.

I believe I need to use the Azure Web job, but I can not find a good example using MVC C# and I am confused if you have to create another project in the existing application.

So the Table name is InstallmentsToSend the columns include:

-Email VARCHAR -SendToEmail VARCHAR -Subject VARCHAR -Body VARCHAR -Attachement VARBINARY -SendDate DATETIME -SmtpHost VARCHAR -Status VARCHAR

I am also wondering if an Azure Function would be a better option than an Azure Web job?

If anyone could include an example or a link to an example to help me with my task I would appreciate it.

Answer 1

https://learn.microsoft.com/en-us/azure/azure-functions/functions-create-your-first-function-visual-studio

This link helped me out to create the Azure Function through Visual Studio.

A couple things I found out

• Entity Framework is very picky with the versions of installs so make sure you get the right one
• SendGrid is one of the only options to be able to send emails through an azure application.
• Time Triggers are easy to set up when you realize what you are doing, and the the time is UTC time.

here is some sample code of what I accomplished. It checks a database for any emails that need to be sent on that date, and if it has not already been sent it will send with the credentials saved in the DB.

public static class Function1
{
    

    [FunctionName("Function1")]
    public static void Run([TimerTrigger(" 0 30 13 * * *")]TimerInfo myTimer, ILogger log) 
    {

        GenericRepository<EmailsToSendAzure> emailRepository = new GenericRepository<EmailsToSendAzure>();

        string cmd = "Select * From EmailsToSendAzure Where SendDate = '" + DateTime.Today + "' And Status = 'Not Sent'";
        List<EmailsToSendAzure> emailsNeedToSend = emailRepository.GetSelectAll(cmd);

        //var apiKey = System.Environment.GetEnvironmentVariable("SendGridKey");
        var client = new SendGridClient("YourKeyFromSendGrid");
        foreach (EmailsToSendAzure email in emailsNeedToSend)
        {

            ExecuteMail(client, email).Wait();

        }

        log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
    }
    static async Task ExecuteMail(SendGridClient client, EmailsToSendAzure email)
    {

        //var response = await client.SendEmailAsync(msg);

        //var msg = new SendGridMessage();
        var from = new EmailAddress(email.Email);
        var to = new EmailAddress(email.SendTo);
        var subject = email.Subject;
        var body = email.Body;
        

        var msg = MailHelper.CreateSingleEmail(from, to, subject, body, body);

        var response = await client.SendEmailAsync(msg);

    }
}

• Create the Azure Function FIRST in your azure portal which you can link to once you are publishing the Function.

-Any email address that is the From will need to be validated on SendGrid

-Send Grid validation through emails do not work well with google chrome. THIS GOT ME GOOD. Kept saying the website was not secure, but if you open it using Microsoft Edge the validation of your email will work, as well as any other validations they need.

If you need to email an attachment using SendGrid this is my Code below for that.

var msg = new SendGridMessage();
using (var stream = new MemoryStream())
            {
                report.ExportToPdf(stream);
                stream.Seek(0, SeekOrigin.Begin);
                
                msg.AddAttachment("YourAttachmentFile.pdf", System.Convert.ToBase64String(stream.ToArray()));
            }

-report.ExportToPDF() is a Devexpress XtraReport Item.

Answer 2

For this case, Best choice is "Logic App" in Azure.

For your purpose, Two steps enough in logic app

1. Recurrence
2. Send Message

Please find the url's as below. https://learn.microsoft.com/en-in/azure/event-grid/compare-messaging-services

Answer 3

1. A timer triggered WebJob could meet your need. Just use a simple code to send emails. Here is a tutorial about How to send emails from C#/.NET. On Azure, deploy your project as WebJob from Visual Studio or other ways. Configure a schedule to trigger it backgroud.

2. Another choice is using SQL Stored Procedures. Please check with this article: Send Email in SQL Server using Stored Procedure.