How to manage SQL Server Agent jobs through Csharp

Please note this is an expansion to the article on HOW TO ALLOW A USER WITH AN SQL LOGIN TO MANAGE SQL SERVER AGENT.

The entire source code, with comments, for this tutorial can be downloaded here.

in the mentioned article we learnt how to set up an SQL user to manage SQL Jobs, in this article we are going to learn how to manage jobs through a C# application,
and we will be using the SQL user created in the mentioned article to connect to the database and actually mange the SQL Server Agent jobs.

1. We will be creating a class library named “SQLServerJobUtility” which will contain the functionality required to manage SQL Server Agent jobs
2. We will be creating a console application to make use of this class library

Please NOTE: SQL Server 2008 R2 or later must be installed on your development machine.

What tools am I using ?

1. Microsoft Visual Studios 2012
2. Microsoft SQL Server 2012 with Management studio

Download Source code with comments
Step 1: Creating the solution:

1. Open up visual studios
2. From the top menu, navigate to File > New > Project (alternatively pressing ctrl+shit+n will open the new project dialog)
3. From the top of the create project pane, ensure .NET Framework 4.5 is selected
4. From the left panel, select “Visual C#”
5. From the center pane, select “Class Library”
6. Clear the name field then type in “SQLServerJobUtility”
7. Click OK
8. Rename “Class1.cs” to “JobUtility.cs”
9. Add the following references to your project
   1. Microsoft.SqlServer.ConnectionInfo
   2. Microsoft.SqlServer.SqlEnum
   3. Microsoft.SqlServer.Smo
   4. Microsoft.SqlServer.Management.Sdk.Sfc
10. add the following files to your class library: LogType.cs

basically we’ve set up our files are we are ready to rock on with the coding, but first a quick explanation on the file you added in step 10, basically LogType.cs will aid us in creating a mechanism to log information when attempting to manage SQL Server agent jobs. In a production environment when something does go wrong you should always look for logs to quickly identify what’s the cause. Logging is important.

Step 2: Time to get coding

Replace the code in LogType.cs with the following:

namespace SQLServerJobUtility
{
    using System;

    public enum LogType : int
    {
        Success = 0,
        Failure = 1,
        Info = 2
    }
}

the above code sets up our Log Types, we have 3 log types.
1. Success, when a request is processed successfully
2. Failure, when a request failed
3. Information, general information about steps in a request

Now to the exciting file, JobUtility.cs
the first thing we are going to do is set up a little mechanism to allow for logging.
replace the code in JobUtility.cs with the following:

namespace SQLServerJobUtility
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Microsoft.SqlServer;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Smo.Agent;

    public class JobUtility
    {
        public delegate void LogHandler(LogType type, string log);

        public event LogHandler Log;
    }
}

We have set up a delegate void which allows any other application that uses to class to receive logs in real time, the caller application will know the log type at the log message
as long as they subscribe to the log event, I will show you how to achieve this when we are testing our library from a console application

Now lets get to the Connect method, this will allow our JobUtility class to connect to an SQL Server

Your job utility class should now look like this:

namespace SQLServerJobUtility
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Microsoft.SqlServer;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Smo.Agent;

    public class JobUtility
    {
        private Server server;

        public delegate void LogHandler(LogType type, string log);

        public event LogHandler Log;

        public bool Connect(string connectionString)
        {
            this.Log(LogType.Info, "Attemping to connect to server.");
            try
            {
                this.server = new Server();
                this.server.ConnectionContext.ConnectionString = connectionString;
                this.server.ConnectionContext.Connect();
                this.Log(LogType.Success, "Successfully connected to serer");
                return true;
            }
            catch (SqlException ex)
            {
                this.Log(LogType.Failure, string.Concat("Failed to connect to serer: ", ex.Message));
                return false;
            }
        }
    }
}

As you can see we are making decent use of our log event.
the connect method requires a connection string as the parameter (A standard SQL Server connection string) and returns weather or not the connection
to the server was successfully

