如何实现定时发送库存表邮件或库存低量告警邮件？

Hey Carol, let's break down how to implement these two inventory monitoring features step by step, building on the email sending code you already have. We'll focus on making the code reusable, reliable, and easy to maintain.

First, let's turn your button-click email logic into a reusable method. This way, both the daily summary and low-stock alerts can use the same core functionality, and we'll move configuration values to Web.config (instead of hardcoding them) for easier updates.

private void SendEmail(string toAddress, string emailSubject, string emailBody)
{
    // Pull configuration from Web.config (we'll set this up later)
    string fromAddress = ConfigurationManager.AppSettings["InventoryAlertSenderEmail"];
    string smtpServer = ConfigurationManager.AppSettings["SmtpServer"];
    int smtpPort = int.Parse(ConfigurationManager.AppSettings["SmtpPort"]);
    string smtpUsername = ConfigurationManager.AppSettings["SmtpUsername"];
    string smtpPassword = ConfigurationManager.AppSettings["SmtpPassword"];

    try
    {
        using (MailMessage mail = new MailMessage(fromAddress, toAddress, emailSubject, emailBody))
        using (SmtpClient client = new SmtpClient(smtpServer))
        {
            client.Port = smtpPort;
            client.Credentials = new NetworkCredential(smtpUsername, smtpPassword);
            client.EnableSsl = true;
            mail.IsBodyHtml = true; // Enable HTML formatting for tables/lists
            client.Send(mail);
        }
    }
    catch (Exception ex)
    {
        // Log the error (use a tool like Log4Net or write to Windows Event Log)
        System.Diagnostics.Trace.WriteLine($"Failed to send inventory email: {ex.Message}");
        // Re-throw if you want to trigger an alert for failed sends, or handle silently
        throw;
    }
}

Next, we need methods to fetch stock data from your Stock Table and check for low-stock items. Let's assume your table has columns: ProductName, CurrentStock, and MinStock (adjust the query to match your actual schema).

// Simple class to hold stock data
public class StockItem
{
    public string ProductName { get; set; }
    public int CurrentStock { get; set; }
    public int MinStock { get; set; }
}

// Fetch all stock items from the database
private List<StockItem> GetCurrentStock()
{
    List<StockItem> stockItems = new List<StockItem>();
    string connectionString = ConfigurationManager.ConnectionStrings["YourDatabaseConnection"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        string query = "SELECT ProductName, CurrentStock, MinStock FROM StockTable";
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    stockItems.Add(new StockItem
                    {
                        ProductName = reader["ProductName"].ToString(),
                        CurrentStock = Convert.ToInt32(reader["CurrentStock"]),
                        MinStock = Convert.ToInt32(reader["MinStock"])
                    });
                }
            }
        }
    }
    return stockItems;
}

// Generate HTML body for daily stock summary
private string BuildDailyStockSummary(List<StockItem> stockItems)
{
    StringBuilder body = new StringBuilder();
    body.Append("<h2>Daily Stock Inventory Summary</h2>");
    body.Append("<table border='1' cellpadding='6' cellspacing='0'>");
    body.Append("<tr><th>Product</th><th>Current Stock</th><th>Minimum Required</th></tr>");
    
    foreach (var item in stockItems)
    {
        body.Append($"<tr><td>{item.ProductName}</td><td>{item.CurrentStock}</td><td>{item.MinStock}</td></tr>");
    }
    
    body.Append("</table>");
    return body.ToString();
}

// Check for low stock and generate alert content
private string CheckForLowStock(List<StockItem> stockItems, out bool hasLowStock)
{
    hasLowStock = false;
    StringBuilder alertBody = new StringBuilder();
    alertBody.Append("<h2>URGENT: Low Stock Alert</h2>");
    alertBody.Append("<p>The following products are below their minimum stock levels:</p>");
    alertBody.Append("<ul>");
    
    foreach (var item in stockItems)
    {
        if (item.CurrentStock < item.MinStock)
        {
            hasLowStock = true;
            alertBody.Append($"<li><strong>{item.ProductName}</strong>: Current stock = {item.CurrentStock} (Minimum required: {item.MinStock})</li>");
        }
    }
    
    alertBody.Append("</ul>");
    return hasLowStock ? alertBody.ToString() : string.Empty;
}

