方針
- コンストラクタで接続（デストラクタで切断）
- プロバイダファクトリクラス（DbProviderFactory）を利用する。
- Oracle 固有クラスについてはリフレクションを使用する。
- Oracle 固有クラスを利用する場合は独立した関数を作成する。
- パラメータの「名前によるバインド」（BindByName）をデフォルトにする。
コード
ODP.NET管理対象外ドライバ前提です。ODP.NET管理対象ドライバを使用する場合は、Oracle.DataAccess.Client の部分を Oracle.ManagedDataAccess に書き換えてください。（未テスト）
ODP.NET管理対象ドライバについてはこちらに良いまとめがあります。
ODP.NET Managed Driver(管理対象ドライバ)についての私的まとめ - しばたテックブログ
ODP.NET Managed Driverについて自分の欲しい形でまとまった資料がなかったのでここでまとめておきます。 ODP.NET Managed Driver(管理対象ドライバ)とは ODP.NET Managed Driver(管理対象ドライバ)とはOracle 12cから追加された新しい形のODP.NETで…
VB.NET
Imports System
Imports System.Data
Imports System.Data.Common
Public Class DbFacade
Implements IDisposable
Private _factory As DbProviderFactory
Private _cnn As DbConnection
Private _cmd As DbCommand
Private _txn As DbTransaction
Public Sub New()
Me.New("Data Source=ExampleDb;User ID=SCOTT;Password=TIGER")
End Sub
Public Sub New(ByVal connectionString As String)
_factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client")
DbConnect(connectionString)
End Sub
Public Function DbConnect(ByVal connectionString As String) As DbConnection
_cnn = _factory.CreateConnection()
Me.ConnectionString = connectionString
Return _cnn
End Function
Public Property ConnectionString() As String
Get
Return _cnn.ConnectionString
End Get
Set(ByVal value As String)
If _cnn.ConnectionString <> value Then
_cnn.ConnectionString = value
End If
End Set
End Property
Public Sub Open()
If _cnn.State = ConnectionState.Closed Then _cnn.Open()
End Sub
Public Sub Close()
ClearParams()
If _cnn.State = ConnectionState.Open Then _cnn.Close()
End Sub
Private Sub SetBindByName(ByVal cmd As DbCommand, ByVal isName As Boolean)
cmd.GetType().GetProperty("BindByName").SetValue(cmd, isName, Nothing)
End Sub
Public Function CreateCommand() As DbCommand
Dim cmd As DbCommand = _factory.CreateCommand()
cmd.Connection = _cnn
SetBindByName(cmd, True)
Return cmd
End Function
Public Function CreateCommand(ByVal sql As String) As DbCommand
Dim cmd As DbCommand = CreateCommand()
cmd.CommandText = sql
Return cmd
End Function
Public Function CreateDataAdapter() As DbDataAdapter
Return _factory.CreateDataAdapter()
End Function
Private Sub AddParam(ByVal cmd As DbCommand, ByVal param As DbParameter)
If cmd.Parameters.Contains(cmd.Parameters(param.ParameterName)) Then
cmd.Parameters.Remove(cmd.Parameters(param.ParameterName))
End If
cmd.Parameters.Add(param)
End Sub
Public Function AddParam(ByVal name As String, ByVal value As Object, ByVal type As DbType) As IDataParameter
If value.GetType().IsArray Then
Return AddParamPLSQLAssociativeArray(name, value, type)
End If
If _cmd Is Nothing Then _cmd = CreateCommand()
Dim param As DbParameter = _cmd.CreateParameter
param.ParameterName = name
param.DbType = type
param.Value = value
AddParam(_cmd, param)
Return param
End Function
Public Function AddParam(ByVal name As String, ByVal type As DbType, ByVal direction As ParameterDirection, ByVal size As Integer) As IDataParameter
If _cmd Is Nothing Then _cmd = CreateCommand()
Dim param As DbParameter = _cmd.CreateParameter()
param.ParameterName = name
param.DbType = type
param.Direction = direction
param.Size = size
AddParam(_cmd, param)
Return param
End Function
Public Function AddParam(ByVal cmd As DbCommand, ByVal name As String, ByVal sourceColumn As String, ByVal type As DbType) As IDataParameter
Dim param As DbParameter = cmd.CreateParameter()
param.ParameterName = name
param.SourceColumn = sourceColumn
param.DbType = type
AddParam(cmd, param)
Return param
End Function
Public Function AddParamRefCursor(ByVal name As String) As IDataParameter
If _cmd Is Nothing Then _cmd = CreateCommand()
Dim asm As System.Reflection.Assembly = _factory.GetType().Assembly
Dim paramType As Type = asm.GetType("Oracle.DataAccess.Client.OracleParameter")
Dim param As Object = Activator.CreateInstance(paramType)
paramType.GetProperty("ParameterName").SetValue(param, name, Nothing)
paramType.GetProperty("Direction").SetValue(param, ParameterDirection.Output, Nothing)
Dim oraDbType As Type = asm.GetType("Oracle.DataAccess.Client.OracleDbType")
paramType.GetProperty("OracleDbType").SetValue(param, oraDbType.GetField("RefCursor").GetValue(Nothing), Nothing)
AddParam(_cmd, DirectCast(param, DbParameter))
Return DirectCast(param, IDataParameter)
End Function
Public Function AddParamPLSQLAssociativeArray(ByVal name As String, ByVal value As Object, ByVal type As DbType) As IDataParameter
If _cmd Is Nothing Then _cmd = CreateCommand()
Dim asm As System.Reflection.Assembly = _factory.GetType().Assembly
Dim oraParamType As Type = asm.GetType("Oracle.DataAccess.Client.OracleParameter")
Dim oraParam As Object = Activator.CreateInstance(oraParamType)
oraParamType.GetProperty("ParameterName").SetValue(oraParam, name, Nothing)
oraParamType.GetProperty("Size").SetValue(oraParam, CType(value, Array).Length, Nothing)
Dim oraCollectionType As Type = asm.GetType("Oracle.DataAccess.Client.OracleCollectionType")
oraParamType.GetProperty("CollectionType").SetValue(oraParam, oraCollectionType.GetField("PLSQLAssociativeArray").GetValue(Nothing), Nothing)
Dim param As IDataParameter = DirectCast(oraParam, IDataParameter)
param.DbType = type
param.Value = value
AddParam(_cmd, DirectCast(param, DbParameter))
Return param
End Function
Public Sub ClearParams()
If _cmd Is Nothing Then Exit Sub
_cmd.Parameters.Clear()
End Sub
Public Function GetDataTable(ByVal sql As String) As DataTable
If _cmd Is Nothing Then _cmd = CreateCommand()
_cmd.CommandText = sql
Dim da As DbDataAdapter = _factory.CreateDataAdapter()
da.SelectCommand = _cmd
Dim dt As DataTable = New DataTable
da.Fill(dt)
Return dt
End Function
Public Sub FillRefCursor(ByRef dataSet As DataSet, ByVal procedureName As String)
If _cmd Is Nothing Then _cmd = CreateCommand()
_cmd.CommandText = procedureName
_cmd.CommandType = CommandType.StoredProcedure
Dim da As DbDataAdapter = _factory.CreateDataAdapter()
da.SelectCommand = _cmd
da.Fill(dataSet)
End Sub
Public Sub FillRefCursor(ByRef dataTable As DataTable, ByVal procedureName As String)
If _cmd Is Nothing Then _cmd = CreateCommand()
_cmd.CommandText = procedureName
_cmd.CommandType = CommandType.StoredProcedure
Dim da As DbDataAdapter = _factory.CreateDataAdapter()
da.SelectCommand = _cmd
da.Fill(dataTable)
End Sub
Public Function ExecuteSql(ByVal sql As String) As Integer
If _cmd Is Nothing Then _cmd = CreateCommand()
_cmd.CommandText = sql
Open()
Dim result As Integer = _cmd.ExecuteNonQuery()
If _txn Is Nothing OrElse _txn.Connection Is Nothing Then
Close()
End If
Return result
End Function
Public Function ExecuteStored(ByVal procedureName As String) As Integer
If _cmd Is Nothing Then _cmd = CreateCommand()
_cmd.CommandType = CommandType.StoredProcedure
_cmd.CommandText = procedureName
Open()
Dim result As Integer = _cmd.ExecuteNonQuery()
If _txn Is Nothing OrElse _txn.Connection Is Nothing Then
Close()
End If
Return result
End Function
Public Function ExecuteReader(ByVal sql As String) As DbDataReader
If _cmd Is Nothing Then _cmd = CreateCommand()
_cmd.CommandText = sql
Open()
Dim result As DbDataReader = _cmd.ExecuteReader(CommandBehavior.CloseConnection)
' Close()
Return result
End Function
Public Function ExecuteScalar(ByVal sql As String) As Object
If _cmd Is Nothing Then _cmd = CreateCommand()
_cmd.CommandText = sql
Open()
Dim result as Object =_cmd.ExecuteScalar()
Close()
Return result
End Function
Public Sub BeginTransaction()
Open()
_txn = _cnn.BeginTransaction()
End Sub
Public Sub Commit()
_txn.Commit()
Close()
End Sub
Public Sub Rollback()
_txn.Rollback()
Close()
End Sub
Private disposedValue As Boolean = False
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
If _txn IsNot Nothing Then _txn.Dispose()
If _cmd IsNot Nothing Then _cmd.Dispose()
If _cnn IsNot Nothing Then
If _cnn.State = ConnectionState.Open Then
_cnn.Close()
End If
_cnn.Dispose()
End If
End If
End If
Me.disposedValue = True
End Sub
#Region " IDisposable Support "
Public Sub Dispose() Implements IDisposable.Dispose
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class
C#
using System;
using System.Data;
using System.Data.Common;
public class DbFacade : IDisposable
{
private DbProviderFactory _factory;
private DbConnection _cnn;
private DbCommand _cmd;
private DbTransaction _txn;
public DbFacade() : this("Data Source=ExampleDb;User ID=SCOTT;Password=TIGER"){}
public DbFacade(string connectionString)
{
_factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
DbConnect(connectionString);
}
public DbConnection DbConnect(string connectionString)
{
_cnn = _factory.CreateConnection();
ConnectionString = connectionString;
return _cnn;
}
public string ConnectionString
{
get
{
return _cnn.ConnectionString;
}
set
{
if ((_cnn.ConnectionString != value))
{
_cnn.ConnectionString = value;
}
}
}
public void Open()
{
if ((_cnn.State == ConnectionState.Closed))
{
_cnn.Open();
}
}
void Close()
{
ClearParams();
if (_cnn.State == ConnectionState.Open)
{
_cnn.Close();
}
}
void SetBindByName(DbCommand cmd, bool isName)
{
cmd.GetType().GetProperty("BindByName").SetValue(cmd, isName, null);
}
public DbCommand CreateCommand()
{
DbCommand cmd = _factory.CreateCommand();
cmd.Connection = _cnn;
SetBindByName(cmd, true);
return cmd;
}
public DbCommand CreateCommand(string sql)
{
DbCommand cmd = CreateCommand();
cmd.CommandText = sql;
return cmd;
}
public DbDataAdapter CreateDataAdapter()
{
return _factory.CreateDataAdapter();
}
private void AddParam(DbCommand cmd, DbParameter param)
{
if (cmd.Parameters.Contains(cmd.Parameters[param.ParameterName]))
{
cmd.Parameters.Remove(cmd.Parameters[param.ParameterName]);
}
cmd.Parameters.Add(param);
}
public IDataParameter AddParam(string name, object value, DbType type)
{
if (value.GetType().IsArray)
{
return AddParamPLSQLAssociativeArray(name, value, type);
}
if (_cmd == null)
{
_cmd = CreateCommand();
}
DbParameter param = _cmd.CreateParameter();
param.ParameterName = name;
param.DbType = type;
param.Value = value;
AddParam(_cmd, param);
return param;
}
public IDataParameter AddParam(string name, DbType type, ParameterDirection direction, int size)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
DbParameter param = _cmd.CreateParameter();
param.ParameterName = name;
param.DbType = type;
param.Direction = direction;
param.Size = size;
AddParam(_cmd, param);
return param;
}
public IDataParameter AddParam(DbCommand cmd, string name, string sourceColumn, DbType type)
{
DbParameter param = cmd.CreateParameter();
param.ParameterName = name;
param.SourceColumn = sourceColumn;
param.DbType = type;
AddParam(cmd, param);
return param;
}
public IDataParameter AddParamRefCursor(string name)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
System.Reflection.Assembly asm = _factory.GetType().Assembly;
Type paramType = asm.GetType("Oracle.DataAccess.Client.OracleParameter");
object param = Activator.CreateInstance(paramType);
paramType.GetProperty("ParameterName").SetValue(param, name, null);
paramType.GetProperty("Direction").SetValue(param, ParameterDirection.Output, null);
Type oraDbType = asm.GetType("Oracle.DataAccess.Client.OracleDbType");
paramType.GetProperty("OracleDbType").SetValue(param, oraDbType.GetField("RefCursor").GetValue(null), null);
AddParam(_cmd, param as DbParameter);
return param as IDataParameter;
}
public IDataParameter AddParamPLSQLAssociativeArray(string name, object value, DbType type)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
System.Reflection.Assembly asm = _factory.GetType().Assembly;
Type oraParamType = asm.GetType("Oracle.DataAccess.Client.OracleParameter");
object oraParam = Activator.CreateInstance(oraParamType);
oraParamType.GetProperty("ParameterName").SetValue(oraParam, name, null);
oraParamType.GetProperty("Size").SetValue(oraParam, ((Array)(value)).Length, null);
Type oraCollectionType = asm.GetType("Oracle.DataAccess.Client.OracleCollectionType");
oraParamType.GetProperty("CollectionType").SetValue(oraParam, oraCollectionType.GetField("PLSQLAssociativeArray").GetValue(null), null);
IDataParameter param = ((IDataParameter)(oraParam));
param.DbType = type;
param.Value = value;
AddParam(_cmd, param as DbParameter);
return param;
}
public void ClearParams()
{
if (_cmd == null)
{
return;
}
_cmd.Parameters.Clear();
}
public DataTable GetDataTable(string sql)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
_cmd.CommandText = sql;
DbDataAdapter da = _factory.CreateDataAdapter();
da.SelectCommand = _cmd;
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public void FillRefCursor(ref DataSet dataSet, string procedureName)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
_cmd.CommandText = procedureName;
_cmd.CommandType = CommandType.StoredProcedure;
DbDataAdapter da = _factory.CreateDataAdapter();
da.SelectCommand = _cmd;
da.Fill(dataSet);
}
public void FillRefCursor(ref DataTable dataTable, string procedureName)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
_cmd.CommandText = procedureName;
_cmd.CommandType = CommandType.StoredProcedure;
DbDataAdapter da = _factory.CreateDataAdapter();
da.SelectCommand = _cmd;
da.Fill(dataTable);
}
public int ExecuteSql(string sql)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
_cmd.CommandText = sql;
Open();
int result = _cmd.ExecuteNonQuery();
if (_txn == null || _txn.Connection == null)
{
Close();
};
return result;
}
public int ExecuteStored(string procedureName)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = procedureName;
Open();
int result = _cmd.ExecuteNonQuery();
if (_txn == null || _txn.Connection == null)
{
Close();
};
return result;
}
public DbDataReader ExecuteReader(string sql)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
_cmd.CommandText = sql;
Open();
DbDataReader result = _cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Close();
return result;
}
public object ExecuteScalar(string sql)
{
if (_cmd == null)
{
_cmd = CreateCommand();
}
_cmd.CommandText = sql;
Open();
object result = _cmd.ExecuteScalar();
Close();
return result;
}
public void BeginTransaction()
{
Open();
_txn = _cnn.BeginTransaction();
}
public void Commit()
{
_txn.Commit();
Close();
}
public void Rollback()
{
_txn.Rollback();
Close();
}
#region IDisposable Support
private bool disposedValue = false;
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
if (_txn != null)
{
_txn.Dispose();
}
if (_cmd != null)
{
_cmd.Dispose();
}
if (_cnn != null)
{
if (_cnn.State == ConnectionState.Open)
{
_cnn.Close();
}
_cmd.Dispose();
}
}
disposedValue = true;
}
}
public void Dispose()
{
Dispose(true);
}
#endregion
}
利用方法
とりあえず、
VB.NET
Using db As New DbFacade()
db.ExecuteSql("UPDATE emp SET sal=sal*1.1 WHERE empno=7369")
Dim dt As DataTable = db.GetDataTable("select * from EMP")
End Using
C#
using (DbFacade db = new DbFacade())
{
db.ExecuteSql("UPDATE emp SET sal=sal*1.1 WHERE empno=7369");
DataTable dt = db.GetDataTable("select * from EMP");
}
