SQL Server 作业检查工具

[c#]代码库

using Microsoft.SqlServer.Management.Smo.Agent;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading;
using System.Windows.Forms;
using Outlook = Microsoft.Office.Interop.Outlook;

namespace SQLServerJobChecker
{
    public partial class JobCheckerUI : Form
    {
        private List<JobConfigurationData> _configurations = new List<JobConfigurationData>();
        private const string JOB_CONFIGURATION_DATA_FILE_NAME = "JobConfiguration.xml";
        private string body;
        private int cntJob;
        private int iJob = 0;

        public JobCheckerUI()
        {
            InitializeComponent();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string instanceName = txtInstanceName.Text.Trim();
            string jobName = txtJobName.Text.Trim();
            string userName = txtUserName.Text.Trim();
            if (string.IsNullOrEmpty(instanceName))
            {
                MessageBox.Show("请输入 SQL Server 实例名", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtInstanceName.Focus();
                return;
            }
            if (string.IsNullOrEmpty(jobName))
            {
                MessageBox.Show("请输入作业名称", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtJobName.Focus();
                return;
            }
            if (FindRow(instanceName, jobName) != null)
            {
                MessageBox.Show("SQL Server 实例 [" + instanceName + "] 的作业 [" + jobName + "] 在检查列表中已存在，请指定一个新的作业", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtInstanceName.Focus();
                return;
            }

            if (this.chkWindowsAuthentication.Checked)
            {
                if (string.IsNullOrEmpty(userName))
                {
                    MessageBox.Show("请输入用户名", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.txtUserName.Focus();
                    return;
                }
                if (string.IsNullOrEmpty(txtPassword.Text))
                {
                    MessageBox.Show("请输入密码", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.txtPassword.Focus();
                    return;
                }
            }

            DataGridViewRow newRow = AddNewServerMornitor(new JobConfigurationData()
            {
                InstanceName = instanceName,
                JobName = jobName,
                UserName = userName,
                Password = txtPassword.Text,
                IsWindowsAuthentication = this.chkWindowsAuthentication.Checked
            });
            newRow.Selected = true;
            UpdateInputFromCurrentRow();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (dgResult.SelectedRows.Count == 0)
            {
                MessageBox.Show("请选择要更新的作业", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            string instanceName = txtInstanceName.Text.Trim();
            string jobName = txtJobName.Text.Trim();
            string userName = txtUserName.Text.Trim();

            if (string.IsNullOrEmpty(instanceName))
            {
                MessageBox.Show("请输入 SQL Server 实例名", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtInstanceName.Focus();
                return;
            }
            else if (string.IsNullOrEmpty(jobName))
            {
                MessageBox.Show("请输入作业名称", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtJobName.Focus();
                return;
            }
            else
            {
                DataGridViewRow row = FindRow(instanceName, jobName);
                if (row != null && dgResult.SelectedRows[0] != row)
                {
                    MessageBox.Show("SQL Server 实例 [" + instanceName + "] 的作业 [" + jobName + "] 在检查列表中已存在，请指定一个新的作业", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtInstanceName.Focus();
                    return;
                }
            }
            if (!chkWindowsAuthentication.Checked)
            {
                if (string.IsNullOrEmpty(userName))
                {
                    MessageBox.Show("请输入用户名", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtUserName.Focus();
                    return;
                }
                if (string.IsNullOrEmpty(txtPassword.Text))
                {
                    MessageBox.Show("请输入密码", "输入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtPassword.Focus();
                    return;
                }
            }
            UpdateJobChecker(dgResult.SelectedRows[0], new JobConfigurationData()
            {
                JobName = jobName,
                InstanceName = instanceName,
                UserName = userName,
                Password = txtPassword.Text,
                IsWindowsAuthentication = chkWindowsAuthentication.Checked
            });
        }

        private void btnRemove_Click(object sender, EventArgs e)
        {
            if (dgResult.SelectedRows.Count == 0)
            {
                MessageBox.Show("请选择要删除的作业", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (MessageBox.Show("请确认删除当前作业？", "删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                RemoveJobChecker(dgResult.SelectedRows[0]);
        }

        public void RemoveJobChecker(DataGridViewRow currentRow)
        {
            string instanceName = currentRow.Cells["ColInstanceName"].Value.ToString();
            string jobName = currentRow.Cells["ColJobName"].Value.ToString();
            dgResult.Rows.Remove(currentRow);

            int configurationDataIndex = _configurations.FindIndex(c =>
                c.InstanceName.Equals(instanceName, StringComparison.CurrentCultureIgnoreCase) &&
                c.JobName.Equals(jobName, StringComparison.CurrentCultureIgnoreCase));
            if (configurationDataIndex >= 0)
                _configurations.RemoveAt(configurationDataIndex);

            btnSave.Enabled = true;
        }

        private void chkWindowsAuthentication_CheckedChanged(object sender, EventArgs e)
        {
            txtUserName.Enabled = !chkWindowsAuthentication.Checked;
            txtPassword.Enabled = !chkWindowsAuthentication.Checked;
        }

        private DataGridViewRow AddNewServerMornitor(JobConfigurationData newConfigurationData)
        {
            dgResult.Rows.Add();
            DataGridViewRow newRow = dgResult.Rows[dgResult.Rows.Count - 1];

            UpdateRowWithConfigurationData(newRow, newConfigurationData);
            GetJobStatusAsync(newConfigurationData);

            _configurations.Add(newConfigurationData);

            btnSave.Enabled = true;

            return newRow;
        }

        private void GetJobStatusAsync(JobConfigurationData configurationData)
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(GetJobStatusAsync), configurationData);
        }

        private void GetJobStatusAsync(object param)
        {
            if (param == null || !(param is JobConfigurationData))
            {
                //Report Error
                BeginInvoke(new Action(() =>
                {
                    MessageBox.Show("Invalid argument: param", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }));
                return;
            }

            JobConfigurationData configuration = param as JobConfigurationData;

            //Report Progress
            this.BeginInvoke(new Action<string, string, decimal>(ReportProgress), configuration.InstanceName, configuration.JobName, new Decimal(0));

            try
            {
                JobCheckerData result = JobCheckerHelper.GetJobStatus(configuration);

                //Complete
                this.BeginInvoke(new Action<JobCheckerData>(OnGetJobStatusAsyncCompleted), result);
            }
            catch (Exception ex)
            {
                //Report Error
                this.BeginInvoke(new Action<string, string, Exception>(ReportError), configuration.InstanceName, configuration.JobName, ex);
            }
        }

        private void ReportProgress(string instanceName, string jobName, decimal percentOfProgress)
        {
            DataGridViewRow theRow = FindRow(instanceName, jobName);

            if (theRow != null)
            {
                if (percentOfProgress == 0)
                {
                    theRow.Cells["ColState"].ToolTipText = null;
                    theRow.Cells["ColState"].Value = this.imageList1.Images[7];
                    theRow.Cells["ColLastRunDateTime"].Value = null;
                }
            }
        }

        private void ReportError(string instanceName, string jobName, Exception ex)
        {
            DataGridViewRow theRow = FindRow(instanceName, jobName);

            if (theRow != null)
            {
                theRow.Cells["ColState"].Value = this.imageList1.Images[2];
                theRow.Cells["ColState"].ToolTipText = ex.Message;
                theRow.Cells["ColLastRunDateTime"].Value = null;
            }
        }

        private void UpdateJobChecker(DataGridViewRow currentRow, JobConfigurationData updatedConfigurationData)
        {
            string originalInstanceName = currentRow.Cells["ColInstanceName"].Value.ToString();
            string originalJobName = currentRow.Cells["ColJobName"].Value.ToString();
            string originalUserName = currentRow.Cells["ColUserName"].Value.ToString();
            string originalPassword = currentRow.Cells["ColPassword"].Value.ToString();
            bool originalWindowsAuthentication = (bool)currentRow.Cells["ColWindowsAuthentication"].Value;

            bool isChanged = !originalInstanceName.Equals(updatedConfigurationData.InstanceName, StringComparison.CurrentCultureIgnoreCase) ||
                 !originalJobName.Equals(updatedConfigurationData.JobName, StringComparison.CurrentCultureIgnoreCase) ||
                !originalUserName.Equals(updatedConfigurationData.UserName, StringComparison.CurrentCultureIgnoreCase) ||
                !originalPassword.Equals(updatedConfigurationData.Password, StringComparison.CurrentCultureIgnoreCase) ||
                originalWindowsAuthentication != updatedConfigurationData.IsWindowsAuthentication;

            if (isChanged)
                UpdateRowWithConfigurationData(currentRow, updatedConfigurationData);

            GetJobStatusAsync(updatedConfigurationData);

            int originalConfigurationDataIndex = _configurations.FindIndex(c =>
                   c.InstanceName.Equals(originalInstanceName, StringComparison.CurrentCultureIgnoreCase) &&
                   c.JobName.Equals(originalJobName, StringComparison.CurrentCultureIgnoreCase)
               );
            if (originalConfigurationDataIndex >= 0)
            {
                _configurations.RemoveAt(originalConfigurationDataIndex);
                _configurations.Insert(originalConfigurationDataIndex, updatedConfigurationData);
            }
            else
                _configurations.Add(updatedConfigurationData);

            if (isChanged)
                btnSave.Enabled = true;
        }

        private static void UpdateRowWithConfigurationData(DataGridViewRow row, JobConfigurationData configurationData)
        {
            row.Cells["ColInstanceName"].Value = configurationData.InstanceName;
            row.Cells["ColJobName"].Value = configurationData.JobName;
            row.Cells["ColWindowsAuthentication"].Value = configurationData.IsWindowsAuthentication;
            row.Cells["ColUserName"].Value = configurationData.UserName;
            row.Cells["ColPassword"].Value = configurationData.Password;
        }

        private void UpdateInputFromCurrentRow()
        {
            if (dgResult.SelectedRows.Count == 0)
            {
                this.txtInstanceName.Text = string.Empty;
                this.txtJobName.Text = string.Empty;
                this.txtUserName.Text = "sa";
                this.txtPassword.Text = string.Empty;
                this.chkWindowsAuthentication.Checked = false;
            }
            else
            {
                DataGridViewRow currentRow = dgResult.SelectedRows[0];
                if (currentRow.Cells["ColInstanceName"].Value != null)
                    this.txtInstanceName.Text = currentRow.Cells["ColInstanceName"].Value.ToString();
                else
                    this.txtInstanceName.Text = string.Empty;
                if (currentRow.Cells["ColJobName"].Value != null)
                    this.txtJobName.Text = currentRow.Cells["ColJobName"].Value.ToString();
                else
                    this.txtJobName.Text = string.Empty;
                if (currentRow.Cells["ColUserName"].Value != null)
                    this.txtUserName.Text = currentRow.Cells["ColUserName"].Value.ToString();
                else
                    this.txtUserName.Text = string.Empty;
                if (currentRow.Cells["ColPassword"].Value != null)
                    this.txtPassword.Text = currentRow.Cells["ColPassword"].Value.ToString();
                else
                    this.txtPassword.Text = string.Empty;
                if (currentRow.Cells["ColWindowsAuthentication"].Value != null)
                    this.chkWindowsAuthentication.Checked = (bool)currentRow.Cells["ColWindowsAuthentication"].Value;
                else
                    this.chkWindowsAuthentication.Checked = false;
            }
        }

        private void chkSendEmail_CheckedChanged(object sender, EventArgs e)
        {
            timer1.Enabled = chkAutoSendEmailtoSupportTeam.Checked;
            if (chkAutoSendEmailtoSupportTeam.Checked)
                CheckServerDataSendEmail();
        }

        private void CheckServerDataSendEmail()
        {
            body = string.Empty;
            cntJob = _configurations.Count;
            foreach (JobConfigurationData configuration in _configurations)
                GetJobUsageAsyncForMail(configuration);
        }

        private void GetJobUsageAsyncForMail(JobConfigurationData configurationData)
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(GetServerUsageAsyncForMail), configurationData);
        }

        private void GetServerUsageAsyncForMail(object param)
        {
            if (param == null || !(param is JobConfigurationData))
            {
                //Report Error
                this.BeginInvoke(new Action(() =>
                {
                    MessageBox.Show("Invalid argument: param", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }));
                iJob++;
                if (iJob == cntJob)
                {
                    if (body != string.Empty)
                        SendEmail();
                    iJob = 0;
                }
                return;
            }

            JobConfigurationData configuration = param as JobConfigurationData;

            try
            {
                JobCheckerData result = JobCheckerHelper.GetJobStatus(configuration);

                iJob++;
                //Complete
                this.BeginInvoke(new Action<JobCheckerData>(OnGetJobStatusAsyncCompletedForMail), result);
            }
            catch (Exception ex)
            {
                iJob++;
                if (iJob == cntJob)
                {
                    if (body != string.Empty)
                        SendEmail();
                    iJob = 0;
                }
                //Report Error
                this.BeginInvoke(new Action<string, string, Exception>(ReportErrorForEmail), configuration.InstanceName, configuration.JobName, ex);
            }
        }

        private void ReportErrorForEmail(string instanceName, string jobName, Exception ex)
        {
            DataGridViewRow theRow = FindRow(instanceName, jobName);

            if (theRow != null)
            {
                theRow.Cells["ColState"].Value = this.imageList1.Images[2];
                theRow.Cells["ColState"].ToolTipText = ex.Message;
            }
        }

        private void OnGetJobStatusAsyncCompletedForMail(JobCheckerData data)
        {
            if (data == null)
                return;

            DataGridViewRow theRow = FindRow(data.InstanceName, data.JobName);
            if (theRow == null)
                return;

            switch (data.result)
            {
                case CompletionResult.Succeeded:
                    break;
                case CompletionResult.InProgress:
                    body += string.Format("<B>{0}\\[{1}]:&nbsp<FONT Color=\"Red\">In Progress</FONT></B></P>", data.InstanceName, data.JobName);
                    break;
                case CompletionResult.Failed:
                    body += string.Format("<B>{0}\\[{1}]:&nbsp<FONT Color=\"Red\">Failed</FONT></B></P>", data.InstanceName, data.JobName);
                    break;
                case CompletionResult.Cancelled:
                    body += string.Format("<B>{0}\\[{1}]:&nbsp<FONT Color=\"Red\">Cancelled</FONT></B></P>", data.InstanceName, data.JobName);
                    break;
                case CompletionResult.Retry:
                    body += string.Format("<B>{0}\\[{1}]:&nbsp<FONT Color=\"Red\">Retry</FONT></B></P>", data.InstanceName, data.JobName);
                    break;
                case CompletionResult.Unknown:
                    body += string.Format("<B>{0}\\[{1}]:&nbsp<FONT Color=\"Red\">Unknown</FONT></B></P>", data.InstanceName, data.JobName);
                    break;
            }

            if (iJob == cntJob)
            {
                if (body != string.Empty)
                    SendEmail();
                iJob = 0;
            }
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            RefreshAllJobCheckers();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            SaveJobConfigurationData();
        }

        private void SaveJobConfigurationData()
        {
            string filePath = string.Format("{0}{1}", AppDomain.CurrentDomain.BaseDirectory, JOB_CONFIGURATION_DATA_FILE_NAME);
            XMLFileSerializationHelper.SerializeObjectToXMLFile<List<JobConfigurationData>>(_configurations, filePath);

            btnSave.Enabled = false;
        }

        private void RefreshAllJobCheckers()
        {
            foreach (JobConfigurationData configuration in _configurations)
                GetJobStatusAsync(configuration);
        }

        private void OnGetJobStatusAsyncCompleted(JobCheckerData data)
        {
            if (data == null)
                return;

            DataGridViewRow theRow = FindRow(data.InstanceName, data.JobName);
            if (theRow == null)
                return;

            if (data.JobRunStatus == JobExecutionStatus.Executing)
            {
                theRow.Cells["ColState"].Value = this.imageList1.Images[6];
                theRow.Cells["ColState"].ToolTipText = "In Progress";
            }
            else
            {
                switch (data.result)
                {
                    case CompletionResult.Succeeded:
                        theRow.Cells["ColState"].Value = this.imageList1.Images[data.IsJobEnabled ? 5 : 1];
                        theRow.Cells["ColState"].ToolTipText = "Succeeded";
                        break;
                    case CompletionResult.InProgress:
                        break;
                    case CompletionResult.Failed:
                        theRow.Cells["ColState"].Value = this.imageList1.Images[2];
                        theRow.Cells["ColState"].ToolTipText = "Failed";
                        break;
                    case CompletionResult.Cancelled:
                        theRow.Cells["ColState"].Value = this.imageList1.Images[0];
                        theRow.Cells["ColState"].ToolTipText = "Cancelled";
                        break;
                    case CompletionResult.Retry:
                        theRow.Cells["ColState"].Value = this.imageList1.Images[6];
                        theRow.Cells["ColState"].ToolTipText = "Retry";
                        break;
                    case CompletionResult.Unknown:
                        theRow.Cells["ColState"].Value = this.imageList1.Images[3];
                        theRow.Cells["ColState"].ToolTipText = "Unknown";
                        break;
                }
            }
            theRow.Cells["ColLastRunDateTime"].Value = data.JobLastRunDateTime.ToString();
        }

        private DataGridViewRow FindRow(string instanceName, string jobName)
        {
            //find the corresponding row with the same server name
            DataGridViewRow theRow = null;
            foreach (DataGridViewRow row in dgResult.Rows)
            {
                if (row.Cells["ColJobName"].Value != null &&
                    jobName.Equals(row.Cells["ColJobName"].Value.ToString(), StringComparison.CurrentCultureIgnoreCase) &&
                    row.Cells["ColInstanceName"].Value != null &&
                    instanceName.Equals(row.Cells["ColInstanceName"].Value.ToString(), StringComparison.CurrentCultureIgnoreCase))
                {
                    theRow = row;
                    break;
                }
            }
            return theRow;
        }

        private void LoadJobConfigurationData()
        {
            string filePath = string.Format("{0}{1}", AppDomain.CurrentDomain.BaseDirectory, JOB_CONFIGURATION_DATA_FILE_NAME);
            if (File.Exists(filePath))
            {
                _configurations = XMLFileSerializationHelper.DeserializeObjectFromXMLFile<List<JobConfigurationData>>(filePath);
            }

            foreach (JobConfigurationData configuration in _configurations)
            {
                dgResult.Rows.Add();
                UpdateRowWithConfigurationData(dgResult.Rows[dgResult.Rows.Count - 1], configuration);
            }
            if (dgResult.Rows.Count > 0)
                dgResult.Rows[dgResult.Rows.Count - 1].Selected = true;

            btnSave.Enabled = false;
        }

        private void SendEmail()
        {
            Outlook.Application olApp = new Outlook.Application();
            Outlook.MailItem mailItem = (Outlook.MailItem)olApp.CreateItem(Outlook.OlItemType.olMailItem);
            mailItem.To = "GCLocal.Application.Support@nike.com";
            mailItem.Subject = "SQL Server Job Checker Tool Warning email for job status";
            mailItem.BodyFormat = Outlook.OlBodyFormat.olFormatHTML;
            mailItem.HTMLBody = "This message as an automated warning information from the SQL Server Job Checker Tool, please don't reply.<P />" + body;
            ((Outlook._MailItem)mailItem).Send();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {

        }

        private void dgResult_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > -1 && dgResult.Columns[e.ColumnIndex].Name == "ColInstanceName")
            {
                if ((bool)dgResult["ColWindowsAuthentication", e.RowIndex].Value)
                    JobCheckerHelper.OpenManagementStudio(dgResult[e.ColumnIndex, e.RowIndex].Value.ToString());
                else
                    JobCheckerHelper.OpenManagementStudio(dgResult[e.ColumnIndex, e.RowIndex].Value.ToString(),
                    dgResult["ColUserName", e.RowIndex].Value.ToString(),
                    dgResult["ColPassword", e.RowIndex].Value.ToString());
            }
        }

        private void dgResult_SelectionChanged(object sender, EventArgs e)
        {
            UpdateInputFromCurrentRow();
        }

        private void JobCheckerUI_Load(object sender, EventArgs e)
        {
            LoadJobConfigurationData();
            RefreshAllJobCheckers();
        }
    }
}

[代码运行效果截图]

[源代码打包下载]