System.Data.SqlClient.SqlDataReader.Read()

Here are the examples of the csharp api System.Data.SqlClient.SqlDataReader.Read() taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

855 Examples 7

19 Source : dm_exec_query_statsTypeA.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

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;

        }

19 Source : dm_exec_query_statsTypeB.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

protected override string GetData()
        {
            // WebApi 로 데이터 전송 
            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.CommandText = GetLatestResultsInJsonQuery;
                        cmd.Parameters.Add("@hostname", SqlDbType.NVarChar, 100).Value = this.MachineName;
                        cmd.Parameters.Add("@private_ip", SqlDbType.NVarChar, 100).Value = this.LocalIp; 
                        cmd.Parameters.Add("@ncp_mbr_no", SqlDbType.NVarChar, 100).Value = config.GetValue(Category.NPOT, Key.ncp_mbr_no);
                        cmd.Parameters.Add("@ncp_group_no", SqlDbType.NVarChar, 100).Value = config.GetValue(Category.NPOT, Key.ncp_group_no);
                        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();
        }

19 Source : dm_exec_query_statsTypeB.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

private string GetStatementData()
        {
            // WebApi 로 데이터 전송 
            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 = GetStatementResultsInJsonQuery;
                        cmd.Parameters.Add("@Machinename", SqlDbType.NVarChar, 100).Value = this.MachineName;
                        cmd.Parameters.Add("@private_ip", SqlDbType.NVarChar, 100).Value = this.LocalIp;
                        cmd.Parameters.Add("@logtypes", SqlDbType.NVarChar, 100).Value = this.ClaLogTypes;
                        cmd.Parameters.Add("@clauserkey", SqlDbType.NVarChar, 100).Value = this.ClaUserKey;
                        cmd.Parameters.Add("@subject", SqlDbType.NVarChar, 100).Value = "dm_exec_query_stats2";
                        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();
        }

19 Source : dm_os_workersTypeA.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

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;
        }

19 Source : dm_os_workersTypeB.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

protected override string GetData()
        {
            // WebApi 로 데이터 전송 
            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, ProbeTime.ToString("yyyy-MM-dd HH:mm:ss.000"));
                        cmd.CommandText = GetLatestResultsInJsonQuery;
                        cmd.Parameters.Add("@hostname", SqlDbType.NVarChar, 100).Value = this.MachineName;
                        cmd.Parameters.Add("@clauserkey", SqlDbType.NVarChar, 100).Value = this.ClaUserKey;
                        cmd.Parameters.Add("@logtypes", SqlDbType.NVarChar, 100).Value = this.ClaLogTypes;
                        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())
                            {
                                //single 관련 에러 없는지 볼것 
                                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();
        }

19 Source : sp_lock2TypeA.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

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;
        }

19 Source : sp_lock2TypeB.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

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

19 Source : BaseSqlmon.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

protected virtual bool GetCurrentTableName(string BaseTableName, out string CurrentTableName)
        {
            bool bReturn = false;
            string TableName = string.Empty;
            try
            {
                using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = @"
select top 1 TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like @BaseTableName+'[_]________[_]______' 
order by TABLE_NAME desc 
option (recompile)
";
                        cmd.Parameters.Add("@BaseTableName", SqlDbType.NVarChar, 100).Value = BaseTableName;

                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                TableName = config.DatabaseValue<string>(reader["TABLE_NAME"]);
                            }
                            bReturn = true;
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
                TableName = "";
            }

            CurrentTableName = TableName;
            return bReturn;
        }

19 Source : dm_exec_query_stats.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

protected override void GetData()
        {
            // 데이터를 구한다. 
            log.Warn(string.Format("{0} GetData started", BaseTableName));
            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 = GetDataQuery;
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                buffer.Add(new dm_exec_query_stats_data
                                {
                                    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<byte[]>(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<byte[]>(reader["plan_handle"]),
                                    query_hash = config.DatabaseValue<byte[]>(reader["query_hash"]),
                                    query_plan_hash = config.DatabaseValue<byte[]>(reader["query_plan_hash"])
                                });
                            }
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
            }
        }

