출처 : 본인

출처1 : https://www.nuget.org/packages/Oracle.ManagedDataAccess/

출처2 : http://www.sysnet.pe.kr/Default.aspx?mode=2&sub=0&detail=1&pageno=0&wid=10928&rssMode=1&wtype=0

오라클 사용시 사용할 수 있는 odp.net 을 사용하여 만든 클래스

nuget 설치시

PM>

DB.7z

using Oracle.ManagedDataAccess.Client;

using Oracle.ManagedDataAccess.Types;

public class Oracle : IDisposable

{

/* 참고 페이지

* http://www.sysnet.pe.kr/Default.aspx?mode=2&sub=0&detail=1&pageno=0&wid=10928&rssMode=1&wtype=0

*/

public void Dispose()

{

GC.SuppressFinalize(this);

}

private static string _conStr = string.Empty;

public Oracle() { }

public Oracle(string conStr) : this() { _conStr = conStr; }

public Oracle(bool appName, string config)

{

}

public Oracle(string ip, string port, string sid, string id, string pw)

{

_conStr = string.Format(

"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};"

, ip, port, sid, id, pw);

}

/// <summary>

/// adhoc 쿼리 받아서 처리

/// </summary>

/// <param name="selectQuery">select 쿼리</param>

/// <returns></returns>

public DataSet ExecuteDataSet(string selectQuery)

{

DataSet ds = new DataSet();

OracleDataAdapter adapter = new OracleDataAdapter(selectQuery, _conStr);

try

{

adapter.Fill(ds);

}

catch (OracleException x)

{

WriteError(x.Message, selectQuery);

throw new Exception(string.Format("Error!!\n{0}\n{1}", x.Message, selectQuery));

}

finally

{

DisposeAdapter(adapter);

}

return ds;

}

/// <summary>

/// preparestatement 쿼리 받아서 처리

/// </summary>

/// <param name="param">parameter</param>

/// <param name="selectProcedureName">sp명 또는 preparestatement 쿼리</param>

/// <param name="commType">command Type</param>

/// <returns></returns>

public DataSet ExecuteDataSet(IDictionary<string, string> param, string selectProcedureName, CommandType commType = CommandType.StoredProcedure)

{

DataSet ds = new DataSet();

OracleCommand comm = GetCommand(selectProcedureName, commType);

GetParams(comm, param);

OracleDataAdapter adapter = new OracleDataAdapter();

adapter.SelectCommand = comm;

comm.CommandTimeout = 1000 * 1800;

try

{

adapter.Fill(ds);

}

catch (OracleException x)

{

WriteError(x.Message, selectProcedureName);

throw new Exception(string.Format("Error!!\n{0}\n{1}", x.Message, selectProcedureName));

}

finally

{

DisposeComm(comm, adapter);

}

return ds;

}

/// <summary>

/// insert, update, delete 사용 함수

/// </summary>

/// <param name="param">parameter</param>

/// <param name="selectProcedureName">sp명 또는 preparestatement 쿼리</param>

/// <param name="commType">command Type</param>

/// <returns></returns>

public int ExecuteCommand(IDictionary<string, string> param, string commStr, CommandType commType = CommandType.StoredProcedure)

{

OracleCommand comm = GetCommand(commStr, commType);

GetParams(comm, param);

int count = 0;

try

{

comm.Connection.Open();

count = comm.ExecuteNonQuery();

}

catch (Exception x)

{

WriteError(x.Message, commStr);

throw new Exception(string.Format("Error!!\n{0}\n{1}", x.Message, commStr));

}

finally

{

DisposeComm(comm);

}

return count;

}

private void GetParams(OracleCommand comm, IDictionary<string, string> _param)

{

comm.BindByName = true;

OracleParameterCollection paramColl = comm.Parameters;

if (_param == null)

{

_param = new Dictionary<string, string>();

}

IEnumerator<KeyValuePair<string, string>> keys = _param.GetEnumerator();

while (keys.MoveNext())

{

if (keys.Current.Value != null)

{

paramColl.Add(new OracleParameter(keys.Current.Key, keys.Current.Value));

}

}

// 리턴값을 받기 위해 파라미터 추가

//comm.Parameters.Add("@nReturn", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue;

}

private OracleCommand GetCommand(string commStr)

{

OracleCommand comm = new OracleCommand();

comm.CommandText = commStr;

comm.CommandType = CommandType.StoredProcedure;

comm.Connection = new OracleConnection(_conStr);

return comm;

}

private OracleCommand GetCommand(string commStr, CommandType commandType)

{

OracleCommand comm = GetCommand(commStr);

comm.CommandType = commandType;

return comm;

}

#region Method finally

private void DisposeAdapter(OracleDataAdapter adapter)

{

if (adapter != null)

{

adapter.Dispose();

}

adapter = null;

}

private void DisposeComm(OracleCommand comm)

{

if (comm != null)

{

if (comm.Connection != null)

{

if (comm.Connection.State == ConnectionState.Open)

{

comm.Connection.Close();

}

comm.Connection.Dispose();

}

comm.Dispose();

}

comm = null;

}

private void DisposeComm(OracleCommand comm, OracleDataAdapter adapter)

{

DisposeComm(comm);

DisposeAdapter(adapter);

}

#endregion

#region 에러 출력

private void WriteError(string msg, string commandText = null)

{

StackTrace s = new StackTrace();

string errorStr = string.Format(

@"

[Error]

Time : {0}

Location : {1}

Message : {2}

Command : {3}

", DateTime.Now.ToString("HH:mm:ss"), s.GetFrame(1).GetMethod().Name, msg, commandText);

Console.WriteLine(errorStr);

}

#endregion

}

오라클 사용 클래스 Oracle Database handler