Read SQL Server Trace file using C# in VS 2010

DBAs love using the function fn_trace_gettable  to read  contents from a  trace file . Just pass the path of the trace file to this function and it will show the content of trace file.But what if developers want to read the same using .NET framework.You  can’t have the luxury of having SQL Server installed in every machine so that you can execute fn_trace_gettable. This function can’t be executed remotely using a linked server.

First thing what you need to do is open VS 2010 and give reference to SQL Server SMO  assembly Microsoft.SqlServer.ConnectionInfoExtended.dll and Microsoft.SqlServer.ConnectionInfo.dll

Once you have reference to these dll’s in your project then you can use  an object of  TraceFile class to read the content of TraceFile.

//Reads the trace file 

TraceFile trcFile = new TraceFile();
 
trcFile.InitializeAsReader(strFileName);

while (trcFile.Read())
{                   
     string strTextData = trcFile.GetString(trcFile.GetOrdinal("TextData")).ToString();

     string  strApplication=trcFile.GetString(trcFile.GetOrdinal("ApplicationName")).ToString();

}   

trcFile.Close();

But before reading these values you may need to check if these columns are null by using the code

trcFile.IsDBNull(trcFile.GetOrdinal("TextData"));

Once you have these values you can load it into a SQL Server remote database and then create reports based on these values.Please don’t forget to include Microsoft.SqlServer.Management.Trace namespace in your code.