19 Source : dm_os_workers.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

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));
            }
        }

19 Source : sp_lock2.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

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));
            }
        }

19 Source : sp_readagentlog.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

protected override bool GetCurrentTableName(string BaseTableName, out string CurrentTableName)
        {
            bool bReturn = false;
            string TableName = string.Empty;
            try
            {
                using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = @"
select top 1 TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like @BaseTableName+'SendHist' 
order by TABLE_NAME desc ";
                        cmd.Parameters.Add("@BaseTableName", SqlDbType.NVarChar, 100).Value = BaseTableName;

                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                TableName = config.DatabaseValue<string>(reader["TABLE_NAME"]);
                            }
                            bReturn = true;
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                TableName = "";
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
            }

            CurrentTableName = TableName;
            return bReturn;
        }

19 Source : sp_readagentlog.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

private void LoadLastSentData()
        {
            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(@"
select 
	  LogDate
	, ErrorLevel
	, Text
from [dbo].[{0}SendHist]
", BaseTableName);
                        cmd.Parameters.Add("@BaseTableName", SqlDbType.NVarChar, 100).Value = BaseTableName;

                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                xpLogTableBuffer.Add(new Tuple<DateTime, string, string>
                                (
                                    config.DatabaseValue<DateTime>(reader["LogDate"]),
                                    config.DatabaseValue<string>(reader["ErrorLevel"]),
                                    config.DatabaseValue<string>(reader["Text"])
                                ), "");
                            }
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
            }
        }

19 Source : sp_readagentlog.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

protected override void GetData()
        {
            // 데이터를 구한다. 

            log.Warn(string.Format("{0} GetData started", BaseTableName));
            try
            {
                if (xpLogTableBuffer.Count > 0)
                {
                    maxDateTime = xpLogTableBuffer.Max(x => x.Key.Item1);
                }

                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("@LogStartTime", SqlDbType.DateTime).Value = maxDateTime;
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                try
                                {
                                    if (!xpLogNewBuffer.ContainsKey(new Tuple<DateTime, string, string>
                                    (
                                        config.DatabaseValue<DateTime>(reader["LogDate"]),
                                        config.DatabaseValue<string>(reader["ProcessInfo"]),
                                        config.DatabaseValue<string>(reader["Text"])
                                    )))
                                        xpLogNewBuffer.Add(new Tuple<DateTime, string, string>
                                        (
                                            config.DatabaseValue<DateTime>(reader["LogDate"]),
                                            config.DatabaseValue<int>(reader["ErrorLevel"]).ToString(),
                                            config.DatabaseValue<string>(reader["Text"])
                                        ), "");
                                }
                                catch (Exception ex) { log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace)); }
                            }
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
            }
        }

19 Source : sp_readerrorlog.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

protected override bool GetCurrentTableName(string BaseTableName, out string CurrentTableName)
        {
            bool bReturn = false;
            string TableName = string.Empty;
            try
            {
                using (SqlConnection conn = new SqlConnection(config.GetConnectionString(InitialCatalog.Repository)))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = @"
select top 1 TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like @BaseTableName+'SendHist' 
order by TABLE_NAME desc ";
                        cmd.Parameters.Add("@BaseTableName", SqlDbType.NVarChar, 100).Value = BaseTableName;

                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                TableName = config.DatabaseValue<string>(reader["TABLE_NAME"]);
                            }
                            bReturn = true;
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
                TableName = "";
            }

            CurrentTableName = TableName;
            return bReturn;
        }

19 Source : sp_readerrorlog.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

private void LoadLastSentData()
        {
            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(@"
select 
	  LogDate
	, ProcessInfo
	, Text
from [dbo].[{0}SendHist]
", BaseTableName);
                        cmd.Parameters.Add("@BaseTableName", SqlDbType.NVarChar, 100).Value = BaseTableName;

                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                xpLogTableBuffer.Add(new Tuple<DateTime, string, string>
                                (
                                    config.DatabaseValue<DateTime>(reader["LogDate"]),
                                    config.DatabaseValue<string>(reader["ProcessInfo"]),
                                    config.DatabaseValue<string>(reader["Text"])
                                ), "");
                            }
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
            }
        }

