如何通过Windows Task Scheduler实现ASP.NET与SQL Server Express定时数据检查？

Great question! Let's break this down step by step since you're working with ASP.NET, SQL Server Express, and need to tie things together with Windows Task Scheduler. SQL Server Express doesn't include SQL Server Agent (the built-in scheduler for full SQL Server editions), so Windows Task Scheduler is actually a perfect fit here—don't worry, it's not just for C++ at all, it works seamlessly with .NET apps too.

You'll need a standalone .NET program (console app or ASP.NET Core Worker Service) to handle three key tasks:

1. Connect to SQL Server Express and query records that meet your criteria (saved for 2+ days with no status change)
2. Call your SendEmail() function for each qualifying record
3. Optional: Mark records as alerted to avoid duplicate emails

Then use Windows Task Scheduler to run this program on a regular schedule (e.g., daily, hourly—whatever fits your needs).

1. Build the .NET Program (Console App Example) #

Console apps are lightweight, easy to deploy, and ideal for scheduled tasks.

1.1 Create the Project #

Open Visual Studio and create a .NET 6+ console app (use .NET Framework if your ASP.NET project is on that stack).

1.2 Add Core Logic #

Install required NuGet packages first:

• Microsoft.Data.SqlClient (for SQL Server connections)
• Your existing email library (e.g., MailKit or System.Net.Mail—match whatever your SendEmail() uses)

Here's the core code (replace placeholders with your actual database/email details):

using Microsoft.Data.SqlClient;
using System.Net.Mail;

namespace DbStatusAlertService
{
    class Program
    {
        static void Main(string[] args)
        {
            // Replace with your SQL Server Express connection string
            string connString = @"Server=.\SQLEXPRESS;Database=YourAppDb;Trusted_Connection=True;TrustServerCertificate=True;";
            
            // Query for records: 2+ days old, unmodified status, not yet alerted
            string query = @"
                SELECT Id, RecipientEmail, Status, CreatedDate
                FROM YourTargetTable
                WHERE DATEDIFF(day, CreatedDate, GETDATE()) >= 2
                AND Status = 'Unchanged' -- Replace with your target status value
                AND IsAlertSent = 0; -- Optional: Add this flag to prevent duplicates
            ";

            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int recordId = reader.GetInt32(0);
                            string email = reader.GetString(1);

                            // Call your existing SendEmail function
                            SendEmail(email, $"Alert: Record {recordId} has remained unchanged for 2 days.");

                            // Optional: Mark the record as alerted
                            MarkAlertAsSent(conn, recordId);
                        }
                    }
                }
            }
        }

        // Replace with your actual SendEmail implementation
        static void SendEmail(string toAddress, string alertBody)
        {
            using (MailMessage mail = new MailMessage("your-alerts@domain.com", toAddress))
            {
                mail.Subject = "Unchanged Record Alert";
                mail.Body = alertBody;
                using (SmtpClient smtp = new SmtpClient("smtp.yourdomain.com", 587))
                {
                    smtp.Credentials = new System.Net.NetworkCredential("your-alerts@domain.com", "your-email-password");
                    smtp.EnableSsl = true;
                    smtp.Send(mail);
                }
            }
        }

        // Optional: Update the database to mark the alert as sent
        static void MarkAlertAsSent(SqlConnection conn, int recordId)
        {
            string updateQuery = @"
                UPDATE YourTargetTable
                SET IsAlertSent = 1, AlertSentDate = GETDATE()
                WHERE Id = @RecordId;
            ";
            using (SqlCommand cmd = new SqlCommand(updateQuery, conn))
            {
                cmd.Parameters.AddWithValue("@RecordId", recordId);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

• Tip: If your SendEmail() is already part of your ASP.NET project, extract it to a shared class library so both projects can use it—no duplicate code!

2. Publish the Program #

Right-click the project → Publish → Choose "Folder" as the target. Save the output to a fixed location (e.g., C:\ScheduledTasks\DbStatusAlert\). Test running the .exe manually first to confirm it connects to the database, sends emails, and updates records correctly.

3. Configure Windows Task Scheduler #

Now hook up the .exe to run on your desired schedule:

3.1 Open Task Scheduler #

Press Win+R, type taskschd.msc, and hit Enter.

3.2 Create a Basic Task #

• Click Create Basic Task, name it (e.g., "Database Status Alert"), add a description, then click Next.
• Choose a trigger (e.g., "Daily"), set the time (e.g., 2 AM to avoid peak traffic), then click Next.
• Select Start a program as the action, then click Next.
• In "Program or script", browse to your published .exe file (e.g., C:\ScheduledTasks\DbStatusAlert\DbStatusAlertService.exe). Add any command-line arguments if needed (e.g., different connection strings for dev/prod), then click Next.
• Check "Open the Properties dialog for this task when I click Finish", then click Finish.

3.3 Tweak Task Properties (Critical) #

In the properties window:

• General tab: Check "Run whether user is logged on or not" (ensures the task runs even if no one is signed into the server).
• Settings tab: Check "Allow task to be run on demand" (for easy testing), "Stop the task if it runs longer than" (e.g., 1 hour to prevent hangs), and "If the task fails, restart every" (e.g., 5 minutes, 3 retries).

4. Alternative: SQL Script + PowerShell #

If you prefer working with SQL over .NET, you can use a SQL query + PowerShell script instead:

1. Create a SQL script (CheckStatus.sql) to fetch qualifying records:

SELECT Id, RecipientEmail FROM YourTargetTable WHERE DATEDIFF(day, CreatedDate, GETDATE()) >=2 AND Status='Unchanged' AND IsAlertSent=0;

2. Create a PowerShell script (SendAlerts.ps1) to run the query and send emails:

# Fetch qualifying records
$records = Invoke-SqlCmd -ServerInstance ".\SQLEXPRESS" -Database "YourAppDb" -InputFile "C:\ScheduledTasks\CheckStatus.sql"

foreach ($record in $records) {
    # Send email via PowerShell
    Send-MailMessage -From "your-alerts@domain.com" -To $record.RecipientEmail -Subject "Unchanged Record Alert" -Body "Alert: Record $($record.Id) is unchanged for 2 days" -SmtpServer "smtp.yourdomain.com" -Port 587 -UseSsl -Credential (Get-Credential)
    
    # Mark record as alerted
    Invoke-SqlCmd -ServerInstance ".\SQLEXPRESS" -Database "YourAppDb" -Query "UPDATE YourTargetTable SET IsAlertSent=1 WHERE Id=$($record.Id)"
}

Then set up Windows Task Scheduler to run powershell.exe with arguments: -ExecutionPolicy Bypass -File "C:\ScheduledTasks\SendAlerts.ps1"

Note: This is less flexible if your SendEmail() has complex logic (e.g., HTML templates, attachments), so stick with the .NET approach for those cases.

• Permissions: Ensure the user running the task has read/write access to your SQL Server Express database.
• Logging: Add logging to your .NET program (e.g., using Serilog or Microsoft.Extensions.Logging) to track runs and troubleshoot issues. Save logs to a dedicated folder (e.g., C:\ScheduledTasks\Logs\).
• Avoid Duplicates: Always add a flag like IsAlertSent to your table to prevent sending the same email multiple times.
• Test Thoroughly: Run the .exe or PowerShell script manually before relying on the scheduled task to catch any connection/email issues.

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