Like

Welcome On Mobius

Mobius was created by professionnal coders and passionate people.

We made all the best only for you, to enjoy great features and design quality. Mobius was build in order to reach a pixel perfect layout.

Mobius includes exclusive features such as the Themeone Slider, Themeone Shorcode Generator and Mobius Grid Generator.

Our Skills

WordPress90%
Design/Graphics75%
HTML/CSS/jQuery100%
Support/Updates80%

How to Monitor SQL Jobs in Windows Application

By Salman Bilal 1 year agoNo Comments

In this Tutorial we will be creating a window application that would monitor the SQL jobs by logging its statuses on the application and alert a pop up notification on any action of the job e.g. “Job start”, “Job completed” and “Job Failed”.

 

  1. Create a new Project. File >New > Project Windows Form Application

  1. Put two buttons for start and stop of monitoring, a progress bar to view the progress, a list box to log the statuses of the jobs, exit button to quit the application and the radio buttons for the time span of scheduling a method to get the statues of the jobs in the below format on the form.

  1. Add references to the following name spaces, we will be using them in this application.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading;

using System.Threading.Tasks;

using System.Windows.Forms;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using System.Data.SqlClient;

using System.Configuration;

using System.Security.Cryptography;

using System.IO;

  1. Create the ENUM for the scheduler on the code behind of this form.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading;

using System.Threading.Tasks;

using System.Windows.Forms;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using System.Data.SqlClient;

using System.Configuration;

using System.Security.Cryptography;

using System.IO;

namespace MonitoringSqlJobs

{

    enum Scheduler

    {

        EveryMinutes,

        Every10Minutes,

        Every15Minutes,

        EveryHalfHour,

        EveryHour,

        Every2hours,

        EveryYear,

    }

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

    }

}

  1. On Start Button click function put the following code

private void btnStart_Click(object sender, EventArgs e)

{

//retrieve current hour and minute

int hour = DateTime.Now.Hour;

int minutes = DateTime.Now.Minute;

//create next date which we need in order to run the code

var dateNow = DateTime.Now;

var date = new DateTime(dateNow.Year, dateNow.Month, dateNow.Day, hour, minutes, 0);

listBox1.Items.Add(“**********Scheduler has been started!**********”);

//get nex date the code need to run

var nextDateValue = getNextDate(date, getScheduler());

runCodeAt(nextDateValue, getScheduler());

}

  1. Put the following code into the application to set the scheduler to call the method on the selected time frame and the date for next scheduled call. Also to prepare the controls for starting canceling and stopping the monitoring.

CancellationTokenSource m_ctSource;

        /// <summary>

        /// Schedule the time the need to be call

        /// </summary>

        /// <param name=”date”></param>

        /// <param name=”scheduler”></param>

        private void runCodeAt(DateTime date, Scheduler scheduler)

        {

            m_ctSource = new CancellationTokenSource();

            var dateNow = DateTime.Now;

            TimeSpan ts;

            if (date > dateNow)

                ts = date – dateNow;

            else

            {

                date = getNextDate(date, scheduler);

                ts = date – dateNow;

            }

            //enable the progressbar

            prepareControlForStart();

//waits certan time and run the code, in meantime you can cancel the task at anty time

            Task.Delay(ts).ContinueWith((x) =>

            {

                //run the code at the time

                methodToCall(date);

                //setup call next day

                runCodeAt(getNextDate(date, scheduler), scheduler);

            }, m_ctSource.Token);

        }

        /// <summary>

        /// prepare the controls for starting scheduler

        /// </summary>

        private void prepareControlForStart()

        {

            progressBar1.Enabled = true;

            progressBar1.Style = ProgressBarStyle.Marquee;

            btnStart.Enabled = false;

            btnStop.Enabled = true;

            groupBox1.Enabled = false;

        }

        /// <summary>

        /// prepare the controls for canceling the scheduler

        /// </summary>

        private void prepareControlsForCancel()

        {

            m_ctSource = null;

            progressBar1.Enabled = false;

            progressBar1.Style = ProgressBarStyle.Blocks;

            groupBox1.Enabled = true;

            btnStart.Enabled = true;

            btnStop.Enabled = false;

        }

        /// <summary>

        /// returns next date the code to be run

        /// </summary>

        /// <param name=”date”></param>

        /// <param name=”scheduler”></param>

        /// <returns></returns>

        private DateTime getNextDate(DateTime date, Scheduler scheduler)

        {

            switch (scheduler)

            {

                case Scheduler.EveryMinutes:

                    return date.AddMinutes(1.0);

                case Scheduler.Every10Minutes:

                    return date.AddMinutes(10.0);

                case Scheduler.Every15Minutes:

                    return date.AddMinutes(15.0);

                case Scheduler.EveryHalfHour:

                    return date.AddMinutes(30.0);

                case Scheduler.EveryHour:

                    return date.AddHours(1);

                case Scheduler.Every2hours:

                    return date.AddHours(2);

                default:

                    throw new Exception(“Invalid scheduler”);

            }

        }

        /// <summary>

        /// based on the selected radion box returns the scheduler enum

        /// </summary>

        /// <returns></returns>

        private Scheduler getScheduler()

        {

            if (radioButton1.Checked)

                return Scheduler.EveryMinutes;

            if (radioButton2.Checked)

                return Scheduler.Every10Minutes;

            if (radioButton3.Checked)

                return Scheduler.Every15Minutes;

            if (radioButton4.Checked)

                return Scheduler.EveryHalfHour;

            if (radioButton5.Checked)

                return Scheduler.EveryHour;

            if (radioButton6.Checked)

                return Scheduler.Every2hours;

            //default

            return Scheduler.EveryMinutes;

        }

        /// <summary>

        /// canceling the sheduler

        /// </summary>

        /// <param name=”sender”></param>

        /// <param name=”e”></param>

        private void cancelBtn_Click(object sender, EventArgs e)

        {

            if (m_ctSource != null)

            {

                m_ctSource.Cancel();

                prepareControlsForCancel();

                listBox1.Items.Add(“**********Scheduler has Stopped!**********”);

            }

        }

        /// <summary>

        /// Exits the app

        /// </summary>

        /// <param name=”sender”></param>

        /// <param name=”e”></param>

        private void exitBtn_Click(object sender, EventArgs e)

        {

            if (progressBar1.Enabled)

MessageBox.Show(“Scheduler is running. Cancel the scheduler first then close the application!”);

            else

                this.Close();

         }

  1. Now the final step is to create the method that is scheduled on the selected time frame and gets repeated. This method is basically used to get the statuses of the SQL jobs and creates logs and notifications for their actions. Before that, add these two references to the Application
    • SqlServer.ConnectionInfo
    • SqlServer.SqlEnum

