1536 lines
58 KiB
C#
1536 lines
58 KiB
C#
//#define LOG_TRACE //@@ 2017.11.01;로그 확인시 사용;girak.kim
|
|
using System;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
|
|
using Cosmos.UserFrame;
|
|
using Cosmos.BaseFrame;
|
|
|
|
/*-----------------------------------------------------------------------------------------------*/
|
|
// 설 명 : MSSQL DB 관련 처리
|
|
// 작 성 자 :
|
|
// 변경 이력 :
|
|
/*-----------------------------------------------------------------------------------------------*/
|
|
namespace Cosmos.Common
|
|
{
|
|
/// <summary>
|
|
/// SqlDB에 대한 요약 설명입니다.
|
|
/// </summary>
|
|
public class SqlDB : IDisposable
|
|
{
|
|
/// <summary>
|
|
/// SQL Connection Object
|
|
/// </summary>
|
|
public SqlConnection sqlConnection;
|
|
|
|
/// <summary>
|
|
/// SQL Command Object
|
|
/// </summary>
|
|
public SqlCommand sqlCommand;
|
|
|
|
/// <summary>
|
|
/// SQL Transaction Object
|
|
/// </summary>
|
|
public SqlTransaction sqlTransaction;
|
|
|
|
// SQL Transaction 성공 Flag
|
|
private bool bBeginTransactionFlag;
|
|
|
|
// SQL Connection 문장
|
|
private string strSqlConnectionString;
|
|
|
|
// SQL Message
|
|
private string strMessage;
|
|
|
|
/// <summary>
|
|
/// SQL Connection 문장
|
|
/// </summary>
|
|
public string SqlConnectionString
|
|
{
|
|
get
|
|
{
|
|
return this.strSqlConnectionString;
|
|
}
|
|
|
|
set
|
|
{
|
|
this.strSqlConnectionString = value;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Connection을 얻음
|
|
/// </summary>
|
|
public SqlConnection Connection
|
|
{
|
|
get { return this.sqlConnection; }
|
|
}
|
|
|
|
/// <summary>
|
|
/// 커맨드를 얻음
|
|
/// </summary>
|
|
public SqlCommand Command
|
|
{
|
|
get { return this.sqlCommand; }
|
|
set { this.sqlCommand = value; }
|
|
}
|
|
#region 오버로드 생성자 함수 주석
|
|
/*
|
|
/// <summary>
|
|
/// 생성자
|
|
/// </summary>
|
|
public MSSqlDB()
|
|
{
|
|
try
|
|
{
|
|
// Sql Connection Object Create
|
|
sqlConnection = new SqlConnection();
|
|
|
|
// SQL Command Object
|
|
sqlCommand = null;
|
|
|
|
// SQL Transaction Object
|
|
sqlTransaction = null;
|
|
|
|
// SQL Transaction 성공 Flag
|
|
bBeginTransactionFlag = false;
|
|
|
|
// Sql Connection String
|
|
this.strSqlConnectionString = "";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
"Process Exception !!! " + e.ToString());
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 생성자
|
|
/// </summary>
|
|
/// <param name="strSqlConnectionString"></param>
|
|
public MSSqlDB(string strSqlConnectionString)
|
|
{
|
|
try
|
|
{
|
|
// Sql Connection Object Create
|
|
sqlConnection = new SqlConnection();
|
|
|
|
// SQL Command Object
|
|
sqlCommand = null;
|
|
|
|
// SQL Transaction Object
|
|
sqlTransaction = null;
|
|
|
|
// SQL Transaction 성공 Flag
|
|
bBeginTransactionFlag = false;
|
|
|
|
// DB Connect String
|
|
this.strSqlConnectionString = strSqlConnectionString;
|
|
|
|
// DB Open
|
|
DBOpen();
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
"Process Exception !!! " + e.ToString());
|
|
}
|
|
}
|
|
*/
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// 생성자
|
|
/// </summary>
|
|
/// <param name="pDataSource"></param>
|
|
/// <param name="pInitialCatalog"></param>
|
|
/// <param name="pUID"></param>
|
|
/// <param name="pPassword"></param>
|
|
public SqlDB(string pDataSource, string pInitialCatalog, string pUID, string pPassword)
|
|
{
|
|
try
|
|
{
|
|
if (IsDBOpen() != true)
|
|
{
|
|
// Sql Connection Object Create
|
|
sqlConnection = new SqlConnection();
|
|
|
|
// SQL Command Object
|
|
sqlCommand = null;
|
|
|
|
// SQL Transaction Object
|
|
sqlTransaction = null;
|
|
|
|
// SQL Transaction 성공 Flag
|
|
bBeginTransactionFlag = false;
|
|
|
|
string strSqlConnectionString = "Data Source=" + pDataSource + "; Initial Catalog = " + pInitialCatalog +
|
|
" ; uid = " + pUID + "; Password = " + pPassword;
|
|
|
|
// DB Connect String
|
|
this.strSqlConnectionString = strSqlConnectionString;
|
|
|
|
// DB Open
|
|
DBOpen();
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.SqlDB()", "Process Exception !!! " + e.ToString());
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Database Open되어 있는지 확인
|
|
/// </summary>
|
|
/// <returns>Database Open 여부</returns>
|
|
#region IS_SQL_OPEN
|
|
public bool IsDBOpen()
|
|
{
|
|
try
|
|
{
|
|
// SQL Database가 Open 되어 있으면 SQL Database를 Close한다
|
|
//if (sqlConnection.State.HasFlag(ConnectionState.Open) == true)
|
|
//if(sqlConnection.State == ConnectionState.Open)
|
|
if(sqlConnection != null)
|
|
{
|
|
if (sqlConnection.State == ConnectionState.Open)
|
|
{
|
|
return true;
|
|
}
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.IsDBOpen()", "Process Exception !!! " + e.ToString());
|
|
}
|
|
|
|
return false;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region SQL_OPEN
|
|
/// <summary>
|
|
/// Sql Database Open
|
|
/// </summary>
|
|
/// <returns>Sql Database Open 성공여부를 리턴</returns>
|
|
public bool DBOpen()
|
|
{
|
|
// SQL Database Open
|
|
try
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
// SQL Database가 Open 되어 있으면 SQL Database를 Close한다
|
|
if (sqlConnection.State == ConnectionState.Open)
|
|
{
|
|
DBClose();
|
|
}
|
|
|
|
// Connection String 설정
|
|
sqlConnection.ConnectionString = this.strSqlConnectionString;
|
|
|
|
// SQL Open
|
|
sqlConnection.Open();
|
|
return true;
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBOpen()", this.strSqlConnectionString + " : " + e.Message);
|
|
return false;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBOpen()", this.strSqlConnectionString + " : " + e.Message);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Sql Database Open
|
|
/// </summary>
|
|
/// <param name="strConnectionString">Sql Database Connection string</param>
|
|
/// <returns></returns>
|
|
public bool DBOpen(string strConnectionString)
|
|
{
|
|
try
|
|
{
|
|
// Sql Connection Object Is Null
|
|
if (sqlConnection == null)
|
|
{
|
|
sqlConnection = new SqlConnection();
|
|
}
|
|
|
|
// Set Sql Connection String
|
|
this.strSqlConnectionString = strConnectionString;
|
|
|
|
// Sql Connection Open
|
|
return DBOpen();
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBOpen()", e.Message);
|
|
return false;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBOpen()", e.Message);
|
|
return false;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// SQL Database Close
|
|
/// </summary>
|
|
#region SQL_CLOSE
|
|
public void DBClose()
|
|
{
|
|
try
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
// SQL Connection이 열려있으면 SQL Connection Close
|
|
if (sqlConnection.State != ConnectionState.Closed)
|
|
{
|
|
sqlConnection.Close();
|
|
}
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBClose()", e.Message);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBClose()", e.Message);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
|
|
/// <summary>
|
|
/// SQL Transaction 객체 소멸
|
|
/// </summary>
|
|
#region SQL_TRANSACTION_DISPOSE
|
|
// SQL 객체 소멸
|
|
public void DBTransactionDispose()
|
|
{
|
|
// Transaction 객체 소멸
|
|
if (sqlTransaction != null)
|
|
{
|
|
sqlTransaction.Dispose();
|
|
sqlTransaction = null;
|
|
}
|
|
|
|
// Command 객체 소멸
|
|
if (sqlCommand != null)
|
|
{
|
|
sqlCommand.Dispose();
|
|
sqlCommand = null;
|
|
}
|
|
|
|
// SQL Transaction 성공 Flag
|
|
bBeginTransactionFlag = false;
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// SQL Transction 시작
|
|
/// </summary>
|
|
/// <returns>Tran Saction 성공여부</returns>
|
|
#region SQL_TRANSACTION_START
|
|
public bool DBBeginTransaction()
|
|
{
|
|
try
|
|
{
|
|
// SQL Database가 Close 되어 있으면 SQL Database Open
|
|
if (sqlConnection.State == ConnectionState.Closed)
|
|
{
|
|
// SQL Database Open
|
|
if (DBOpen() == false)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
// SQL Command 객체 생성
|
|
sqlCommand = sqlConnection.CreateCommand();
|
|
|
|
// SQL Transaction 생성
|
|
sqlTransaction = sqlConnection.BeginTransaction();
|
|
|
|
// SQL Command에 SQL Connection 설정
|
|
sqlCommand.Connection = sqlConnection;
|
|
|
|
// SQL Command에 SQL Transaction 설정
|
|
sqlCommand.Transaction = sqlTransaction;
|
|
|
|
// SQL Transaction 성공 Flag
|
|
bBeginTransactionFlag = true;
|
|
return true;
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBBeginTransaction()", e.Message);
|
|
return false;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBBeginTransaction()", e.Message);
|
|
return false;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// SQL Transaction 실행
|
|
/// </summary>
|
|
/// <returns>SQL Transaction 실행 성공 여부</returns>
|
|
#region SQL_TRANSACTION_COMMIT
|
|
public bool DBTransactionCommit()
|
|
{
|
|
try
|
|
{
|
|
// SQL Transaction 성공 Flag
|
|
if (bBeginTransactionFlag == false)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.SqlDB", "DBTransactionCommit", "SQL Transaction Not Start");
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, UserCom.INFO_LEVEL, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBTransactionCommit()", strMessage);
|
|
return false;
|
|
}
|
|
|
|
// Transaction 객체 소멸
|
|
if (sqlTransaction == null)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, UserCom.INFO_LEVEL, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBTransactionCommit()", "SQL Transaction Not Create");
|
|
return false;
|
|
}
|
|
|
|
// SQL Transaction Commit
|
|
sqlTransaction.Commit();
|
|
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
return true;
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBTransactionCommit()", e.Message);
|
|
return false;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBTransactionCommit()", e.Message);
|
|
return false;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// SQL Transaction 취소
|
|
/// </summary>
|
|
#region SQL_TRANSACTION_ROLLBACK
|
|
public bool DBTransactionRollback()
|
|
{
|
|
try
|
|
{
|
|
// SQL Transaction 성공 Flag
|
|
if (bBeginTransactionFlag == false)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, UserCom.INFO_LEVEL, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBTransactionRollback()", "SQL Transaction Not Start");
|
|
return false;
|
|
}
|
|
|
|
// Transaction 객체 소멸
|
|
if (sqlTransaction == null)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_DEBUG, UserCom.INFO_LEVEL, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBTransactionRollback()", "SQL Transaction Not Create");
|
|
return false;
|
|
}
|
|
|
|
// SQL Transaction Rollback
|
|
sqlTransaction.Rollback();
|
|
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
return true;
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBTransactionRollback()", e.Message);
|
|
return false;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBTransactionRollback()", e.Message);
|
|
return false;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region SQL_DATA_NONQUERY
|
|
public int DBExecuteNonQuery(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters)
|
|
{
|
|
return DBExecuteNonQuery(strCommand, sqlCommandType, sqlParameters, 120);
|
|
}
|
|
public int DBExecuteNonQuery(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters, int nCommandTimeOut)
|
|
{
|
|
int iRet = 0;
|
|
try
|
|
{
|
|
// SQL Database가 Close 되어 있으면 SQL Database Open
|
|
if (sqlConnection.State == ConnectionState.Closed)
|
|
{
|
|
// SQL Transaction 성공 Flag
|
|
if (bBeginTransactionFlag == true)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, UserCom.INFO_LEVEL, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SQL Database Opened Error!!");
|
|
return UserCom.NG1; //Connection 에러
|
|
}
|
|
else
|
|
{
|
|
// SQL Database Open
|
|
if (DBOpen() == false)
|
|
{
|
|
return UserCom.NG1; //Connection 에러
|
|
}
|
|
}
|
|
}
|
|
|
|
// SQL Transaction 성공 Flag
|
|
if (bBeginTransactionFlag == false)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
sqlCommand = new SqlCommand(strCommand, sqlConnection);
|
|
}
|
|
else
|
|
{
|
|
sqlCommand.CommandText = strCommand;
|
|
}
|
|
|
|
if (strCommand == null) return UserCom.OK;
|
|
if (strCommand.Trim() == "") return UserCom.OK;
|
|
|
|
// SetSQL Command Type
|
|
sqlCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlCommand.Parameters.Clear();
|
|
if (sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach (SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
sqlCommand.CommandTimeout = nCommandTimeOut;
|
|
// SQL Data NonQuery 생성
|
|
iRet = sqlCommand.ExecuteNonQuery();
|
|
|
|
#if(LOG_TRACE) //@@ 로그 확인 용
|
|
UserLog.WriteLogFile(UserCom.LOG_OP, "DB Query : ", System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name + "," + System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()", strCommand);
|
|
#endif
|
|
|
|
// Update문에서 적용된 행이 없으면 에러로 반환되어야 하므로
|
|
//if(iRet < 0)
|
|
// 적용된 행이 없을 경우 에러를 반환하면 안되므로 원복(CHOCY - 20161215)
|
|
//if( iRet <= 0)
|
|
if(iRet < 0)
|
|
{
|
|
return UserCom.NG4;
|
|
}
|
|
|
|
return UserCom.OK;
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
if (e.Number == 2601 || e.Number == 2627)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SqlException!!! Query=" + strCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message + " " + e.Number.ToString());
|
|
|
|
return UserCom.NG; // Duplicate key error
|
|
}
|
|
else
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SqlException!!! Query=" + strCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message + " " + e.Number.ToString());
|
|
return UserCom.NG2; //SQL 에러
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "Exception!!! Query=" + strCommand);
|
|
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message);
|
|
return UserCom.NG3; //기타 에러;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// return -> 1:OK, 0:Duplicate key 에러, -1:Connection 에러, -2:SQL 에러, -3:기타 에러
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <returns></returns>
|
|
public int DBExecuteNonQuery(string[] strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters)
|
|
{
|
|
return DBExecuteNonQuery(strCommand, sqlCommandType, sqlParameters, 120);
|
|
}
|
|
public int DBExecuteNonQuery(string[] strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters, int nCommandTimeOut)
|
|
{
|
|
//return DBExecuteNonQuery(strCommand, sqlCommandType, sqlParameters, false);
|
|
|
|
int iRet = 0;
|
|
string eachCommand = string.Empty;
|
|
|
|
try
|
|
{
|
|
// SQL Database가 Close 되어 있으면 SQL Database Open
|
|
if (sqlConnection.State == ConnectionState.Closed)
|
|
{
|
|
// SQL Transaction 성공 Flag
|
|
if (bBeginTransactionFlag == true)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, UserCom.INFO_LEVEL, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SQL Database Opened Error!!");
|
|
return UserCom.NG1; //Connection 에러
|
|
}
|
|
else
|
|
{
|
|
// SQL Database Open
|
|
if (DBOpen() == false)
|
|
{
|
|
return UserCom.NG1; //Connection 에러
|
|
}
|
|
}
|
|
}
|
|
|
|
// SQL Command 객체 생성
|
|
foreach (string sCommand in strCommand)
|
|
{
|
|
if (sCommand == null) continue;
|
|
if (sCommand.Trim() == "") continue;
|
|
|
|
eachCommand = sCommand;
|
|
|
|
// SQL Transaction 성공 Flag
|
|
if (bBeginTransactionFlag == false)
|
|
{
|
|
sqlCommand = new SqlCommand(eachCommand, sqlConnection);
|
|
}
|
|
else
|
|
{
|
|
sqlCommand.CommandText = eachCommand;
|
|
}
|
|
|
|
// SetSQL Command Type
|
|
sqlCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlCommand.Parameters.Clear();
|
|
if (sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach (SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
sqlCommand.CommandTimeout = nCommandTimeOut;
|
|
// SQL Data NonQuery 생성
|
|
iRet = sqlCommand.ExecuteNonQuery();
|
|
|
|
#if(LOG_TRACE) //@@ 로그 확인 용
|
|
UserLog.WriteLogFile(UserCom.LOG_OP, "DB Query : ", System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name + "," + System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()", eachCommand);
|
|
#endif
|
|
// Update문에서 적용된 행이 없으면 에러로 반환되어야 하므로
|
|
//if(iRet < 0)
|
|
// 적용된 행이 없을 경우 에러를 반환하면 안되므로 원복(CHOCY - 20161215)
|
|
//if( iRet <= 0)
|
|
if (iRet < 0)
|
|
{
|
|
return UserCom.NG4; //적용된 Row 없음
|
|
}
|
|
}
|
|
|
|
return UserCom.OK;
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
if (e.Number == 2601 || e.Number == 2627)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SqlException!!! Query=" + eachCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message + " " + e.Number.ToString());
|
|
|
|
return UserCom.NG; // Duplicate key error
|
|
}
|
|
else
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SqlException!!! Query=" + eachCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message + " " + e.Number.ToString());
|
|
return UserCom.NG2; //SQL 에러
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "Exception!!! Query=" + eachCommand);
|
|
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message);
|
|
return UserCom.NG3; //기타 에러;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// return -> 1:OK, -1:Connection 에러, -2:SQL 에러, -3:기타 에러
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <returns></returns>
|
|
public int DBExecuteNonQuery(string[] strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters, bool bStandalone)
|
|
{
|
|
return DBExecuteNonQuery(strCommand, sqlCommandType, sqlParameters, bStandalone, 120);
|
|
}
|
|
public int DBExecuteNonQuery(string[] strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters, bool bStandalone, int nCommandTimeOut)
|
|
{
|
|
int iRet = 0;
|
|
string eachCommand = string.Empty;
|
|
|
|
try
|
|
{
|
|
if (bStandalone == true) DBBeginTransaction();
|
|
|
|
// SQL Database가 Close 되어 있으면 SQL Database Open
|
|
if (sqlConnection.State == ConnectionState.Closed)
|
|
{
|
|
// SQL Transaction 성공 Flag
|
|
if (bBeginTransactionFlag == true)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, UserCom.INFO_LEVEL, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SQL Database Opened Error!!");
|
|
return UserCom.NG1; //Connection 에러
|
|
}
|
|
else
|
|
{
|
|
// SQL Database Open
|
|
if (DBOpen() == false)
|
|
{
|
|
return UserCom.NG1; //Connection 에러
|
|
}
|
|
}
|
|
}
|
|
|
|
// SQL Command 객체 생성
|
|
foreach (string sCommand in strCommand)
|
|
{
|
|
if (sCommand == null) continue;
|
|
if (sCommand.Trim() == "") continue;
|
|
|
|
eachCommand = sCommand;
|
|
|
|
// SQL Transaction 성공 Flag
|
|
if (bBeginTransactionFlag == false)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
if (bStandalone == true) DBTransactionDispose();
|
|
|
|
sqlCommand = new SqlCommand(eachCommand, sqlConnection);
|
|
}
|
|
else
|
|
{
|
|
sqlCommand.CommandText = eachCommand;
|
|
}
|
|
|
|
// SetSQL Command Type
|
|
sqlCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlCommand.Parameters.Clear();
|
|
if (sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach (SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
sqlCommand.CommandTimeout = nCommandTimeOut;
|
|
// SQL Data NonQuery 생성
|
|
iRet = sqlCommand.ExecuteNonQuery();
|
|
|
|
// Update문에서 적용된 행이 없으면 에러로 반환되어야 하므로
|
|
//if(iRet < 0)
|
|
// 적용된 행이 없을 경우 에러를 반환하면 안되므로 원복(CHOCY - 20161215)
|
|
//if( iRet <= 0)
|
|
if (iRet < 0)
|
|
{
|
|
if (bStandalone == true) DBTransactionRollback();
|
|
return UserCom.NG4; //적용된 Row 없음
|
|
}
|
|
}
|
|
|
|
if (bStandalone == true) DBTransactionCommit();
|
|
|
|
return UserCom.OK;
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
if (bStandalone == true) DBTransactionRollback();
|
|
|
|
if (e.Number == 2601 || e.Number == 2627)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SqlException!!! Query=" + eachCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message + " " + e.Number.ToString());
|
|
|
|
return UserCom.NG; // Duplicate key error
|
|
}
|
|
else
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "SqlException!!! Query=" + eachCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message + " " + e.Number.ToString());
|
|
return UserCom.NG2; //SQL 에러
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
if (bStandalone == true) DBTransactionRollback();
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", "Exception!!! Query=" + eachCommand);
|
|
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBExecyteNonQuery()", e.Message);
|
|
return UserCom.NG3; //기타 에러;
|
|
}
|
|
}
|
|
|
|
#region 오버로드함수 주석
|
|
/*
|
|
/// <summary>
|
|
/// SQL DataNonQuery를 실행
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <param name="sqlParameters">SQL 실행 명령 파라메타</param>
|
|
/// <returns>삽입/삭제/갱신 건수</returns>
|
|
public int DBExecuteNonQuery(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters)
|
|
{
|
|
try
|
|
{
|
|
// SQL Database가 Close 되어 있으면 SQL Database Open
|
|
if(sqlConnection.State == ConnectionState.Closed)
|
|
{
|
|
// SQL Transaction 성공 Flag
|
|
if(bBeginTransactionFlag == true)
|
|
{
|
|
strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.MSSqlDB", "DBExecuteNonQuery", "SQL Database Opened Error!!");
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
strMessage);
|
|
return -1;
|
|
}
|
|
else
|
|
{
|
|
// SQL Database Open
|
|
if(DBOpen() == false)
|
|
{
|
|
return -1;
|
|
}
|
|
}
|
|
}
|
|
|
|
// SQL Transaction 성공 Flag
|
|
if(bBeginTransactionFlag == false)
|
|
{
|
|
// SQL Transaction 객체 소멸
|
|
DBTransactionDispose();
|
|
|
|
// SQL Command 객체 생성
|
|
// SqlCommand sqlCommand = new SqlCommand(strCommand, sqlConnection);
|
|
sqlCommand = new SqlCommand(strCommand, sqlConnection);
|
|
}
|
|
else
|
|
{
|
|
sqlCommand.CommandText = strCommand;
|
|
}
|
|
|
|
// SetSQL Command Type
|
|
sqlCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlCommand.Parameters.Clear();
|
|
if(sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach(SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
// SQL Data NonQuery 생성
|
|
return sqlCommand.ExecuteNonQuery();
|
|
}
|
|
catch(SqlException e)
|
|
{
|
|
strMessage = string.Format("[{0}][{1}]{2}{3}", "Cosmos.Common.MSSqlDB", "DBExecuteNonQuery", e.Message, e.Number);
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
strMessage);
|
|
return -1;
|
|
}
|
|
catch(Exception e)
|
|
{
|
|
strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.MSSqlDB", "DBExecuteNonQuery", e.Message);
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
strMessage);
|
|
return -1;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <returns>삽입/삭제/갱신 건수</returns>
|
|
public int DBExecuteNonQuery(string strCommand, CommandType sqlCommandType)
|
|
{
|
|
return DBExecuteNonQuery(strCommand, sqlCommandType, (SqlParameter[])null);
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <returns>삽입/삭제/갱신 건수</returns>
|
|
public int DBExecuteNonQuery(string strCommand)
|
|
{
|
|
return DBExecuteNonQuery(strCommand, CommandType.Text, (SqlParameter[])null);
|
|
}
|
|
*/
|
|
|
|
#endregion
|
|
#endregion
|
|
|
|
|
|
|
|
#region SQL_DATA_TABLE
|
|
|
|
/// <summary>
|
|
/// SQL DataTable를 이용한 Select
|
|
/// <para>return -> 1:OK, -1:Connection 정보 에러, -2:SQL 에러, -3:기타 에러, 0:Select 결과 없음</para>
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <param name="sqlParameters">SQL 실행 명령 파라메타</param>
|
|
/// <param name="outDtReturn" >Select 결과 DataTable</param>
|
|
/// <returns>Select 결과</returns>
|
|
public int DBDataTableSelect(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters, out DataTable outDtReturn)
|
|
{
|
|
return DBDataTableSelect(strCommand, sqlCommandType, sqlParameters, out outDtReturn, 120);
|
|
}
|
|
public int DBDataTableSelect(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters, out DataTable outDtReturn, int nCommandTimeout)
|
|
{
|
|
outDtReturn = null;
|
|
|
|
try
|
|
{
|
|
// SQL Connection String을 체크한다.
|
|
this.strSqlConnectionString.Trim();
|
|
if (this.strSqlConnectionString.Length == 0)
|
|
{
|
|
return UserCom.NG1; //Connection 정보 에러
|
|
}
|
|
|
|
// SQL Connection 객체 생성
|
|
SqlConnection sqlCnnt = new SqlConnection();
|
|
|
|
// SQL Connection 문자 설정
|
|
sqlCnnt.ConnectionString = this.strSqlConnectionString;
|
|
|
|
// SQL DataAdapter 생성
|
|
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
|
|
|
|
// SQL DataAdapter Select Command 생성
|
|
sqlDataAdapter.SelectCommand = new SqlCommand(strCommand);
|
|
|
|
// Set SQL Command Type
|
|
sqlDataAdapter.SelectCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlDataAdapter.SelectCommand.Parameters.Clear();
|
|
if (sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach (SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlDataAdapter.SelectCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
// SQL Connection 설정
|
|
sqlDataAdapter.SelectCommand.Connection = sqlCnnt;
|
|
sqlDataAdapter.SelectCommand.CommandTimeout = nCommandTimeout;
|
|
|
|
// SQL DataSet 객체 생성
|
|
DataSet sqlDataSet = new DataSet();
|
|
|
|
// SQL DataSet 객체에 실행된 Data를 체움
|
|
int iSelCnt = sqlDataAdapter.Fill(sqlDataSet);
|
|
|
|
if (iSelCnt <= 0)
|
|
{
|
|
return UserCom.NG; //Select 결과 없음
|
|
}
|
|
|
|
outDtReturn = sqlDataSet.Tables[0];
|
|
return UserCom.OK;
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBDataTableSelect()", "SqlException Query=" + strCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBDataTableSelect()", e.Message);
|
|
return UserCom.NG2; //SQL 에러
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBDataTableSelect()", "Exception Query=" + strCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR, System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
"SqlDB.DBDataTableSelect()", e.Message);
|
|
return UserCom.NG3; //기타 에러
|
|
}
|
|
}
|
|
|
|
#region 오버로드함수 주석
|
|
/*
|
|
/// <summary>
|
|
/// SQL DataTable를 이용한 Select문장
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <param name="sqlParameters">SQL 실행 명령 파라메타</param>
|
|
/// <returns>Select DataTable</returns>
|
|
public DataTable DBDataTableSelect(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters)
|
|
{
|
|
try
|
|
{
|
|
// SQL Connection String을 체크한다.
|
|
this.strSqlConnectionString.Trim();
|
|
if (this.strSqlConnectionString.Length == 0)
|
|
{
|
|
return (DataTable)null;
|
|
}
|
|
|
|
// SQL Connection 객체 생성
|
|
SqlConnection sqlCnnt = new SqlConnection();
|
|
|
|
// SQL Connection 문자 설정
|
|
sqlCnnt.ConnectionString = this.strSqlConnectionString;
|
|
|
|
// SQL DataAdapter 생성
|
|
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
|
|
|
|
// SQL DataAdapter Select Command 생성
|
|
sqlDataAdapter.SelectCommand = new SqlCommand(strCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_IOS,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
"Select Query=" + strCommand);
|
|
|
|
// Set SQL Command Type
|
|
sqlDataAdapter.SelectCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlDataAdapter.SelectCommand.Parameters.Clear();
|
|
if (sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach (SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlDataAdapter.SelectCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
// SQL Connection 설정
|
|
sqlDataAdapter.SelectCommand.Connection = sqlCnnt;
|
|
|
|
// SQL DataSet 객체 생성
|
|
DataSet sqlDataSet = new DataSet();
|
|
|
|
// SQL DataSet 객체에 실행된 Data를 체움
|
|
sqlDataAdapter.Fill(sqlDataSet);
|
|
|
|
if (sqlDataSet.Tables.Count != 1)
|
|
{
|
|
return (DataTable)null;
|
|
}
|
|
|
|
// SQL Data Reader 생성
|
|
return sqlDataSet.Tables[0];
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_IOS,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
"SqlException Query=" + strCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
e.Message);
|
|
return (DataTable)null;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
UserLog.WriteLogFile(UserCom.LOG_IOS,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
"Exception Query=" + strCommand);
|
|
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
e.Message);
|
|
return (DataTable)null;
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// SQL DataTable를 이용한 Select문장
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <returns>Select DataTable</returns>
|
|
public DataTable DBDataTableSelect(string strCommand, CommandType sqlCommandType)
|
|
{
|
|
return DBDataTableSelect(strCommand, sqlCommandType, (SqlParameter[])null);
|
|
}
|
|
|
|
/// <summary>
|
|
/// SQL DataTable를 이용한 Select문장
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <returns>Select DataTable</returns>
|
|
public DataTable DBDataTableSelect(string strCommand)
|
|
{
|
|
return DBDataTableSelect(strCommand, CommandType.Text, (SqlParameter[])null);
|
|
}
|
|
*/
|
|
#endregion
|
|
|
|
#endregion
|
|
|
|
|
|
#region IDisposable 멤버
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
public void Dispose()
|
|
{
|
|
if (sqlConnection != null)
|
|
{
|
|
// SQL Database Close
|
|
DBClose();
|
|
|
|
// SQL Connection Object Dispose
|
|
sqlConnection.Dispose();
|
|
sqlConnection = null;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region SQL_DATA_SET - 주석
|
|
/*
|
|
/// <summary>
|
|
/// SQL DataSet를 이용한 Select문장
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <param name="sqlParameters">SQL 실행 명령 파라메타</param>
|
|
/// <returns>Select DataSet</returns>
|
|
public DataSet DBDataSetSelect(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters)
|
|
{
|
|
try
|
|
{
|
|
// SQL Connection String을 체크한다.
|
|
this.strSqlConnectionString.Trim();
|
|
if(this.strSqlConnectionString.Length == 0)
|
|
{
|
|
return (DataSet)null;
|
|
}
|
|
|
|
// SQL Connection 객체 생성
|
|
SqlConnection sqlCnnt = new SqlConnection();
|
|
|
|
// SQL Connection 문자 설정
|
|
sqlCnnt.ConnectionString = this.strSqlConnectionString;
|
|
|
|
// SQL DataAdapter 생성
|
|
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
|
|
|
|
// SQL DataAdapter Select Command 생성
|
|
sqlDataAdapter.SelectCommand = new SqlCommand(strCommand);
|
|
|
|
// Set SQL Command Type
|
|
sqlDataAdapter.SelectCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlDataAdapter.SelectCommand.Parameters.Clear();
|
|
if(sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach(SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlDataAdapter.SelectCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
// SQL Connection 설정
|
|
sqlDataAdapter.SelectCommand.Connection = sqlCnnt;
|
|
|
|
// SQL DataSet 객체 생성
|
|
DataSet sqlDataSet = new DataSet();
|
|
|
|
// SQL DataSet 객체에 실행된 Data를 체움
|
|
sqlDataAdapter.Fill(sqlDataSet);
|
|
|
|
// SQL Data Reader 생성
|
|
return sqlDataSet;
|
|
}
|
|
catch(SqlException e)
|
|
{
|
|
//strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.MSSqlDB", "DBDataSetSelect", e.Message);
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
e.Message);
|
|
return (DataSet)null;
|
|
}
|
|
catch(Exception e)
|
|
{
|
|
//strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.MSSqlDB", "DBDataSetSelect", e.Message);
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
e.Message);
|
|
return (DataSet)null;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// SQL DataSet를 이용한 Select문장
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <returns>Select DataSet</returns>
|
|
public DataSet DBDataSetSelect(string strCommand, CommandType sqlCommandType)
|
|
{
|
|
return DBDataSetSelect(strCommand, sqlCommandType, (SqlParameter[])null);
|
|
}
|
|
|
|
/// <summary>
|
|
/// SQL DataSet를 이용한 Select문장
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <returns>Select DataSet</returns>
|
|
public DataSet DBDataSetSelect(string strCommand)
|
|
{
|
|
return DBDataSetSelect(strCommand, CommandType.Text, (SqlParameter[])null);
|
|
}
|
|
*/
|
|
#endregion
|
|
|
|
#region SQL_DATA_READER - 주석
|
|
/*
|
|
/// <summary>
|
|
/// SQL DataReader를 실행
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <param name="sqlParameters">SQL 실행 명령 파라메타</param>
|
|
/// <returns></returns>
|
|
public SqlDataReader DBExecuteReader(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters)
|
|
{
|
|
try
|
|
{
|
|
// SQL Database가 Close 되어 있으면 SQL Database Open
|
|
if(sqlConnection.State == ConnectionState.Closed)
|
|
{
|
|
// SQL Database Open
|
|
if(DBOpen() == false)
|
|
{
|
|
return (SqlDataReader)null;
|
|
}
|
|
}
|
|
|
|
// SQL Command 객체 생성
|
|
SqlCommand sqlLocalCommand = new SqlCommand(strCommand, sqlConnection);
|
|
|
|
// SetSQL Command Type
|
|
sqlLocalCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlLocalCommand.Parameters.Clear();
|
|
if(sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach(SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlLocalCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
// SQL Data Reader 생성
|
|
return sqlLocalCommand.ExecuteReader();
|
|
}
|
|
catch(SqlException e)
|
|
{
|
|
strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.MSSqlDB", "DBExecuteReader", e.Message);
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
strMessage);
|
|
return (SqlDataReader)null;
|
|
}
|
|
catch(Exception e)
|
|
{
|
|
strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.MSSqlDB", "DBExecuteReader", e.Message);
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
strMessage);
|
|
return (SqlDataReader)null;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <returns></returns>
|
|
public SqlDataReader DBExecuteReader(string strCommand, CommandType sqlCommandType)
|
|
{
|
|
return DBExecuteReader(strCommand, sqlCommandType, (SqlParameter[])null);
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <returns></returns>
|
|
public SqlDataReader DBExecuteReader(string strCommand)
|
|
{
|
|
return DBExecuteReader(strCommand, CommandType.Text, (SqlParameter[])null);
|
|
}
|
|
*/
|
|
#endregion
|
|
|
|
#region SQL_DTAT_SCALAR - 주석
|
|
/*
|
|
/// <summary>
|
|
/// SQL DataScalar를 실행
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <param name="sqlParameters">SQL 실행 명령 파라메타</param>
|
|
/// <returns>SQL 단일 실행 결과 객체</returns>
|
|
public object DBExecuteScalar(string strCommand, CommandType sqlCommandType, SqlParameter[] sqlParameters)
|
|
{
|
|
try
|
|
{
|
|
// SQL Database가 Close 되어 있으면 SQL Database Open
|
|
if(sqlConnection.State == ConnectionState.Closed)
|
|
{
|
|
// SQL Database Open
|
|
if(DBOpen() == false)
|
|
{
|
|
return (object)null;
|
|
}
|
|
}
|
|
|
|
// SQL Command 객체 생성
|
|
SqlCommand sqlLocalCommand = new SqlCommand(strCommand, sqlConnection);
|
|
|
|
// SetSQL Command Type
|
|
sqlLocalCommand.CommandType = sqlCommandType;
|
|
|
|
// Set SQL Parameter
|
|
sqlLocalCommand.Parameters.Clear();
|
|
if(sqlParameters != null)
|
|
{
|
|
// SQL Paramater
|
|
foreach(SqlParameter sqlParameter in sqlParameters)
|
|
{
|
|
sqlLocalCommand.Parameters.Add(sqlParameter);
|
|
}
|
|
}
|
|
|
|
// SQL Data Reader 생성
|
|
return (object)sqlLocalCommand.ExecuteScalar();
|
|
}
|
|
catch(SqlException e)
|
|
{
|
|
strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.MSSqlDB", "DBExecuteScalar", e.Message);
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
strMessage);
|
|
return (object)null;
|
|
}
|
|
catch(Exception e)
|
|
{
|
|
strMessage = string.Format("[{0}][{1}]{2}", "Cosmos.Common.MSSqlDB", "DBExecuteScalar", e.Message);
|
|
UserLog.WriteLogFile(UserCom.LOG_ERROR,
|
|
System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." +
|
|
System.Reflection.MethodBase.GetCurrentMethod().Name + "()",
|
|
strMessage);
|
|
return (object)null;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <param name="sqlCommandType">SQL 실행 명령 유형</param>
|
|
/// <returns>SQL 단일 실행 결과 객체</returns>
|
|
public object DBExecuteScalar(string strCommand, CommandType sqlCommandType)
|
|
{
|
|
return DBExecuteScalar(strCommand, sqlCommandType, (SqlParameter[])null);
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="strCommand">SQL 실행 명령 문장</param>
|
|
/// <returns>SQL 단일 실행 결과 객체</returns>
|
|
public object DBExecuteScalar(string strCommand)
|
|
{
|
|
return DBExecuteScalar(strCommand, CommandType.Text, (SqlParameter[])null);
|
|
}
|
|
*/
|
|
#endregion
|
|
}
|
|
}
|