[c#] 오라클(Oracle) 연동 Helper

1. [c#] 오라클(Oracle) 연동 Helper

- C# 으로 개발을 위해서 가장 많이 사용하면서 꼭필요하신게 연동입니다.

- 가장 많이 사용하기도 하지만 처음 한번만 셋팅해두면 신경않쓰고 호출해서 사용하면 편하기때문에 많이들 가지고 있을거라고 생각됩니다.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OracleClient;
using static DevExpress.XtraEditors.Mask.MaskSettings;

namespace factory.Database
{
    public static class DBHelper
    {
        private static OracleConnection dbConn = null;
        private static string dbConnString;

        public static void Init()
        {
            dbConnString =
                $"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={전역변수.gv_ora_svr})(PORT={전역변수.gv_ora_port})))" +
                $"(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={전역변수.gv_ora_tns})));" +
                $"User Id={전역변수.gv_ora_user};Password={전역변수.gv_ora_pass};";
        }

        public static void Init(string hostName,
                                string user,
                                string pass,
                                string dbase,
                                string port,
                                bool perfCounter = false)
        {
            dbConnString =
                $"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={hostName})(PORT={port})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={dbase})));User Id={user};Password={pass};";
        }

        public static DataTable executeProcedure(string PROC_NAME, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                DataTable a = new DataTable();
                List<OracleParameter> filters = new List<OracleParameter>();

                string query = "EXEC " + PROC_NAME;

                bool first = true;
                for (int i = 0; i < parameters.Length; i += 2)
                {
                    filters.Add(new OracleParameter(parameters[i] as string, parameters[i + 1]));
                    query += (first ? " " : ", ") + ((string)parameters[i]);
                    first = false;
                }

                return Query(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static int nonProcedure(String query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                List<OracleParameter> filters = new List<OracleParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new OracleParameter(parameters[i] as string, parameters[i + 1]));

                using (dbConn = new OracleConnection(dbConnString))
                {
                    OracleCommand command = new OracleCommand(query, dbConn);

                    try
                    {
                        dbConn.Open();
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(filters.ToArray());
                        return command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                        throw ex;
                    }
                    finally
                    {
                        if (dbConn != null)
                            dbConn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static DataTable executeQuery(string query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                List<OracleParameter> filters = new List<OracleParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new OracleParameter(parameters[i] as string, parameters[i + 1]));

                return Query(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static string executeQuery1(string query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                List<OracleParameter> filters = new List<OracleParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new OracleParameter(parameters[i] as string, parameters[i + 1]));

                return Query1(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static int executeNonQuery(string query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                List<OracleParameter> filters = new List<OracleParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new OracleParameter(parameters[i] as string, parameters[i + 1]));

                return NonQuery(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static object executeScalar(string query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to query. Expected an even number.");
                List<OracleParameter> filters = new List<OracleParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new OracleParameter(parameters[i] as string, parameters[i + 1]));

                return Scalar(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        #region Private Methods
        private static DataTable Query(String query, IList<OracleParameter> parametros)
        {
            DataTable data = new DataTable();

            using (dbConn = new OracleConnection(dbConnString))
            {
                OracleCommand command = new OracleCommand(query, dbConn);

                try
                {
                    dbConn.Open();
                    command.Parameters.AddRange(parametros.ToArray());

                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        data.Load(reader);
                    }

                    return data;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    throw ex;
                }
                finally
                {
                    if (dbConn != null)
                        dbConn.Close();
                }
            }
        }

        private static string Query1(String query, IList<OracleParameter> parametros)
        {
            string rtnData = "";

            using (dbConn = new OracleConnection(dbConnString))
            {
                OracleCommand command = new OracleCommand(query, dbConn);

                try
                {
                    dbConn.Open();
                    command.Parameters.AddRange(parametros.ToArray());

                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        reader.Read();
                        rtnData = reader.GetString(0);
                    }

                    return rtnData;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    throw ex;
                }
                finally
                {
                    if (dbConn != null)
                        dbConn.Close();
                }
            }
        }

        private static int NonQuery(string query, IList<OracleParameter> parametros)
        {
            using (dbConn = new OracleConnection(dbConnString))
            {
                OracleCommand command = new OracleCommand(query, dbConn);

                try
                {
                    dbConn.Open();
                    command.Parameters.AddRange(parametros.ToArray());
                    return command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    throw ex;
                }
                finally
                {
                    if (dbConn != null)
                        dbConn.Close();
                }
            }
        }

        private static object Scalar(string query, List<OracleParameter> parametros)
        {
            using (dbConn = new OracleConnection(dbConnString))
            {
                OracleCommand command = new OracleCommand(query, dbConn);

                try
                {
                    dbConn.Open();
                    command.Parameters.AddRange(parametros.ToArray());
                    return command.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    throw ex;
                }
                finally
                {
                    if (dbConn != null)
                        dbConn.Close();
                }
            }
        }
        #endregion
    }
}

 

2. 호출방법

- 선언

using factory.Database;

 

- 호출

    string query = "select 컬럼1, 컬럼2, 컬럼3 from 테이블" +

              " where 컬럼1 = :ag_code and 컬럼2 = :ag_status";
    object[] param = { "ag_code", 값1, "ag_status", 값2 };

 

    DBHelper.Init();
    DataTable retData = DBHelper.executeQuery(query, param);

 

3. 파일첨부

DBHelper.cs

0.01MB