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();
}
}
}
Rss Feed