C#에서 Oracle 사용
C#에서 Oracle을 엑세스하기 위해서는 오라클에서 제공하는 Oracle Data Provider for .NET (ODP.NET)을 사용하거나 오래된 방식이지만 OLEDB나 ODBC 방식 등을 사용할 수 있다. (주: 예전에 마이크로소프트 .NET에서 제공했던 .NET Framework Data Provider for Oracle은 더 이상 사용되지 않고 있다. .NET Data Provider이나 ODP.NET은 실제 코딩에서 사용하는 방식은 비슷하기 때문에 기존에 .NET Data Provider를 사용했던 부분은 쉽게 ODP.NET으로 마이그레이션할 수 있다)
여기서는 Oracle 데이타베이스에 ADO.NET 을 사용하도록 하는 Oracle Data Provider for .NET (ODP.NET)의 예제를 살펴 본다. 이 Oralce 드라이버를 사용하기 위해서는 ODAC (Oracle Data Access Components) 을 설치하면 된다. ODAC을 설치한 후에, Visual Studio에서 아래 그림처럼 ODP.NET 드라이버를 참조 추가한다. ODP.NET은 크게 Managed Driver와 Unmanaged Driver를 제공하는데, Oracle.ManagedDataAccess는 Managed Driver이고, Oracle.DataAccess는 Unmanaged Driver이다. 또한, EntityFramework 기능을 사용하기 위해서는 Oracle.ManagedDataAccess.EntityFramework (Managed) 와 Oracle.DataAccess.EntityFramework (Unmanaged)을 사용할 수 있다. Managed Driver는 .NET Managed 코드로 만들어진 것을 의미하며, x86/x64에 관계없이 프로그램을 배포할 수 있는 장점이 있다.
여기서는 Oracle 데이타베이스에 ADO.NET 을 사용하도록 하는 Oracle Data Provider for .NET (ODP.NET)의 예제를 살펴 본다. 이 Oralce 드라이버를 사용하기 위해서는 ODAC (Oracle Data Access Components) 을 설치하면 된다. ODAC을 설치한 후에, Visual Studio에서 아래 그림처럼 ODP.NET 드라이버를 참조 추가한다. ODP.NET은 크게 Managed Driver와 Unmanaged Driver를 제공하는데, Oracle.ManagedDataAccess는 Managed Driver이고, Oracle.DataAccess는 Unmanaged Driver이다. 또한, EntityFramework 기능을 사용하기 위해서는 Oracle.ManagedDataAccess.EntityFramework (Managed) 와 Oracle.DataAccess.EntityFramework (Unmanaged)을 사용할 수 있다. Managed Driver는 .NET Managed 코드로 만들어진 것을 의미하며, x86/x64에 관계없이 프로그램을 배포할 수 있는 장점이 있다.
.NET Core용 ODP.NET
.NET Core에서 ODP.NET을 사용하기 위해서는 ODP.NET Core 패키지를 nuget에서 다운받아 설치하면 된다. ODP.NET Core는 ODAC과 동일한 클래스를 제공하므로 사용법은 동일하다.
PM> Install-Package Oracle.ManagedDataAccess.Core
Oracle 데이타베이스 연결
Oracle 데이타베이스를 연결하기 위해서는 OracleConnection 클래스를 사용한다. Connection 클래스를 생성할 때는 Connection String을 넣어 주어야 하는데, Oracle Connection String을 지정하는 방법은 여러가지 방식이 있다. 가장 기본적으로 Data Source, User Id, Password등을 지정해 줄 수 있는데, Data Source에는 tnsnames.ora에 지정된 net service name을 지정하고, User Id, Password에는 Oracle 사용자명과 암호를 지정한다. 아래 예제는 orcl 이라는 net service name에 해당하는 네트워크 연결 정보와 hr 사용자를 사용하여 오라클 데이타베이스에 접속하는 예이다. (주: tnsnames.ora에 net service name이 있더라도 tnsnames.ora 자체를 못 찾는 경우가 있는데, 이때는 TNS_ADMIN 환경변수를 설정해 본다) net service name를 사용하지 않고 직접 네트워크 연결정보를 넣어서 사용할 수도 있는데, 아래 예제의 4번째 라인은 직접 서버 연결정보를 넣는 방식을 예시한 것이다.
예제
// 오라클 연결 문자열
string strConn = "Data Source=orcl;User Id=hr;Password=hr;";
// 네트워크 연결 정보 직접 대입
//string s = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));User Id=hr;Password=hr;";
// 오라클 연결
OracleConnection conn = new OracleConnection(strConn);
conn.Open();
//...
conn.Close();
string strConn = "Data Source=orcl;User Id=hr;Password=hr;";
// 네트워크 연결 정보 직접 대입
//string s = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));User Id=hr;Password=hr;";
// 오라클 연결
OracleConnection conn = new OracleConnection(strConn);
conn.Open();
//...
conn.Close();
Oracle 데이타 INSERT, UPDATE, DELETE
데이타의 삽입, 삭제, 갱신등은 OracleCommand에 해당 SQL문을 지정하여 실행한다. 먼저 OracleConnection을 사용해 Oracle 서버 연결을 한 후, OracleCommand에 INSERT, UPDATE, DELETE 등의 SQL문을 지정하여 실행하면 된다. 아래는 한 ROW를 INSERT하는 예제이다.
예제
// .NET Framework Data Provider for Oracle
// 오라클 연결 문자열
private string _strConn = "Data Source=orcl;User Id=hr;Password=hr;";
// 데이타 쓰기
private void btnAdd_Click(object sender, EventArgs e)
{
// 오라클 연결
OracleConnection conn = new OracleConnection(_strConn);
conn.Open();
// 명령 객체 생성
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
// SQL문 지정 및 INSERT 실행
cmd.CommandText = "INSERT INTO REGIONS VALUES (5, 'Africa')";
cmd.ExecuteNonQuery();
conn.Close();
}
// 오라클 연결 문자열
private string _strConn = "Data Source=orcl;User Id=hr;Password=hr;";
// 데이타 쓰기
private void btnAdd_Click(object sender, EventArgs e)
{
// 오라클 연결
OracleConnection conn = new OracleConnection(_strConn);
conn.Open();
// 명령 객체 생성
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
// SQL문 지정 및 INSERT 실행
cmd.CommandText = "INSERT INTO REGIONS VALUES (5, 'Africa')";
cmd.ExecuteNonQuery();
conn.Close();
}
Oracle 데이타 읽기
Oracle에서 데이타를 가져오기 위해서는 OracleCommand와 OracleDataReader 혹은 OracleDataAdapter 클래스를 사용한다. OracleDataReader는 연결모드로 데이타를 서버에서 가져오게 되며, 클라이언트에서 루프를 돌며 한 ROW씩 가져오게 된다. OracleDataAdapter는 한꺼번에 클라이언트 메모리로 데이타를 가져온후 연결을 끊게 되는데, DataGridView와 같은 그리드 컨트롤에 바인딩할 때 유용하다. 아래 예제에서는 SELECT 문에 WHERE 절을 두고 id 파라미터를 바인딩하는 코드를 사용하였다. Oracle 파라미터 바인딩은 SQL문에서 :id 와 같이 파라미터명 앞에 콜론을 사용하고, OracleParameter 클래스에서는 콜론 없이 id 만 파라미터명으로 사용한다.
예제
// .NET Framework Data Provider for Oracle : 오라클 연결 문자열
private string _strConn = "Data Source=orcl;User Id=hr;Password=hr;";
// 데이타 읽기
private void btnGetData_Click(object sender, EventArgs e)
{
// 오라클 서버 연결 객체 생성
using (OracleConnection conn = new OracleConnection(_strConn))
{
// 연결
conn.Open();
// 명령 객체 생성
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
// 파라미터 바인딩
cmd.CommandText = "SELECT * FROM employees WHERE Id >= :id";
cmd.Parameters.Add(new OracleParameter("id", 1));
// 결과 리더 객체를 리턴
OracleDataReader rdr = cmd.ExecuteReader();
// 레코드 계속 가져와서 루핑
while (rdr.Read())
{
// 필드 데이타 읽기
string s = rdr["FIRST_NAME"] as string;
// 데이타를 리스트박스에 추가
listBox1.Items.Add(s);
}
// 사용후 닫음
rdr.Close();
}
}
private string _strConn = "Data Source=orcl;User Id=hr;Password=hr;";
// 데이타 읽기
private void btnGetData_Click(object sender, EventArgs e)
{
// 오라클 서버 연결 객체 생성
using (OracleConnection conn = new OracleConnection(_strConn))
{
// 연결
conn.Open();
// 명령 객체 생성
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
// 파라미터 바인딩
cmd.CommandText = "SELECT * FROM employees WHERE Id >= :id";
cmd.Parameters.Add(new OracleParameter("id", 1));
// 결과 리더 객체를 리턴
OracleDataReader rdr = cmd.ExecuteReader();
// 레코드 계속 가져와서 루핑
while (rdr.Read())
{
// 필드 데이타 읽기
string s = rdr["FIRST_NAME"] as string;
// 데이타를 리스트박스에 추가
listBox1.Items.Add(s);
}
// 사용후 닫음
rdr.Close();
}
}
Oracle Stored Procedure 호출
Oracle에서 Stored Procedure(SP)를 호출하기 위해서는 OracleCommand 객체의 CommandText에 SP명을 적고, CommandType을 StoredProcedure로 설정하면 된다. SP가 Rowset을 리턴하는 경우, 아래 예제와 같이 OracleDataReader 혹은 OracleDataAdapter을 사용하여 결과를 리턴받을 수 있다.
예제
string strConn = "Data Source=orcl;User Id=SCOTT;Password=TIGER;";
using (var conn = new OracleConnection(strConn))
{
conn.Open();
// SP 호출
var cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_GetEmps";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_DeptName", OracleDbType.Varchar2).Value = "ACCOUNTING";
var curs = new OracleParameter("p_Cursor", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add(curs);
// (1) OracleDataReader 사용
OracleDataReader rs = cmd.ExecuteReader();
while (rs.Read())
{
Console.WriteLine($"{rs[0]}, {rs[1]}, {rs[2]}");
}
// (2) OracleDataAdapter 사용
// DataSet ds = new DataSet();
// var da = new OracleDataAdapter(cmd);
// da.Fill(ds);
// foreach (DataRow r in ds.Tables[0].Rows)
// {
// Console.WriteLine($"{r[0]}, {r[1]}, {r[2]}");
// }
}
/* 관련 Oracle Procedure
CREATE OR REPLACE PROCEDURE sp_GetEmps
(
p_DeptName IN VARCHAR2,
p_Cursor OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN p_Cursor
FOR SELECT e.* FROM EMP e
JOIN DEPT d ON d.DEPTNO = e.DEPTNO
WHERE d.DNAME = p_DeptName;
END;
*/
using (var conn = new OracleConnection(strConn))
{
conn.Open();
// SP 호출
var cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_GetEmps";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_DeptName", OracleDbType.Varchar2).Value = "ACCOUNTING";
var curs = new OracleParameter("p_Cursor", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add(curs);
// (1) OracleDataReader 사용
OracleDataReader rs = cmd.ExecuteReader();
while (rs.Read())
{
Console.WriteLine($"{rs[0]}, {rs[1]}, {rs[2]}");
}
// (2) OracleDataAdapter 사용
// DataSet ds = new DataSet();
// var da = new OracleDataAdapter(cmd);
// da.Fill(ds);
// foreach (DataRow r in ds.Tables[0].Rows)
// {
// Console.WriteLine($"{r[0]}, {r[1]}, {r[2]}");
// }
}
/* 관련 Oracle Procedure
CREATE OR REPLACE PROCEDURE sp_GetEmps
(
p_DeptName IN VARCHAR2,
p_Cursor OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN p_Cursor
FOR SELECT e.* FROM EMP e
JOIN DEPT d ON d.DEPTNO = e.DEPTNO
WHERE d.DNAME = p_DeptName;
END;
*/