基於 ODP.NET 的 Oracle 資料存取基類完整實作

編輯：關於C語言

設計目標

封裝 Oracle 資料庫常用操作 (存儲過程、SQL 語句、DataReader、DataSet)，提供統一的連線管理、參數處理與資源釋放機制，作為資料存取層 (DAL) 的基礎類別，減少重複程式碼並強化可維護性。

完整原始碼 (已整理優化)

using System;
using System.Configuration;
using System.Data;
using Oracle.DataAccess.Client;

namespace WIS.Base.Data
{
    /// 
    /// Oracle 資料存取基類，封裝連線、命令建構、執行邏輯
    /// 
    public class DbObject : IDisposable
    {
        #region 成員變數
        protected OracleConnection Connection { get; private set; }
        private readonly string _connectionString;
        private bool _disposed = false;
        #endregion

        #region 建構函式
        /// 使用設定檔預設連線字串
        public DbObject()
        {
            _connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            Connection = new OracleConnection(_connectionString);
        }

        /// 指定自訂連線字串
        public DbObject(string connectionString)
        {
            _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
            Connection = new OracleConnection(_connectionString);
        }
        #endregion

        #region 私有輔助方法
        /// 建構 OracleCommand (存儲過程)
        private OracleCommand BuildCommand(string storedProcName, IDataParameter[] parameters)
        {
            var cmd = new OracleCommand(storedProcName, Connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            
            if (parameters != null)
                foreach (var p in parameters)
                    cmd.Parameters.Add(p);
            
            return cmd;
        }
        #endregion

        #region 執行存儲過程
        /// 執行存儲過程，回傳影響列數與 ReturnValue
        public object RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            EnsureConnectionOpen();
            var cmd = BuildCommand(storedProcName, parameters);
            rowsAffected = cmd.ExecuteNonQuery();
            
            // 尋找 ReturnValue 參數
            var returnParam = Array.Find(parameters, p => p.Direction == ParameterDirection.ReturnValue);
            return returnParam?.Value;
        }

        /// 執行存儲過程，回傳 OracleDataReader (呼叫端負責關閉)
        public OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            EnsureConnectionOpen();
            var cmd = BuildCommand(storedProcName, parameters);
            // CommandBehavior.CloseConnection: 讀取器關閉時自動關閉連線
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// 執行存儲過程，填充 DataSet (指定表名)
        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            var ds = new DataSet();
            FillDataSet(storedProcName, parameters, ds, tableName);
            return ds;
        }

        /// 執行存儲過程，填充既有 DataSet
        public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName)
        {
            FillDataSet(storedProcName, parameters, dataSet, tableName);
        }

        private void FillDataSet(string storedProcName, IDataParameter[] parameters, DataSet ds, string tableName)
        {
            EnsureConnectionOpen();
            using (var adapter = new OracleDataAdapter(BuildCommand(storedProcName, parameters)))
            {
                adapter.Fill(ds, tableName);
            }
            // 注意：使用 DataAdapter 會自動開啟/關閉連線，這裡不額外 Close
        }
        #endregion

        #region 執行 SQL 語句
        public int ExecNonQuery(string sql)
        {
            EnsureConnectionOpen();
            using (var cmd = new OracleCommand(sql, Connection))
            {
                return cmd.ExecuteNonQuery();
            }
        }

        public OracleDataReader ExecSqlString(string sql)
        {
            EnsureConnectionOpen();
            var cmd = new OracleCommand(sql, Connection);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public DataSet ExecSqlString(string sql, string tableName)
        {
            var ds = new DataSet();
            FillDataSetFromSql(sql, ds, tableName);
            return ds;
        }

        public void ExecSqlString(string sql, DataSet dataSet, string tableName)
        {
            FillDataSetFromSql(sql, dataSet, tableName);
        }

        private void FillDataSetFromSql(string sql, DataSet ds, string tableName)
        {
            EnsureConnectionOpen();
            using (var adapter = new OracleDataAdapter(sql, Connection))
            {
                adapter.Fill(ds, tableName);
            }
        }

        public object ExecScalar(string sql)
        {
            EnsureConnectionOpen();
            using (var cmd = new OracleCommand(sql, Connection))
            {
                return cmd.ExecuteScalar();
            }
        }
        #endregion

        #region 連線管理與資源釋放
        private void EnsureConnectionOpen()
        {
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
        }

        public void Close()
        {
            if (Connection.State == ConnectionState.Open)
                Connection.Close();
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    Connection?.Close();
                    Connection?.Dispose();
                }
                _disposed = true;
            }
        }

        ~DbObject()
        {
            Dispose(false);
        }
        #endregion
    }
}

關鍵設計亮點解析

1. 連線狀態智能管理

EnsureConnectionOpen() 統一檢查並開啟連線，避免重複 Open() 擲出例外
DataReader 使用 CommandBehavior.CloseConnection，讀取完畢自動關閉連線
DataAdapter 內部自動管理連線開關，無需手動干預

2. 參數陣列靈活處理

接受 IDataParameter[] 介面，支援 OracleParameter 等具體實作
自動尋找 ReturnValue 方向參數，簡化存儲過程回傳值取得

3. 完整的 Dispose Pattern 實作

實作 IDisposable，支援 using 區塊確定性釋放
雙重檢查鎖定模式 (_disposed flag) 防止重複釋放
終結子 (~DbObject) 作為安全網，但不建議依賴 GC

4. 方法多載滿足不同場景

方法簽章	適用場景
RunProcedure(..., out rowsAffected)	INSERT/UPDATE/DELETE，需知道影響列數
RunProcedure(...) : OracleDataReader	大量資料流式讀取，記憶體效率佳
RunProcedure(..., tableName) : DataSet	中小資料量，需離線操作/綁定 UI
RunProcedure(..., dataSet, tableName)	合併多查詢結果至單一 DataSet

使用範例

// 1. 執行存儲過程新增資料
using (var db = new DbObject())
{
    var params = new OracleParameter[]
    {
        new OracleParameter("p_id", OracleDbType.Int32, 101, ParameterDirection.Input),
        new OracleParameter("p_name", OracleDbType.Varchar2, "測試商品", ParameterDirection.Input),
        new OracleParameter("p_result", OracleDbType.Int32, ParameterDirection.ReturnValue)
    };
    
    int rows;
    var result = db.RunProcedure("PKG_PRODUCT.ADD_PRODUCT", params, out rows);
    Console.WriteLine($"影響列數: {rows}, 回傳代碼: {result}");
}

// 2. 查詢資料綁定 GridView
using (var db = new DbObject())
{
    DataSet ds = db.ExecSqlString("SELECT * FROM PRODUCTS WHERE STATUS = 1", "Products");
    gridView.DataSource = ds.Tables["Products"];
    gridView.DataBind();
}

// 3. 大量資料流式處理
using (var db = new DbObject())
using (var reader = db.ExecSqlString("SELECT * FROM BIG_TABLE"))
{
    while (reader.Read())
    {
        // 處理每一列，不將所有資料載入記憶體
        ProcessRow(reader);
    }
}