Capture SQL Server Queries Fired from your Application

This article describes how to create a handy tool to capture the SQL Queries, stored procedures and functions that are fired from your application. This will help developers in diagnosing the code related issues.

• Source code is available here
• Tool Setup file is available here

Introduction

As we all know, the SQL Server Profiler is one of the powerful tools that helps developers and DBAs in troubleshooting issues. But from a developer point of view, mostly they work on a specific application and want to see what queries/procs/functions are getting fired on database along with parameters and their values from an application. I am one of them and created a small handy tool that helps in my work. I thought of sharing this experience so that it might help someone.

Note: This tool is not a replacement for SQL Server profiler. It is just a simple tool which is handy to analyze code related issues.

Using the Code

Before going into the coding part, first we need a .tdf (Trace Definition File) file. It is a template file that contains the field related information that gets captured from SQL Server.

We can create a new .tdf file by starting a new SQL Server Profiler session (From SSMS > Tools > Sql Server Profiler Menu). File > Templates > New Template.

Add these two references in your project:

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.ConnectionInfoExtended.dll

You can get them from Nuget’s SMO package or from the below SQL Server default installed location (blue color highlighted number will change according to your SQL Server version).

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\

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

Below is the core function that takes care of tracing the SQL Server. There are two important classes that we deal with. They are SqlConnectionInfo and TraceServer.

SqlConnectionInfo instance is used to hold the server related information with credentials. TraceServer instance is used to read the trace information logged by target SQL server.

private void Start(object sender, DoWorkEventArgs e)
        {            
            SqlConnectionInfo conn = new SqlConnectionInfo();
            conn.ServerName = txtServer.Text;
            conn.DatabaseName = txtDB.Text;
            conn.UserName = txtUser.Text;
            conn.Password = txtPwd.Text;
            conn.UseIntegratedSecurity = checkIS.Checked;
            ConnectionInfoBase conninfo = conn;

            string appName = txtApp.Text;

            TraceServer trace = new TraceServer();

            try
            {
                trace.InitializeAsReader(conninfo, @"SqlTrace.tdf");
                RunningStatus(true);
                while (trace.Read())
                {
                    if (backgroundWorker1.CancellationPending)
                    {
                        RunningStatus(false);
                        e.Cancel = true;
                        break;
                    }

                    string eventClass = trace["EventClass"].ToString();
                    if (eventClass.Equals("RPC:Completed") || 
                    eventClass.Equals("SQL:BatchCompleted"))
                    {
                        string traceAppName = trace["ApplicationName"].ToString();
                        if (appName.Equals(traceAppName, StringComparison.InvariantCultureIgnoreCase))
                        {
                            string query = "Event Class: " + eventClass + 
                            "\n" + trace["TextData"].ToString();
                            if (!query.Contains("network protocol"))
                            {
                                if (this.InvokeRequired)
                                {
                                    this.Invoke(new Action((msg) =>
                                    {
                                        rtbLog.AppendText("\n" + msg + "\n");
                                    }), query);
                                }
                            }
                        }
                    }               
                }
            }
            catch (Exception exc)
            {
                string message = exc.Message;
                if (exc.InnerException != null)
                    message += ",\n" + exc.InnerException.Message;
                MessageBox.Show("Error: " + exc.Message);
            }
        }

In the above method, trace.InitializeAsReader(connInfo, “SqlTrace.tdf”) statement should be inside a try catch block because it throws exceptions if the provided connection information is wrong or the credentials doesn’t have enough permissions to read the trace information or the provided .tdf file doesn’t have the fields that the program is trying to access. For example, if we are trying to read trace[“ApplicationName”] but while creating template file, we have not selected ApplicationName then it throws an error. So, we should be careful in handling this statement.

Note: Here, the application name is not the one that is displayed on your application. It is the name that you specified in your application’s connection string. Example connection string with application name is as follows:

Server='myServerAddress';Database='myDataBase';User Id='myUsername';
Password='myPassword';Application Name='MyApplication';

Here is the complete code behind of a Windows Form for your reference.

using App2SqlTrace.Properties;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;
using System;
using System.ComponentModel;
using System.Windows.Forms;