19 Source : sp_readerrorlog.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

protected override void GetData()
        {
            // 데이터를 구한다. 
            
            log.Warn(string.Format("{0} GetData started", BaseTableName));
            try
            {
                if (xpLogTableBuffer.Count > 0)
                {
                    maxDateTime = xpLogTableBuffer.Max(x => x.Key.Item1);
                }

                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("@LogStartTime", SqlDbType.DateTime).Value = maxDateTime;
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                try
                                {
                                    if (!xpLogNewBuffer.ContainsKey(new Tuple<DateTime, string, string>
                                        (
                                            config.DatabaseValue<DateTime>(reader["LogDate"]),
                                            config.DatabaseValue<string>(reader["ProcessInfo"]),
                                            config.DatabaseValue<string>(reader["Text"])
                                        )))
                                    xpLogNewBuffer.Add(new Tuple<DateTime, string, string>
                                        (
                                            config.DatabaseValue<DateTime>(reader["LogDate"]),
                                            config.DatabaseValue<string>(reader["ProcessInfo"]),
                                            config.DatabaseValue<string>(reader["Text"])
                                        ), "");
                                }
                                catch (Exception ex)
                                {
                                    log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
                                } 
                            }
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
            }
        }

19 Source : ExecuterSql.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform

bool QueryExecuter(string listStringQuery, int connectionTimeout = 5, int commandTimeout = 30, string queryEchoYN="N", string countYN="N", string headerYN = "N")
        {
            bool bReturn = false;
            sbResultAll.Clear();
            
            List<string> querys = TranString.ReadQuery(listStringQuery);
            if (queryEchoYN.Equals("Y", StringComparison.OrdinalIgnoreCase))
            {
                sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                sbResultAll.Append(DateTime.Now + Environment.NewLine);
                sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);
            }
            foreach (var query in querys)
            {
                try
                {
                    if (queryEchoYN.Equals("Y", StringComparison.OrdinalIgnoreCase))
                    {
                        sbResultAll.Append("-->>---------------------------------");
                        sbResultAll.Append(query + Environment.NewLine);
                        sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);
                    }
                    if (query.Trim().ToUpper().StartsWith("USE"))
                    {
                        string[] database = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None);
                        connectionString = SetConnectionString(database[1], connectionTimeout);
                        //comboBoxDatabase.InvokeIfRequired(s =>
                        //{
                        //    s.Text = "";
                        //    s.SelectedText = database[1].ToString();
                        //});
                    }
                    string result = string.Empty;
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                        conn.InfoMessage += Conn_InfoMessage; // message hook (like backup message) 

                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.StatementCompleted += Cmd_StatementCompleted; // retrive row count
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = query;
                            cmd.CommandTimeout = commandTimeout;
                            SqlDataReader reader = cmd.ExecuteReader();
                            int recordsAffected = reader.RecordsAffected;

                            do
                            {
                                StringBuilder sb = new StringBuilder();
                                string Header = string.Empty;
                                string Line = string.Empty;
                                DataTable schemaTable = reader.GetSchemaTable();
                                if (schemaTable != null)
                                {

                                    try
                                    {
                                        if (headerYN.Equals("Y", StringComparison.OrdinalIgnoreCase))
                                        {
                                            foreach (DataRow row in schemaTable.Rows)
                                            {
                                                foreach (DataColumn column in schemaTable.Columns)
                                                {
                                                    if (column.ColumnName == "ColumnName")
                                                    {
                                                        Header = Header + row[column] + "\t";
                                                        Line = Line + "------- ";
                                                    }
                                                }
                                            }
                                            Header = Header + Environment.NewLine;
                                            Line = Line + Environment.NewLine;
                                            sb.Append(Header);
                                            sb.Append(Line);
                                        }

                                        while (reader.Read())
                                        {
                                            for (int i = 0; i < reader.FieldCount; i++)
                                            {
                                                if (reader.GetValue(i).ToString() == "System.Byte[]")
                                                    sb.Append("0x" + BitConverter.ToString((byte[])reader.GetValue(i)).Replace("-", ""));
                                                else
                                                    sb.Append(reader.GetValue(i).ToString());
                                                sb.Append("\t");
                                            }
                                            sb.Append(Environment.NewLine);
                                        }

                                    }
                                    catch (SqlException ex)
                                    {
                                        errorCnt++;
                                        sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                                        sbResultAll.Append("--sql exception info : " + Environment.NewLine);
                                        sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                                        for (int i = 0; i < ex.Errors.Count; i++)
                                        {
                                            sbResultAll.Append("Inner SqlException No #" + i + Environment.NewLine +
                                            "Message: " + ex.Errors[i].Message + Environment.NewLine +
                                            "Source: " + ex.Errors[i].Source + Environment.NewLine +
                                            "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine);
                                        }
                                    }
                                    finally
                                    {
                                        sb.Append(Environment.NewLine);
                                        sbResultAll.Append(sb);
                                        if (countYN.Equals("Y", StringComparison.OrdinalIgnoreCase))
                                        {
                                            sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows"));
                                        }
                                    }
                                }
                                else
                                {
                                    string[] Query = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None);
                                    if (
                                        Query[0].Equals("update", StringComparison.OrdinalIgnoreCase)
                                        || Query[0].Equals("insert", StringComparison.OrdinalIgnoreCase)
                                        || Query[0].Equals("delete", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("update", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("insert", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("delete", StringComparison.OrdinalIgnoreCase)
                                        )
                                        sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows"));
                                    else
                                        sbResultAll.Append(string.Format("Commands completed successfully." + Environment.NewLine + Environment.NewLine));
                                }
                                reader.NextResult();
                            } while (reader.HasRows);
                        }
                        conn.Close();
                        bReturn = true;
                    }
                }

                catch (SqlException ex)
                {
                    errorCnt++;
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                    sbResultAll.Append("--sql exception info : " + Environment.NewLine);
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);

                    for (int i = 0; i < ex.Errors.Count; i++)
                    {
                        sbResultAll.Append("SqlException No #" + i + Environment.NewLine +
                        "Message: " + ex.Errors[i].Message + Environment.NewLine +
                        "Source: " + ex.Errors[i].Source + Environment.NewLine +
                        "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine);
                    }

                    sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);
                    bReturn = false;
                }
                catch (Exception ex)
                {
                    errorCnt++;
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                    sbResultAll.Append("--exception info : " + Environment.NewLine);
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                    sbResultAll.Append(ex.Message);
                    sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);

                    bReturn = false;
                }
                finally
                {
                    if (syncAsync.Equals("Async", StringComparison.OrdinalIgnoreCase))
                        Common.FileLogWriteLine(logFileFullname, sbResultAll.ToString());

                }
            }

            if (syncAsync.Equals("Async", StringComparison.OrdinalIgnoreCase))
                Common.FileLogWriteLine(logFileFullname, "async cmd completed");

            return bReturn;
        }

