IDbConnection 等の拡張メソッド

DatabaseExtensions.vb

Imports System.Runtime.CompilerServices
Imports System.Dynamic

Module DatabaseExtensions

    Sub Main()

        Dim conn = New SqlClient.SqlConnection("Data Source=...")

        Dim one = Convert.ToInt32(conn.ExecuteScalar("SELECT 1"))

        conn.ExecuteNonQuery("DELETE FROM TABLE_NAME WHERE COL1=@COL1", Sub(cmd) cmd.AddParameter("COL1").Value = 1)

        For Each item In conn.ExecuteReader("SELECT ...").Select(Function(record) New With {.AAA = record.GetInt32(0), .BBB = record.GetString(1)})
            Console.WriteLine(item.BBB)
            If item.AAA > 10 Then
                Exit For
            End If
        Next

        conn.Transaction(
            Sub(trans)
                conn.ExecuteNonQuery("SELETE FROM T1", transaction:=trans)
                conn.ExecuteNonQuery("SELETE FROM T2", transaction:=trans)
                conn.ExecuteNonQuery("SELETE FROM T3", transaction:=trans)
            End Sub)

    End Sub

    ''' <summary>
    ''' 新しくコマンドを作成し、ExecuteScalar 実行します。
    ''' </summary>
    ''' <param name="connection">コネクション</param>
    ''' <param name="commandText">SQL文</param>
    ''' <param name="before">コマンドを実行する前に呼び出されるアクション</param>
    ''' <param name="transaction">トランザクション</param>
    ''' <returns>ExecuteScalar の実行結果</returns>
    ''' <remarks></remarks>
    <Extension>
    Public Function ExecuteScalar(connection As IDbConnection, commandText As String, Optional before As Action(Of IDbCommand) = Nothing, Optional transaction As IDbTransaction = Nothing) As Object
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If
        Using command = connection.CreateCommand()
            command.CommandText = commandText
            If transaction IsNot Nothing Then
                command.Transaction = transaction
            End If
            If before IsNot Nothing Then
                before(command)
            End If
            Dim result = command.ExecuteScalar()
            Return result
        End Using
    End Function

    ''' <summary>
    ''' 新しくコマンドを作成し、ExecuteNonQuery 実行します。
    ''' </summary>
    ''' <param name="connection">コネクション</param>
    ''' <param name="commandText">SQL文</param>
    ''' <param name="before">コマンドを実行する前に呼び出されるアクション</param>
    ''' <param name="after">コマンドが実行された後に呼び出されるアクション</param>
    ''' <param name="transaction">トランザクション</param>
    ''' <returns>ExecuteNonQuery の実行結果</returns>
    ''' <remarks></remarks>
    <Extension>
    Public Function ExecuteNonQuery(connection As IDbConnection, commandText As String, Optional before As Action(Of IDbCommand) = Nothing, Optional after As Action(Of IDbCommand) = Nothing, Optional transaction As IDbTransaction = Nothing) As Integer
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If
        Using command = connection.CreateCommand()
            command.CommandText = commandText
            If transaction IsNot Nothing Then
                command.Transaction = transaction
            End If
            If before IsNot Nothing Then
                before(command)
            End If
            Dim result = command.ExecuteNonQuery()
            If after IsNot Nothing Then
                after(command)
            End If
            Return result
        End Using
    End Function

    ''' <summary>
    ''' 新しくコマンドを作成し、ExecuteReader 実行します。
    ''' </summary>
    ''' <param name="connection">コネクション</param>
    ''' <param name="commandText">SQL文</param>
    ''' <param name="before">コマンドを実行する前に呼び出されるアクション</param>
    ''' <param name="transaction">トランザクション</param>
    ''' <returns>IDataRecord(IDataReader) のシーケンス</returns>
    ''' <remarks></remarks>
    <Extension>
    Public Iterator Function ExecuteReader(connection As IDbConnection, commandText As String, Optional before As Action(Of IDbCommand) = Nothing, Optional transaction As IDbTransaction = Nothing) As IEnumerable(Of IDataRecord)
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If
        Using command = connection.CreateCommand()
            command.CommandText = commandText
            If transaction IsNot Nothing Then
                command.Transaction = transaction
            End If
            If before IsNot Nothing Then
                before(command)
            End If
            Using reader = command.ExecuteReader()
                While reader.Read()
                    Yield reader
                End While
            End Using
        End Using
    End Function

    ''' <summary>
    ''' トランザクションを開始し、<paramref name="execute"/> を実行します。
    ''' <paramref name="execute"/> が正常に終了すると Commit が行われ、
    ''' 例外が発生すると Rollback が実行されます。
    ''' </summary>
    ''' <typeparam name="T">結果の型</typeparam>
    ''' <param name="connection">コネクション</param>
    ''' <param name="execute">トランザクション内で実行されるファンクション</param>
    ''' <returns>ファンクションの結果</returns>
    ''' <remarks></remarks>
    <Extension>
    Public Function Transaction(Of T)(connection As IDbConnection, execute As Func(Of IDbTransaction, T)) As T
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If
        Dim trans = connection.BeginTransaction()
        Try
            Dim result = execute(trans)
            trans.Commit()
            Return result
        Catch
            trans.Rollback()
            Throw
        Finally
            trans.Dispose()
        End Try
    End Function

    ''' <summary>
    ''' トランザクションを開始し、<paramref name="execute"/> を実行します。
    ''' <paramref name="execute"/> が正常に終了すると Commit が行われ、
    ''' 例外が発生すると Rollback が実行されます。
    ''' </summary>
    ''' <param name="connection">コネクション</param>
    ''' <param name="execute">トランザクション内で実行されるアクション</param>
    ''' <remarks></remarks>
    <Extension>
    Public Sub Transaction(connection As IDbConnection, execute As Action(Of IDbTransaction))
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If
        Dim trans = connection.BeginTransaction()
        Try
            execute(trans)
            trans.Commit()
        Catch
            trans.Rollback()
            Throw
        Finally
            trans.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' IDbCommand にパラメータを追加します。
    ''' </summary>
    ''' <param name="command">コマンド</param>
    ''' <param name="name">パラメータ名</param>
    ''' <returns>追加されたパラメータ</returns>
    ''' <remarks></remarks>
    <Extension>
    Public Function AddParameter(command As IDbCommand, name As String) As IDbDataParameter
        Dim parameter = command.CreateParameter()
        parameter.ParameterName = name
        command.Parameters.Add(parameter)
        Return parameter
    End Function

    ''' <summary>
    ''' IDataReader から名前で値を取得する
    ''' </summary>
    ''' <param name="reader">データリーダ</param>
    ''' <param name="name">名前</param>
    ''' <returns>値</returns>
    ''' <remarks></remarks>
    <Extension>
    Public Function GetValue(reader As IDataReader, name As String) As Object
        Dim index = reader.GetOrdinal(name)
        Return reader.GetValue(name)
    End Function
End Module