2012. 10. 4. 16:25

SQL Agent 체크후 SMS 보내기.

SQL Agent 를체크하여 고객이 입력한 핸펀 번호를 메세지를 발송하는 툴입니다. 

(파일첨부는 보안상 생략)

C# 으로 만들었으며, 매일 SQL Agent 작업사항을 체크하는 고객이 있어어서 만들게 되었습니다.

추후 기능 추가등을 통해서 여러가지 사항들을 추가할수 있으나, 현재는 요기까지만 하여 고객에게 배포 할 상태입니다.

(허접 코딩 ㅋㅋ)


** 고객이 직접 컨트롤할수 있다는것이 가장 큰 장점입니다.**

핸펀 메세지 출력 방식은 아래와 같습니다.

[서버IP] / [작업이름] 작업실패 / 날자 : 20121004 / 시간 : 110411




소스

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Sql;
using MySql.Data;
using MySql.Data.MySqlClient;



namespace Agent_Check
{
    public partial class ComboBox1 : Form
    {
       
        public ComboBox1()
        {
            InitializeComponent();

        }
       
       
        //변수선언
        private SqlConnection Conn;
        private SqlConnection Conn2;
        private MySqlConnection MySqlConn;
        private String ConnStr;
        private String MySqlConnStr;






 


        //버튼클릭
        private void button1_Click(object sender, EventArgs e)
        {
            ValidateForm();
            timer1.Start();
        }

        //버튼클릭 중지
        private void button2_Click(object sender, EventArgs e)
        {
            timer1.Stop();
        }

        //timer 진행
        private void timer1_Tick(object sender, EventArgs e)
        {

            label1.Text = DateTime.Now.ToString("hh:mm:ss");

            //timer 시간 체크
            timer1.Interval = int.Parse(timer_interval.Text);

            DB_Check();
            DataGridView_LOAD_DATA();
 
          
        }

  
        private void ValidateForm()
        {
            bool bValidateID = ValidateID();
            bool bValidateIP = ValidateIP();
            bool bValidatePass = ValidatePass();
            bool bValidateTimer_inter = ValidateTimer_inter();
            bool bValidatePhone1 = ValidatePhone1();
            //bool bValidatePhone2 = ValidatePhone2();
            //bool bValidatePhone3 = ValidatePhone3();

            if (bValidateID && bValidateIP && bValidatePass && bValidateTimer_inter && bValidatePhone1)
            {
                MessageBox.Show("모니터링을 시작합니다");

                //if (MessageBox.Show("취소를 누르시면 입력하신 내용이 모두 초기화 됩니다.\r계속 하시겠습니까?", "입력 취소", MessageBoxButtons.YesNo) == DialogResult.Yes)

            }
            else
            {
                MessageBox.Show("값이 빠졌습니다. ! 부분을 확인하시기 바랍니다.");
            }
        }

        private bool ValidateID()
        {
            bool bStatus = true;
            if(Txt_ID.Text == "")
                {
                    errorProvider1.SetError(Txt_ID, "ID 를 기재해주세요");
                    bStatus = false;
                }
            else
                errorProvider1.SetError(Txt_ID, "");
                return bStatus;
        }

        private bool ValidatePass()
        {
            bool bStatus = true;
            if (Txt_ID.Text == "")
            {
                errorProvider1.SetError(Txt_Pass, "Password 를 기재해주세요");
                bStatus = false;
            }
            else
                errorProvider1.SetError(Txt_Pass, "");
            return bStatus;
        }
        private bool ValidateIP()
        {
            bool bStatus = true;
            if (Txt_ID.Text == "")
            {
                errorProvider1.SetError(Txt_ServerIP, "데이터 베이스 서버 IP 를 기재해주세요");
                bStatus = false;
            }
            else
                errorProvider1.SetError(Txt_ServerIP, "");
            return bStatus;
        }
        private bool ValidateTimer_inter()
        {
            bool bStatus = true;
            if (Txt_ID.Text == "")
            {
                errorProvider1.SetError(timer_interval, "체크할 주기를 넣어주세요");
                bStatus = false;
            }
            else
                errorProvider1.SetError(timer_interval, "");
            return bStatus;
        }
        private bool ValidatePhone1()
        {
            bool bStatus = true;
            if (Txt_ID.Text == "")
            {
                errorProvider1.SetError(txt_Phon1, "핸드폰 번호를 넣어주세요");
                bStatus = false;
            }
            else
                errorProvider1.SetError(txt_Phon1, "");
            return bStatus;
        }
       