List<string> LastRunDateString = new List<string>();

        List<string> ExecutingRunDate = new List<string>();

        private void methodToCall(DateTime time)

        {

            //setup next call

            var nextTimeToCall = getNextDate(time, getScheduler());

            this.BeginInvoke((Action)(() =>

            {

var strText = string.Format(“Method is called at {0}. The next call will be at {1}”, time, nextTimeToCall);

                listBox1.Items.Add(strText);

                // Connect local server for the SQL Jobs

                Server srv = new Server(“(local)”);

                NotifyIcon trayIcon = new NotifyIcon();

                //Create a list of notifications to be displayed later in a loop

                List<string> notificaiton = new List<string>();

                foreach (Job res in srv.JobServer.Jobs)

                {

                    String CurrentStatus = Convert.ToString(res.CurrentRunStatus);

                    var JobName = res.Name;

                    DateTime LastRunDate = res.LastRunDate;

                    var LastRunOutcome = Convert.ToString(res.LastRunOutcome);

                    // if the job job state is Idle

                    if (CurrentStatus == “Idle”)

                    {

                        if ((time >= LastRunDate))

                        {

string result = LastRunDateString.FirstOrDefault(s => s.Contains(JobName));

// first time when the method is called, it will store all the last run dates in a list

                            if (result == null)

                            {

                                LastRunDateString.Add(JobName + ” | ” + LastRunDate);

                            }

// Else if not first time, it will compare if the last run date has changed or not.

                            else

                            {

                                string[] StringDate = result.Split(‘|’);

DateTime LastDate = Convert.ToDateTime(StringDate[1].Trim(‘ ‘));

                                if (LastRunDate > LastDate)

                                {

                                    //Store notification information in a list variable

                                    if (LastRunOutcome == “Succeeded”)

 notificaiton.Add(JobName + ” Job has been successfully Completed at ” + LastRunDate);

                                    else if (LastRunOutcome == “Failed”)

  notificaiton.Add(JobName + ” Job has been Failed at ” + LastRunDate);

//Update the last run date in the LastRunDateString string list.

int indexOfResult = LastRunDateString.FindIndex(s => s.Contains(JobName));

                                    string newstr = JobName + ” | ” + LastRunDate;

                                    LastRunDateString[indexOfResult] = newstr;

                                }

                            }

                        }

                    }

                    // if the job state is Executing

                    else if (CurrentStatus == “Executing”)

                    {

                        if ((time >= LastRunDate))

                        {

string result = ExecutingRunDate.FirstOrDefault(s => s.Contains(JobName));

                            if (result == null)

                            {

                                ExecutingRunDate.Add(JobName + ” | ” + LastRunDate);

                                notificaiton.Add(JobName + ” Job has Started at ” + time);

                            }

                            else

                            {

                                string[] StringDate = result.Split(‘|’);

DateTime LastDate = Convert.ToDateTime(StringDate[1].Trim(‘ ‘));

                                if (LastRunDate > LastDate)

                                {

                                    notificaiton.Add(JobName + ” Job has Started at ” + time);

int indexOfResult = ExecutingRunDate.FindIndex(s => s.Contains(JobName));

                                    string newstr = JobName + ” | ” + LastRunDate;

                                    ExecutingRunDate[indexOfResult] = newstr;

                                }

                            }

                        }

                    }

                }

                // this loop is to display the notifications for the job statues.

foreach (var t in notificaiton)

                {

                    // log the status on the list box

                    listBox1.Items.Add(t.ToString());

                    // show notification

                    trayIcon.BalloonTipText = t.ToString();

                    trayIcon.Visible = true;

                    trayIcon.ShowBalloonTip(100);

                }

            }));

        }

  1. Build the solution and debug the application. All the jobs statuses in the local server will be pulled through this application on real time and a notification would be popped up informing on every status change.

Category:
  asp.net
this post was shared 0 times
 000
About

 Salman Bilal

  (2 articles)

Leave a Reply

Your email address will not be published.