Page 1 of 1

Create Custom Visualizers(Command object script Visualizer) Rate Topic: ***** 1 Votes

#1 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Posted 07 April 2014 - 06:30 AM

*
POPULAR

What are Visualizers?
Visualizers are normally used in Visual studio at the time of debugging to get a summarize preview of any object at a glance as we want. For example View the data of a data table
Well, you may say the visual studio has already a lot of visualizers, why do we need to create a new one?
My answer there are times we need to do, see the following scenarios,
1)I have a data table which has at least 20 columns and 100 rows
a. I just want to view few columns value only
b. I just want to see the first, middle, last columns simultaneously
c. I just want to see some rows based on some criteria
d. I just want to view a Total or average or blah blah...

Well, you may say these are not usually we do, here it is, I will give you a certain scenario,
2)Well, do you ever think, I want to view a Sqlcommand, Oledbcommand or any command objectís SQL script with all parameters and its values(I am sure you think at least once in your career, if you are a .Net Database application developer)
Like this

Attached Image
Attached Image


Let us create a Script visualizer for command object, before going to kick start, let us know some important classes and links that took part in this visualizer creation,
DialogDebuggerVisualizer
VisualizerObjectSource
Before start have a read at the following links please
How to: Write a Visualizer
How to: Install a Visualizer

How it works?
DialogDebuggerVisualizer get the data from the VisualizerObjectSource and display it using the help of the window service IDialogVisualizerService, we are going to inherit these classes and use this interface To prepare our script visualizer.

ok letís Start,
Create a Class Library Object with a appropriate name, then it has a default class file Class1.vb,
Rename the class1 as whatever you want I am going to name it as DBCommandVisualizer, Wait a minute, usually a VB class file doesnít have any namespace, but here we need to create a namespace which wraps our entire class, give a Reasonable Namespace name, I am going to Name it as DBCommandScriptVisualizer
Why we need a namespace here?
Using this namespace and its attribute we are going to connect the visual studio, The attribute workout is the most important key part here
Now the tricky part is coming you need to create some attribute for your namespace this attribute is used by the visual studio to interpret the object so keep a closer look here
<Assembly: System.Diagnostics.DebuggerVisualizer(GetType(<yourNamespace>.<YourClassName>), GetType(VisualizerObjectSource), Target:=GetType(<Name  of the type you are going to visualize>), Description:="Any string")> 


Before I am going to start I want to create it common for all the command objects, since all commands are derived from Dbcommand I am going use it, Now my visualizer will automatically adapt all the commands which are having the base as Dbcommand
<Assembly: System.Diagnostics.DebuggerVisualizer(GetType(DBCommandScriptVisualizer.DBCommandVisualizer), GetType(VisualizerObjectSource), Target:=GetType(System.Data.Common.DbCommand), Description:="Visualize as a TSQL Script")> 
 

Now came back to the class part if you just take a look at the class DialogDebuggerVisualizer, There is a method Show, this is where we are going to override this method, before that we need to inherit the class DialogDebuggerVisualizer for DBCommandVisualizer
	Protected Overrides Sub Show(windowService As IDialogVisualizerService, objectProvider As IVisualizerObjectProvider)
            ĎDim dbc As DbCommand = objectProvider.GetObject
	     Dim Dbc as string = ďThis is TestingĒ
            Using form As New Form()
                form.Text = "Command Visualizer"
                form.ClientSize = New Size(400, 300)
                form.FormBorderStyle = FormBorderStyle.FixedToolWindow
 
                Dim Txtbox As New RichTextBox()
                Txtbox.ScrollBars = ScrollBars.Both
                Txtbox.Multiline = True
                Txtbox.WordWrap = False
                Txtbox.Parent = form
                Txtbox.Dock = DockStyle.Fill
                Txtbox.Text = dbc
                windowService.ShowDialog(form)
            End Using
	End Sub


Take a closer look at the above code we host the form in the window service, this window service Is used to displace our form in visual studio
Now Add one more procedure to Test whether the form show correct message, add the following Procedure in the class DBCommandVisualizer
 Public Shared Sub TestShowVisualizer(Dataobject As Object)
            Dim host As New VisualizerDevelopmentHost(Dataobject, GetType(DBCommandVisualizer)) 
            host.ShowVisualizer()
        End Sub