now that we can connect to a server, we will get all jobs from the serer.
add the following method to JobUtility.cs

        public JobCollection GetAllJobs()
        {
            this.Log(LogType.Info, "Getting all jobs");
            return this.server.JobServer.Jobs;
        }

this method only retrieves a collection of jobs.

now lets try and find a specific job, add the following method to JobUtility.cs

        public Job FindJob(string jobName)
        {
            this.Log(LogType.Info, string.Concat("Attempting to find job '", jobName, "'"));
            JobCollection jobCollection = this.GetAllJobs();

            foreach (Job job in jobCollection)
            {
                if (job.Name == jobName)
                {
                    this.Log(LogType.Failure, "Job Found");
                    return job;
                }
            }

            this.Log(LogType.Failure, "Unablet o find job");
            return null;
        }

all we are doing is looping through the job collection and returning that job to the call method, if found

next we are going to attempt to get the state of a specific job
add the following method to JobUtility.cs

        public string GetJobState(string jobName)
        {
            Job job = this.FindJob(jobName);

            if (job == null)
            {
                this.Log(LogType.Failure, string.Concat("Unable to find job: " + jobName));
                return "Not Found";
            }

            string jobState = job.CurrentRunStatus.ToString(); ;
            this.Log(LogType.Success, string.Concat("Job State: " + jobState));

            return jobState;
        }

All we are doing is is utilizing the methods we created previous (code re-use is always a plus) and returning the state accordingly.
notice we are doing a null check on the job, if the job is null, it means the requested job was not found

Now to start a job, add the following to JobUtility.cs

        public bool StartJob(string jobName)
        {
            Job job = this.FindJob(jobName);

            if (job == null)
            {
                this.Log(LogType.Failure, string.Concat("Unable to find job: " + jobName));
                return false;
            }

            if (!job.IsEnabled)
            {
                job.IsEnabled = true;
                this.Log(LogType.Info, string.Concat("Enabling job: " + jobName));
            }

            try
            {
                job.Start();
                this.Log(LogType.Success, string.Concat("Job started: " + jobName));
                return true;
            }
            catch (Exception ex)
            {
                this.Log(LogType.Failure, string.Concat("Unable to start SQL Job:", jobName, "Exception message = ", ex.Message));
                return false;
            }
        }

now that we know how to start a job, we should know how to stop a job.
add the following method to JobUtility.cs

        public bool StopJob(string jobName)
        {
            Job job = this.FindJob(jobName);

            if (job == null)
            {
                this.Log(LogType.Failure, string.Concat("Unable to find job: " + jobName));
                return false;
            }

            try
            {
                job.Stop();
                this.Log(LogType.Success, string.Concat("Job Stopped: " + jobName));
                return true;
            }
            catch (Exception ex)
            {
                this.Log(LogType.Failure, string.Concat("Unable to Stop SQL Job:", jobName, "Exception message = ", ex.Message));
                return false;
            }
        }

There is a lot more than can be regarding SQL Server Agent management through C#, such as creating job schedules etc – we will get to that in a part 2

Now to test out our job utility

add a new project of type Console Application, named JobUtilityTester, to the solution
Add a reference to our SQLServerJobUtility project

your Program.cs should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLServerJobUtility;

namespace JobUtilityTester
{
    class Program
    {
        static void Main(string[] args)
        {
            // init job utility
            JobUtility utility = new JobUtility();

            // listen to logs in real time
            utility.Log += utility_Log;

            string connectionstring = "Server=localhost;User Id=someUser;Password=somePass;";
            if (utility.Connect(connectionstring))
            {
                utility.GetJobState("somejob");
                utility.StartJob("somejob");
                utility.GetJobState("somejob");
                utility.StopJob("somejob");
                utility.GetJobState("somejob");
            }

            Console.ReadKey();
        }

        static void utility_Log(LogType type, string log)
        {
            Console.WriteLine(string.Concat(type.ToString(), ": ", log));
        }
    }
}

Download Source code with comments