/// <summary> /// 지정 Query 실행 /// </summary> /// <param name="query">Query</param> /// <param name="type">Query의 실행 타입 (SELECT/NOSELECT)</param> /// <returns>함수 실행 결과 (DbmsResult 객체)</returns> public CommonStruct.DbmsResult Execute(string query, CommonEnum.ExecuteType type) { CommonStruct.DbmsResult result = new CommonStruct.DbmsResult(); SqlCommand mssqlCommand = null; SqlDataReader mssqlReader = null; DataTable resultTable = null; stopWatch.Start(); try { if (dbState != System.Data.ConnectionState.Open) { Connect(); } resultTable = new DataTable(); mssqlCommand = new SqlCommand(); mssqlCommand.Connection = mssqlConn; mssqlCommand.CommandType = System.Data.CommandType.Text; mssqlCommand.CommandText = query; mssqlCommand.CommandTimeout = 10; if (type == CommonEnum.ExecuteType.SELECT) { mssqlReader = mssqlCommand.ExecuteReader(); resultTable.Load(mssqlReader); } else { resultTable = null; mssqlCommand.ExecuteNonQuery(); } //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Execute(Query = {1}) Success :: Type = {2} RowCount = {3}", this.ToString(), query, type, executeRows)); result.funcResult.isSuccess = true; result.funcResult.funcException = null; result.resultTable = resultTable; } catch (Exception ex) { //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Execute(Query = {1}) Exception :: Message = {2}", this.ToString(), query, ex.Message)); result.funcResult.isSuccess = false; result.funcResult.funcException = ex; result.resultTable = null; } finally { if (mssqlCommand != null) { mssqlCommand.Dispose(); mssqlCommand = null; } } stopWatch.Stop(); result.funcResult.totalMilliseconds = stopWatch.ElapsedMilliseconds; stopWatch.Reset(); return(result); }
/// <summary> /// 지정 Procedure 실행 /// </summary> /// <param name="procName">Procedure</param> /// <param name="param">Procedure Parameter</param> /// <param name="type">Procedure의 실행 타입 (ExecuteType 변수)</param> /// <returns>함수 실행 결과 (DbmsResult 객체)</returns> public CommonStruct.DbmsResult Execute(string procName, Dictionary <string, object> param, CommonEnum.ExecuteType type) { CommonStruct.DbmsResult result = new CommonStruct.DbmsResult(); SqlCommand mssqlCommand = null; SqlDataReader mssqlReader = null; DataTable resultTable = null; stopWatch.Start(); try { if (dbState != System.Data.ConnectionState.Open) { Connect(); } resultTable = new DataTable(); mssqlCommand = new SqlCommand(); mssqlCommand.Connection = mssqlConn; mssqlCommand.CommandType = System.Data.CommandType.StoredProcedure; mssqlCommand.CommandText = procName; mssqlCommand.CommandTimeout = 10; foreach (KeyValuePair <string, object> p in param) { mssqlCommand.Parameters.AddWithValue(p.Key, p.Value.ToString()); } if (type == CommonEnum.ExecuteType.SELECT) { mssqlReader = mssqlCommand.ExecuteReader(); resultTable.Load(mssqlReader); } else { resultTable = null; mssqlCommand.ExecuteNonQuery(); } //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Execute(ProcName = {1}) Success :: Type = {2} RowCount = {3}", this.ToString(), procName, type, executeRows)); result.funcResult.isSuccess = true; result.funcResult.funcException = null; result.resultTable = resultTable; } catch (Exception ex) { //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Execute(ProcName = {1}) Exception :: Message = {2}", this.ToString(), procName, ex.Message)); result.funcResult.isSuccess = false; result.funcResult.funcException = ex; result.resultTable = null; } finally { if (mssqlCommand != null) { mssqlCommand.Dispose(); mssqlCommand = null; } } stopWatch.Stop(); result.funcResult.totalMilliseconds = stopWatch.ElapsedMilliseconds; stopWatch.Reset(); return(result); }
/// <summary> /// 지정 테이블의 데이터 DELETE /// </summary> /// <param name="tableName">테이블</param> /// <param name="condition">조건(컬럼명, 값)</param> /// <returns>함수 실행 결과 (DbmsResult 객체)</returns> public CommonStruct.DbmsResult Delete(string tableName, string condition = null) { CommonStruct.DbmsResult result = new CommonStruct.DbmsResult(); SqlCommand mssqlCommand = null; stopWatch.Start(); try { if (dbState != System.Data.ConnectionState.Open) { Connect(); } string deleteQuery = string.Empty; string tableQuery = string.Empty; string conditionQuery = string.Empty; tableQuery = string.Format("DELETE FROM [{0}]", tableName); if (condition != null) { conditionQuery = string.Format("WHERE {0}", condition); } deleteQuery = string.Format("{0} {1}", tableQuery, conditionQuery); mssqlCommand = new SqlCommand(); mssqlCommand.Connection = mssqlConn; mssqlCommand.CommandType = System.Data.CommandType.Text; mssqlCommand.CommandText = deleteQuery; mssqlCommand.CommandTimeout = 1; mssqlCommand.ExecuteNonQuery(); //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Delete(TableName = {1}) Success :: RowCount = {2}", this.ToString(), tableName, result.resultRowCount)); result.funcResult.isSuccess = true; result.funcResult.funcException = null; result.resultTable = null; } catch (Exception ex) { //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Delete(TableName = {1}) Exception :: Message = {2}", this.ToString(), tableName, ex.Message)); result.funcResult.isSuccess = false; result.funcResult.funcException = ex; result.resultTable = null; } finally { if (mssqlCommand != null) { mssqlCommand.Dispose(); mssqlCommand = null; } } stopWatch.Stop(); result.funcResult.totalMilliseconds = stopWatch.ElapsedMilliseconds; stopWatch.Reset(); return(result); }
/// <summary> /// 지정 테이블의 데이터 UPDATE /// </summary> /// <param name="tableName">테이블</param> /// <param name="data">수정 데이터</param> /// <param name="condition">조건(컬럼명, 값)</param> /// <returns>함수 실행 결과 (DbmsResult 객체)</returns> public CommonStruct.DbmsResult Update(string tableName, Dictionary <string, object> data, string condition = null) { CommonStruct.DbmsResult result = new CommonStruct.DbmsResult(); SqlCommand mssqlCommand = null; stopWatch.Start(); try { if (dbState != System.Data.ConnectionState.Open) { Connect(); } string updateQuery = string.Empty; string setQuery = string.Empty; string conditionQuery = string.Empty; for (int i = 0; i < data.Count; i++) { if (i == 0) { setQuery = string.Format("SET [{0}] = '{1}',", data.Keys.ElementAt(i), data.Values.ElementAt(i)); } else { setQuery += string.Format("[{0}] = '{1}',", data.Keys.ElementAt(i), data.Values.ElementAt(i)); } } if (condition != null) { conditionQuery = string.Format("WHERE {0}", condition); } setQuery = setQuery.Substring(0, setQuery.Length - 1); updateQuery = string.Format("UPDATE [{0}] WITH(UPDLOCK) {1} {2}", tableName, setQuery, conditionQuery); mssqlCommand = new SqlCommand(); mssqlCommand.Connection = mssqlConn; mssqlCommand.CommandType = System.Data.CommandType.Text; mssqlCommand.CommandText = updateQuery; mssqlCommand.CommandTimeout = 1; mssqlCommand.ExecuteNonQuery(); //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Update(TableName = {1}) Success :: RowCount = {2}", this.ToString(), tableName, result.resultRowCount)); result.funcResult.isSuccess = true; result.funcResult.funcException = null; result.resultTable = null; } catch (Exception ex) { //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Update(TableName = {1}) Exception :: Message = {2}", this.ToString(), tableName, ex.Message)); result.funcResult.isSuccess = false; result.funcResult.funcException = ex; result.resultTable = null; } finally { if (mssqlCommand != null) { mssqlCommand.Dispose(); mssqlCommand = null; } } stopWatch.Stop(); result.funcResult.totalMilliseconds = stopWatch.ElapsedMilliseconds; stopWatch.Reset(); return(result); }
/// <summary> /// 지정 테이블의 데이터 INSERT /// </summary> /// <param name="tableName">테이블</param> /// <param name="data">데이터</param> /// <returns>함수 실행 결과 (DbmsResult 객체)</returns> public CommonStruct.DbmsResult Insert(string tableName, Dictionary <string, object> data) { CommonStruct.DbmsResult result = new CommonStruct.DbmsResult(); SqlCommand mssqlCommand = null; stopWatch.Start(); try { string insertQuery = string.Empty; string columnQuery = string.Empty; string valueQuery = string.Empty; if (dbState != System.Data.ConnectionState.Open) { Connect(); } foreach (KeyValuePair <string, object> d in data) { columnQuery += string.Format("[{0}],", d.Key); valueQuery += string.Format("'{0}',", d.Value); } columnQuery = columnQuery.Substring(0, columnQuery.Length - 1); valueQuery = valueQuery.Substring(0, valueQuery.Length - 1); insertQuery = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", tableName, columnQuery, valueQuery); mssqlCommand = new SqlCommand(); mssqlCommand.Connection = mssqlConn; mssqlCommand.CommandType = System.Data.CommandType.Text; mssqlCommand.CommandText = insertQuery; mssqlCommand.CommandTimeout = 1; mssqlCommand.ExecuteNonQuery(); //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Insert(TableName = {1}) Success", this.ToString(), tableName)); result.funcResult.isSuccess = true; result.funcResult.funcException = null; result.resultTable = null; } catch (Exception ex) { //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Insert(TableName = {1}) Exception :: Message = {1}", this.ToString(), ex.Message)); result.funcResult.isSuccess = false; result.funcResult.funcException = ex; result.resultTable = null; } finally { if (mssqlCommand != null) { mssqlCommand.Dispose(); mssqlCommand = null; } } stopWatch.Stop(); result.funcResult.totalMilliseconds = stopWatch.ElapsedMilliseconds; stopWatch.Reset(); return(result); }
/// <summary> /// 지정 테이블의 데이터 SELECT /// </summary> /// <param name="tableName">SELECT 테이블명</param> /// <param name="condition">조건(컬럼명, 값)</param> /// <returns>함수 실행 결과 (DbmsResult 객체)</returns> public CommonStruct.DbmsResult Select(string tableName, string condition = null) { CommonStruct.DbmsResult result = new CommonStruct.DbmsResult(); SqlCommand mssqlCommand = null; SqlDataReader mssqlReader = null; DataTable resultTable = null; stopWatch.Start(); try { string selectQuery = string.Empty; string tableQuery = string.Empty; string conditionQuery = string.Empty; if (dbState != System.Data.ConnectionState.Open) { Connect(); } tableQuery = string.Format("SELECT * FROM [{0}] WITH(NOLOCK)", tableName); if (condition != null) { conditionQuery = string.Format("WHERE {0}", condition); } selectQuery = string.Format("{0} {1}", tableQuery, conditionQuery); resultTable = new DataTable(); mssqlCommand = new SqlCommand(); mssqlCommand.Connection = mssqlConn; mssqlCommand.CommandType = System.Data.CommandType.Text; mssqlCommand.CommandText = selectQuery; mssqlCommand.CommandTimeout = 1; mssqlReader = mssqlCommand.ExecuteReader(); resultTable.Load(mssqlReader); //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Select(TableName = {1}) Success :: RowCount = {2}", this.ToString(), tableName, result.resultRowCount)); result.funcResult.isSuccess = true; result.funcResult.funcException = null; result.resultTable = resultTable; } catch (Exception ex) { //LogHandler.WriteLog(string.Empty, string.Format("{0} :: Select(TableName = {1}) Exception :: Message = {2}", this.ToString(), tableName, ex.Message)); result.funcResult.isSuccess = false; result.funcResult.funcException = ex; result.resultTable = null; } finally { if (mssqlReader != null) { mssqlReader.Close(); mssqlReader = null; } if (mssqlCommand != null) { mssqlCommand.Dispose(); mssqlCommand = null; } } stopWatch.Stop(); result.funcResult.totalMilliseconds = stopWatch.ElapsedMilliseconds; return(result); }