protected override string GetData()
{
try
{
list_dm_exec_query_stats_data_typeA.Clear();
using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
//cmd.CommandText = string.Format(GetDataQuery, CurrentTableName);
cmd.CommandText = GetDataQuery;
cmd.Parameters.Add("@ProbeTime", SqlDbType.DateTime).Value = Convert.ToDateTime(ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"));
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
list_dm_exec_query_stats_data_typeA.Add(
new dm_exec_query_stats_typeA
{
ip = LocalIp,
port = LocalPort,
probe_time = ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"),
execution_count = config.DatabaseValue <long>(reader["execution_count"]),
total_worker_time = config.DatabaseValue <long>(reader["total_worker_time"]),
total_logical_reads = config.DatabaseValue <long>(reader["total_logical_reads"]),
total_physical_reads = config.DatabaseValue <long>(reader["total_physical_reads"]),
total_logical_writes = config.DatabaseValue <long>(reader["total_logical_writes"]),
total_elapsed_time = config.DatabaseValue <long>(reader["total_elapsed_time"]),
total_grant_kb = config.DatabaseValue <long>(reader["total_grant_kb"]),
sql_handle = config.DatabaseValue <string>(reader["sql_handle"]),
statement_start_offset = config.DatabaseValue <int>(reader["statement_start_offset"]),
statement_end_offset = config.DatabaseValue <int>(reader["statement_end_offset"]),
plan_handle = config.DatabaseValue <string>(reader["plan_handle"]),
query_hash = config.DatabaseValue <string>(reader["query_hash"]),
query_plan_hash = config.DatabaseValue <string>(reader["query_plan_hash"])
});
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
var settings = new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore,
MissingMemberHandling = MissingMemberHandling.Ignore
};
var json = JsonConvert.SerializeObject(list_dm_exec_query_stats_data_typeA, settings);
return(json);
}
private string GetStatementData()
{
try
{
list_dm_exec_query_stats_statement_TypeA.Clear();
using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = GetDataStatementQuery;
cmd.Parameters.Add("@ProbeTime", SqlDbType.DateTime).Value = Convert.ToDateTime(ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"));
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
list_dm_exec_query_stats_statement_TypeA.Add(
new dm_exec_query_stats_statement_TypeA
{
ip = LocalIp,
port = LocalPort,
probe_time = ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"),
query_hash = config.DatabaseValue <string>(reader["query_hash"]),
query_statement = config.DatabaseValue <string>(reader["query_statement"])
});
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
var settings = new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore,
MissingMemberHandling = MissingMemberHandling.Ignore
};
var json = JsonConvert.SerializeObject(list_dm_exec_query_stats_statement_TypeA, settings);
return(json);
}
protected override string GetData()
{
try
{
list_sp_loc2_TypeA.Clear();
using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = GetDataQuery;
cmd.Parameters.Add("@ProbeTime", SqlDbType.DateTime).Value = Convert.ToDateTime(ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"));
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
list_sp_loc2_TypeA.Add(
new sp_lock2_TypeA
{
ip = LocalIp,
port = LocalPort,
probe_time = ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"),
hh_mm_ss = config.DatabaseValue <string>(reader["hh_mm_ss"]),
wait_sec = config.DatabaseValue <long>(reader["wait_sec"]),
locktree = config.DatabaseValue <string>(reader["locktree"]).Trim(),
spid = config.DatabaseValue <int>(reader["spid"]),
kpid = config.DatabaseValue <int>(reader["kpid"]),
blocked = config.DatabaseValue <int>(reader["blocked"]),
waittype = config.DatabaseValue <string>(reader["waittype"]),
waittime = config.DatabaseValue <long>(reader["waittime"]),
lastwaittype = config.DatabaseValue <string>(reader["lastwaittype"]).Trim(),
waitresource = config.DatabaseValue <string>(reader["waitresource"]).Trim(),
dbid = config.DatabaseValue <int>(reader["dbid"]),
uid = config.DatabaseValue <int>(reader["uid"]),
cpu = config.DatabaseValue <long>(reader["cpu"]),
physical_io = config.DatabaseValue <long>(reader["physical_io"]),
memusage = config.DatabaseValue <long>(reader["memusage"]),
login_time = config.DatabaseValue <DateTime>(reader["login_time"]),
last_batch = config.DatabaseValue <DateTime>(reader["last_batch"]),
ecid = config.DatabaseValue <int>(reader["ecid"]),
open_tran = config.DatabaseValue <int>(reader["open_tran"]),
status = config.DatabaseValue <string>(reader["status"]).Trim(),
sid = config.DatabaseValue <string>(reader["sid"]).Trim(),
hostname = config.DatabaseValue <string>(reader["hostname"]).Trim(),
program_name = config.DatabaseValue <string>(reader["program_name"]).Trim(),
hostprocess = config.DatabaseValue <string>(reader["hostprocess"]).Trim(),
cmd = config.DatabaseValue <string>(reader["cmd"]).Trim(),
nt_domain = config.DatabaseValue <string>(reader["nt_domain"]).Trim(),
nt_username = config.DatabaseValue <string>(reader["nt_username"]).Trim(),
net_address = config.DatabaseValue <string>(reader["net_address"]).Trim(),
net_library = config.DatabaseValue <string>(reader["net_library"]).Trim(),
loginame = config.DatabaseValue <string>(reader["loginame"]).Trim(),
context_info = config.DatabaseValue <string>(reader["context_info"]).Trim(),
sql_handle = config.DatabaseValue <string>(reader["sql_handle"]).Trim(),
stmt_start = config.DatabaseValue <int>(reader["stmt_start"]),
stmt_end = config.DatabaseValue <int>(reader["stmt_end"]),
request_id = config.DatabaseValue <int>(reader["request_id"]),
number = config.DatabaseValue <int>(reader["number"]),
encrypted = config.DatabaseValue <string>(reader["encrypted"]).Trim(),
text = config.DatabaseValue <string>(reader["text"])
});
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
var settings = new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore,
MissingMemberHandling = MissingMemberHandling.Ignore
};
var json = JsonConvert.SerializeObject(list_sp_loc2_TypeA, settings);
return(json);
}
protected override void GetData()
{
// 데이터를 구한다.
log.Warn(string.Format("{0} GetData started", BaseTableName));
try
{
string probe_time = ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000");
using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = GetDataQuery;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
buffer.Add(new sp_lock2_data
{
//MachineName = config.GetValue(Category.GLOBAL, Key.hostname),
//FullInstanceName = config.DatabaseValue<string>(reader["servicename"]),
//MachinePrivateIp = config.GetValue(Category.GLOBAL, Key.private_ip),
probe_time = Convert.ToDateTime(probe_time),
hh_mm_ss = config.DatabaseValue <string>(reader["hh_mm_ss"]),
wait_sec = config.DatabaseValue <long>(reader["wait_sec"]),
locktree = config.DatabaseValue <string>(reader["locktree"]),
spid = config.DatabaseValue <short>(reader["spid"]),
kpid = config.DatabaseValue <short>(reader["kpid"]),
blocked = config.DatabaseValue <short>(reader["blocked"]),
waittype = config.DatabaseValue <byte[]>(reader["waittype"]),
waittime = config.DatabaseValue <long>(reader["waittime"]),
lastwaittype = config.DatabaseValue <string>(reader["lastwaittype"]),
waitresource = config.DatabaseValue <string>(reader["waitresource"]),
dbid = config.DatabaseValue <short>(reader["dbid"]),
uid = config.DatabaseValue <short>(reader["uid"]),
cpu = config.DatabaseValue <int>(reader["cpu"]),
physical_io = config.DatabaseValue <long>(reader["physical_io"]),
memusage = config.DatabaseValue <int>(reader["memusage"]),
login_time = config.DatabaseValue <DateTime>(reader["login_time"]),
last_batch = config.DatabaseValue <DateTime>(reader["last_batch"]),
ecid = config.DatabaseValue <short>(reader["ecid"]),
open_tran = config.DatabaseValue <short>(reader["open_tran"]),
status = config.DatabaseValue <string>(reader["status"]),
sid = config.DatabaseValue <byte[]>(reader["sid"]),
hostname = config.DatabaseValue <string>(reader["hostname"]),
program_name = config.DatabaseValue <string>(reader["program_name"]),
hostprocess = config.DatabaseValue <string>(reader["hostprocess"]),
cmd = config.DatabaseValue <string>(reader["cmd"]),
nt_domain = config.DatabaseValue <string>(reader["nt_domain"]),
nt_username = config.DatabaseValue <string>(reader["nt_username"]),
net_address = config.DatabaseValue <string>(reader["net_address"]),
net_library = config.DatabaseValue <string>(reader["net_library"]),
loginame = config.DatabaseValue <string>(reader["loginame"]),
context_info = config.DatabaseValue <byte[]>(reader["context_info"]),
sql_handle = config.DatabaseValue <byte[]>(reader["sql_handle"]),
stmt_start = config.DatabaseValue <int>(reader["stmt_start"]),
stmt_end = config.DatabaseValue <int>(reader["stmt_end"]),
request_id = config.DatabaseValue <int>(reader["request_id"]),
objectid = config.DatabaseValue <int>(reader["objectid"]),
number = config.DatabaseValue <short>(reader["number"]),
encrypted = config.DatabaseValue <bool>(reader["encrypted"]),
text = config.DatabaseValue <string>(reader["text"])
//죽 다 넣어준다.
});
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
}
protected override string GetData()
{
try
{
list_dm_os_workers_data_TypeA.Clear();
using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = GetDataQuery;
cmd.Parameters.Add("@ProbeTime", SqlDbType.DateTime).Value = Convert.ToDateTime(ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"));
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
list_dm_os_workers_data_TypeA.Add(
new dm_os_workers_data_TypeA
{
ip = LocalIp,
port = LocalPort,
probe_time = ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"),
session_limit = config.DatabaseValue <int>(reader["session_limit"]),
current_session_cnt = config.DatabaseValue <int>(reader["current_session_cnt"]),
max_worker_thread = config.DatabaseValue <int>(reader["max_worker_thread"]),
current_worker_cnt = config.DatabaseValue <int>(reader["current_worker_cnt"]),
scheduler_id = config.DatabaseValue <int>(reader["scheduler_id"]),
quantum_used = config.DatabaseValue <long>(reader["quantum_used"]),
is_preemptive = config.DatabaseValue <bool>(reader["is_preemptive"]).ToString(),
context_switch_count = config.DatabaseValue <long>(reader["context_switch_count"]),
state = config.DatabaseValue <string>(reader["state"]),
last_wait_type = config.DatabaseValue <string>(reader["last_wait_type"]),
processor_group = config.DatabaseValue <short>(reader["processor_group"]),
tasks_processed_count = config.DatabaseValue <int>(reader["tasks_processed_count"]),
task_address = config.DatabaseValue <string>(reader["task_address"]),
session_id = config.DatabaseValue <int>(reader["session_id"]),
original_login_name = config.DatabaseValue <string>(reader["original_login_name"]),
host_name = config.DatabaseValue <string>(reader["host_name"]),
program_name = config.DatabaseValue <string>(reader["program_name"]),
command = config.DatabaseValue <string>(reader["command"]),
cpu_time = config.DatabaseValue <long>(reader["cpu_time"]),
total_elapsed_time = config.DatabaseValue <long>(reader["total_elapsed_time"]),
reads = config.DatabaseValue <long>(reader["reads"]),
writes = config.DatabaseValue <long>(reader["writes"]),
logical_reads = config.DatabaseValue <long>(reader["logical_reads"]),
query_hash = config.DatabaseValue <string>(reader["query_hash"]),
sql_handle = config.DatabaseValue <string>(reader["sql_handle"]),
statement_start_offset = config.DatabaseValue <int>(reader["statement_start_offset"]),
statement_end_offset = config.DatabaseValue <int>(reader["statement_end_offset"]),
database_id = config.DatabaseValue <short>(reader["database_id"]),
blocking_session_id = config.DatabaseValue <int>(reader["blocking_session_id"]),
open_transaction_count = config.DatabaseValue <int>(reader["open_transaction_count"]),
percent_complete = config.DatabaseValue <Single>(reader["percent_complete"]),
transaction_isolation_level = config.DatabaseValue <short>(reader["transaction_isolation_level"]),
query_plan_hash = config.DatabaseValue <string>(reader["query_plan_hash"]),
plan_handle = config.DatabaseValue <string>(reader["plan_handle"]),
query_text = config.DatabaseValue <string>(reader["query_text"]),
});
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
var settings = new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore,
MissingMemberHandling = MissingMemberHandling.Ignore
};
var json = JsonConvert.SerializeObject(list_dm_os_workers_data_TypeA, settings);
return(json);
}
protected override void GetData()
{
// 데이터를 구한다.
log.Warn(string.Format("{0} GetData started", BaseTableName));
try
{
string probe_time = ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000");
using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = GetDataQuery;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
buffer.Add(new dm_os_workers_data
{
probe_time = Convert.ToDateTime(probe_time),
session_limit = config.DatabaseValue <int>(reader["session_limit"]),
current_session_cnt = config.DatabaseValue <int>(reader["current_session_cnt"]),
max_worker_thread = config.DatabaseValue <int>(reader["max_worker_thread"]),
current_worker_cnt = config.DatabaseValue <int>(reader["current_worker_cnt"]),
scheduler_id = config.DatabaseValue <int>(reader["scheduler_id"]),
quantum_used = config.DatabaseValue <long>(reader["quantum_used"]),
is_preemptive = config.DatabaseValue <bool>(reader["is_preemptive"]),
context_switch_count = config.DatabaseValue <int>(reader["context_switch_count"]),
state = config.DatabaseValue <string>(reader["state"]),
last_wait_type = config.DatabaseValue <string>(reader["last_wait_type"]),
processor_group = config.DatabaseValue <short>(reader["processor_group"]),
tasks_processed_count = config.DatabaseValue <int>(reader["tasks_processed_count"]),
task_address = config.DatabaseValue <byte[]>(reader["task_address"]),
session_id = config.DatabaseValue <short>(reader["session_id"]),
original_login_name = config.DatabaseValue <string>(reader["original_login_name"]),
host_name = config.DatabaseValue <string>(reader["host_name"]),
program_name = config.DatabaseValue <string>(reader["program_name"]),
command = config.DatabaseValue <string>(reader["command"]),
cpu_time = config.DatabaseValue <int>(reader["cpu_time"]),
total_elapsed_time = config.DatabaseValue <int>(reader["total_elapsed_time"]),
reads = config.DatabaseValue <long>(reader["reads"]),
writes = config.DatabaseValue <long>(reader["writes"]),
logical_reads = config.DatabaseValue <long>(reader["logical_reads"]),
query_hash = config.DatabaseValue <byte[]>(reader["query_hash"]),
sql_handle = config.DatabaseValue <byte[]>(reader["sql_handle"]),
statement_start_offset = config.DatabaseValue <int>(reader["statement_start_offset"]),
statement_end_offset = config.DatabaseValue <int>(reader["statement_end_offset"]),
database_id = config.DatabaseValue <short>(reader["database_id"]),
blocking_session_id = config.DatabaseValue <short>(reader["blocking_session_id"]),
open_transaction_count = config.DatabaseValue <int>(reader["open_transaction_count"]),
percent_complete = config.DatabaseValue <Single>(reader["percent_complete"]),
transaction_isolation_level = config.DatabaseValue <short>(reader["transaction_isolation_level"]),
query_plan_hash = config.DatabaseValue <byte[]>(reader["query_plan_hash"]),
plan_handle = config.DatabaseValue <byte[]>(reader["plan_handle"]),
query_text = config.DatabaseValue <string>(reader["query_text"])
});
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
}
protected override string GetData()
{
StringBuilder sb = new StringBuilder();
try
{
using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(GetLatestResultsInJsonQuery, CurrentTableName);
cmd.Parameters.Add("@clauserkey", SqlDbType.NVarChar, 100).Value = this.ClaUserKey;
cmd.Parameters.Add("@logtypes", SqlDbType.NVarChar, 100).Value = this.ClaLogTypes;
cmd.Parameters.Add("@hostname", SqlDbType.NVarChar, 100).Value = this.MachineName;
cmd.Parameters.Add("@subject", SqlDbType.NVarChar, 100).Value = BaseTableName;
cmd.Parameters.Add("@private_ip", SqlDbType.NVarChar, 100).Value = this.LocalIp;
cmd.Parameters.Add("@ProbeTime", SqlDbType.DateTime).Value = Convert.ToDateTime(ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"));
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
sb.Append(config.DatabaseValue <string>(reader[0]));
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
return(sb.ToString());
}
private void LocalSaveDeltaValue()
{
log.Warn("LocalSaveDeltaValue");
try
{
DateTime DatetimeNow = DateTime.Now;
using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = LocalSaveDeltaQuery;
cmd.Parameters.Add("@probe_time", SqlDbType.DateTime).Value = Convert.ToDateTime(ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"));
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
}