For reliable scheduled tasks in an ASP.NET Web Forms app, Hangfire is a great choice (it's free, easy to set up, and handles app pool recycles better than raw timers). Here's how to set it up:

1. Install the NuGet packages: Hangfire and Hangfire.SqlServer
2. Update your Global.asax to configure Hangfire and schedule the daily job:

protected void Application_Start(object sender, EventArgs e)
{
    // Configure Hangfire to use your database
    string dbConnection = ConfigurationManager.ConnectionStrings["YourDatabaseConnection"].ConnectionString;
    GlobalConfiguration.Configuration.UseSqlServerStorage(dbConnection);

    // Start the Hangfire background server
    var serverOptions = new BackgroundJobServerOptions();
    app.UseHangfireServer(serverOptions);
    app.UseHangfireDashboard(); // Access via your app URL + /hangfire

    // Schedule daily summary at 9 AM local time (Cron expression: 0 0 9 * * ?)
    RecurringJob.AddOrUpdate(
        "daily-stock-summary",
        () => SendDailyStockSummary(),
        "0 0 9 * * ?",
        TimeZoneInfo.Local
    );

    // Schedule low-stock checks every hour (adjust frequency as needed)
    RecurringJob.AddOrUpdate(
        "low-stock-check",
        () => SendLowStockAlerts(),
        "0 */1 * * *",
        TimeZoneInfo.Local
    );
}

// Method to trigger daily summary email
public void SendDailyStockSummary()
{
    string managerEmail = ConfigurationManager.AppSettings["ManagerEmail"];
    var stockData = GetCurrentStock();
    string emailBody = BuildDailyStockSummary(stockData);
    SendEmail(managerEmail, "Daily Stock Summary", emailBody);
}

// Method to check and send low-stock alerts
public void SendLowStockAlerts()
{
    string managerEmail = ConfigurationManager.AppSettings["ManagerEmail"];
    var stockData = GetCurrentStock();
    bool hasLowStock;
    string alertBody = CheckForLowStock(stockData, out hasLowStock);
    
    if (hasLowStock)
    {
        SendEmail(managerEmail, "Low Stock Alert - Immediate Action Needed", alertBody);
    }
}

Add these settings to your Web.config to store all configurable values in one place:

<appSettings>
  <!-- Email settings -->
  <add key="InventoryAlertSenderEmail" value="your-alert-email@example.com"/>
  <add key="SmtpServer" value="smtp.example.com"/>
  <add key="SmtpPort" value="587"/>
  <add key="SmtpUsername" value="your-alert-email@example.com"/>
  <add key="SmtpPassword" value="your-app-specific-password"/> <!-- Use app password if 2FA is enabled -->
  <!-- Recipient email -->
  <add key="ManagerEmail" value="manager@yourcompany.com"/>
</appSettings>

<connectionStrings>
  <add name="YourDatabaseConnection" connectionString="Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DB_NAME;Integrated Security=True;" providerName="System.Data.SqlClient"/>
</connectionStrings>

• Hangfire Reliability: If your app pool recycles, Hangfire will automatically resume pending jobs. For even more reliability, you can run Hangfire as a separate Windows service instead of within the web app.
• Error Handling: Always log email failures so you can troubleshoot if alerts aren't being sent.
• Alternative to Hangfire: If you don't want to use a third-party library, you can set up a Windows Task Scheduler job that makes an HTTP request to a dedicated web method (e.g., StockAlert.aspx/TriggerDailySummary) to trigger the email.
• Real-Time Alerts: If you want alerts immediately when stock drops below the threshold (instead of hourly checks), add the low-stock check logic to your inventory update code (e.g., when a product is sold or restocked).

内容的提问来源于stack exchange，提问作者carol cashman