OraDatabase クラスでは、トランザクション実行中を除き、コネクションを取得して SQL を実行したらすぐに閉じます。

CreateDynaset メソッドでは、DataTable を作成して OraDynaset に渡しています。

OracleDataReader.GetSchemaTable でスキーマ情報が取得できます。

ProviderType 列に項目の型(OracleDbType)が入っており、OracleDbType.Decimal のときは、OracleDecimal を取得して文字列に変換します。

using System;
using System.Data;
using System.Runtime.InteropServices;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

namespace OracleInProcServerForVBS
{
    [ComVisible(true)]
    [Guid(OraDatabase.InterfaceId)]
    public interface IOraDatabase
    {
        OraDynasetClass CreateDynaset(string sqlStmt, int dynOption);
        int ExecuteSQL(string sqlStmt);
        void BeginTrans();
        void CommitTrans();
        void Rollback();
        void ResetTrans();
    }

    [ComVisible(true)]
    [Guid(OraDatabase.ClassId)]
    [ClassInterface(ClassInterfaceType.None)]
    public class OraDatabase : IOraDatabase, IDisposable
    {
        public const string ClassId = "E5745D70-86BE-43B5-BD22-78D96F0DADA3";
        public const string InterfaceId = "5B720FBE-6BEC-4560-A6A9-7AF8FFCC20CB";

        private string m_ConnectionString;
        private OracleConnection m_OracleConnection = null;
        private OracleTransaction m_Transaction = null;

        internal OraDatabase(string connectionString) {
            m_ConnectionString = connectionString;
        }

        private OracleConnection CreateConnection() {
            var con = new OracleConnection();
            con.ConnectionString = m_ConnectionString;
            con.Open();
            return con;
        }

        private OracleCommand CreateCommand(OracleConnection con, string sqlStmt) {
            var cmd = con.CreateCommand();
            cmd.CommandText = sqlStmt.Replace("\r\n", "\n");
            return cmd;
        }

        public OraDynasetClass CreateDynaset(string sqlStmt, int dynOption) {
            if (m_OracleConnection == null) {
                using (var con = CreateConnection()) {
                    var dyn = CreateDynasetInternal(con, sqlStmt, dynOption);
                    return dyn;
                }
            } else {
                return CreateDynasetInternal(m_OracleConnection, sqlStmt, dynOption);
            }
        }

        private OraDynasetClass CreateDynasetInternal(OracleConnection con, string sqlStmt, int dynOption) {
            using (var cmd = CreateCommand(con, sqlStmt))
            using (var reader = cmd.ExecuteReader()) {
                return new OraDynasetClass(CreateDataTable(reader));
            }
        }

        private DataTable CreateDataTable(OracleDataReader reader) {
            var dt = new DataTable();

            var schemaTable = reader.GetSchemaTable();
            int fieldCount = reader.FieldCount;
            var dbTypes = new OracleDbType[fieldCount];

            dt.BeginInit();
            for (int i = 0; i < fieldCount; i++) {
                var sc = schemaTable.Rows[i];
                var columnName = (string)sc["ColumnName"];
                dbTypes[i] = (OracleDbType)(int)sc["ProviderType"];
                var dc = new DataColumn(columnName, typeof(object));
                dt.Columns.Add(dc);
            }
            dt.EndInit();

            dt.BeginLoadData();
            while (reader.Read()) {
                var row = dt.NewRow();
                for (int i = 0; i < fieldCount; i++) {
                    if (reader.IsDBNull(i)) {
                        row[i] = DBNull.Value;
                    } else {
                        switch (dbTypes[i]) {
                            case OracleDbType.Decimal:
                                var dec = reader.GetOracleDecimal(i);
                                OracleDecimal.SetPrecision(dec, 28);
                                row[i] = dec.ToString();
                                break;
                            default:
                                row[i] = reader.GetValue(i);
                                break;
                        }
                    }
                }
                dt.Rows.Add(row);
            }
            dt.EndLoadData();

            dt.AcceptChanges();
            return dt;
        }

        public int ExecuteSQL(string sqlStmt) {
            if (m_OracleConnection == null) {
                using (var con = CreateConnection()) {
                    return ExecuteSQLInternal(con, sqlStmt);
                }
            } else {
                return ExecuteSQLInternal(m_OracleConnection, sqlStmt);
            }
        }

        private int ExecuteSQLInternal(OracleConnection con, string sqlStmt) {
            using (OracleCommand cmd = con.CreateCommand()) {
                cmd.CommandText = sqlStmt.Replace("\r\n", "\n");
                return cmd.ExecuteNonQuery();
            }
        }

        public void BeginTrans() {
            if (m_OracleConnection == null) {
                m_OracleConnection = CreateConnection();
            }
            m_Transaction = m_OracleConnection.BeginTransaction();
        }

        public void CommitTrans() {
            m_Transaction.Commit();
            ResetTrans();
        }

        public void Rollback() {
            m_Transaction.Rollback();
            ResetTrans();
        }

        public void ResetTrans() {
            if (m_Transaction != null) {
                m_Transaction.Dispose();
                m_Transaction = null;
            }
            if (m_OracleConnection != null) {
                m_OracleConnection.Dispose();
                m_OracleConnection = null;
            }
        }

        public void Dispose() {
            ResetTrans();
        }
    }
}



トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   最終更新のRSS
Last-modified: 2020-04-14 (火) 16:06:17 (42d)