using Oracle.ManagedDataAccess.Client;using Oracle.ManagedDataAccess.Types;using System;using System.Collections.Generic;using System.Data;using System.Diagnostics;using System.Linq;using System.Text;using System.Threading.Tasks; namespace DB{&nbsp; &nbsp; public class Oracle : IDisposable&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; /* 참고 페이지&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;* http://www.sysnet.pe.kr/Default.aspx?mode=2&amp;sub=0&amp;detail=1&amp;pageno=0&amp;wid=10928&amp;rssMode=1&amp;wtype=0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;*/ &nbsp; &nbsp; &nbsp; &nbsp; public void Dispose()&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GC.SuppressFinalize(this);&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; private static string _conStr = string.Empty;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; public Oracle() { }&nbsp; &nbsp; &nbsp; &nbsp; public Oracle(string conStr) : this() { _conStr = conStr; }&nbsp; &nbsp; &nbsp; &nbsp; public Oracle(string ip, string port, string sid, string id, string pw)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _conStr = string.Format(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , ip, port, sid, id, pw);&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; /// &lt;summary&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// adhoc 쿼리 받아서 처리&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;/summary&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;param name="selectQuery"&gt;select 쿼리&lt;/param&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;returns&gt;&lt;/returns&gt;&nbsp; &nbsp; &nbsp; &nbsp; public DataSet ExecuteDataSet(string selectQuery)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DataSet ds = new DataSet();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OracleDataAdapter adapter = new OracleDataAdapter(selectQuery, _conStr);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; adapter.Fill(ds);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; catch (OracleException x)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WriteError(x.Message, selectQuery);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; throw new Exception(string.Format("Error!!\n{0}\n{1}", x.Message, selectQuery));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; finally&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DisposeAdapter(adapter);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return ds;&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; /// &lt;summary&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// preparestatement 쿼리 받아서 처리&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;/summary&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;param name="param"&gt;parameter&lt;/param&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;param name="selectProcedureName"&gt;sp명 또는 preparestatement 쿼리&lt;/param&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;param name="commType"&gt;command Type&lt;/param&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;returns&gt;&lt;/returns&gt;&nbsp; &nbsp; &nbsp; &nbsp; public DataSet ExecuteDataSet(IDictionary&lt;string, string&gt; param, string selectProcedureName, CommandType commType = CommandType.StoredProcedure)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DataSet ds = new DataSet(); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OracleCommand comm = GetCommand(selectProcedureName, commType);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GetParams(comm, param);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OracleDataAdapter adapter = new OracleDataAdapter();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; adapter.SelectCommand = comm;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.CommandTimeout = 1000 * 1800; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; adapter.Fill(ds);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; catch (OracleException x)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WriteError(x.Message, selectProcedureName);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; throw new Exception(string.Format("Error!!\n{0}\n{1}", x.Message, selectProcedureName));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; finally&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DisposeComm(comm, adapter);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return ds;&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; /// &lt;summary&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// insert, update, delete 사용 함수&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;/summary&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;param name="param"&gt;parameter&lt;/param&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;param name="selectProcedureName"&gt;sp명 또는 preparestatement 쿼리&lt;/param&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;param name="commType"&gt;command Type&lt;/param&gt;&nbsp; &nbsp; &nbsp; &nbsp; /// &lt;returns&gt;&lt;/returns&gt;&nbsp; &nbsp; &nbsp; &nbsp; public int ExecuteCommand(IDictionary&lt;string, string&gt; param, string commStr, CommandType commType = CommandType.StoredProcedure)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OracleCommand comm = GetCommand(commStr, commType);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GetParams(comm, param);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int count = 0;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.Connection.Open();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; count = comm.ExecuteNonQuery();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; catch (Exception x)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WriteError(x.Message, commStr);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; throw new Exception(string.Format("Error!!\n{0}\n{1}", x.Message, commStr));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; finally&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DisposeComm(comm);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return count;&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; private void GetParams(OracleCommand comm, IDictionary&lt;string, string&gt; _param)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.BindByName = true;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OracleParameterCollection paramColl = comm.Parameters;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (_param == null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _param = new Dictionary&lt;string, string&gt;();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IEnumerator&lt;KeyValuePair&lt;string, string&gt;&gt; keys = _param.GetEnumerator();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while (keys.MoveNext())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (keys.Current.Value != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; paramColl.Add(new OracleParameter(keys.Current.Key, keys.Current.Value));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // 리턴값을 받기 위해 파라미터 추가&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //comm.Parameters.Add("@nReturn", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue;&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; private OracleCommand GetCommand(string commStr)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OracleCommand comm = new OracleCommand();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.CommandText = commStr;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.CommandType = CommandType.StoredProcedure;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.Connection = new OracleConnection(_conStr);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return comm;&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; private OracleCommand GetCommand(string commStr, CommandType commandType)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OracleCommand comm = GetCommand(commStr);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.CommandType = commandType;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return comm;&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; #region Method finally&nbsp; &nbsp; &nbsp; &nbsp; private void DisposeAdapter(OracleDataAdapter adapter)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (adapter != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; adapter.Dispose();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; adapter = null;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; private void DisposeComm(OracleCommand comm)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (comm != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (comm.Connection != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (comm.Connection.State == ConnectionState.Open)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.Connection.Close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.Connection.Dispose();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm.Dispose();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; comm = null;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; private void DisposeComm(OracleCommand comm, OracleDataAdapter adapter)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DisposeComm(comm); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DisposeAdapter(adapter);&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; #endregion &nbsp; &nbsp; &nbsp; &nbsp; #region 에러 출력 &nbsp; &nbsp; &nbsp; &nbsp; private void WriteError(string msg, string commandText = null)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StackTrace s = new StackTrace();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string errorStr = string.Format(&nbsp;@"[Error]&nbsp;Time : {0}Location : {1}Message : {2}Command : {3}", DateTime.Now.ToString("HH:mm:ss"), s.GetFrame(1).GetMethod().Name, msg, commandText);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Console.WriteLine(errorStr);&nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; #endregion &nbsp; &nbsp; }}<div> </div>

C#에서 오라클 사용하기 Oracle Handle