如何通过C#控制台应用执行SQL Server作业并在完成后返回值？

Great question! Unfortunately, the SQL Server Agent job objects in .NET (like those from Microsoft.SqlServer.Management.Smo.Agent) don’t expose built-in events to notify you when a job finishes—you’re right that the Start() and Stop() methods are void and don’t provide callbacks. But there are a few solid workarounds to achieve exactly what you want:

Workaround 1: Poll the job status periodically #

This is the most straightforward approach. You can query SQL Server’s system views to check the job’s execution status on a regular interval. The key views here are sysjobactivity (tracks active job runs) and sysjobs (stores basic job metadata).

Here’s a simple C# example you can adapt for your console app:

using System;
using System.Data.SqlClient;
using System.Threading;

public static bool WaitForJobCompletion(string jobName, string connectionString, int pollIntervalMs = 5000)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        // First get the job's unique ID
        var getJobIdCmd = new SqlCommand($"SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName", conn);
        getJobIdCmd.Parameters.AddWithValue("@JobName", jobName);
        var jobId = (Guid)getJobIdCmd.ExecuteScalar();

        while (true)
        {
            // Check if the job is still running
            var checkRunningCmd = new SqlCommand(@"
                SELECT 1 
                FROM msdb.dbo.sysjobactivity
                WHERE job_id = @JobId
                  AND start_execution_date IS NOT NULL
                  AND stop_execution_date IS NULL
                ORDER BY start_execution_date DESC
                OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY", conn);
            checkRunningCmd.Parameters.AddWithValue("@JobId", jobId);

            var isRunning = checkRunningCmd.ExecuteScalar() != null;

            if (!isRunning)
            {
                // Get the final result of the last run
                var getResultCmd = new SqlCommand(@"
                    CASE WHEN run_status = 1 THEN 1 ELSE 0 END
                    FROM msdb.dbo.sysjobhistory
                    WHERE job_id = @JobId
                      AND step_id = 0  -- Step 0 represents the entire job's result
                    ORDER BY run_date DESC, run_time DESC
                    OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY", conn);
                getResultCmd.Parameters.AddWithValue("@JobId", jobId);
                
                var succeeded = (int)getResultCmd.ExecuteScalar() == 1;
                return succeeded; // Return true if job succeeded, false otherwise
            }

            Thread.Sleep(pollIntervalMs); // Wait before checking again
        }
    }
}

Pros: Easy to implement without modifying existing jobs. Cons: Has slight latency depending on your poll interval.

Workaround 2: Add a job step to notify your app #

If you have permission to edit the SQL Server job, you can add a final step that sends a completion signal to your console app. For example:

1. Create a simple notification table in your database:

   CREATE TABLE JobCompletionLogs (
       JobName NVARCHAR(128) NOT NULL,
       CompletedAt DATETIME NOT NULL DEFAULT GETDATE(),
       IsSuccessful BIT NOT NULL
   )
   

2. Add a new T-SQL step to the end of your job (configure it to run even if previous steps fail):

   INSERT INTO JobCompletionLogs (JobName, IsSuccessful)
   VALUES ('YourTargetJobName', 
           CASE WHEN EXISTS(SELECT 1 FROM msdb.dbo.sysjobhistory WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0 AND run_status = 0) 
                THEN 0 ELSE 1 END)
   

3. Your console app can then use SqlDependency to listen for changes to this table (for real-time notifications) or poll it until a new entry for your job appears.

Workaround 3: Use SMO to check job history #

If you’re already using the Microsoft.SqlServer.Smo library, you can query the job’s history entries directly via the SMO object model:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

public static bool WaitForJobCompletionSMO(string jobName, string connectionString)
{
    var server = new Server(new ServerConnection(connectionString));
    var job = server.JobServer.Jobs[jobName];
    job.Start();

    while (true)
    {
        job.Refresh();
        var latestRun = job.JobHistoryEntries.Cast<JobHistoryEntry>().OrderByDescending(h => h.StartDate).FirstOrDefault();
        
        if (latestRun != null && !latestRun.Running)
        {
            return latestRun.RunStatus == CompletionStatus.Succeeded;
        }
        
        Thread.Sleep(5000);
    }
}

This uses SMO’s built-in properties to track the job’s state, which feels more integrated if you’re already working with SMO objects.

Just make sure your console app has the necessary permissions to read from the msdb database and access SQL Server Agent objects.

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