19 Source : WonkaBizQueryRule.cs
with MIT License
from Nethereum

public string ExecuteQuery(string psArg1, string psArg2, string psArg3, string psArg4)
        {
            string sResultValue = "";

            try
            {
                using (SqlConnection DbConn = GetConnection())
                {
                    DbConn.Open();

                    // NOTE: Use parameters in prepared statement with query here?
                    string sSqlQuery =
                        String.Format(this.CustomOpSource.SqlQueryOrProcedure, psArg1, psArg2, psArg3, psArg4);

                    using (SqlCommand QueryCmd = new SqlCommand(sSqlQuery, DbConn))
                    {
                        using (SqlDataReader dataReader = QueryCmd.ExecuteReader())
                        {
                            if (dataReader.Read())
                            {
                                // NOTE: For the time being, it will return the first value of the first column
                                if (!dataReader.IsDBNull(0))
                                    sResultValue = dataReader[0].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                // NOTE: Do something here?
            }

            return sResultValue;
        }

19 Source : WonkaBizQueryRule.cs
with MIT License
from Nethereum

public HashSet<string> RetrieveDomainSet()
        {
            var DomainSet = new HashSet<string>();

            if (IsDomainQuery)
            {
                try
                {
                    using (SqlConnection DbConn = GetConnection())
                    {
                        DbConn.Open();

                        // NOTE: Use parameters in prepared statement with query here?
                        string sSqlQuery =
                            String.Format(this.CustomOpSource.SqlQueryOrProcedure);

                        using (SqlCommand QueryCmd = new SqlCommand(sSqlQuery, DbConn))
                        {
                            using (SqlDataReader dataReader = QueryCmd.ExecuteReader())
                            {
                                if (dataReader.Read())
                                {
                                    // NOTE: For the time being, it will return the first value of the first column
                                    if (!dataReader.IsDBNull(0))
                                    {
                                        string sResultValue = dataReader[0].ToString();
                                        CachedDomain.Add(sResultValue);
                                    }
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    // NOTE: Do something here?
                }
            }

            return DomainSet;
        }

19 Source : WonkaBizStoredProcRule.cs
with MIT License
from Nethereum

public string ExecuteStoredProcedure(string psArg1, string psArg2, string psArg3, string psArg4)
        {
            string sResultValue = "";

            string sConnString = "Data Source=" + this.CustomOpSource.SqlServer +
                                 ";Initial Catalog=" + this.CustomOpSource.SqlDatabase +
                                 ";User ID=" + this.CustomOpSource.SqlUsername +
                                 ";Preplacedword=" + this.CustomOpSource.SqlPreplacedword;

            // NOTE: Use parameters in prepared statement with query here?
            string sSqlQuery = this.CustomOpSource.SqlQueryOrProcedure;

            using (SqlConnection DbConn = new SqlConnection(sConnString))
            {
                try
                {
                    DbConn.Open();

                    using (SqlCommand StoredProcCmd = new SqlCommand())
                    {
                        StoredProcCmd.CommandType = CommandType.StoredProcedure;
                        StoredProcCmd.CommandText = sSqlQuery;
                        StoredProcCmd.Parameters.Clear();

                        string[] Args1 = psArg1.Split('=');
                        string[] Args2 = psArg2.Split('=');
                        string[] Args3 = psArg3.Split('=');
                        string[] Args4 = psArg4.Split('=');

                        AddParameter(StoredProcCmd, Args1);
                        AddParameter(StoredProcCmd, Args2);
                        AddParameter(StoredProcCmd, Args3);
                        AddParameter(StoredProcCmd, Args4);

                        StoredProcCmd.Parameters.Add(CONST_OUT_PARAM_RET_VALUE, SqlDbType.VarChar, 128);
                        StoredProcCmd.Parameters[CONST_OUT_PARAM_RET_VALUE].Direction = ParameterDirection.ReturnValue;

                        using (SqlDataReader ProcReader = StoredProcCmd.ExecuteReader())
                        {
                            if (ProcReader.Read())
                            {
                                if (StoredProcCmd.Parameters[CONST_OUT_PARAM_RET_VALUE].Value != DBNull.Value)
                                {
                                    sResultValue = 
                                        StoredProcCmd.Parameters[CONST_OUT_PARAM_RET_VALUE].Value.ToString();
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    // NOTE: Do something here?
                }
            }

            return sResultValue;
        }

19 Source : MsSqlController.cs
with Apache License 2.0
from newrelic

[HttpGet]
        public string MsSql(string tableName)
        {
            var teamMembers = new List<string>();

            using (var connection = new SqlConnection(MsSqlConfiguration.MsSqlConnectionString))
            {
                connection.Open();

                using (var command = new SqlCommand("SELECT * FROM NewRelic.dbo.TeamMembers WHERE FirstName = 'John'", connection))
                {

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            teamMembers.Add(reader.GetString(reader.GetOrdinal("FirstName")));
                            if (reader.NextResult())
                            {
                                teamMembers.Add(reader.GetString(reader.GetOrdinal("FirstName")));
                            }
                        }
                    }
                }

                var insertSql = string.Format(InsertPersonMsSql, tableName);
                var countSql = string.Format(CountPersonMsSql, tableName);
                var deleteSql = string.Format(DeletePersonMsSql, tableName);

                using (var command = new SqlCommand(insertSql, connection))
                {
                    var insertCount = command.ExecuteNonQuery();
                }

                using (var command = new SqlCommand(countSql, connection))
                {
                    var teamMemberCount = command.ExecuteScalar();
                }

                using (var command = new SqlCommand(deleteSql, connection))
                {
                    var deleteCount = command.ExecuteNonQuery();
                }
            }

            return string.Join(",", teamMembers);
        }

19 Source : MsSqlController.cs
with Apache License 2.0
from newrelic

public string MsSql_WithParameterizedQuery(string tableName, bool paramsWithAtSign)
        {
            var teamMembers = new List<string>();

            using (var connection = new SqlConnection(MsSqlConfiguration.MsSqlConnectionString))
            {
                connection.Open();

                using (var command = new SqlCommand("SELECT * FROM NewRelic.dbo.TeamMembers WHERE FirstName = @FN", connection))
                {
                    command.Parameters.Add(new SqlParameter(paramsWithAtSign ? "@FN" : "FN", "O'Keefe"));
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            teamMembers.Add(reader.GetString(reader.GetOrdinal("FirstName")));
                            if (reader.NextResult())
                            {
                                teamMembers.Add(reader.GetString(reader.GetOrdinal("FirstName")));
                            }
                        }
                    }
                }

            }

            return string.Join(",", teamMembers);
        }

19 Source : Employee.cs
with GNU General Public License v3.0
from nightness

private static Employee Get(SqlConnection cn, int id)
        {
            Employee result = null;
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Employee WHERE EmployeeId=" + id, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildEmployee(rdr);
                    }
                }
            }
            return result;
        }

19 Source : Employee.cs
with GNU General Public License v3.0
from nightness

public static int GetPersonId(int employeeId)
        {
            int personId = 0;

            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT EmployeePersonId FROM Employee WHERE EmployeeId=" + employeeId, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        try
                        {
                            personId = Convert.ToInt32(rdr[0].ToString());
                        }
                        catch
                        {
                        }
                    }
                }
            }
            FinishedWithConnection(cn);
            return personId;
        }

19 Source : Employee.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<EmployeeJob> GetJobs(int employeeId)
        {
            List<int> jobIds = new List<int>();
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT EmployeePayRateEmployeeJobId FROM EmployeePayRate INNER JOIN EmployeeJob ON EmployeePayRateEmployeeJobId = EmployeeJobId WHERE EmployeePayRateEmployeeId=@EmployeePayRateEmployeeId", cn))
            {
                BuildSqlParameter(cmd, "@EmployeePayRateEmployeeId", SqlDbType.Int, employeeId);
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        try
                        {
                            jobIds.Add(Convert.ToInt32(rdr[0]));
                        }
                        catch
                        {
                        }
                    }
                }
                foreach (int jobId in jobIds)
                {
                    yield return EmployeeJob.Get(jobId);
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : EmployeeCheck.cs
with GNU General Public License v3.0
from nightness

private static EmployeeCheck Get(SqlConnection cn, int id)
        {
            EmployeeCheck result = null;
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeCheck WHERE EmployeeCheckId=" + id, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildEmployeeCheck(rdr);
                    }
                }
            }
            return result;
        }

