OracleCommand Visualizer-使用VB.NET(2.0)撰寫視覺化檢視

2011-07-22

3087
0
2012-04-20

Dotblogs 的標籤：VS2008,VB.NET,Visualizer,Oracle

這篇文章的起因是黑暗大這篇文章：OracleCommand Visualizer for VS2010。

因為我手邊好幾個專案，都是使用System.Data.OracleClient，所以偵錯階段常常要查看OracleCommand的參數和SQL，而我以前真的不知道可以撰寫Visualizer來方便偵錯，恰巧黑暗大寫了這篇文章，讓我好開心啊~~(轉圈圈~~)。但是問題來了，黑暗大用的是VS2010開發，而且有用到Linq to XML，但是我手邊的專案最高只到 Net Framework 2.0 (使用Visual Studio 2008)，所以【可能】黑暗大的成品我沒辦法直接用。

為什麼說【可能】呢，因為我想說可以順便練功，所以沒拿來測試，看完黑大的文章，找個空檔就給他Coding下去啦~

我寫的 OracleCommand Visualizer 沒黑大做的漂亮，請見諒 ^^|||

使用 Visual Studio 2008 + VB.NET 撰寫，Targer .NET Framework 2.0 (所以不能用 Linq)。

成品可在 VS2008 / VS2010 中順利執行，因為沒安裝 VS2005，所以不保證 VS2005 可執行：

因為沒寫過 Visualizer，所以一開始我先依 MSDN：逐步解說：在 Visual Basic 中撰寫視覺化檢視做一個雛型，了解結構和確定可以執行 Hello World 後，就開始參考黑暗大文章開始撰寫自己的 Visualizer。和黑暗大文章中最大的不同，是 OracleCommand 的資料轉 XML 的部分，因為不能用 Linq，所以這邊就大改。Ok，廢話不多說，貼 Code 上來囉~

請注意，因為 OracleCommand 不支援序列化(Serializable)，所以無法直接使用內建的 VisualizerObjectSource 讀取要監看的 OracleCommand，所以必須自己把要監看的 OracleCommand 內容轉成可序列化的 XML。這點其實就是這次 Visualizer 設計最最最最重要的一件事！

DebugMonitor.vb：


Option Strict On

Imports Microsoft.VisualStudio.DebuggerVisualizers
Imports System.Windows.Forms

<Assembly: System.Diagnostics.DebuggerVisualizer(GetType(DebugMonitor), _
                                                 GetType(OracleCommandVisualizerObjectSource), _
                                                 Target:=GetType(System.Data.OracleClient.OracleCommand), _
                                                 Description:="Monitor OracleCommander.Parameters")> 
Public Class DebugMonitor
    Inherits DialogDebuggerVisualizer

    Protected Overrides Sub Show(ByVal windowService As Microsoft.VisualStudio.DebuggerVisualizers.IDialogVisualizerService, _
                                 ByVal objectProvider As Microsoft.VisualStudio.DebuggerVisualizers.IVisualizerObjectProvider)
        Dim myForm As New DebugInformation
        myForm.Cmd = objectProvider.GetObject().ToString()
        myForm.ShowDialog()
    End Sub

    Public Shared Sub TestShowVisualizer(ByVal objectToVisualize As Object)
        Dim visualizerHost As New VisualizerDevelopmentHost(objectToVisualize, GetType(DebugMonitor), _
                                                            GetType(OracleCommandVisualizerObjectSource))
        visualizerHost.ShowVisualizer()
    End Sub

End Class

OracleCommandVisualizerObjectSource.vb：


Option Strict On

Imports Microsoft.VisualStudio.DebuggerVisualizers
Imports System.Xml
Imports System.IO

