Running Sql Server Agent Jobs from C#

Question

While searching on above topic in the internet, I found two approaches,Both are working fine, But I need to know the difference between the two, Which one is suitable for what occasion etc... Our Jobs take some time and I need a way to wait till the Job finishes before the next C# line executes.

Approach One

        var dbConn = new SqlConnection(myConString);
        var execJob = new SqlCommand
        {
            CommandType = CommandType.StoredProcedure,
            CommandText = "msdb.dbo.sp_start_job"
        };
        execJob.Parameters.AddWithValue("@job_name", p0);
        execJob.Connection = dbConn;

        using (dbConn)
        {
            dbConn.Open();
            using (execJob)
            {
                execJob.ExecuteNonQuery();
                Thread.Sleep(5000);
            }
        }

Approach Two

        using System.Threading;
        using Microsoft.SqlServer.Management.Smo;
        using Microsoft.SqlServer.Management.Smo.Agent;

        var server = new Server(@"localhost\myinstance");
        var isStopped = false;
        try
        {
            server.ConnectionContext.LoginSecure = true;
            server.ConnectionContext.Connect();
            var job = server.JobServer.Jobs[jobName];
            job.Start();
            Thread.Sleep(1000);
            job.Refresh();
            while (job.CurrentRunStatus == JobExecutionStatus.Executing)
            {
                Thread.Sleep(1000);
                job.Refresh();
            }
            isStopped = true;
        }
        finally
        {
            if (server.ConnectionContext.IsOpen)
            {
                server.ConnectionContext.Disconnect();
            }
        }

Answer 1

sp_start_job - sample 1

Your first example calls your job via the sp_start_job system stored procedure. Note that it kicks off the job asynchronously, and the thread sleeps for an arbitrary period of time (5 seconds) before continuing regardless of the job's success or failure.

SQL Server Management Objects (SMO) - sample 2

Your second example uses (and therefore has a dependency on) the SQL Server Management Objects to achieve the same goal.

In the second case, the job also commences running asynchronously, but the subsequent loop watches the Job Status until it is not longer Executing. Note that the "isStopped" flag appears to serve no purpose, and the loop could be refactored somewhat as:

        job.Start();
        do
        {
            Thread.Sleep(1000);
            job.Refresh();
        } while (job.CurrentRunStatus == JobExecutionStatus.Executing);

You'd probably want to add a break-out of that loop after a certain period of time.

Other Considerations

It seems the same permissions are required by each of your examples; essentially the solution using SMO is a wrapper around sp_start_job, but provides you with (arguably) more robust code which has a clearer purpose.

Use whichever suits you best, or do some profiling and pick the most efficient if performance is a concern.