19 Source : EmployeePayRate.cs
with GNU General Public License v3.0
from nightness

private static EmployeePayRate Get(SqlConnection cn, int id)
        {
            EmployeePayRate result = null;
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeePayRate WHERE EmployeePayRateId=" + id, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildEmployeePayRate(rdr);
                    }
                }
            }
            return result;
        }

19 Source : EmployeePayRate.cs
with GNU General Public License v3.0
from nightness

public static EmployeePayRate GetEmployeePayRateForJob(int employeeId, int jobId)
        {
            EmployeePayRate result = null;
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand(
                "SELECT * FROM EmployeePayRate WHERE (EmployeePayRateEmployeeId="
                + employeeId + " AND EmployeePayRateEmployeeJobId=" + jobId + ")", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildEmployeePayRate(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
            return result;
        }

19 Source : Coupon.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<Coupon> GetAll()
        {
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Coupon WHERE CouponIsDiscontinued=0", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildCoupon(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : Coupon.cs
with GNU General Public License v3.0
from nightness

private static Coupon Get(SqlConnection cn, int couponId)
        {
            Coupon result = null;
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Coupon WHERE CouponId=" + couponId, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildCoupon(rdr);
                    }
                }
            }
            return result;
        }

19 Source : CouponCategory.cs
with GNU General Public License v3.0
from nightness

