[C#] OracleConnection Example (오라클 연결 예제) - 범상치 않은 블로그

C# Oracle Connection Example

마이크로소프트는 서드파티 연동을 권장하기에 Obsolete 처리하였습니다. 순서대로 최초에는 오라클 데이터베이스에 연결을 해볼 것이며, 커맨드 텍스트를 생성하고, 셀렉트를 해보도록 하겠습니다.

[Obsolete]
private void BeomSang()
{
    //Connect
    OracleConnection con = new OracleConnection("User Id=userName;Password=password;Data Source=DataSourceName");
    con.Open();

    //Create Command Text
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "SELECT * FROM EMP";

    //Execute Reader
    OracleDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
        Debug.WriteLine(reader["ENAME"]);

    //Dispose
    reader.Dispose();
    cmd.Dispose();
    con.Dispose();
}

Connect

//Connect
OracleConnection con = new OracleConnection("User Id=userName;Password=password;Data Source=DataSourceName");
con.Open();

Connection String 에 간단히 User Id 와 Password 그리고 Data Source 를 입력하였습니다.

더 필요한 매개변수는 오라클 홈페이지를 참조해 주십시오.

아래는 11g 기준 매개변수목록입니다.

Connection String Attribute	Description	Default Value
Connection Lifetime	Minimum life time (in seconds) of the connection.	0
Connection Timeout	Minimum time (in seconds) to wait for a free connection from the pool.	15
Context Connection	Returns an implicit database connection if set to true. Supported in a .NET stored procedure only	false
Data Source	Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect.	empty string
DBA Privilege	Administrative privileges: SYSDBA or SYSOPER.	empty string
Decr Pool Size	Number of connections that are closed when an excessive amount of established connections are unused.	1
Enlist	Controls the enlistment behavior and capabilities of a connection in context of COM+ transactions or System.Transactions.	true
HA Events	Enables ODP.NET connection pool to proactively remove connections from the pool when an Oracle database service, service member, instance, or node goes down. Works with Oracle Global Data Services, including Oracle RAC, Data Guard, GoldenGate, and some single instance deployments.	false
Load Balancing	Enables ODP.NET connection pool to balance work requests across Oracle database instances based on the load balancing advisory and service goal. Works with Oracle Global Data Services, including Oracle RAC, Active Data Guard, and GoldenGate.	false
Incr Pool Size	Number of new connections to be created when all connections in the pool are in use.	5
Max Pool Size	Maximum number of connections in a pool.	100
Metadata Pooling	Caches metadata information.	True
Min Pool Size	Minimum number of connections in a pool.	1
Password	Password for the user specified by User Id.	empty string
Persist Security Info	Retrieval of the password in the connection string.	false
Pooling	Connection pooling.	true
Promotable Transaction	Indicates whether or not a transaction is local or distributed throughout its lifetime.	promotable
Proxy User Id	User name of the proxy user.	empty string
Proxy Password	Password of the proxy user.	empty string
Self Tuning	Enables or disables self-tuning for a connection.	true
Statement Cache Purge	Statement cache purged when the connection goes back to the pool.	false
Statement Cache Size	Statement cache enabled and cache size, that is, the maximum number of statements that can be cached.	0
User Id	Oracle user name.	empty string
Validate Connection	Validation of connections coming from the pool.	false

Create Command Text

//Create Command Text
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM EMP";

Execute Reader (Select)

//Execute Reader
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
    Debug.WriteLine(reader["ENAME"]);

{EMP} 테이블의 [ENAME] 컬럼 조회 예시입니다.

{EMP} 테이블 명세입니다.

Dispose

//Dispose
reader.Dispose();
cmd.Dispose();
con.Dispose();