Writting a SQL Profiler

In case you're wondering how to write a SQL Profiler to automate some of your task, here are some code that you might want to try out :)


Add reference to Microsoft.SqlServer.ConnectionInfo in your project and you are good to go!


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

using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;

using System.IO;
using System.Configuration;

namespace MSSQLProfiler
{

public class MSSqlTracer
{

ConnectionInfoBase conninfo = null;

public string TemplateTraceFile { get; set; }
public string TraceOutputFile { get; set; }


public void StartTrace(string TemplateFileSource, string OutputFileName)
{

TemplateTraceFile = TemplateFileSource;
TraceOutputFile = OutputFileName;

Initialization();

/// Perform Database Tracing ////

RunTracing();


}

private void RunTracing()
{

TraceServer tracer = new TraceServer();
tracer.InitializeAsReader(conninfo, TemplateTraceFile);
string BufferStr = "";

StreamWriter OutWriter = new StreamWriter(TraceOutputFile + ".trace", true);

while (tracer.Read())
{
if (tracer.IsDBNull(1))
continue;

BufferStr = tracer["TextData"].ToString();
Console.WriteLine(BufferStr);
OutWriter.Flush();
OutWriter.WriteLine(BufferStr);

}


OutWriter.Close();
}

private void Initialization()
{

SetupConnection();
}

private void SetupConnection()
{
conninfo = new SqlConnectionInfo();
((SqlConnectionInfo)conninfo).ServerName = ConfigurationManager.AppSettings["ServerName"];
((SqlConnectionInfo)conninfo).UserName = ConfigurationManager.AppSettings["Username"];
((SqlConnectionInfo)conninfo).Password = ConfigurationManager.AppSettings["Password"];
((SqlConnectionInfo)conninfo).UseIntegratedSecurity = false;

}

}
}