public static int Count(int couponId)
        {
            int result = 0;
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM CouponCategory WHERE CouponCategoryCouponId=" + couponId, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = Convert.ToInt32(rdr[0].ToString());
                    }
                }
            }
            FinishedWithConnection(cn);
            return result;
        }

19 Source : EmployeeStatus.cs
with GNU General Public License v3.0
from nightness

private static EmployeeStatus Get(SqlConnection cn, int id)
        {
            EmployeeStatus result = null;
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeStatus WHERE EmployeeStatusId=" + id, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildEmployeeTermination(rdr);
                    }
                }
            }
            return result;
        }

19 Source : EmployeeStatus.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<EmployeeStatus> GetAll()
        {
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeStatus", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildEmployeeTermination(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : EmployeeStatus.cs
with GNU General Public License v3.0
from nightness

public static EmployeeStatus GetEmployeesActiveStatus(int employeeId)
        {
            EmployeeStatus result = null;
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeStatus WHERE ((EmployeeStatusTerminationDate IS NOT NULL) AND EmployeeStatusEmployeeId=" + employeeId + ")", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildEmployeeTermination(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
            return result;
        }

19 Source : EmployeeTimesheet.cs
with GNU General Public License v3.0
from nightness

public static bool TableIsEmpty()
        {
            bool foundEntry = false;
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT TOP 1 EmployeeTimesheetId FROM EmployeeTimesheet", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                            foundEntry = true;
                    }
                }
            }
            FinishedWithConnection(cn);
            return !foundEntry;
        }