Public Class OracleCommandVisualizerObjectSource
    Inherits VisualizerObjectSource

    Public Overrides Sub GetData(ByVal target As Object, ByVal outgoingData As System.IO.Stream)
        Try

            Dim cmd As OracleClient.OracleCommand = Nothing
            Try
                If TypeOf (target) Is OracleClient.OracleCommand Then
                    cmd = CType(target, OracleClient.OracleCommand)
                Else
                    MsgBox("型別不是OracleCommand，無法監控參數：" + target.GetType.ToString)
                End If
            Catch ex As Exception
                MsgBox("轉換傳入Command時發生錯誤！" + ex.Message + vbCrLf + target.GetType.ToString())
            End Try
            Dim xd As XmlDocument = New XmlDocument
            Dim rootElement As XmlElement = xd.CreateElement("OracleCommand")
            Dim SqlTextElement As XmlElement = xd.CreateElement("CommandText")
            SqlTextElement.SetAttribute("Value", IIf(IsNothing(cmd), "null", cmd.CommandText).ToString)
            rootElement.AppendChild(SqlTextElement)
            Dim ParametersElement As XmlElement = xd.CreateElement("Parameters")
            rootElement.AppendChild(ParametersElement)
            If Not IsNothing(cmd) Then
                For i As Integer = 0 To cmd.Parameters.Count - 1
                    Dim param As OracleClient.OracleParameter = cmd.Parameters(i)
                    Dim xe As XmlElement = xd.CreateElement("Parameter")
                    xe.SetAttribute("SN", CStr(i + 1))
                    xe.SetAttribute("ParameterName", param.ParameterName)
                    xe.SetAttribute("ParameterValue", IIf(DBNull.Value.Equals(param.Value), "DBNull.Value", param.Value).ToString)
                    xe.SetAttribute("DbType", param.OracleType.ToString())
                    ParametersElement.AppendChild(xe)
                Next
            End If
            xd.AppendChild(rootElement)
            MyBase.GetData(xd.OuterXml, outgoingData)
        Catch ex As Exception
            MsgBox("發生未預期的錯誤！" + ex.Message)
        End Try

    End Sub

End Class

DebugInformation.vb：


Imports System.Data
Imports System.Xml
Imports System.Text.RegularExpressions

Public Class DebugInformation

    Private _Cmd As String

    Private Sub DebugInformation_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim xd As New XmlDocument
            Dim InvalidXMLCharacter As Regex = New Regex("[\u0000-\u0008\u000B\u000C\u000E-\u001F\uD800-\uDFFF\uFFFE\uFFFF]", RegexOptions.Compiled)
            Cmd = InvalidXMLCharacter.Replace(Cmd, " ")

            Try
                xd.LoadXml(Cmd)
            Catch ex As Exception
                MsgBox("無法識別的資料來源。" + vbCrLf + ex.Message)
                Exit Sub
            End Try

            Dim ParentNode As XmlNode = xd.SelectNodes("OracleCommand").Item(0)
            txtSql.Text = ParentNode.SelectNodes("CommandText").Item(0).Attributes("Value").Value
            Dim NodeList As XmlNodeList = ParentNode.SelectNodes("Parameters").Item(0).SelectNodes("Parameter")
            dgParam.DataSource = ConvertDataTable(NodeList)

        Catch ex As Exception
            MsgBox("Command參數監視器發生錯誤！" + ex.Message)
            Exit Sub
        End Try

    End Sub

    Private Function ConvertDataTable(ByVal NodeList As XmlNodeList) As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("SN")
        dt.Columns.Add("ParameterName")
        dt.Columns.Add("DbType")
        dt.Columns.Add("ParameterValue")
        For i As Integer = 0 To NodeList.Count - 1
            Dim node As XmlNode = NodeList.Item(i)
            Dim dr As DataRow = dt.NewRow
            dr.Item(0) = node.Attributes("SN").Value
            dr.Item(1) = node.Attributes("ParameterName").Value
            dr.Item(2) = node.Attributes("DbType").Value
            dr.Item(3) = node.Attributes("ParameterValue").Value
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

    Public Property Cmd() As String
        Get
            Return _Cmd
        End Get
        Set(ByVal value As String)
            _Cmd = value
        End Set
    End Property

End Class

收工囉~

--------
沒什麼特別的~
不過是一些筆記而已

回首頁