Npgsql Logging in VB.NET

Question

I used to use npgsql 2.2.5 and to log everything that npgsql was doing, I used the following:

NpgsqlEventLog.Level = LogLevel.Normal
NpgsqlEventLog.LogName = ("c:\npgsql.log")
NpgsqlEventLog.EchoMessages = True

No, with version 3.2.5, the instructions (in C#) are:

NpgsqlLogManager.Provider = new ConsoleLoggingProvider(<min level>, <print level?>, <print connector id?>);

I don't understand C# and it says to put this at the beginning of your application (See the info page.) and I need to put it into a website.

Where in the website should I put this code and how to I get npgsql to send the log to a text file?

Answer 1

I used the following:

dlLoggingNpgSQL:

Imports Npgsql
Imports NLog
Imports Npgsql.Logging

Namespace dlLoggingNpgSQL
Public Class NLogLoggingProvider
    Implements INpgsqlLoggingProvider

    Public Function INpgsqlLoggingProvider_CreateLogger(name As String) As NpgsqlLogger Implements INpgsqlLoggingProvider.CreateLogger
        Return New NLogLogger(name)
    End Function
End Class

Public Class NLogLogger
    Inherits NpgsqlLogger

    ReadOnly _log As Logger

    Public Sub New(name As String)
        _log = LogManager.GetLogger(name)
    End Sub

    Public Overrides Function IsEnabled(level As NpgsqlLogLevel) As Boolean
        Return _log.IsEnabled(ToNLogLogLevel(level))
    End Function

    Public Overrides Sub Log(level As NpgsqlLogLevel, connectorId As Integer, msg As String, Optional exception As Exception = Nothing)
        Dim ev = New LogEventInfo(ToNLogLogLevel(level), "", msg)
        If exception Is Nothing Then
            ev.Exception = exception
        End If

        If connectorId <> 0 Then
            ev.Properties("ConnectorId") = connectorId
        End If

        _log.Log(ev)
        _log.Log(ev.Level, msg)
    End Sub

    Public Function ToNLogLogLevel(level As NpgsqlLogLevel) As LogLevel
        Select Case level
            Case NpgsqlLogLevel.Trace
                Return LogLevel.Trace
            Case NpgsqlLogLevel.Debug
                Return LogLevel.Debug
            Case NpgsqlLogLevel.Info
                Return LogLevel.Info
            Case NpgsqlLogLevel.Warn
                Return LogLevel.Warn
            Case NpgsqlLogLevel.Error
                Return LogLevel.Error
            Case NpgsqlLogLevel.Fatal
                Return LogLevel.Fatal
            Case Else
                Throw New ArgumentOutOfRangeException("level")
        End Select
    End Function
End Class
End Namespace

dlNpgSQLwithUsing:

Imports NLog
Imports Npgsql
Imports Npgsql.Logging


Public Class dlNpgSQLwithUsing
Dim _connectionString As String
Dim _sqlConnection As NpgsqlConnection
Dim _sqlCommand As NpgsqlCommand
Dim _sqlDataAdapter As NpgsqlDataAdapter
Dim _dataset As DataSet
Dim _datatable As DataTable

Private _NLogLoggingProvider As New NLogLoggingProvider
Private Sub InitializeLogggingProvider()
    If NpgsqlLogManager.IsParameterLoggingEnabled = False Then
        NpgsqlLogManager.IsParameterLoggingEnabled = True
        NpgsqlLogManager.Provider = New NLogLoggingProvider()
    End If
End Sub

Public Function GetConnectionStringByName(Optional ByVal ConnectionString As String = "")
    If String.IsNullOrEmpty(ConnectionString) Then
        Return ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Else
        Return ConfigurationManager.ConnectionStrings(ConnectionString).ConnectionString
    End If
End Function

Public Function GetDataSet(ByVal connectionStringName As String, ByVal strQuery As String) As DataSet

    _dataset = New DataSet

    Try
        _connectionString = GetConnectionStringByName(connectionStringName)
        _NLogLoggingProvider.INpgsqlLoggingProvider_CreateLogger("Npgsql.NpgsqlConnection")

        Using _connection As New NpgsqlConnection(_connectionString)
            Using _sqlDataAdapter = New NpgsqlDataAdapter(strQuery, _connection)
                _sqlDataAdapter.Fill(_dataset)
            End Using
        End Using
    Catch ex As Exception
        Throw New System.Exception(ex.Message)
    End Try

    Return _dataset
End Function

Public Function GetDataTable(ByVal connectionStringName As String, ByVal strQuery As String) As DataTable

    _datatable = New DataTable

    Try
        _connectionString = GetConnectionStringByName(connectionStringName)
        _NLogLoggingProvider.INpgsqlLoggingProvider_CreateLogger("Npgsql.NpgsqlConnection")

        Using _connection As New NpgsqlConnection(_connectionString)
            Using _sqlDataAdapter = New NpgsqlDataAdapter(strQuery, _connection)
                _sqlDataAdapter.Fill(_datatable)
            End Using
        End Using
    Catch ex As Exception
        Throw New System.Exception(ex.Message)
    End Try

    Return _datatable
End Function


Public Function ExecuteQuery(ByVal connectionStringName As String, ByVal strQuery As String) As String

    Dim RecordsReturned As String = ""

    Try
        _connectionString = GetConnectionStringByName(connectionStringName)
        _NLogLoggingProvider.INpgsqlLoggingProvider_CreateLogger("Npgsql.NpgsqlConnection")

        Using _connection As New NpgsqlConnection(_connectionString)
            _connection.Open()
            _sqlCommand = New NpgsqlCommand(strQuery, _connection)
            RecordsReturned = _sqlCommand.ExecuteNonQuery()
            _connection.Close()
        End Using

    Catch ex As Exception
       Throw
    End Try

    Return RecordsReturned

End Function

Public Function ExecuteScalar(ByVal connectionStringName As String, ByVal strQuery As String) As String

    Dim RecordsReturned As String = ""

    Try
        _connectionString = GetConnectionStringByName(connectionStringName)
        _NLogLoggingProvider.INpgsqlLoggingProvider_CreateLogger("Npgsql.NpgsqlConnection")

        Using _connection As New NpgsqlConnection(_connectionString)
            _connection.Open()
            _sqlCommand = New NpgsqlCommand(strQuery, _connection)
            RecordsReturned = _sqlCommand.ExecuteScalar()
            _connection.Close()
        End Using

    Catch ex As Exception
        Throw
    End Try

    Return RecordsReturned
End Function

End Class

Hope this helps.