19 Source : EmployeeTimesheet.cs
with GNU General Public License v3.0
from nightness

private static EmployeeTimesheet Get(SqlConnection cn, int id)
        {
            EmployeeTimesheet result = null;
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeTimesheet WHERE EmployeeTimesheetId=" + id, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildEmployeeTimesheet(rdr);
                    }
                }
            }
            return result;
        }

19 Source : EmployeeTimesheet.cs
with GNU General Public License v3.0
from nightness

public static EmployeeTimesheet GetOpenEntryForEmployee(int employeeId)
        {
            EmployeeTimesheet result = null;

            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeTimesheet WHERE EmployeeTimesheetEmployeeId=" + employeeId +
                    " AND EmployeeTimesheetEndTime IS NULL", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildEmployeeTimesheet(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
            return result;
        }

19 Source : EmployeeTimesheet.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<EmployeeTimesheet> GetAllOpen()
        {
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeTimesheet WHERE EmployeeTimesheetEndTime IS NULL", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildEmployeeTimesheet(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : EmployeeTimesheet.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<EmployeeTimesheet> GetAll()
        {
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeTimesheet", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildEmployeeTimesheet(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : EmployeeTimesheet.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<EmployeeTimesheet> GetAll(DateTime startDate, DateTime endDate)
        {
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeTimesheet WHERE (EmployeeTimesheetStartTime >= @EmployeeTimesheetSearchStartTime AND EmployeeTimesheetStartTime <= @EmployeeTimesheetSearchEndTime AND EmployeeTimesheetIsDeleted = 0)", cn))
            {
                BuildSqlParameter(cmd, "@EmployeeTimesheetSearchStartTime", SqlDbType.DateTime, startDate);
                BuildSqlParameter(cmd, "@EmployeeTimesheetSearchEndTime", SqlDbType.DateTime, endDate);
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildEmployeeTimesheet(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : EmployeeTimesheet.cs
with GNU General Public License v3.0
from nightness

public static bool IsOverlapping(int existingId, int employeeId, DateTime startDate, DateTime endDate)
        {
            bool result = false;
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT TOP 1 * FROM EmployeeTimesheet WHERE " +
                "(EmployeeTimesheetEmployeeId = @EmployeeTimesheetEmployeeId) AND (EmployeeTimesheetIsDeleted = 0) AND " +
                "(EmployeeTimesheetId != @EmployeeTimesheetId) AND " +
                "((EmployeeTimesheetStartTime >= @EmployeeTimesheetSearchStartTime AND EmployeeTimesheetStartTime <= @EmployeeTimesheetSearchEndTime) OR " +
                "(EmployeeTimesheetEndTime >= @EmployeeTimesheetSearchStartTime AND EmployeeTimesheetEndTime <= @EmployeeTimesheetSearchEndTime))", cn))
            {
                BuildSqlParameter(cmd, "@EmployeeTimesheetId", SqlDbType.Int, existingId);
                BuildSqlParameter(cmd, "@EmployeeTimesheetEmployeeId", SqlDbType.Int, employeeId);
                BuildSqlParameter(cmd, "@EmployeeTimesheetSearchStartTime", SqlDbType.DateTime, startDate);
                BuildSqlParameter(cmd, "@EmployeeTimesheetSearchEndTime", SqlDbType.DateTime, endDate);
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                        result = true;
                }
            }
            FinishedWithConnection(cn);
            return result;
        }

19 Source : CouponCategory.cs
with GNU General Public License v3.0
from nightness

private static CouponCategory Get(SqlConnection cn, int id)
        {
            CouponCategory result = null;
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM CouponCategory WHERE CouponCategoryId=" + id, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildCouponCategory(rdr);
                    }
                }
            }
            return result;
        }

