611 lines
16 KiB
C#
611 lines
16 KiB
C#
using Microsoft.Extensions.Configuration;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Data.SqlClient;
|
|
using System.Data;
|
|
|
|
namespace REPORT_API_ver6.Services
|
|
{
|
|
public class DAL
|
|
{
|
|
static readonly log4net.ILog logger = log4net.LogManager.GetLogger(typeof(DAL));
|
|
|
|
/// <summary>
|
|
/// Report DB 연결
|
|
/// </summary>
|
|
/// <returns>DB 연결 객체</returns>
|
|
public SqlConnection ConnectReport()
|
|
{
|
|
SqlConnection connection = new SqlConnection();
|
|
try
|
|
{
|
|
var configuration = Common.GetConfiguration();
|
|
connection.ConnectionString = configuration.GetSection("ConnectionStrings").GetSection("ReportString").Value;
|
|
connection.Open();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in ConnectReport : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
return connection;
|
|
}
|
|
|
|
/// <summary>
|
|
/// KON / KIWI DB 연결
|
|
/// </summary>
|
|
/// <param name="isKiwi">0:kon 1:kiwi</param>
|
|
/// <returns>DB 연결 객체</returns>
|
|
public SqlConnection ConnectKonKiwi(int isKiwi)
|
|
{
|
|
SqlConnection connection = new SqlConnection();
|
|
string connectionString = string.Empty;
|
|
try
|
|
{
|
|
var configuration = Common.GetConfiguration();
|
|
|
|
if (isKiwi == 0)
|
|
{
|
|
connectionString = configuration.GetSection("ConnectionStrings").GetSection("KonString").Value;
|
|
}
|
|
else
|
|
{
|
|
connectionString = configuration.GetSection("ConnectionStrings").GetSection("KiwiString").Value;
|
|
}
|
|
|
|
connection.ConnectionString = connectionString;
|
|
connection.Open();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in ConnectKonKiwi : " + connectionString + "///" + ex.ToString() + Environment.NewLine);
|
|
}
|
|
return connection;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 인트라넷 DB 연결
|
|
/// </summary>
|
|
/// <returns>DB 연결 객체</returns>
|
|
public SqlConnection ConnectIntra()
|
|
{
|
|
SqlConnection connection = new SqlConnection();
|
|
try
|
|
{
|
|
var configuration = Common.GetConfiguration();
|
|
connection.ConnectionString = configuration.GetSection("ConnectionStrings").GetSection("IntraString").Value;
|
|
connection.Open();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in ConnectIntra : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
return connection;
|
|
}
|
|
|
|
/// <summary>
|
|
/// DB 연결
|
|
/// </summary>
|
|
/// <param name="connectionString">Connection String</param>
|
|
/// <returns>DB 연결 객체</returns>
|
|
public SqlConnection Connect(string connectionString)
|
|
{
|
|
SqlConnection connection = new SqlConnection();
|
|
try
|
|
{
|
|
connection.ConnectionString = connectionString;
|
|
connection.Open();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in Connect : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
return connection;
|
|
}
|
|
|
|
/// <summary>
|
|
/// DB 연결 해제
|
|
/// </summary>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
public void Disconnect(SqlConnection connection)
|
|
{
|
|
try
|
|
{
|
|
connection.Close();
|
|
connection.Dispose();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in Disconnect : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// DB접속객체에서 Procedure 실행하여 DataReader로 반환
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <param name="sqlParameter">인수배열</param>
|
|
/// <returns>결과 행이 저장되어 있는 DataReader 객체</returns>
|
|
public SqlDataReader GetDataReader(string procedureName, SqlConnection connection, List<SqlParameter> sqlParameter)
|
|
{
|
|
SqlDataReader reader = null;
|
|
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
for (int i = 0; i < sqlParameter.Count; i++)
|
|
{
|
|
cmd.Parameters.Add(sqlParameter[i]);
|
|
}
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
reader = cmd.ExecuteReader();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataReader : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
|
|
return reader;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 프로시저를 수행하여 DataReader로 반환
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <returns>결과 행이 저장되어 있는 DataReader 객체</returns>
|
|
public SqlDataReader GetDataReaderWithProcedure(string procedureName, SqlConnection connection)
|
|
{
|
|
SqlDataReader reader = null;
|
|
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
reader = cmd.ExecuteReader();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataReader : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
|
|
return reader;
|
|
}
|
|
|
|
/// <summary>
|
|
/// SQL문을 수행하여 DataReader로 반환
|
|
/// </summary>
|
|
/// <param name="sql">SQL문</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <returns>결과 행이 저장되어 있는 DataReader 객체</returns>
|
|
public SqlDataReader GetDataReader(string sql, SqlConnection connection)
|
|
{
|
|
SqlDataReader reader = null;
|
|
/*
|
|
string sessionID = hcAccessor.HttpContext.Session.Id;
|
|
//killProcessQuery(sessionID, connection);
|
|
sql = "--" + sessionID + "--" + Environment.NewLine + sql;
|
|
*/
|
|
SqlCommand cmd = new SqlCommand(sql, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.Text;
|
|
reader = cmd.ExecuteReader();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataReader : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
|
|
return reader;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 프로시저를 수행하여 DataTable 객체 반환
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <param name="sqlParameter">인수배열</param>
|
|
/// <returns>프로시저 수행 결과 DataTable</returns>
|
|
public DataTable GetDataTable(string procedureName, SqlConnection connection, SqlParameter[] sqlParameter)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
SqlDataReader reader = null;
|
|
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
for (int i = 0; i < sqlParameter.Length; i++)
|
|
{
|
|
cmd.Parameters.Add(sqlParameter[i]);
|
|
}
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
reader = cmd.ExecuteReader();
|
|
dataTable.Load(reader);
|
|
reader.Close();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataTable : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
|
|
return dataTable;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 파라미터 없이 프로시저를 수행하여 DataTable 객체 반환
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <returns></returns>
|
|
public DataTable GetDataTableNonePara(string procedureName, SqlConnection connection)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
SqlDataReader reader = null;
|
|
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
reader = cmd.ExecuteReader();
|
|
dataTable.Load(reader);
|
|
reader.Close();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataTable : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
return dataTable;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 프로시저를 수행하여 DataTable 객체 반환
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <param name="sqlParameter">인수 List</param>
|
|
/// <returns></returns>
|
|
public DataTable GetDataTable(string procedureName, SqlConnection connection, List<SqlParameter> sqlParameter)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
SqlDataReader reader = null;
|
|
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
for (int i = 0; i < sqlParameter.Count; i++)
|
|
{
|
|
cmd.Parameters.Add(sqlParameter[i]);
|
|
}
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
reader = cmd.ExecuteReader();
|
|
dataTable.Load(reader);
|
|
reader.Close();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataTable(" + procedureName + ") : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
|
|
return dataTable;
|
|
}
|
|
|
|
/// <summary>
|
|
/// SQL문을 수행하여 DataTable 객체 반환
|
|
/// </summary>
|
|
/// <param name="sql">SQL문</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <returns>SQL문 수행 결과 DataTable</returns>
|
|
public DataTable GetDataTable(string sql, SqlConnection connection)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
SqlDataReader reader = null;
|
|
/*
|
|
string sessionID = hcAccessor.HttpContext.Session.Id;
|
|
//killProcessQuery(sessionID, connection);
|
|
sql = "--" + sessionID + "--" + Environment.NewLine + sql;
|
|
*/
|
|
SqlCommand cmd = new SqlCommand(sql, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.Text;
|
|
reader = cmd.ExecuteReader();
|
|
dataTable.Load(reader);
|
|
reader.Close();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataTable : " + Environment.NewLine + sql + Environment.NewLine + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
return dataTable;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 프로시저를 수행하여 DataSet 객체 반환
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객제</param>
|
|
/// <param name="sqlParameter">인수배열</param>
|
|
/// <returns>프로시저를 수행결과 DataSet</returns>
|
|
public DataSet GetDataSet(string procedureName, SqlConnection connection, SqlParameter[] sqlParameter)
|
|
{
|
|
SqlDataAdapter adapter = null;
|
|
DataSet ds = new DataSet();
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
for (int i = 0; i < sqlParameter.Length; i++)
|
|
{
|
|
cmd.Parameters.Add(sqlParameter[i]);
|
|
}
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
adapter = new SqlDataAdapter(cmd);
|
|
adapter.Fill(ds);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataSet : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
adapter.Dispose();
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
|
|
/// <summary>
|
|
/// SQL문을 수행하여 DataSet 객체 반환
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객제</param>
|
|
/// <param name="sqlParameter">인수배열</param>
|
|
/// <returns>프로시저를 수행결과 DataSet</returns>
|
|
public DataSet GetDataSet(string sql, SqlConnection connection)
|
|
{
|
|
SqlDataAdapter adapter = null;
|
|
DataSet ds = new DataSet();
|
|
SqlCommand cmd = new SqlCommand(sql, connection);
|
|
cmd.CommandTimeout = 300;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.Text;
|
|
adapter = new SqlDataAdapter(cmd);
|
|
adapter.Fill(ds);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in GetDataSet : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
adapter.Dispose();
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
|
|
/// <summary>
|
|
/// SQL문 실행
|
|
/// </summary>
|
|
/// <param name="sql">SQL문</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <returns>실패 : -1</returns>
|
|
public int ExecuteSQL(string sql, SqlConnection connection)
|
|
{
|
|
int affectedRow = 0;
|
|
|
|
/*
|
|
string sessionID = hcAccessor.HttpContext.Session.Id;
|
|
//killProcessQuery(sessionID, connection);
|
|
sql = "--" + sessionID + "--" + Environment.NewLine + sql;
|
|
*/
|
|
|
|
try
|
|
{
|
|
SqlCommand cmd = new SqlCommand(sql, connection);
|
|
cmd.CommandType = System.Data.CommandType.Text;
|
|
cmd.CommandTimeout = 300;
|
|
|
|
cmd.ExecuteNonQuery();
|
|
cmd.Dispose();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
affectedRow = -1;
|
|
logger.Info("DAL class exception in ExecuteSQL : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
|
|
return affectedRow;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 프로시저 수행
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <param name="sqlParameter">인수 배열</param>
|
|
/// <returns>실패 : -1</returns>
|
|
public int ExecuteStoredProcedure(string procedureName, SqlConnection connection, SqlParameter[] sqlParameter)
|
|
{
|
|
int affectedRow = -1;
|
|
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = System.Data.CommandType.StoredProcedure;
|
|
cmd.CommandTimeout = 300;
|
|
|
|
for (int i = 0; i < sqlParameter.Length; i++)
|
|
{
|
|
cmd.Parameters.Add(sqlParameter[i]);
|
|
}
|
|
|
|
affectedRow = cmd.ExecuteNonQuery();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in ExecuteStoredProcedure : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
|
|
return affectedRow;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 프로시저 수행
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <param name="sqlParameter">인수 List</param>
|
|
/// <returns>실패 : -1</returns>
|
|
public int ExecuteStoredProcedure(string procedureName, SqlConnection connection, List<SqlParameter> sqlParameter)
|
|
{
|
|
int affectedRow = -1;
|
|
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = System.Data.CommandType.StoredProcedure;
|
|
cmd.CommandTimeout = 300;
|
|
|
|
for (int i = 0; i < sqlParameter.Count; i++)
|
|
{
|
|
cmd.Parameters.Add(sqlParameter[i]);
|
|
}
|
|
|
|
affectedRow = cmd.ExecuteNonQuery();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in ExecuteStoredProcedure : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
|
|
return affectedRow;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 프로시저 수행
|
|
/// </summary>
|
|
/// <param name="procedureName">프로시저명</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
/// <returns>실패 : -1</returns>
|
|
public int ExecuteStoredProcedure(string procedureName, SqlConnection connection)
|
|
{
|
|
int affectedRow = -1;
|
|
|
|
SqlCommand cmd = new SqlCommand(procedureName, connection);
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = System.Data.CommandType.StoredProcedure;
|
|
cmd.CommandTimeout = 300;
|
|
|
|
affectedRow = cmd.ExecuteNonQuery();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in ExecuteStoredProcedure : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
}
|
|
|
|
return affectedRow;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 쿼리 강제로 KILL
|
|
/// </summary>
|
|
/// <param name="sessionID">Session ID</param>
|
|
/// <param name="connection">DB 연결 객체</param>
|
|
public void killProcessQuery(string sessionID, SqlConnection connection)
|
|
{
|
|
string killQuery = "declare @spid int" + Environment.NewLine;
|
|
killQuery += " select @spid = session_id" + Environment.NewLine;
|
|
killQuery += " from sys.dm_exec_requests handle" + Environment.NewLine;
|
|
killQuery += " outer apply sys.fn_get_sql(handle.sql_handle) spname" + Environment.NewLine;
|
|
killQuery += " where spname.text is not null" + Environment.NewLine;
|
|
killQuery += " and spname.text like '%" + sessionID + "%'" + Environment.NewLine;
|
|
killQuery += " and spname.text not like '%from sys.dm_exec_requests handle%'" + Environment.NewLine;
|
|
killQuery += " if @spid <> ''" + Environment.NewLine;
|
|
killQuery += " begin" + Environment.NewLine;
|
|
killQuery += " exec('kill '+@spid)" + Environment.NewLine;
|
|
killQuery += " end" + Environment.NewLine;
|
|
|
|
|
|
try
|
|
{
|
|
SqlCommand cmd = new SqlCommand(killQuery, connection);
|
|
cmd.CommandType = System.Data.CommandType.Text;
|
|
cmd.CommandTimeout = 300;
|
|
|
|
cmd.ExecuteNonQuery();
|
|
cmd.Dispose();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
logger.Info("DAL class exception in killProcessQuery : " + ex.ToString() + Environment.NewLine);
|
|
}
|
|
}
|
|
}
|
|
}
|