Need logic for sending email

Question

Hi guys,

I am working on a worker service. I am getting data from the SQL server and converting it into an IEnum object with Dapper.

private async Task GetData()
        {
            var connString = _configuration["ConnectionStrings:Production"];
            await using var sqlConnection = new SqlConnection(connString);
            sqlConnection.Open();

            await using var command = new SqlCommand { Connection = sqlConnection };
            const string sql =
                @"select c.Pin, c.Serial, gcr.productCode, gcr.productDescription, gcr.purchaseStatusDate, gcr.totalPrice, o.orderBy, o.email, o.phone,                         o.orderNo, od.id as OrderDetailId
                           from GameConfirmResponses gcr
                           inner join Coupons c ON gcr.Id = c.ConfirmResponseID
                           inner join OrderDetail od ON od.referenceId = gcr.referenceId
                           inner join Orders o On o.orderNo = gcr.clientTrxRef
                           where od.status = 2";
			//Using Dapper
            var results = await sqlConnection
                .QueryAsync<OrderEmailDto>(sql);
...

Here is the screenshot of data getting from database.

I would like to send emails based on orderNo. As you can see from the screenshot, 2 emails (628CG9895K with 6 records and 879BP2934N with 2 records) should be sent based on orderNo. How can I achieve this logic?

Here is a portion of email method:

private async Task SendEmail(IEnumerable<OrderEmailDto> result)
    {
        var sb = new StringBuilder();
        sb.AppendLine("Dear " + Coupons.orderBy + ",");
        sb.AppendLine("<br />");
        sb.AppendLine("<br />");
        sb.AppendLine("Your order has been successfully completed. Thank you for choosing us.");
        sb.AppendLine("<br />");
        sb.AppendLine("<br />");

        sb.Append("<table border='1'>");
       
        foreach (var code in result)
        {
            sb.Append("<tr>");
            sb.Append($"<td>Product: <b>{code?.ProductCode}</b></td>");
            sb.Append($"<td>Description: <b>{code?.ProductDescription}</b></td>");
            sb.Append($"<td>Pin: <b>{code?.Pin}</b></td>");
            sb.Append($"<td>Serial: <b>{code?.Serial}</b></td>");
            sb.Append("</tr>");

        }

        sb.Append("</table>");
        sb.AppendLine("<br />");
...

Answer 1

Here's an example of how you can modify your SendEmail method

private async Task SendEmail(IEnumerable<OrderEmailDto> result)
{
    // Grouping the orders based on the order number
    var groupedOrders = result.GroupBy(o => o.OrderNo);

    foreach (var orderGroup in groupedOrders)
    {
        var sb = new StringBuilder();
        sb.AppendLine($"Dear {orderGroup.First().OrderBy},");
        sb.AppendLine("<br />");
        sb.AppendLine("<br />");
        sb.AppendLine("Your order has been successfully completed. Thank you for choosing us.");
        sb.AppendLine("<br />");
        sb.AppendLine("<br />");

        sb.Append("<table border='1'>");

        foreach (var code in orderGroup)
        {
            sb.Append("<tr>");
            sb.Append($"<td>Product: <b>{code?.ProductCode}</b></td>");
            sb.Append($"<td>Description: <b>{code?.ProductDescription}</b></td>");
            sb.Append($"<td>Pin: <b>{code?.Pin}</b></td>");
            sb.Append($"<td>Serial: <b>{code?.Serial}</b></td>");
            sb.Append("</tr>");
        }

        sb.Append("</table>");
        sb.AppendLine("<br />");
        // ...

        // Send the email here. You need the email of the recipient and the email content.
        // You can get the recipient's email from any object in the orderGroup.
        string recipientEmail = orderGroup.First().Email;
        string emailContent = sb.ToString();

        // Send the email based on your preferred way (SMTP, MailKit, SendGrid, etc.)
        // await SendTheEmail(recipientEmail, emailContent);
    }
}