19 Source : CouponCategory.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<CouponCategory> GetAll()
        {
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM CouponCategory", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildCouponCategory(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : CouponCategory.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<CouponCategory> GetAll(int couponId)
        {
            SqlConnection cn = GetConnection(); 
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM CouponCategory WHERE CouponCategoryCouponId=" + couponId, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildCouponCategory(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : CouponItem.cs
with GNU General Public License v3.0
from nightness

private static CouponItem Get(SqlConnection cn, int id)
        {
            CouponItem result = null;
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM CouponItem WHERE CouponItemId=" + id, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildCouponItem(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
            return result;
        }

19 Source : CouponItem.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<CouponItem> GetAll()
        {
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM CouponItem", cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildCouponItem(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : CouponItem.cs
with GNU General Public License v3.0
from nightness

public static IEnumerable<CouponItem> GetAll(int couponId)
        {
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM CouponItem WHERE CouponItemCouponId=" + couponId, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return BuildCouponItem(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
        }

19 Source : Customer.cs
with GNU General Public License v3.0
from nightness

private static Customer Get(SqlConnection cn, int id)
        {
            Customer result = null;

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customer WHERE CustomerId=" + id, cn))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildCustomer(rdr);
                    }
                }
            }
            return result;
        }

19 Source : Customer.cs
with GNU General Public License v3.0
from nightness

public static Customer GetByPhoneNumber(string phoneNumber)
        {
            Customer result = null;
            PhoneNumber phone = PhoneNumber.Get(phoneNumber);
            if (phone == null)
                return null;
            SqlConnection cn = GetConnection();
            using (SqlCommand cmd = new SqlCommand("SELECT Customer.*,Person.* FROM Customer INNER JOIN Person ON CustomerPersonId = PersonId WHERE ((PersonPhoneNumberId1=@PhoneNumberId) OR (PersonPhoneNumberId2=@PhoneNumberId) OR (PersonPhoneNumberId3=@PhoneNumberId) OR (PersonPhoneNumberId4=@PhoneNumberId) OR (PersonPhoneNumberId5=@PhoneNumberId) OR (PersonPhoneNumberId6=@PhoneNumberId))", cn))
            {
                BuildSqlParameter(cmd, "@PhoneNumberId", SqlDbType.Int, phone.Id);
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        result = BuildCustomer(rdr);
                    }
                }
            }
            FinishedWithConnection(cn);
            return result;
        }

See More Examples