The above method is only for testing purpose nowhere else it is used, So your entire code will be looking like this
Namespace DBCommandScriptVisualizer
    Public Class DBCommandVisualizer
        Inherits DialogDebuggerVisualizer
 
        Protected Overrides Sub Show(windowService As IDialogVisualizerService, objectProvider As IVisualizerObjectProvider)
            Dim dbc As String = "this is testing" ' 
            Using form As New Form()
                form.Text = "Command Visualizer"
                form.ClientSize = New Size(400, 300)
                form.FormBorderStyle = FormBorderStyle.FixedToolWindow
 
                Dim Txtbox As New RichTextBox()
                Txtbox.ScrollBars = ScrollBars.Both
                Txtbox.Multiline = True
                Txtbox.WordWrap = False
                Txtbox.Parent = form
                Txtbox.Dock = DockStyle.Fill
                Txtbox.Text = dbc
                windowService.ShowDialog(form)
            End Using
        End Sub
 
        Public Shared Sub TestShowVisualizer(Dataobject As Object)
            Dim host As New VisualizerDevelopmentHost(Dataobject, GetType(DBCommandVisualizer)) ', GetType(DBcommandVisualizerSource))
            host.ShowVisualizer()
        End Sub 
    End Class
End NameSpace


Now add a winforms project to test the Command visualizer, add the previous project reference (i.e the class library object), Now add a button to the form on the button click event write the following code
     Dim cmd As New SqlClient.SqlCommand
     cmd.CommandText = "SELECT * FROM TEst Where Id =@ID"
     cmd.Parameters.AddWithValue("@ID", 1)
     DBCommandScriptVisualizer.DBCommandVisualizer.TestShowVisualizer(cmd)


Donít forget to import the previous project reference, set the win form project as startup project
Now run it click the button if you see a window with the message ďThis is TestingĒ Everything works fine ,
But this is not what I want to show, I want to show the script that is in the command with parameters
So by default the object will be pass by the getopject or getdata method of object provider
Dim DBO As DbCommand = objectProvider.GetObject
Dim Dbc as string = DBO.commandtext


If I run the Winform project again, it will throw an error dbcommand object is not serializable
Since we just want the command text to be visualized, we donít need to stream the entire object we just stream the command text only, we need to add one more class inside the same namespace in the same file( this is not mandatory you can do it in a separate file too), this is where VisualizerObjectSource came into action, this class used to stream the what data of our object to be visualize or how we want to visualize our object , add the following code
	Public Class DBcommandVisualizerSource
        Inherits VisualizerObjectSource
 
        Public Overrides Sub GetData(target As Object, outgoingData As System.IO.Stream)
            Dim strScript As String = ""
            Dim dbc As DbCommand = DirectCast(target, DbCommand)
            If dbc.CommandType = CommandType.Text Then
                strScript &= dbc.CommandText & vbNewLine
            ElseIf dbc.CommandType = CommandType.StoredProcedure Then
                strScript &= "EXEC " & dbc.CommandText & vbNewLine
            Else
                strScript &= "SELECT * FROM " & dbc.CommandText
            End If
            Dim writer = New StreamWriter(outgoingData)
            writer.WriteLine(strScript)
            writer.Flush()
        End Sub


Now again look back into the attribute of the name space, we have just inherited the VisualizerObjectSource so we are going to use our own class now instead of that class

<Assembly: System.Diagnostics.DebuggerVisualizer(GetType(DBCommandScriptVisualizer.DBCommandVisualizer), GetType(DBCommandScriptVisualizer.DBcommandVisualizerSource), Target:=GetType(System.Data.Common.DbCommand), Description:="Visualize as a TSQL Script")> 


Also, donít forget to change the Testing method too
  Public Shared Sub TestShowVisualizer(Dataobject As Object)
            Dim host As New VisualizerDevelopmentHost(Dataobject, GetType(DBCommandVisualizer), GetType(DBcommandVisualizerSource))
            host.ShowVisualizer()
        End Sub


