VB.NET•Output Your Trace to SQL Server

Listing 2. SQLTraceListener, which outputs SQL Server records, is one of many types of custom trace listeners you can derive from SpecialListener. Because SpecialListener does most of the work, the custom trace listeners only need to handle custom constructors and two overloads of the WriteLine method, one of which is shown here.

Option Strict On
Option Explicit On 

Imports System

Public Class SQLTraceListener
   Inherits SpecialListener

   Private mConnString As String

   Public Sub New(ByVal connString As String)
      mConnString = connstring
   End Sub

   Public Overloads Overrides Sub _
      WriteMessage(ByVal traceEntry As _
      TraceEntry)
      Dim cn As New _
         Data.SqlClient.SqlConnection( _
         mConnString)
      Dim cmd As New _
         Data.SqlClient.SqlCommand( _
         "TraceOutput_Insert", cn)
      Dim sVal As String
      If traceEntry.Value Is Nothing Then
         sVal = ""
      Else
         sVal = traceEntry.Value.ToString
      End If
      Me.mMessage &= traceEntry.Issue
      cmd.CommandType = _
         Data.CommandType.StoredProcedure
      cmd.Parameters.Add("@RunGUID", _
         traceEntry.RunGuid)
      cmd.Parameters.Add("@NamespaceName", _
         traceEntry.NameSpaceName)
      cmd.Parameters.Add("@ClassName", _
         traceEntry.ClassName)
      cmd.Parameters.Add("@MethodName", _
         traceEntry.MethodName)
      cmd.Parameters.Add("@Row", traceEntry.Row)
      cmd.Parameters.Add("@Col", traceEntry.Col)
      cmd.Parameters.Add("@Level", _
         traceEntry.TraceLevel)
      cmd.Parameters.Add("@Val", sVal)
      cmd.Parameters.Add("@ValueName", _
         traceEntry.ValueName)
      cmd.Parameters.Add("@Issue", Me.mMessage)
      cmd.Parameters.Add("@OutputTime", _
         DateTime.Now)
      Me.mMessage = ""
      Try
         cmd.Connection.Open()
         cmd.ExecuteNonQuery()
      Catch ex As System.Exception
         Diagnostics.Debug.WriteLine(ex)
      Finally
         If Not cmd.Connection.State = _
            Data.ConnectionState.Closed _
            Then
            cmd.Connection.Close()
         End If
      End Try
   End Sub