Community & Support
Home / Community & Support / Knowledge Base / Telerik OpenAccess ORM / General / Tracing the SQL Statements Generated by Telerik OpenAccess ORM

Tracing the SQL Statements Generated by Telerik OpenAccess ORM

Article Info

Rating: Not rated

Article information

Article relates to

 Telerik OpenAccess ORM

Created by

 Alexander Filipov

Last modified

 July 17, 2009

Last modified by

 Alexander Filipov

DESCRIPTION
Telerik OpenAccess ORM has an integrated mechanism for tracing and logging the communication between the application and the database server. This includes tracing of the generated SQL statements. This article describes the different ways to use this functionality.

SOLUTION
There are three available approaches for observing the events that can be used in different cases:
  • At runtime, using the TraceAdapter class;
  • Writing to the Output console while debugging;
  • Writing to a text file.
Backend Configuration

First of all, to enable the tracing, open the Backend Configuration dialog from the Telerik -> OpenAccess -> Configuration menu. Then scroll to the Logging node and enable the Event tracing option. Another setting that is common for all tracing approaches is the Log level. It determines how detailed the log will be. The available logging levels are as follows:
  • none (default value): Logs no events. Use this only while doing benchmarking.
  • errors: Logs only serious errors (such as connections timing out)
  • normal: Logs the most useful events that do not reduce performance significantly (e.g. SQL executed)
  • verbose: Logs lots of events (this slows down the performance, could be used while debugging)
  • all: Logs all possible events (this slows down the performance very significantly, could be used while debugging)
If you want to trace the SQL statements, the normal level will be enough.

Approach 1:
The API of Telerik OpenAccess ORM provides the TraceAdapter class which can be used to trace events dynamically and process them in the code. To use the class, another “Listener” class is required. In the sample project related to this article a custom OpenAccessTracer class is implemented. It derives from System.Diagnostics.TraceListener and overrides some of its methods. In this case, the tracer class writes the output messages to a TextBox control and counts the executed SQL statements.
The important step here is to add the tracer to the Listeners collection of the singleton Telerik.OpenAccess.Diagnostics.TraceAdapter instance. This can be done in the constructor of the class.

public class OpenAccessTracer : TraceListener 
{ 
    int id; 
    int count; 
    TextBox textBox; 
    Thread myThread; 
 
    public OpenAccessTracer(TextBox textBox) 
        : base("Telerik OpenAccess ORM") 
    { 
 
        myThread = Thread.CurrentThread; 
 
        Telerik.OpenAccess.Diagnostics.TraceAdapter.Instance.Level = "4"; 
        id = Telerik.OpenAccess.Diagnostics.TraceAdapter.Instance.Listeners.Add(this); 
        this.textBox = textBox; 
        count = 0; 
    } 
 
    protected override void Dispose(bool disposing) 
    { 
        this.textBox.Text += count.ToString() + " queries executed."; 
        Telerik.OpenAccess.Diagnostics.TraceAdapter.Instance.Listeners.RemoveAt(id); 
        base.Dispose(disposing); 
    } 
 
    internal void Reset() 
    { 
        this.textBox.Text = ""; 
        this.count = 0; 
    } 
 
    public override void Write(string message) 
    { 
    } 
 
    public override void WriteLine(string message) 
    { 
        if (!myThread.Equals(Thread.CurrentThread)) 
            return; 
 
        if (message.StartsWith("driver.stat.exec")) 
        { 
            count++; 
            int index; 
            if(message.IndexOf("SELECT") >= 0)  
                index = message.IndexOf("SELECT"); 
            else  
                index = message.IndexOf("INSERT"); 
 
            this.textBox.Text += string.Format("{0}: {1}\r\n", count.ToString(), message.ToString().Substring(index)); 
            this.textBox.SelectionStart = this.textBox.Text.Length; 
            this.textBox.SelectionLength = 0; 
            this.textBox.ScrollToCaret(); 
        } 
    } 
} 
The WriteLine() method filters the messages and prints only the SQL statements. They can be easily recognized because they start with the "driver.stat.exec" string. To use the custom tracer class, just create an instance and initialize it.

Approach 2:
To enable logging to the console, set the Write Log Output to Console option to True. Then while debugging the application the events will be shown in the Output window of Visual Studio.

Approach 3:
To log the events to a text file, open the Backend Configuration dialog, enable the Write events to text file option and specify the file name. The “.txt” extension will be added automatically. Note that the path specified in the file name can be either absolute or relative to the path of the application.

The demo application related to this article uses all the three methods described above simultaneously. The full source code is available for download in our Code library.

Comments

There are no comments yet.
If you'd like to comment on this KB article, please, send us a Support Ticket.
Thank you!

Please Sign In to rate this article.