using System;
using System.Data.SqlClient;

namespace UnitTestFrameWork
{
public class GetQueryPlan
{
public string GetQueryPlanForQuery(string conn, string batch)
{
SqlConnection _conn = new SqlConnection(conn);
_conn.Open();
SqlCommand cmd = _conn.CreateCommand();

cmd.CommandText = "SET SHOWPLAN_XML ON";
cmd.ExecuteNonQuery();
cmd.CommandText = batch;
try
{
String QueryPlan = String.Empty;
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read()) QueryPlan += sdr.GetSqlString(0).ToString();
sdr.Close();
cmd.CommandText = "SET SHOWPLAN_XML OFF";
cmd.ExecuteNonQuery();
return QueryPlan;
}

catch (SqlException)
{
string e = "Query plan could not be returned.";
return e;
}
}
}
}

using System;
using System.Xml;
using System.Xml.XPath;
using System.IO;

namespace UnitTestFrameWork
{
public class GetQueryPlanValues
{

public XPathNodeIterator GetXpathIterator(string QueryPlan, string SqlNamespace, string xpath)
{
//read query plan to tring, then xml reader
StringReader strReader = new StringReader(QueryPlan);
XmlTextReader xreader = new XmlTextReader(strReader);

//xpath document from the xreader, preserving whitespace,
//then a nvaigator so that we can search using xpath
XPathDocument doc = new XPathDocument(xreader, XmlSpace.Preserve);
XPathNavigator navigator = doc.CreateNavigator();

//define namespace
XmlNamespaceManager nsmgr = new XmlNamespaceManager(navigator.NameTable);
nsmgr.AddNamespace("sql", SqlNamespace);

//create xpath expression to search
XPathExpression xpression;
xpression = navigator.Compile(xpath);
//set namespace in the expression
xpression.SetContext(nsmgr);
//iterate over the nodes
XPathNodeIterator iterator = navigator.Select(xpression);
return iterator;
}


public string GetSumOfValues(string QueryPlan, string SqlNamespace, string xpath)
{
try
{

string _QueryPlan = QueryPlan;
string _SqlNameSpace = SqlNamespace;
string _xpath = xpath;

XPathNodeIterator i = GetXpathIterator(_QueryPlan, _SqlNameSpace, _xpath);

//get total value of each of the expressions we are looking for in the node
Single TotalValue = 0;
while (i.MoveNext()) TotalValue += Single.Parse(i.Current.Value);
string ret = String.Empty;
ret = Convert.ToString(TotalValue);
return ret;
}
catch (Exception e)
{
string error = e.Message;
return "-1";
}
}

public string GetSingleValue(string QueryPlan, string SqlNamespace, string xpath)
{
try
{
string _QueryPlan = QueryPlan;
string _SqlNameSpace = SqlNamespace;
string _xpath = xpath;

XPathNodeIterator i = GetXpathIterator(_QueryPlan, _SqlNameSpace, _xpath);

string ret = String.Empty;
while (i.MoveNext()) ret = i.Current.Value;
return ret;
}
catch (Exception e)
{
string error = e.Message;
return "-1";
}
}
}
}

USE NORTHWND
GO

SELECT * FROM dbo.Categories c
GO

SELECT c.CategoryName, s.CompanyName, s.ContactName, s.Phone, p.ProductName FROM dbo.[Order Details] od
INNER JOIN dbo.Products p ON p.ProductID = od.ProductID
INNER JOIN dbo.Suppliers s  ON s.SupplierID = p.SupplierID
INNER JOIN  dbo.Categories c  ON  c.CategoryID = p.CategoryID
WHERE  c.CategoryName  = 'Condiments'
AND p.Discontinued  = 0
GROUP BY c.CategoryName, s.CompanyName, s.ContactName, p.ProductName,s.Phone

using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using QueryPlanFrameWork;

namespace DemoUnitTest
{
  [TestClass]
  public class RunTest
  {
    [TestMethod]
    public void RunTestScriptTestMethod()
    {
      GetQueryPlan qp = new GetQueryPlan();
      GetQueryPlanValues qv = new GetQueryPlanValues();

      string constring_1 = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NORTHWND;Data Source=.";
      string fileContent = File.ReadAllText(@"..\\..\\TestScript\\TestScript.sql");
      string[] batches = Regex.Split(fileContent, "GO");
      string SqlNamespace = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
      string XPath = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan/sql:RelOp/@PhysicalOp";
      string XPath_2 = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan/sql:RelOp//sql:StreamAggregate/sql:RelOp/sql:NestedLoops/sql:RelOp[1]/sql:Sort/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp[1]/@PhysicalOp";
      string XPath_3 = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan/sql:RelOp//sql:StreamAggregate/sql:RelOp/sql:NestedLoops/sql:RelOp[1]/sql:Sort/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp[2]/@PhysicalOp";
      string XPath_4 = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan/sql:RelOp//sql:StreamAggregate/sql:RelOp/sql:NestedLoops/sql:RelOp[1]/sql:Sort/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp/@EstimatedTotalSubtreeCost";

      for (int i = 0; i < batches.Length; i++)
      {
        string _conn = constring_1;
        string batch = batches[i];
        SqlDataAdapter da = new SqlDataAdapter(batch, _conn);
        DataSet ds = new DataSet();

        try
        {
          da.Fill(ds);
        }
        catch (Exception e)
        {
          string Error = e.Message;
        }
        da.Dispose();

        DataTable dt = ds.Tables["Table"];

        if (i == 2)
        {
          string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
          string _operator = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath);
          Assert.AreEqual("Stream Aggregate", _operator);
          string _operator_2 = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath_2);
          Assert.AreEqual("Index Seek", _operator_2);
          string _operator_3 = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath_3);
          Assert.AreEqual("Index Seek", _operator_3);
          string _operator_4 = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPath_4);
          Assert.IsTrue(0.01 > Convert.ToSingle(_operator_4));
        }
      }
    }
  }
}