I found a great resource for building a serverless report server with azure functions that was using javascript so I thought I would make a version using C#.

The Problem

We have a task tracking application where we want task owners to be alerted about tasks that are due the next day, and managers to be alerted when a task is overdue. Alerts should be delivered through email everyday at 8:00 AM.

Dependencies

• Send Grid Account

Solution

To simplify the query that we have to send from the Azure Function, we created two SQL views that will already give us the list of tasks due tomorrow and the tasks that are overdue.

CREATE VIEW DueTomorrow AS 
SELECT
 ... where (CONVERT(date, dbo.EmployeeTasks.DateToDo) = CONVERT(date, DATEADD(DAY, 1, GETDATE())))

CREATE VIEW OverdueTasks AS 
SELECT
... where (CONVERT(date, dbo.EmployeeTasks.DateToDo) < CONVERT(date, GETDATE()))

The next step was to create the Azure Function. As this is a task we want to execute everyday, the type of Azure Function we should create is a Timer Trigger.

A thing to take note of is that the default for CRON expressions on the TimerTrigger are in UTC so if we would like a localized time, we'll need to add an app setting WEBSITE_TIME_ZONE to set this. The Microsoft Time Zone Index has a list of valid values for this setting.

We create 2 helper classes to help us package the data from SQL for sending into SendGrid. These are basically the fields that we want to be able to pass into SendGrid's email template.

Next we connect to the database and fetch the fields from the views we created and add them to the TaskList object. Note that the DateToDo is stored in the class as a string as there are not much formatting options when the data has been sent across to the SendGrid side. On creation, we already format this to a ShortDateString.

var tasklist = new TaskList();
var connectionString = Environment.GetEnvironmentVariable("dbConnection");
using (SqlConnection conn = new SqlConnection(connectionString)) {
    conn.Open();
    var query = @"select TaskName, GenericDescription, OwnerEmail, 
OwnerName, DateToDo, ManagerName, ManagerEmail, Name from 
DueTomorrowWithManagerDetails";
    SqlCommand cmd = new SqlCommand(query, conn);
    using (SqlDataReader reader = cmd.ExecuteReader()) {
        if (reader.HasRows) {
            while (reader.Read()) {
                tasklist.Tasks.Add(new EmployeeTask {
                    TaskName = reader.GetString(0),
                    GenericDescription = reader.GetString(1),
                    OwnerEmail = reader.GetString(2),
                    OwnerName = reader.GetString(3),
                    DateToDo = reader.GetDateTime(4).ToShortDateString(),
                    ManagerName =reader.GetString(5),
                    ManagerEmail = reader.GetString(6),
                    Name = reader.GetString(7)
                });
            }
        }
        reader.Close();
    }
}

Now that we have the data, we can pass this along to send grid. Let's update the function signature to include a reference to the send grid message collector. The ApiKey attribute will be the name of the app setting that contains our actual send grid api key

The first thing we get is the template ID. This will be the ID of the SendGrid template to use for this batch of emails (more on this later).

We then group the tasks by task owners so that they get a consolidated list of tasks that are due tomorrow instead of receiving an email for each one.

For each grouped task, we create a SendGridMessage object with the corresponding template data and add this to the message collector. At the end of the processing, the SendGrid web job takes all the messages and sends them out.

var templateID = Environment.GetEnvironmentVariable("dueTomorrowTemplate");
var byBrickOnboarding = new EmailAddress("noreply@bybrick.se", "byBrick Onboarding");
            
var groupByTaskOwner = tasklist.Tasks.GroupBy(e => e.OwnerEmail);
            
foreach (var group in groupByTaskOwner) {
    var message = new SendGridMessage()
    {
        TemplateId = templateID,
        From = byBrickOnboarding
    };
    message.AddTo(group.Key, group.First().OwnerName);
    message.SetTemplateData(new TaskList { Tasks = group.ToList() });

    await messageCollector.AddAsync(message);
}

The last part is creating the SendGrid Template.

The SendGrid Dashboard will have a link to create Transactional Templates (1). Once we create our template, we will have the ID (2) which we set as an app setting for templateID in our function app.

We can design the template with code where we can provide test data and see in real time what the email will look like.

Remember to replicate the app settings when deploying your function app so the right API key, DB Connection string and template IDs are available on the deployed function app.

The complete code to the function app is below

[FunctionName("DueTomorrow")]
    public static async Task Run([TimerTrigger("0 30 10 * * *")]TimerInfo myTimer,
                                    ILogger log,
            [SendGrid(ApiKey = "SendGridAPI")] IAsyncCollector messageCollector)
    {
        var status = "No Emails Sent.";
        var tasklist = new TaskList();
        var connectionString = Environment.GetEnvironmentVariable("dbConnection");
        using (SqlConnection conn = new SqlConnection(connectionString)) {
            conn.Open();

            var query = @"select TaskName, GenericDescription, OwnerEmail,
 OwnerName, DateToDo, ManagerName, ManagerEmail, Name from 
DueTomorrowWithManagerDetails";
            SqlCommand cmd = new SqlCommand(query, conn);
            using (SqlDataReader reader = cmd.ExecuteReader()) {
                if (reader.HasRows) {
                    while (reader.Read()) {
                        tasklist.Tasks.Add(new EmployeeTask {
                            TaskName = reader.GetString(0),
                            GenericDescription = reader.GetString(1),
                            OwnerEmail = reader.GetString(2),
                            OwnerName = reader.GetString(3),
                            DateToDo = reader.GetDateTime(4).ToShortDateString(),
                            ManagerName =reader.GetString(5),
                            ManagerEmail = reader.GetString(6),
                            Name = reader.GetString(7)
                        });
                    }
                }
                reader.Close();
            }
        }
                       
        var template = Environment.GetEnvironmentVariable("dueTomorrowTemplate");
        var byBrickOnboarding = new EmailAddress("noreply@bybrick.se", "byBrick Onboarding");
            
        var groupByTaskOwner = tasklist.Tasks.GroupBy(e => e.OwnerEmail);
            
        if (tasklist.Tasks.Count()>0)
            status = @"{groupByTaskOwner.Count()} emails sent";

        foreach (var group in groupByTaskOwner) {
            var message = new SendGridMessage()
            {
                Subject = "byOnboarding: Tasks Due Tomorrow",
                TemplateId = template,
                From = byBrickOnboarding
            };
            message.AddTo(group.Key, group.First().OwnerName);
            message.SetTemplateData(new TaskList { Tasks = group.ToList() });

            await messageCollector.AddAsync(message);
        }
            
        log.LogInformation(@"{status} for {DateTime.Now.ToShortDateString()}");
    }
}