check and wait for sql agent job status smo c#

To check and wait for an SQL Server Agent job status using the SQL Server Management Objects (SMO) in C#, you can follow this approach. Essentially, after starting the job, you can repeatedly check the status of the job in a loop. You can update the UI elements like a textbox or label to show the job's status, but you also need to make sure you're not blocking the UI thread.

using System;
using System.Threading.Tasks; // For async operations
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent; // SMO Agent namespace
using Microsoft.SqlServer.Management.Common; // For ServerConnection

public class SqlAgentJobRunner
{
    private Server _server;

    public SqlAgentJobRunner(string serverName)
    {
        // Connect to the server
        ServerConnection serverConnection = new ServerConnection(serverName);
        _server = new Server(serverConnection);
    }

    public async Task RunJobAndMonitorAsync(string jobName, Label statusLabel)
    {
        try
        {
            Job job = _server.JobServer.Jobs[jobName];

            if (job == null)
            {
                throw new InvalidOperationException("Job not found.");
            }

            // Start the job
            job.Start();
            statusLabel.Text = "Job started...";

            // Wait for the job to complete by polling its status
            await MonitorJobAsync(job, statusLabel);
        }
        catch (Exception ex)
        {
            statusLabel.Text = $"Error: {ex.Message}";
        }
    }

    private async Task MonitorJobAsync(Job job, Label statusLabel)
    {
        // Loop until the job execution completes
        while (true)
        {
            // Get the current job execution status
            job.Refresh(); // Refresh job status
            var jobStatus = job.CurrentRunStatus;

            switch (jobStatus)
            {
                case JobExecutionStatus.Executing:
                    statusLabel.Text = "Job is executing...";
                    break;

                case JobExecutionStatus.WaitingForWorkerThread:
                    statusLabel.Text = "Job is waiting for worker thread...";
                    break;

                case JobExecutionStatus.BetweenRetries:
                    statusLabel.Text = "Job is retrying...";
                    break;

                case JobExecutionStatus.Succeeded:
                    statusLabel.Text = "Job completed successfully.";
                    return; // Exit the loop

                case JobExecutionStatus.Failed:
                    statusLabel.Text = "Job failed.";
                    return; // Exit the loop

                default:
                    statusLabel.Text = $"Job status: {jobStatus}.";
                    break;
            }

            // Sleep for a few seconds to avoid excessive polling (adjust time as needed)
            await Task.Delay(3000);
        }
    }
}

The Server object is used to connect to the SQL Server and interact with the SQL Agent jobs. It connects via SMO using ServerConnection. The job.Start() method launches the job, and we then enter a loop to monitor its status. The MonitorJobAsync() method uses a while (true) loop to continuously check the job’s status until it completes or fails. The job.Refresh() call is important as it refreshes the job's status. The statusLabel.Text is updated with the current job status in the loop. Since this is done asynchronously, the UI won’t be blocked. A Task.Delay(3000) ensures that the status is polled every 3 seconds, but you can adjust this delay as per your requirements.