        private void DB_Check()
        {

            string[] Phone_Num = new string[] { txt_Phon1.Text, txt_Phon2.Text, txt_Phon3.Text };
           
           
           ConnStr = "Data Source=" + Txt_ServerIP.Text + ";Initial Catalog=msdb;" + "Persist Security Info=True;User ID="+ Txt_ID.Text +";Password="+ Txt_Pass.Text +"";
           
            Conn = new SqlConnection();

            Conn.ConnectionString = ConnStr;
            Conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Conn;
            cmd.CommandText = "select step_name,last_run_outcome from sysjobsteps";
            SqlDataReader dbRead = cmd.ExecuteReader();

                 



            Conn2 = new SqlConnection();

            Conn2.ConnectionString = ConnStr;
            Conn2.Open();
            SqlCommand cmd2 = new SqlCommand();
            cmd2.Connection = Conn2;
            cmd2.CommandText = "select step_name,last_run_date,last_run_time from sysjobsteps where last_run_outcome = '0'";
            SqlDataReader dbRead2 = cmd2.ExecuteReader();




            while (dbRead.Read())
            {
                if (dbRead["last_run_outcome"].ToString().Contains("0"))
                {
                    while (dbRead2.Read())
                    {
                    

                        foreach (string ss in Phone_Num)                        
                                {
                                   
                                    MySqlConnStr = "Data Source=xxx.xxx.xxx.xxx;Database=SSS;User id=XXX;Password=XXXXXXXXX";
                                    MySqlConn = new MySqlConnection();
                                    MySqlConn.ConnectionString = MySqlConnStr;
                                    MySqlConn.Open();
                                    MySqlCommand mycmd = new MySqlCommand();
                                    mycmd.Connection = MySqlConn;

                                    // 공백문자일경우 넘긴다.
                                    if (ss.Trim() == "") continue;
                                    mycmd.CommandText = "insert into test (tbl1, tbl2, tbl3, tbl4) values ('" + ss + "','" + Txt_ServerIP.Text + " / " + dbRead2[0] + " 작업 실패" + " / " + "날자:" + dbRead2[1] + " / " + "시간:" + dbRead2[2] + "','check값','123456')";

                                    //DB 서버 입력.
                                    mycmd.ExecuteNonQuery();
                                    MySqlConn.Close();
                                }
                    }
                    dbRead2.Close();
                    Conn2.Close();
                }
                else
                {
              
                }
               
            }
            dbRead.Close();
            Conn.Close();

        }

        private void DataGridView_LOAD_DATA()
        {
                       
            ConnStr = "Data Source=xxxxxxxxx;Initial Catalog=msdb;Persist Security Info=True;User ID=xxxxxxx;Password=xxxxxxx";          
            Conn = new SqlConnection();
            Conn.ConnectionString = ConnStr;
            Conn.Open();
            SqlCommand cmd = new SqlCommand();


            cmd.Connection = Conn;


            cmd.CommandText = "select step_name,last_run_outcome from sysjobsteps";


            DataTable DT = new DataTable("DT");
            DT.Load(cmd.ExecuteReader());

            dataGridView1.DataSource = DT;

            //카운터
//            SqlDataAdapter adapter = new SqlDataAdapter("select count(last_run_outcome) from sysjobsteps where last_run_outcome='0'", Conn);

            ////DataSet에 테이블 데이타를 넣음
            //DataSet ds = new DataSet();
            //adapter.Fill(ds,"Tab1");

            //label2.Text = adapter.ToString();
           

            Conn.Close();  



        }
        private void Txt_ID_TextChanged(object sender, EventArgs e)
        {
           ValidateID();
        }

        private void Txt_Pass_TextChanged(object sender, EventArgs e)
        {
            ValidatePass();
        }

        private void Txt_ServerIP_TextChanged(object sender, EventArgs e)
        {
            ValidateIP();
        }

        private void timer_interval_SelectedIndexChanged(object sender, EventArgs e)
        {
            ValidateTimer_inter();
        }

        private void txt_Phon1_TextChanged(object sender, EventArgs e)
        {
            ValidatePhone1();

        }   
    }
}



Trackback 0 Comment 0