Now its time to work in the show method
We override the Getdata procedure in VisualizerObjectSource , so In the show method we use the Getdata Proc
 Protected Overrides Sub Show(windowService As IDialogVisualizerService, objectProvider As IVisualizerObjectProvider)
            Dim dbc As String = New StreamReader(objectProvider.GetData()).ReadToEnd
            Using form As New Form()
                form.Text = "Command Visualizer"
                form.ClientSize = New Size(400, 300)
                form.FormBorderStyle = FormBorderStyle.FixedToolWindow
 
                Dim Txtbox As New RichTextBox()
                Txtbox.ScrollBars = ScrollBars.Both
                Txtbox.Multiline = True
                Txtbox.WordWrap = False
                Txtbox.Parent = form
                Txtbox.Dock = DockStyle.Fill
                Txtbox.Text = dbc
                windowService.ShowDialog(form)
            End Using
        End Sub


Since I am using the stream writer, I used the stream reader.
Similarly, you can use whatever the streaming logic you want
Now run the winforms and test it now you are able to see the command text you given to the command, the next step we want also use the parameters in the script section (this is purely my logic if have a better one try to adapt it here)
Public Overrides Sub GetData(target As Object, outgoingData As System.IO.Stream)
            Dim strScript As String = ""
            Dim strType As String = ""
            Dim dbc As DbCommand = DirectCast(target, DbCommand)
            Dim StrParams As String = ""
            Dim StrValues As String = ""
            If dbc.Parameters.Count > 0 Then
                For Each param As DbParameter In dbc.Parameters
                    strType = GetSqlDatabaseType(param)
                    strScript &= "DECLARE " & param.ParameterName & " " & strType & vbNewLine
                    StrParams &= "     " & param.ParameterName & " = " & param.ParameterName & ", " & vbNewLine
                    Try
                        If IsDBNull(param.Value) Then
                            StrValues &= "SET " & param.ParameterName & " = NULL" & vbNewLine
                        ElseIf IsNumeric(param.Value.ToString) = True Then
                            StrValues &= "SET " & param.ParameterName & " = " & param.Value.ToString & vbNewLine
                        ElseIf strType = "BIT" Then
                            StrValues &= "SET " & param.ParameterName & " = " & IIf(param.Value = True, "1", "0") & vbNewLine
                        ElseIf strType.Contains("DATE") Then
                            StrValues &= "SET " & param.ParameterName & " = '" & param.Value.ToString("dd/MMMM/yyyy HH:mm:ss AM/PM") & "' " & vbNewLine
                        Else
                            StrValues &= "SET " & param.ParameterName & " = '" & param.Value.ToString & "' " & vbNewLine
                        End If
                    Catch ex As Exception
                        StrValues &= "SET " & param.ParameterName & " = '" & param.Value.ToString & "' --Problem in value convertion Verify this value" & vbNewLine
                    End Try
                Next
                strScript &= StrValues
            End If
            If dbc.CommandType = CommandType.Text Then
                strScript &= dbc.CommandText & vbNewLine
            ElseIf dbc.CommandType = CommandType.StoredProcedure Then
                strScript &= "EXEC " & dbc.CommandText & vbNewLine
                If StrParams.Length > 0 Then strScript &= StrParams.Substring(0, StrParams.Length - 4)
            Else
                strScript &= "SELECT * FROM " & dbc.CommandText
            End If
            Dim writer = New StreamWriter(outgoingData)
            writer.WriteLine(strScript)
            writer.Flush()
        End Sub
 
        Private Function GetSqlDatabaseType(DParam As DbParameter) As String
            Dim param As New SqlClient.SqlParameter
            Try
                param.DbType = DParam.DbType
            Catch ex As Exception
                ' msgbox ex.message
            End Try
            Dim res As String = param.SqlDbType.ToString.ToUpper
            If res.Contains("VARCHAR") Then
                res &= "(" & IIf(DParam.Size > 4000, "MAX", IIf(DParam.Size < 10, "10", DParam.Size.ToString)) & ")"
            End If
            Return res
        End Function



If you run the winform project and test the button now you are able to see the script with itís parameter
Thatís it I am just finished my workout Preparing a Tsql Script from a command object,
Now itís time to deployment
Build the project and pick the dll and paste it the folder
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\Packages\Debugger\Visualizers


If this path is not exists then Search you visual studio installation folder from there
[Visual studio installation folder]\Common7\Packages\Debugger\Visualizers


Now restart your visual studio you are now able to see a new visualizer for any command object
*Edited*
Add My entire class file as a text attachment

Thatís all folks
Hope you enjoy the article and itís use

Attached File(s)


This post has been edited by thava: 08 April 2014 - 07:51 PM


Is This A Good Question/Topic? 5
  • +

Page 1 of 1