TableAdapterで実行されるSQL文をログする

個人的にはTableAdapterはあまり利用しません。これは、いろいろ制限があるためで、そのひとつにアプリでSQL文をログできない点がありました。
最近これを解決する方法が見つかったのでサンプルコードを載せておきます。DataSetデザイナ上のテーブルアダプタのBaseClassプロパティにサンプルコードで作成したBaseTableAdapterクラスを設定するだけで、SQL文の実行直前にSQL文をTrace出力してくれます。

もちろん、現行の生成コードを前提にいろいろとやっていますので、生成されるコードが変化した場合正常に動作しない可能性があることは留意しておいてください。

Imports System.ComponentModel
Imports System.Reflection
Imports System.Data.Common
Imports System.Runtime.Remoting
Imports System.Runtime.Remoting.Proxies
Imports System.Runtime.Remoting.Services
Imports System.Runtime.Remoting.Messaging
Imports System.Runtime.Remoting.Activation

 _
Public Class BaseTableAdapter
    Inherits ContextBoundObject
    Implements IComponent

#Region "PROXYクラス"
     _
    Friend Class InjectionAttribute
        Inherits ProxyAttribute

        Public Overrides Function CreateInstance(ByVal serverType As Type) As MarshalByRefObject
            Dim target As MarshalByRefObject = MyBase.CreateInstance(serverType)
            Dim ap As New BaseTableAdapterProxy(serverType, CType(target, BaseTableAdapter))
            Return CType(ap.GetTransparentProxy(), MarshalByRefObject)
        End Function
    End Class

    Friend Class BaseTableAdapterProxy
        Inherits RealProxy

        Private _target As BaseTableAdapter

        Friend Sub New(ByVal targetType As Type, ByVal target As BaseTableAdapter)
            MyBase.New(targetType)
            _target = target
        End Sub

        Public Overrides Function Invoke(ByVal msg As IMessage) As IMessage
            Dim constMethod As IConstructionCallMessage = TryCast(msg, IConstructionCallMessage)
            If constMethod IsNot Nothing Then
                RemotingServices.GetRealProxy(_target).InitializeServerObject(constMethod)
                Dim mrm As IConstructionReturnMessage _
                    = EnterpriseServicesHelper.CreateConstructionReturnMessage( _
                        constMethod, CType(GetTransparentProxy(), MarshalByRefObject))
                _target.AfterConstructor()
                Return mrm
            End If
            Dim callMethod As IMethodCallMessage = TryCast(msg, IMethodCallMessage)
            If callMethod IsNot Nothing Then
                Return RemotingServices.ExecuteMessage(_target, callMethod)
            End If
            Return Nothing
        End Function
    End Class

    Friend Class DbCommandProxy
        Inherits RealProxy

        Private _target As DbCommand
        Private _container As BaseTableAdapter
        Public Sub New(ByVal target As DbCommand, ByVal container As BaseTableAdapter)
            MyBase.New(target.GetType())
            _target = target
            _container = container
        End Sub

        Public Overrides Function Invoke(ByVal msg As IMessage) As IMessage
            Dim methodMsg As IMethodCallMessage = DirectCast(msg, IMethodCallMessage)
            If methodMsg.MethodName.StartsWith("Execute") Then
                _container.BeforeExecuteDbCommandMethod(_target)
            End If
            Dim mrm As IMethodReturnMessage = RemotingServices.ExecuteMessage(CType(_target, MarshalByRefObject), methodMsg)
            Return mrm
        End Function
    End Class
#End Region

    Public Sub New()
    End Sub

#Region "Reflection Helper"
    Private Function GetPrivateProperty(ByVal name As String) As Object
        Dim propertyInfo As PropertyInfo = _
            Me.GetType.GetProperty( _
                    name, BindingFlags.GetProperty _
                    Or BindingFlags.Instance _
                    Or BindingFlags.IgnoreCase _
                    Or BindingFlags.NonPublic)

        If propertyInfo IsNot Nothing Then
            Return propertyInfo.GetValue(Me, Nothing)
        Else
            Return Nothing
        End If
    End Function

    Private Sub SetPrivateField(ByVal name As String, ByVal value As Object)

        Dim fInfo As FieldInfo = _
            Me.GetType.GetField( _
                    name, BindingFlags.GetField _
                    Or BindingFlags.Instance _
                    Or BindingFlags.IgnoreCase _
                    Or BindingFlags.NonPublic)

        If fInfo IsNot Nothing Then
            fInfo.SetValue(Me, value)
        End If
    End Sub
#End Region

    Friend Sub AfterConstructor()
        Dim adapter As DbDataAdapter = CType(GetPrivateProperty("Adapter"), DbDataAdapter)
        Dim commandList As ICollection = CType(GetPrivateProperty("CommandCollection"), ICollection)

        Dim commandCollection As New ArrayList
        Dim cmdType As Type = Nothing
        For Each cmd As DbCommand In commandList
            cmdType = cmd.GetType()
            Dim proxy As New DbCommandProxy(cmd, Me)
            commandCollection.Add(proxy.GetTransparentProxy())
        Next
        SetPrivateField("_commandCollection", commandCollection.ToArray(cmdType))

        If adapter.SelectCommand IsNot Nothing Then
            adapter.SelectCommand = CType(New DbCommandProxy(adapter.SelectCommand, Me).GetTransparentProxy(), DbCommand)
        End If
        If adapter.UpdateCommand IsNot Nothing Then
            adapter.UpdateCommand = CType(New DbCommandProxy(adapter.UpdateCommand, Me).GetTransparentProxy(), DbCommand)
        End If
        If adapter.InsertCommand IsNot Nothing Then
            adapter.InsertCommand = CType(New DbCommandProxy(adapter.InsertCommand, Me).GetTransparentProxy(), DbCommand)
        End If
        If adapter.DeleteCommand IsNot Nothing Then
            adapter.DeleteCommand = CType(New DbCommandProxy(adapter.DeleteCommand, Me).GetTransparentProxy(), DbCommand)
        End If
    End Sub

    Protected Overridable Sub BeforeExecuteDbCommandMethod(ByVal cmd As DbCommand)
        Trace.WriteLine(cmd.CommandText)
    End Sub

    Public Event Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Implements System.ComponentModel.IComponent.Disposed

    Private _site As ISite
    Public Property Site() As System.ComponentModel.ISite Implements System.ComponentModel.IComponent.Site
        Get
            Return _site
        End Get
        Set(ByVal value As System.ComponentModel.ISite)
            _site = value
        End Set
    End Property

    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            RaiseEvent Disposed(Me, EventArgs.Empty)
        End If
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
End Class