Reading a Trace File using C# in SQL Server 2005By 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 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();
myTraceFile.InitializeAsReader(@"D:\TraceFile.trc"); We're calling the 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 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 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 Sourceusing 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."); } } } |
- Advertisement - |