Tracing SQL Server from .NET

Tracing facilities in SQL Server 2005 have been improved with the development of a new API designed to provide SQL Server Profiler functionality. I have been recently working on a prototype application for one of our customers designed to trace every operation performed on a SQL Server instance from a .NET application.

The new SQL Server Profiler API is called SQL-SMO (SQL Server Management Objects) and you can do anything that Profiler does but on a custom .NET application (i.e. creating traces, filters, reading/writing trace files, etc.). SQL-SMO is the evolution of the previous SQL-DMO (SQL Server Distributed Management Objects) and preserves full compatibility with older SQL Server versions such as 2000 and 7. You can read more about this new technology in MSDN.

So, let's do some coding now and try to get SQL sentences from the database…

In order to try the following code samples you will need to add some DLL references to your application

• Microsoft.SqlServer.Smo (located at [Microsoft SQL Server Installation Folder]\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll)
  
• Microsoft.SqlServer.ConnectionInfo (located at [Microsoft SQL Server Installation Folder]\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll)

//
// Create a SQL Server Connection
//
SqlConnectionInfo connectionInfo = new SqlConnectionInfo("localhost");
connectionInfo.UseIntegratedSecurity = true;

//
// Create a Server Data Reader to get Tracing Entries Database Engine
//
TraceServer reader = new TraceServer();
string tracingTemplateFilename = @"C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Microsoft SQL Server\90\Standard.tdf";

reader.InitializeAsReader(
connectionInfo, // connection to server
tracingTemplateFilename /* tracing template used */);

while(reader.Read()) // reader blocks if tracing entries are not available
{
   string eventType = reader.GetValue(0).ToString();
   string entry = reader.IsDBNull(1) ? string.Empty : reader.GetValue(1).ToString();

   Console.WriteLine("{0} : {1}", eventType, entry);
}

The code sample creates a new TraceServer reader object (it implements the IDataReader interface) to get information from the specified server (localhost in the previous sample). An important thing to notice is the need to provide a SQL Server Profiler Tracing Template in order to get data (in this sample I am providing the Standard template that ships with the product, but of course, you can provide your own templates).

Now, try the previous code with a SQL Server Management Studio Console opened on any given database and see what happens:

I will be posting a downloading sample with more features next week.