Reading a Trace File using C# in SQL Server 2005

By Bill Graziano on 25 October 2004 | Tags: .NET , SMO , Profiler & Trace

SQL Server 2005 includes Server Managed Objects (SMO) which is a managed API to SQL Server. These are primarily used to administrative tasks and replaces DMO. One of the features of this is the ability to programmatically read trace files. This article walks through the code needed to read a trace file.

This article uses SQL Server 2005 Beta 2 and Visual Studio 2005 Beta 1. The first step is to create a new C# console project. I called mine TraceFileReader. Next I added reference to oneof the SMO DLLs. Right click the project and choose Add Reference. The file is located in D:\Program Files\Microsoft SQL Server\90\SDK\Assemblies and it's called Microsoft.SqlServer.ConnectionInfo.dll. This DLL contains the code we'll need to read the trace file. It should now be listed in the References section of the project.

Now we need a using reference to the namespace we're going to need. The code for reading trace files is in Microsoft.SqlServer.Management.Trace. So add the following line with the other using statements:

using Microsoft.SqlServer.Management.Trace;

This tells the application we plan to use the objects defined within this area of the DLL.

Before we can open the trace file we'll need two variables to hold the values we're going to read. The following code declares two string variables for this.

string textData;
string eventClass;

Now we need to add code to open the trace file. First we'll declare a variable to hold the trace file. That code looks like this:

TraceFile myTraceFile = new TraceFile();

TraceFile is the type of object we're declaring and I named the variable myTraceFile. Next we'll open the actual trace file.

myTraceFile.InitializeAsReader(@"D:\TraceFile.trc");

We're calling the InitializeAsReader method and passing it the name of our trace file. The "@" means to treat the string as a literal so the backslash isn't an escape character. After calling this method our TraceFile object (myTraceFile) is going to act like a TraceReader object. The TraceReader object has it's own methods and properties that I'll discuss in a second. At this point the code should run and open the trace file.

The TraceFile object also supports two other methods. These are InitializeAsReplayOutputWriter and InitializeAsWriter. The second method make it possible to write a trace file yourself and the first I haven't explored yet.

The TraceReader object implements a couple of interfaces that make our life much easier. The two we care about are IDataReader and IDataRecord. These make the TraceReader act just like a record set from the database. We can use the Read method to read each trace event just like we would read records from a database. We'll put this code inside a while loop to read each record. That code looks like this:

while (myTraceFile.Read())
{
}

myTraceFile.Close();

The Read method will return true as long as there are records remaining to read. I also added a line after the while loop to close the trace file. Now add the following code inside the while loop:

eventClass = myTraceFile.GetString(myTraceFile.GetOrdinal("EventClass"));
textData = myTraceFile.GetString(myTraceFile.GetOrdinal("TextData"));


Console.WriteLine("===========================================");
Console.WriteLine("EventClass: " + eventClass);
Console.WriteLine();
Console.WriteLine(textData);
Console.WriteLine();

We call the GetString method on myTraceFile to get the contents of a particular column in this record. The GetString method accepts a single integer which determines which column is returned. In my case I'm passing it the result of the GetOrdinal method of myTraceFile. This method accepts a column name as a parameter and returns the number of the column. Next I write out the results and are shown below. The complete program is shown following the output. Please be aware that this runs very slowly when run inside Visual Studio so experiment with a small trace file.

Starting...
===========================================
EventClass: Trace Start


===========================================
EventClass: ExistingConnection

-- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

===========================================
EventClass: SQL:BatchCompleted

SELECT	*
FROM	dbo.titles
===========================================
EventClass: SQL:BatchCompleted

SELECT	*
FROM	dbo.authors
===========================================
EventClass: Trace Stop


===========================================
Done.

Program Source

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.SqlServer.Management.Trace;

namespace TraceFileReader
{
    class Program
    {
        static void Main(string[] args)
        {

            Console.WriteLine("Starting...");

            string textData;
            string eventClass;

            // Create a TraceFile object
            TraceFile myTraceFile = new TraceFile();

            // Open the trace file as a reader
            myTraceFile.InitializeAsReader(@"D:\TraceFile.trc");

            while (myTraceFile.Read())
            {
                // textData = String.Empty;
                eventClass = myTraceFile.GetString(myTraceFile.GetOrdinal("EventClass"));
                textData = myTraceFile.GetString(myTraceFile.GetOrdinal("TextData"));


                Console.WriteLine("===========================================");
                Console.WriteLine("EventClass: " + eventClass);
                Console.WriteLine();
                Console.WriteLine(textData);
                Console.WriteLine();

            }

            Console.WriteLine("===========================================");
            myTraceFile.Close();

            Console.WriteLine("Done.");

        }
    }
}