如何基于MSSQL与Mvc实现邮件调度：逾期未更新数据提醒

Alright, let's walk through how to build this email alert system for your MSSQL database using ASP.NET MVC. Here's a practical, step-by-step breakdown that you can adapt to your project:

First, you need a query to pull records where the last reviewed column hasn't been updated in over 5 days. Use the DATEDIFF function to calculate the gap between the last review date and today:

SELECT * 
FROM YourTableName
WHERE DATEDIFF(day, [last reviewed], GETDATE()) > 5
AND [last reviewed] IS NOT NULL -- Exclude records that were never reviewed

Pro tip: Test this query first to make sure it's returning the right records before moving on.

ASP.NET MVC doesn't have built-in scheduling, so you have two solid options:

Option A: Use Hangfire (Recommended) #

Hangfire is a lightweight, easy-to-integrate library for background tasks. It even comes with a dashboard to monitor job status.

1. Install the Hangfire NuGet package:

   Install-Package Hangfire
   

2. Configure Hangfire in Startup.cs to connect to your MSSQL database:

   public void ConfigureServices(IServiceCollection services)
   {
       // Add Hangfire with SQL Server storage
       services.AddHangfire(config => 
           config.UseSqlServerStorage(Configuration.GetConnectionString("YourDbConnection")));
       services.AddHangfireServer();
       
       // Your existing MVC service configuration...
   }
   
   public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
   {
       // Your existing middleware setup...
       app.UseHangfireDashboard(); // Access via /hangfire to monitor jobs
   }
   

3. Create a service to handle checking stale records and sending emails:

   public class StaleReviewAlertService
   {
       private readonly YourDbContext _dbContext;
       private readonly IEmailSender _emailSender;
   
       public StaleReviewAlertService(YourDbContext dbContext, IEmailSender emailSender)
       {
           _dbContext = dbContext;
           _emailSender = emailSender;
       }
   
       public void CheckAndSendAlerts()
       {
           // Fetch stale records using EF Core
           var staleRecords = _dbContext.YourTable
               .Where(r => DbFunctions.DiffDays(r.LastReviewed, DateTime.Now) > 5
                           && r.LastReviewed != null)
               .ToList();
   
           if (staleRecords.Any())
           {
               // Build email content
               var emailBody = $"⚠️ Found {staleRecords.Count} records that need review (over 5 days stale):\n\n";
               foreach (var record in staleRecords)
               {
                   emailBody += $"Record ID: {record.Id} | Last Reviewed: {record.LastReviewed:yyyy-MM-dd}\n";
               }
   
               // Send alert email
               _emailSender.SendEmailAsync("user@yourdomain.com", "Stale Review Alert", emailBody);
           }
       }
   }
   

4. Register the service and set up a recurring job (e.g., run every morning at 9 AM):

   public void ConfigureServices(IServiceCollection services)
   {
       // Existing config...
       services.AddScoped<StaleReviewAlertService>();
       services.AddTransient<IEmailSender, SmtpEmailSender>(); // We'll build this next
   }
   
   public void Configure(IApplicationBuilder app, IRecurringJobManager recurringJobManager, StaleReviewAlertService alertService)
   {
       // Existing config...
       // Schedule daily 9 AM check (Cron expression: "0 9 * * *")
       recurringJobManager.AddOrUpdate(
           "Daily-Stale-Review-Check",
           () => alertService.CheckAndSendAlerts(),
           "0 9 * * *"
       );
   }
   

Option B: Use Windows Task Scheduler (No Third-Party Libraries) #

If you prefer not to add a library, create an API endpoint and use Windows Task Scheduler to call it periodically:

1. Add an API controller to trigger the alert check:

   [Route("api/[controller]")]
   [ApiController]
   public class AlertController : ControllerBase
   {
       private readonly StaleReviewAlertService _alertService;
   
       public AlertController(StaleReviewAlertService alertService)
       {
           _alertService = alertService;
       }
   
       [HttpGet("TriggerStaleAlerts")]
       public IActionResult TriggerStaleAlerts()
       {
           _alertService.CheckAndSendAlerts();
           return Ok("Stale review check completed successfully.");
       }
   }
   

2. Create a PowerShell script to call the endpoint:

   Invoke-WebRequest -Uri "http://yourdomain/api/Alert/TriggerStaleAlerts"
   

3. Set up a Windows Task Scheduler job to run this script on your desired schedule (e.g., daily at 9 AM).

Build a basic SMTP email sender to handle sending alerts:

public interface IEmailSender
{
    Task SendEmailAsync(string email, string subject, string message);
}

public class SmtpEmailSender : IEmailSender
{
    private readonly IConfiguration _config;

    public SmtpEmailSender(IConfiguration config)
    {
        _config = config;
    }

    public async Task SendEmailAsync(string email, string subject, string message)
    {
        using var smtpClient = new SmtpClient(_config["Smtp:Host"])
        {
            Port = int.Parse(_config["Smtp:Port"]),
            Credentials = new NetworkCredential(_config["Smtp:Username"], _config["Smtp:Password"]),
            EnableSsl = bool.Parse(_config["Smtp:EnableSsl"]),
        };

        var mailMessage = new MailMessage
        {
            From = new MailAddress(_config["Smtp:FromEmail"]),
            Subject = subject,
            Body = message,
            IsBodyHtml = false, // Set to true if you want HTML emails
        };
        mailMessage.To.Add(email);

        await smtpClient.SendMailAsync(mailMessage);
    }
}

Add your SMTP settings to appsettings.json:

{
  "Smtp": {
    "Host": "smtp.yourdomain.com",
    "Port": "587",
    "Username": "alerts@yourdomain.com",
    "Password": "your-smtp-password",
    "EnableSsl": "true",
    "FromEmail": "alerts@yourdomain.com"
  }
}

• Manually trigger the job via the Hangfire dashboard or by calling the API endpoint to confirm it pulls stale records and sends emails correctly.
• Verify the last reviewed date logic works as expected (e.g., a record updated 6 days ago should trigger an alert, while one updated 4 days ago shouldn't).

内容的提问来源于stack exchange，提问作者Ashok Hoskera