csharp/NaverCloudPlatform/SqlServerDbaTool/NaverCloudPlatform-SqlServerDbaTool-ffd4834/LazyLog/Sqlmon/WebApi/dm_os_workersTypeA.cs

dm_os_workersTypeA.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Newtonsoft.Json;

namespace lazylog
{
    clast dm_os_workersTypeA : BaseSender
    {
        public dm_os_workersTypeA(string CurrentTableName, string BaseTableName, DateTime ProbeTime) 
            : base(CurrentTableName, BaseTableName, ProbeTime)
        {
            list_dm_os_workers_data_TypeA = new List();
        }

        List list_dm_os_workers_data_TypeA;

        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(reader["session_limit"]),
                                        current_session_cnt = config.DatabaseValue(reader["current_session_cnt"]),
                                        max_worker_thread = config.DatabaseValue(reader["max_worker_thread"]),
                                        current_worker_cnt = config.DatabaseValue(reader["current_worker_cnt"]),
                                        scheduler_id = config.DatabaseValue(reader["scheduler_id"]),
                                        quantum_used = config.DatabaseValue(reader["quantum_used"]),
                                        is_preemptive = config.DatabaseValue(reader["is_preemptive"]).ToString(),
                                        context_switch_count = config.DatabaseValue(reader["context_switch_count"]),
                                        state = config.DatabaseValue(reader["state"]),
                                        last_wait_type = config.DatabaseValue(reader["last_wait_type"]),
                                        processor_group = config.DatabaseValue(reader["processor_group"]),
                                        tasks_processed_count = config.DatabaseValue(reader["tasks_processed_count"]),
                                        task_address = config.DatabaseValue(reader["task_address"]),
                                        session_id = config.DatabaseValue(reader["session_id"]),
                                        original_login_name = config.DatabaseValue(reader["original_login_name"]),
                                        host_name = config.DatabaseValue(reader["host_name"]),
                                        program_name = config.DatabaseValue(reader["program_name"]),
                                        command = config.DatabaseValue(reader["command"]),
                                        cpu_time = config.DatabaseValue(reader["cpu_time"]),
                                        total_elapsed_time = config.DatabaseValue(reader["total_elapsed_time"]),
                                        reads = config.DatabaseValue(reader["reads"]),
                                        writes = config.DatabaseValue(reader["writes"]),
                                        logical_reads = config.DatabaseValue(reader["logical_reads"]),
                                        query_hash = config.DatabaseValue(reader["query_hash"]),
                                        sql_handle = config.DatabaseValue(reader["sql_handle"]),
                                        statement_start_offset = config.DatabaseValue(reader["statement_start_offset"]),
                                        statement_end_offset = config.DatabaseValue(reader["statement_end_offset"]),
                                        database_id = config.DatabaseValue(reader["database_id"]),
                                        blocking_session_id = config.DatabaseValue(reader["blocking_session_id"]),
                                        open_transaction_count = config.DatabaseValue(reader["open_transaction_count"]),
                                        percent_complete = config.DatabaseValue(reader["percent_complete"]),
                                        transaction_isolation_level = config.DatabaseValue(reader["transaction_isolation_level"]),
                                        query_plan_hash = config.DatabaseValue(reader["query_plan_hash"]),
                                        plan_handle = config.DatabaseValue(reader["plan_handle"]),
                                        query_text = config.DatabaseValue(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 Initialization()
        {
            try
            {
                GetDataQuery = @"
select
	  probe_time
	, session_limit
	, current_session_cnt
	, max_worker_thread
	, current_worker_cnt
	, scheduler_id
	, quantum_used
	, is_preemptive
	, context_switch_count
	, state
	, last_wait_type
	, processor_group
	, tasks_processed_count
	, convert(varchar(max), task_address, 1) task_address
	, session_id
	, original_login_name
	, host_name
	, program_name
	, command
	, cpu_time
	, total_elapsed_time
	, reads
	, writes
	, logical_reads
	, convert(varchar(max), query_hash, 1) query_hash
	, convert(varchar(max), sql_handle, 1) sql_handle
	, statement_start_offset
	, statement_end_offset
	, database_id
	, blocking_session_id
	, open_transaction_count
	, percent_complete
	, transaction_isolation_level
	, convert(varchar(max), query_plan_hash, 1) query_plan_hash
	, convert(varchar(max), plan_handle, 1) plan_handle
	, convert(varchar(max), query_text, 1) query_text
from [dbo].[view_dm_os_workers]
where probe_time = @ProbeTime
";
                EndPointUrl = config.GetValue(Category.Sqlmon, Key.SqlmonWebApiCallEndPointUrl);
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
            }
        }

        internal override void SendData()
        {
            try
            {
                SendToGeneralRepository(GetData(), "/api/sqlmon/dm_os_workers");
                list_dm_os_workers_data_TypeA = null;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
            }
        }
    }
}