Need logic for sending email

Cenk 981 Reputation points
2023-05-16T12:18:39.5866667+00:00

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.

sampledata

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 />");
...
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,483 questions
0 comments No comments
{count} votes

Accepted answer
  1. Sedat SALMAN 13,265 Reputation points
    2023-05-16T13:06:21.7966667+00:00

    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);
        }
    }
    
    
    
    

0 additional answers

Sort by: Most helpful