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