Microsoft SQL Server

Parsing Microsoft SQL Profiler Trace XML using *DynamicXmlStream*

This article will show how compact syntax is extracting SQL statements from SQL Profiler Trace (Microsoft SQL Server) using Mahesh DyanmicXmlStream – which is *dynamic* (http://www.codeproject.com/Articles/436406/Power-of-Dynamic-Reading-XML-and-CSV-files-made-ea)

And the code to parse it (Don’t get more compact than this!)

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using LearningMahesh.DynamicIOStream;
using LearningMahesh.DynamicIOStream.Xml;

static void Main(string[] args)
        {
            string TraceFileName = null;
            string SQLStatement = null;
            IList SQLStatements = null;

                if (args != null)
                {
                    TraceFileName = args[0];
                }

                #region STEP 1. Extract SQL from profiler trace
                dynamic profilerReader = DynamicXmlStream.Load(new FileStream(TraceFileName, FileMode.Open));

                SQLStatements = new List();

                foreach (
                     dynamic Event in 
                        (profilerReader.TraceData.Events.Event 
                            as DynamicXmlStream).AsDynamicEnumerable()
                            .Where(Event => Event.name.Value =="SQL:BatchStarting")
                    )
                {
                    foreach (dynamic Column in 
                        (Event.Column as DynamicXmlStream).AsDynamicEnumerable()
                        .Where(Column => Column.name.Value == "TextData")
                        )
                    {
                        SQLStatement = Column.Value;
                        SQLStatements.Add(SQLStatement);
                        Console.WriteLine(SQLStatement);
                    }
                }
                
            return;
        }
Advertisements