How to Log SQL Queries
- 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.
<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 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.
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");
}
}
XPO Logger (.NET 6)
Implement a custom ILogger class. Use the static DevExpress.Xpo.Logger.LogManager.SetTransport method to register the custom logger class.
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);
}
}
}
}