namespace App2SqlTrace
{
    public partial class App2Sql : Form
    {
        public App2Sql()
        {
            InitializeComponent();
        }

        private void btnStart_Click(object sender, EventArgs e)
        {            
            backgroundWorker1.DoWork += Start;
            backgroundWorker1.RunWorkerAsync();
        }

        private void Start(object sender, DoWorkEventArgs e)
        {            
            SqlConnectionInfo conn = new SqlConnectionInfo();
            conn.ServerName = txtServer.Text;
            conn.DatabaseName = txtDB.Text;
            conn.UserName = txtUser.Text;
            conn.Password = txtPwd.Text;
            conn.UseIntegratedSecurity = checkIS.Checked;
            ConnectionInfoBase conninfo = conn;

            string appName = txtApp.Text;

            TraceServer trace = new TraceServer();

            try
            {
                trace.InitializeAsReader(conninfo, @"SqlTrace.tdf");
                RunningStatus(true);
                while (trace.Read())
                {
                    if (backgroundWorker1.CancellationPending)
                    {
                        RunningStatus(false);
                        e.Cancel = true;
                        break;
                    }

                    string eventClass = trace["EventClass"].ToString();
                    if (eventClass.Equals("RPC:Completed") || 
                    eventClass.Equals("SQL:BatchCompleted"))
                    {
                        string traceAppName = trace["ApplicationName"].ToString();
                        if (appName.Equals(traceAppName, StringComparison.InvariantCultureIgnoreCase))
                        {
                            string query = "Event Class: " + eventClass + 
                            "\n" + trace["TextData"].ToString();
                            if (!query.Contains("network protocol"))
                            {
                                if (this.InvokeRequired)
                                {
                                    this.Invoke(new Action((msg) =>
                                    {
                                        rtbLog.AppendText("\n" + msg + "\n");
                                    }), query);
                                }
                            }
                        }
                    }               
                }
            }
            catch (Exception exc)
            {
                string message = exc.Message;
                if (exc.InnerException != null)
                    message += ",\n" + exc.InnerException.Message;
                MessageBox.Show("Error: " + exc.Message);
            }
        }

        private void btnStop_Click(object sender, EventArgs e)
        {
            try
            {                
                backgroundWorker1.CancelAsync();       
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error: " + exc.Message);
            }
        }

        private void RunningStatus(bool state)
        {
            if (this.InvokeRequired)
            {
                this.BeginInvoke(new Action((st) =>
                    {
                        if (st) rtbLog.AppendText("Tracing started\n");
                        else rtbLog.AppendText("\nTracing stopped\n");
                        btnStart.Enabled = !st;
                        btnStop.Enabled = st;
                    }), state);
            }            
        }

        private void saveLogToolStripMenuItem_Click(object sender, EventArgs e)
        {
            using (SaveFileDialog save = new SaveFileDialog())
            {
                // Default file extension
                save.DefaultExt = "rtf";
                // SaveFileDialog title
                save.Title = "Save File As";
                // Available file extensions
                save.Filter = "RTF Files (*.rtf)|*.rtf";
                // Show SaveFileDialog box and save file
                if (save.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    rtbLog.SaveFile(save.FileName, RichTextBoxStreamType.RichText);
                }
            }
        }

        private void clearLogToolStripMenuItem_Click(object sender, EventArgs e)
        {
            rtbLog.Text = string.Empty;
        }

        private void App2Sql_Load(object sender, EventArgs e)
        {
            txtServer.Text = Settings.Default.Server;
            txtDB.Text = Settings.Default.Database;
            txtUser.Text = Settings.Default.User;
            txtPwd.Text = Settings.Default.Password;
            txtApp.Text = Settings.Default.AppName;
            checkIS.Checked = Settings.Default.UseIntSec;

            btnStop.Enabled = false;
        }

        private void App2Sql_FormClosing(object sender, FormClosingEventArgs e)
        {
            Settings.Default.Server = txtServer.Text;
            Settings.Default.Database = txtDB.Text;
            Settings.Default.User = txtUser.Text;
            Settings.Default.Password = txtPwd.Text;
            Settings.Default.AppName = txtApp.Text;
            Settings.Default.UseIntSec = checkIS.Checked;
            Settings.Default.Save();
        }
    }    
}

CodeProject