How to Log SQL Queries

• Nov 14, 2022
• 3 minutes to read

External Tools

Native Database Tools

Use the Microsoft SQL Server Profiler, Extended Events or similar tools for your database engine to analyze SQL queries.

XPO Profiler

The XPO ORM library ships with the XPO Profiler. With this tool, you can log SQL queries, track XPO-specific events, and analyze methods that execute specific queries.

Application Code

Trace Listeners (.NET Framework)

Add the corresponding diagnostic switch to the application’s configuration file (App.config or Web.config) to see SQL queries generated by XPO.

XML

<system.diagnostics>  
    <switches>  
        <add name="XPO" value="3" />  
    </switches>  
</system.diagnostics> 

You can find the log in the Visual Studio Output window. Output lines correspond to executed SQL queries, and include query parameters and results.

Write the following code in your application’s configuration file to log XPO SQL commands. XPO creates a text file (trace.log) in the application directory. This file contains all SQL queries.

XML

<?xml version="1.0" encoding="utf-8" ?>  
<configuration>  
    <system.diagnostics>  
        <trace autoflush="true" indentsize="4">  
            <listeners>  
                <add name="LogFileTraceListener" type="System.Diagnostics.TextWriterTraceListener"  
                    initializeData="trace.log" />  
                <remove name="Default" />  
            </listeners>  
        </trace>  
        <switches>  
            <add name="XPO" value="3" />  
        </switches>  
    </system.diagnostics>  
</configuration>

XPO utilizes the standard System.Diagnostics trace logging mechanism. You can also create your own TraceListener class and log queries into a database or text box.

C#

System.Diagnostics.Trace.Listeners.Add(new MyTraceListner(textBox1));  

class MyTraceListner : System.Diagnostics.TraceListener {  
    TextBox outputWindow;  
    public MyTraceListner(TextBox outputWindow) {  
        this.outputWindow = outputWindow;  
    }  
    public override void Write(string message) {  
        outputWindow.AppendText(message);  
    }  
    public override void WriteLine(string message) {  
        outputWindow.AppendText(message + "\r\n");  
    }  
} 

VB.NET

Imports Microsoft.VisualBasic

System.Diagnostics.Trace.Listeners.Add(New MyTraceListner(textBox1))

class MyTraceListner : System.Diagnostics.TraceListener
    Dim outputWindow As TextBox
    public MyTraceListner(TextBox outputWindow)
        Me.outputWindow = outputWindow
    public override void Write(String message)
        outputWindow.AppendText(message)
    public override void WriteLine(String message)
        outputWindow.AppendText(message & ControlChars.CrLf)

XPO Logger (.NET 6)

Implement a custom ILogger class. Use the static DevExpress.Xpo.Logger.LogManager.SetTransport method to register the custom logger class.

C#

using DevExpress.Xpo.Logger;
using System;

namespace ConsoleApp1 {
    class Program {
        static void Main(string[] args) {
            // Registers your custom logger.
            DevExpress.Xpo.Logger.LogManager.SetTransport(new XpoConsoleLogger());
        }
    }
    class XpoConsoleLogger : DevExpress.Xpo.Logger.ILogger {
        public int Count => 0;
        public int LostMessageCount => 0;
        public bool IsServerActive => true;
        public bool Enabled { get; set; } = true;
        public int Capacity => 0;
        public void ClearLog() { }

        public void Log(LogMessage message) {
            // Implement your custom logic (for instance, to output messages in Console)
            if(Enabled) {
                Console.WriteLine(message.ToString());
            }
        }

        public void Log(LogMessage[] messages) {
            foreach(var msg in messages) {
                Log(msg);
            }
        }
    }
}

VB.NET

Imports DevExpress.Xpo.Logger
Imports System

Namespace ConsoleApp1
    Friend Class Program
        Shared Sub Main(ByVal args() As String)
            ' Registers your logger.
            DevExpress.Xpo.Logger.LogManager.SetTransport(New XpoConsoleLogger())
        End Sub
    End Class
    Friend Class XpoConsoleLogger
        Implements DevExpress.Xpo.Logger.ILogger

        Public Count As Function(Integer) 0
        Public LostMessageCount As Function(Integer) 0
        Public IsServerActive As Function(Boolean) True
        Public Property Enabled() As Boolean
        = True
        public Integer Function(Capacity) 0
        public void ClearLog()

        public void Log(LogMessage message)
            If Enabled Then
                Console.WriteLine(message.ToString())
            End If

        public void Log(LogMessage() messages)
            For Each msg In messages
                Log(msg)
            Next msg
    End Class
End Namespace