附录 A：“ShowplanXPath.cs”的代码清单（解决方案 1）

using System;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.XPath;
public class xmlshowplanaccess
{
public static void GetXMLShowplan(string tsqlStmt, ref string tsqlStmtCost)
{
// tsqlStmt contains the query whose cost needs to be calculated
// tsqlStmtCost will contain the tsqlStmt's cost
// Open a connection and create a command
SqlConnection conn = new SqlConnection("context connection = true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "set showplan_xml on";
cmd.ExecuteNonQuery();  // turns showplan_xml mode on
cmd.CommandText = tsqlStmt;
try {
// thePlan will contain the showplan in XMLformat
String thePlan = String.Empty;
SqlDataReader sdr = cmd.ExecuteReader();
// In case the result set is chunked, we concatenate
while (sdr.Read()) thePlan += sdr.GetSqlString(0).ToString();
sdr.Close();
cmd.CommandText = "set showplan_xml off";
cmd.ExecuteNonQuery();  // turns showplan_xml mode off
// Now the showplan in XMLformat is contained in thePlan.
// We shall now evaluate an XPathexpression against the showplan.
StringReader strReader = new StringReader(thePlan);
System.Xml.XmlTextReader xreader =
new System.Xml.XmlTextReader(strReader);
XPathDocument doc = new XPathDocument(xreader, XmlSpace.Preserve);
System.Xml.XPath.XPathNavigator navigator = doc.CreateNavigator();
XmlNamespaceManager nsmgr = new XmlNamespaceManager(navigator.NameTable);
nsmgr.AddNamespace("sql", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");
// The exact namespace will depend on the showplan's version.
// Please modify the year and month appropriately.
XPathExpression xpression;
// The XPaththat points to the estimated execution cost of the query
xpression =
navigator.Compile("//sql:Batch/sql:Statements/sql:StmtSimple/"
+ "sql:QueryPlan[1]/sql:RelOp[1]/@EstimatedTotalSubtreeCost");
xpression.SetContext(nsmgr);
XPathNodeIterator iterator = navigator.Select(xpression);
String val = String.Empty;
System.Single totalCost = 0;
// sum costs of all query plans in this batch
while(iterator.MoveNext()) totalCost += Single.Parse(iterator.Current.Value);
tsqlStmtCost = totalCost.ToString();  // set the return value
} catch (SqlException) { // return -1 if there are any errors in SQL code
tsqlStmtCost = "-1";
}
}  // GetXMLShowplan ends
}  // xmlshowplanaccess class ends

附录 B：“ReturnShowplanXML.cs”的代码清单（解决方案 2）

using System;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
public class xmlshowplanaccess
{
public static void GetXMLShowplan(string tsqlStmt, ref string tsqlStmtShowplan)
{
// tsqlStmt contains the statement whose showplan needs to be returned
// tsqlStmtShowplan will return the showplan of tsqlStmt in XMLformat
// Open a connection and create a command
SqlConnection conn = new SqlConnection("context connection = true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "set showplan_xml on";
cmd.ExecuteNonQuery();  // turn the showplan_xml mode on
cmd.CommandText = tsqlStmt;
try
{
// thePlan will contain the showplan in XMLformat
String thePlan = String.Empty;
SqlDataReader sdr = cmd.ExecuteReader();
// In case the result set is chunked, concatenate
while (sdr.Read())
thePlan += sdr.GetSqlString(0).ToString();
sdr.Close();
cmd.CommandText = "set showplan_xml off" ;
cmd.ExecuteNonQuery();  // turn the showplan_xml mode off
tsqlStmtShowplan = thePlan;  // return the showplan in XMLformat
}
catch (SqlException e) // return well formed xml document with the text of exception
{
tsqlStmtShowplan = "" + e.ToString() + "";
}
}  // GetXMLShowplan ends
}  // xmlshowplanaccess ends