如何改造C#邮件告警系统实现多用户批量邮件发送？

Got it, let's get your alert system sending emails to all users instead of just User1! The main issues in your current code are:

• You're hardcoding the query to only pull User1's data
• You're looping through all rows but only sending one email at the end, not grouping alerts per user

Here's how to refactor your code to handle multiple users properly:

Step 1: Update the SQL Query #

First, remove the hardcoded WHERE RptAlertRecipient.Name= 'User1' clause so we get alerts for all users:

SELECT 
    RptAlertRecipient.Name, 
    RptAlertRecipient.Email, 
    RptAlerts.Factory, 
    RptAlerts.AlertTime, 
    RptAlerts.Description 
FROM RptAlerts 
INNER JOIN RptAlertTypes ON RptAlerts.AlertTypeID = RptAlertTypes.ID 
INNER JOIN RptAlertType_RecipientMapping ON RptAlertTypes.ID = RptAlertType_RecipientMapping.AlertTypeID 
INNER JOIN RptAlertRecipient ON RptAlertType_RecipientMapping.AlertRecipientID = RptAlertRecipient.ID 
ORDER BY RptAlertRecipient.Name ASC

Step 2: Group Alerts by User #

We'll use LINQ to group the DataTable rows by each user (using their Name and Email as the key). This way, each group contains all alerts for a single user.

Step 3: Send Emails for Each User Group #

Loop through each user group, build their personalized alert email, and send it.

Modified Complete Code #

using System;
using System.Timers;
using System.Windows.Forms;
using System.Net.Mail;
using System.Data;
using System.Speech.Synthesis;
using System.Collections.Generic;
using System.Linq; // Add this namespace for LINQ grouping

namespace Alerts
{
    public partial class frmAlerts : Form
    {
        SpeechSynthesizer speechSynthesizerObj;
        Common ComMsg = new Common();
        AlertException error = new AlertException();

        public frmAlerts()
        {
            InitializeComponent();
            this.WindowState = FormWindowState.Minimized;
        }

        private void frmAlerts_Load(object sender, EventArgs e)
        {
            try
            {
                System.Timers.Timer timer = new System.Timers.Timer(20 * 60 * 1000);
                timer.Elapsed += new ElapsedEventHandler(SendAlerts);
                timer.Start();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error in application Load: " + ex.Message);
            }
        }

        public void SendAlerts(object source, ElapsedEventArgs e)
        {
            try
            {
                // Fetch all alerts for all mapped users (no hardcoded filter)
                DataSet DatMsg = ComMsg.ReturnDataSet(@"
                    SELECT RptAlertRecipient.Name, RptAlertRecipient.Email, RptAlerts.Factory, RptAlerts.AlertTime, RptAlerts.Description 
                    FROM RptAlerts 
                    INNER JOIN RptAlertTypes ON RptAlerts.AlertTypeID = RptAlertTypes.ID 
                    INNER JOIN RptAlertType_RecipientMapping ON RptAlertTypes.ID = RptAlertType_RecipientMapping.AlertTypeID 
                    INNER JOIN RptAlertRecipient ON RptAlertType_RecipientMapping.AlertRecipientID = RptAlertRecipient.ID 
                    ORDER BY RptAlertRecipient.Name ASC");

                // Exit early if there are no alerts to send
                if (DatMsg.Tables.Count == 0 || DatMsg.Tables[0].Rows.Count == 0)
                {
                    return;
                }

                // Group rows by unique user (Name + Email ensures no duplicate user groups)
                var userAlertGroups = DatMsg.Tables[0].AsEnumerable()
                    .GroupBy(row => new 
                    { 
                        Name = row.Field<string>("Name"), 
                        Email = row.Field<string>("Email") 
                    })
                    .ToList();

                // Process each user's alerts and send their email
                foreach (var group in userAlertGroups)
                {
                    string toName = group.Key.Name;
                    string toEmail = group.Key.Email;
                    int alertCount = group.Count();

                    // Build personalized email body
                    string msgBody = $"Dear {toName},<br/><br/>";
                    msgBody += $"<b>You Have {alertCount} Alerts</b><br/><br/>";
                    
                    // Add each alert with context (factory + time)
                    foreach (var row in group)
                    {
                        string factory = row.Field<string>("Factory");
                        string alertTime = row.Field<string>("AlertTime");
                        string description = row.Field<string>("Description");
                        msgBody += $"• <b>{factory} - {alertTime}</b>: {description}<br/>";
                    }

                    msgBody += "<br/><br/>Regards<br/>Sent by Alert Service<br/>(Please do not reply to this email.)";

                    // Create and send the email
                    MailMessage msg = new MailMessage(
                        from: "helpdesk@mydomain.com",
                        to: toEmail,
                        subject: $"Alert In Time : You Have {alertCount} Alerts",
                        body: msgBody
                    );
                    msg.IsBodyHtml = true;

                    // Use 'using' to ensure SMTP client is disposed properly
                    using (SmtpClient clnt = new SmtpClient("outlook.mydomain.local", 25))
                    {
                        clnt.EnableSsl = false;
                        clnt.Credentials = new System.Net.NetworkCredential("helpdesk@mydomain.com", "password");
                        clnt.Send(msg);
                    }
                }
            }
            catch (Exception ex)
            {
                error.ExceptionMessage = ex.ToString();
                speechSynthesizerObj = new SpeechSynthesizer();
                speechSynthesizerObj.SpeakAsync(ex.Message);
            }
        }
    }
}

Key Improvements Explained #

• Removed hardcoded user filter: Now the query fetches alerts for all users mapped to alert types
• LINQ grouping: GroupBy creates a separate group for each unique user (using both Name and Email to avoid duplicates if users share names)
• Per-user personalized emails: Each user gets an email with only their alerts, formatted with clear context (factory and alert time)
• Proper resource disposal: Using using for the SmtpClient ensures the connection is closed properly after sending
• Early exit logic: Avoids unnecessary processing when there are no alerts to send

内容的提问来源于stack exchange，提问作者Lahiru Sandaruwan