求C#代码或MSSQL触发器：Idea_Date_Of_Submission超5天发待办提醒邮件

Hey there! Let's tackle this problem of sending reminders for pending ideas that have been submitted over 5 days ago. I'll cover two practical approaches depending on where you want to handle the logic—either directly in SQL Server or in your C# application.

一、MSSQL 定时作业 + 存储方案 #

This is a great option if you want to keep the logic within your database layer. First, you'll need to set up Database Mail in SQL Server (if you haven't already) to enable email sending.

Step 1: Configure Database Mail #

1. Open SQL Server Management Studio (SSMS), expand your server → Management → Database Mail
2. Follow the wizard to create a mail profile and link it to your email account (e.g., a company SMTP account)

Step 2: Create the Reminder Stored Procedure #

CREATE PROCEDURE SendPendingIdeaReminders
AS
BEGIN
    SET NOCOUNT ON;

    -- Variables to hold email details and idea info
    DECLARE @RecipientEmail NVARCHAR(100), @IdeaId INT, @SubmissionDate DATE;

    -- Cursor to loop through eligible records (adjust the status filter to match your actual 'pending' value)
    DECLARE IdeaCursor CURSOR FOR
    SELECT 
        u.Email, -- Assume you have a Users table linked to Idea via CreatedByUserId
        i.IdeaId,
        i.Idea_Date_Of_Submission
    FROM Idea i
    JOIN Users u ON i.CreatedByUserId = u.UserId
    WHERE 
        DATEDIFF(DAY, i.Idea_Date_Of_Submission, GETDATE()) > 5
        AND i.idea_status = 'Pending'; -- Replace with your actual pending status value (e.g., 0, 'Unprocessed')

    OPEN IdeaCursor;
    FETCH NEXT FROM IdeaCursor INTO @RecipientEmail, @IdeaId, @SubmissionDate;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Send the reminder email
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'YourDatabaseMailProfile', -- Replace with your configured mail profile name
            @recipients = @RecipientEmail,
            @subject = '操作待处理提醒',
            @body = '您提交的创意（ID: ' + CAST(@IdeaId AS NVARCHAR(10)) + '）已超过5天未处理，请及时跟进。提交日期：' + CONVERT(NVARCHAR(20), @SubmissionDate, 120);

        FETCH NEXT FROM IdeaCursor INTO @RecipientEmail, @IdeaId, @SubmissionDate;
    END

    CLOSE IdeaCursor;
    DEALLOCATE IdeaCursor;
END

Step 3: Set Up a SQL Server Agent Job #

1. In SSMS, expand SQL Server Agent → Jobs → Right-click → New Job
2. Add a step that executes the SendPendingIdeaReminders stored procedure
3. Configure a schedule to run daily (e.g., every morning at 9 AM)

二、C# 后台服务实现 #

If you prefer to handle this logic in your application layer (for more flexibility, like integrating with your app's user system), a .NET Core Hosted Service works perfectly. You can also use libraries like Hangfire for more advanced scheduling.

Example .NET Core Background Service #

using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System;
using System.Data.SqlClient;
using System.Net;
using System.Net.Mail;
using System.Threading;
using System.Threading.Tasks;

public class IdeaReminderService : BackgroundService
{
    private readonly ILogger<IdeaReminderService> _logger;
    private readonly string _dbConnectionString;
    private readonly SmtpClient _smtpClient;

    // Inject configuration from appsettings.json
    public IdeaReminderService(ILogger<IdeaReminderService> logger, IConfiguration configuration)
    {
        _logger = logger;
        _dbConnectionString = configuration.GetConnectionString("YourDatabaseConnection");
        
        // Initialize SMTP client (update with your email server details)
        _smtpClient = new SmtpClient("smtp.yourcompany.com")
        {
            Port = 587,
            Credentials = new NetworkCredential("noreply@yourcompany.com", "your-email-password"),
            EnableSsl = true,
        };
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        _logger.LogInformation("Idea reminder service started.");

        // Run daily (adjust the delay as needed)
        while (!stoppingToken.IsCancellationRequested)
        {
            try
            {
                await SendPendingRemindersAsync();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to send idea reminders.");
            }

            // Wait 24 hours before next run
            await Task.Delay(TimeSpan.FromHours(24), stoppingToken);
        }

        _logger.LogInformation("Idea reminder service stopped.");
    }

    private async Task SendPendingRemindersAsync()
    {
        const string query = @"
            SELECT 
                u.Email,
                i.IdeaId,
                i.Idea_Date_Of_Submission
            FROM Idea i
            JOIN Users u ON i.CreatedByUserId = u.UserId
            WHERE 
                DATEDIFF(DAY, i.Idea_Date_Of_Submission, GETDATE()) > 5
                AND i.idea_status = 'Pending'"; // Replace with your actual pending status

        using (var conn = new SqlConnection(_dbConnectionString))
        {
            await conn.OpenAsync();
            using (var cmd = new SqlCommand(query, conn))
            using (var reader = await cmd.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    var recipient = reader.GetString(0);
                    var ideaId = reader.GetInt32(1);
                    var submissionDate = reader.GetDateTime(2);

                    var mail = new MailMessage("noreply@yourcompany.com", recipient)
                    {
                        Subject = "操作待处理提醒",
                        Body = $"您提交的创意（ID: {ideaId}）已超过5天未处理，请及时跟进。提交日期：{submissionDate:yyyy-MM-dd HH:mm:ss}",
                        IsBodyHtml = false // Set to true if you want HTML-formatted emails
                    };

                    await _smtpClient.SendMailAsync(mail);
                    _logger.LogInformation($"Reminder sent to {recipient} for Idea ID {ideaId}.");
                }
            }
        }
    }
}

// Register the service in Program.cs
// builder.Services.AddHostedService<IdeaReminderService>();

Key Notes #

• Replace placeholders like connection strings, SMTP details, and idea_status values with your actual system's settings
• If you need more precise scheduling (e.g., run only on weekdays), consider using Hangfire instead of a simple delay loop—it's great for recurring jobs
• Ensure your application has permission to access the database and send emails through your SMTP server

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