ODP.NET ラッパー（Facade）クラスの作成

方針

• コンストラクタで接続（デストラクタで切断）
• プロバイダファクトリクラス（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で…

blog.shibata.tech

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

    ''' <summary>
    ''' BindByNameプロパティをセットする
    ''' </summary>
    ''' <param name="isName">True:有効 False:無効</param>
    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

    ''' <summary>
    ''' パラメータを追加する。
    ''' </summary>
    ''' <param name="cmd"></param>
    ''' <param name="param"></param>
    ''' <remarks>Removeしなければ、「PLS-00703: リストに名前付き引数のインスタンスが複数あります。」が発生する</remarks>
    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

    ''' <summary>
    ''' パラメータを追加する
    ''' </summary>
    ''' <param name="name">名称</param>
    ''' <param name="value">値</param>
    ''' <param name="type">DbType</param>
    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

    ''' <summary>
    ''' パラメータを追加する
    ''' </summary>
    ''' <param name="name">名称</param>
    ''' <param name="type">DbType</param>
    ''' <param name="direction">Direction</param>
    ''' <param name="size">Size</param>
    ''' <remarks>ストアド戻り値用</remarks>
    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

    ''' <summary>
    ''' パラメータを追加する
    ''' </summary>
    ''' <param name="cmd">DbCommand</param>
    ''' <param name="name">名称</param>
    ''' <param name="sourceColumn">列名</param>
    ''' <param name="type">DbType</param>
    ''' <remarks>DataAdapter用</remarks>
    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

    ''' <summary>
    ''' RefCursor型のパラメータを追加する
    ''' </summary>
    ''' <param name="name">名称</param>
    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

    ''' <summary>
    ''' PL/SQL連想配列のパラメータを追加する
    ''' </summary>
    ''' <param name="name">名称</param>
    ''' <param name="value">値</param>
    ''' <param name="type">DbType</param>
    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

    ''' <summary>
    ''' パラメータをクリアする
    ''' </summary>
    Public Sub ClearParams()
        If _cmd Is Nothing Then Exit Sub
        _cmd.Parameters.Clear()
    End Sub

    ''' <summary>
    ''' DataTableを返す
    ''' </summary>
    ''' <param name="sql"></param>
    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

    ''' <summary>
    ''' RefCursorをDataSetにセットする
    ''' </summary>
    ''' <param name="dataSet"></param>
    ''' <param name="procedureName"></param>
    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

    ''' <summary>
    ''' RefCursorをDataTableにセットする
    ''' </summary>
    ''' <param name="dataTable"></param>
    ''' <param name="procedureName"></param>
    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

    ''' <summary>
    ''' SQLを実行する
    ''' </summary>
    ''' <param name="sql"></param>
    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

    ''' <summary>
    ''' ストアドプロシージャを実行する
    ''' </summary>
    ''' <param name="procedureName"></param>
    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        ' 重複する呼び出しを検出するには

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: 明示的に呼び出されたときにマネージ リソースを解放します
                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

            ' TODO: 共有のアンマネージ リソースを解放します
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' このコードは、破棄可能なパターンを正しく実装できるように Visual Basic によって追加されました。
    Public Sub Dispose() Implements IDisposable.Dispose
        ' このコードを変更しないでください。クリーンアップ コードを上の Dispose(ByVal disposing As Boolean) に記述します。
        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();
        }
    }

    /// <summary>
    /// BindByNameプロパティをセットする
    /// </summary>
    /// <param name="isName">True:有効 False:無効</param>
    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();
    }

    /// <summary>
    /// パラメータを追加する。
    /// </summary>
    /// <param name="cmd"></param>
    /// <param name="param"></param>
    /// <remarks>Removeしなければ、「PLS-00703: リストに名前付き引数のインスタンスが複数あります。」が発生する</remarks>
    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);
    }

    /// <summary>
    /// パラメータを追加する
    /// </summary>
    /// <param name="name">名称</param>
    /// <param name="value">値</param>
    /// <param name="type">DbType</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;
    }

    /// <summary>
    /// パラメータを追加する
    /// </summary>
    /// <param name="name">名称</param>
    /// <param name="type">DbType</param>
    /// <param name="direction">Direction</param>
    /// <param name="size">Size</param>
    /// <remarks>ストアド戻り値用</remarks>
    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;
    }

    /// <summary>
    /// パラメータを追加する
    /// </summary>
    /// <param name="cmd">DbCommand</param>
    /// <param name="name">名称</param>
    /// <param name="sourceColumn">列名</param>
    /// <param name="type">DbType</param>
    /// <remarks>DataAdapter用</remarks>
    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;
    }

    /// <summary>
    /// RefCursor型のパラメータを追加する
    /// </summary>
    /// <param name="name">名称</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;
    }

    /// <summary>
    /// PL/SQL連想配列のパラメータを追加する
    /// </summary>
    /// <param name="name">名称</param>
    /// <param name="value">値</param>
    /// <param name="type">DbType</param>
    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;
    }

    /// <summary>
    /// パラメータをクリアする
    /// </summary>
    public void ClearParams()
    {
        if (_cmd == null)
        {
            return;
        }
        _cmd.Parameters.Clear();
    }

    /// <summary>
    /// DataTableを返す
    /// </summary>
    /// <param name="sql"></param>
    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;
    }

    /// <summary>
    /// RefCursorをDataSetにセットする
    /// </summary>
    /// <param name="dataSet"></param>
    /// <param name="procedureName"></param>
    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);
    }

    /// <summary>
    /// RefCursorをDataTableにセットする
    /// </summary>
    /// <param name="dataTable"></param>
    /// <param name="procedureName"></param>
    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);
    }

    /// <summary>
    /// SQLを実行する
    /// </summary>
    /// <param name="sql"></param>
    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;
    }

    /// <summary>
    /// ストアドプロシージャを実行する
    /// </summary>
    /// <param name="procedureName"></param>
    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)
            {
                // TODO: マネージ状態を破棄します (マネージ オブジェクト)。
                if (_txn != null)
                {
                    _txn.Dispose();
                }
                if (_cmd != null)
                {
                    _cmd.Dispose();
                }
                if (_cnn != null)
                {
                    if (_cnn.State == ConnectionState.Open)
                    {
                        _cnn.Close();
                    }
                    _cmd.Dispose();
                }
            }

            // TODO: アンマネージ リソース (アンマネージ オブジェクト) を解放し、下のファイナライザーをオーバーライドします。
            // TODO: 大きなフィールドを null に設定します。

            disposedValue = true;
        }
    }

    // TODO: 上の Dispose(bool disposing) にアンマネージ リソースを解放するコードが含まれる場合にのみ、ファイナライザーをオーバーライドします。
    // ~DbFacade() {
    //   // このコードを変更しないでください。クリーンアップ コードを上の Dispose(bool disposing) に記述します。
    //   Dispose(false);
    // }

    // このコードは、破棄可能なパターンを正しく実装できるように追加されました。
    public void Dispose()
    {
        // このコードを変更しないでください。クリーンアップ コードを上の Dispose(bool disposing) に記述します。
        Dispose(true);
        // TODO: 上のファイナライザーがオーバーライドされる場合は、次の行のコメントを解除してください。
        // GC.SuppressFinalize(this);
    }
    #endregion
}

利用方法

とりあえず、

VB.NET

Using db As New DbFacade()
    'Update
    db.ExecuteSql("UPDATE emp SET sal=sal*1.1 WHERE empno=7369")
    'DataTable
    Dim dt As DataTable = db.GetDataTable("select * from EMP")
End Using

C#

using (DbFacade db = new DbFacade())
{
    // Update
    db.ExecuteSql("UPDATE emp SET sal=sal*1.1 WHERE empno=7369");
    // DataTable
    DataTable dt = db.GetDataTable("select * from EMP");
}