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
19
Source : dm_exec_query_statsTypeA.cs
with GNU Lesser General Public License v3.0
from NaverCloudPlatform
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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