[C#/DAPPER] SYS_REFCURSOR 출력 타입의 입력 매개 변수를 갖는 오라클 저장 프로시저 호출하기

TestProject.zip

다운로드

▶ 저장 프로시저

CREATE OR REPLACE PROCEDURE SCOTT.GETMEMBERLIST
(
    I_NAME   IN  VARCHAR2,
    O_CURSOR OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN O_CURSOR FOR
        SELECT *
        FROM   SCOTT.EMP
        WHERE  ENAME LIKE I_NAME || '%';
END;

※ 오라클 11G의 경우 설치하면 SCOTT 계정이 설치되어 있다.

 

728x90

 

▶ OracleDynamicParameter.cs

using System.Collections.Generic;
using System.Data;

using Dapper;

using Oracle.ManagedDataAccess.Client;

namespace TestProject
{
    /// <summary>
    /// 오라클 동적 매개 변수 목록
    /// </summary>
    public class OracleDynamicParameters : SqlMapper.IDynamicParameters
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 동적 매개 변수 목록
        /// </summary>
        private readonly DynamicParameters dynamicParameters = new DynamicParameters();

        /// <summary>
        /// 오라클 매개 변수 리스트
        /// </summary>
        private readonly List<OracleParameter> oracleParameterList = new List<OracleParameter>();

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 추가하기 - Add(name, value, dbType, direction, size)

        /// <summary>
        /// 추가하기
        /// </summary>
        /// <param name="name">명칭</param>
        /// <param name="value">값</param>
        /// <param name="dbType">DB 타입</param>
        /// <param name="direction">방향</param>
        /// <param name="size">크기</param>
        public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)
        {
            this.dynamicParameters.Add(name, value, dbType, direction, size);
        }

        #endregion
        #region 추가하기 - Add(name, oracleDbType, direction)

        /// <summary>
        /// 추가하기
        /// </summary>
        /// <param name="name">명칭</param>
        /// <param name="oracleDbType">오라클 DB 타입</param>
        /// <param name="direction">방향</param>
        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
        {
            OracleParameter parameter = new OracleParameter(name, oracleDbType, direction);

            this.oracleParameterList.Add(parameter);
        }

        #endregion

        #region (SqlMapper.IDynamicParameters) 매개 변수 목록 추가하기 - AddParameters(command, identity)

        /// <summary>
        /// 매개 변수 목록 추가하기
        /// </summary>
        /// <param name="command">명령</param>
        /// <param name="identity">식별자</param>
        public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            ((SqlMapper.IDynamicParameters)this.dynamicParameters).AddParameters(command, identity);

            OracleCommand oracleCommand = command as OracleCommand;

            if(oracleCommand != null)
            {
                oracleCommand.Parameters.AddRange(this.oracleParameterList.ToArray());
            }
        }

        #endregion
    }
}

 

300x250

 

▶ Program.cs

using System;
using System.Data;
using System.Linq;

using Oracle.ManagedDataAccess.Client;

using Dapper;

namespace TestProject
{
    /// <summary>
    /// 프로그램
    /// </summary>
    class Program
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

        #region 프로그램 시작하기 - Main()

        /// <summary>
        /// 프로그램 시작하기
        /// </summary>
        private static void Main()
        {
            string connectionString = "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = orcl)));User ID=SYSTEM;Password=P@ssw0rd";

            using(OracleConnection connection = new OracleConnection(connectionString))
            {
                OracleDynamicParameters parameters = new OracleDynamicParameters();

                parameters.Add("I_NAME"  , "S");
                parameters.Add("O_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

                var resultList = connection.Query<dynamic>
                (
                    "SCOTT.GETMEMBERLIST",
                    param : parameters,
                    commandType : CommandType.StoredProcedure
                )
                .ToList();

                foreach(var item in resultList)
                {
                    Console.WriteLine(item.ENAME);
                }
            }
        }

        #endregion
    }
}