in sqlnexus/fmNexus.cs [1180:1315]
public static string GetQueryText(string filename, ReportParameterInfoCollection paramc, string datasetname)
{
XmlDocument doc = new XmlDocument();
doc.Load(filename);
XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
//nsmgr.AddNamespace("rds", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition");
String strNameSpace = ReportUtil.GetReportNameSpace(doc);
if (strNameSpace != null)
{
nsmgr.AddNamespace("rds", strNameSpace);
}
else
{
nsmgr.AddNamespace("rds", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition");
}
XmlNode qrynode = doc.DocumentElement.SelectSingleNode("//rds:Report/rds:DataSets/rds:DataSet[@Name = '" + datasetname + "']/rds:Query", nsmgr);
if (null == qrynode)
return "";
XmlNode cmdTextNode = qrynode.SelectSingleNode("rds:CommandText", nsmgr);
if (null == cmdTextNode)
return "";
string qrytext = cmdTextNode.InnerText;
XmlNodeList qparams = qrynode.SelectNodes("rds:QueryParameters/rds:QueryParameter", nsmgr);
// e.g. <QueryParameter Name="@Filter3"> <Value>=Parameters!Filter3.Value</Value> </QueryParameter>
// A report may contain a query like this: "EXEC proc @Filter1, @Filter1Name". The result of this could
// be "EXEC proc 'filter1val', 'filter1val'Name", which will cause a syntax error. To avoid this problem,
// sort the parameters in descending order of parameter name length. In the above example, we avoid the
// problem by searching and replacing "@Filter1Name" first.
ArrayList arrayXmlNodes = new ArrayList();
foreach (XmlNode node in qparams)
{
arrayXmlNodes.Add(node);
}
arrayXmlNodes.Sort(new CompareXmlNodeNameAttrLength());
// For each query parameter
foreach (XmlNode n in arrayXmlNodes)
{
XmlNode qparamval = n.SelectSingleNode("rds:Value", nsmgr);
string paramname, qpname;
if ((null != qparamval) && (0 == qparamval.InnerText.IndexOf(PARAM_TOKEN, StringComparison.InvariantCultureIgnoreCase)))
{
string[] parts = qparamval.InnerText.Substring(PARAM_TOKEN.Length).Split('.');
paramname = parts[0]; // "Filter3"
}
else //Default to name of var
{
paramname = n.Attributes["Name"].Value;
if ('@' == paramname[0])
paramname = paramname.Substring(1); // "Filter3"
}
qpname = n.Attributes["Name"].Value; // e.g. "@Filter3"
// If the query parameter should take on the value of a report parameter, we need to find the
// report parameter's current value.
string paramstr = "";
foreach (ReportParameterInfo p in paramc)
{
if (0!=string.Compare(paramname,p.Name,true,CultureInfo.InvariantCulture))
continue; // keep going until we find the right param name
if ((p.Values.Count>0) && (null != p.Values[0]))
{ // param has an existing value
switch (p.DataType)
{
case ParameterDataType.DateTime:
{
paramstr = "'" + DateTimeUtil.USString(Convert.ToDateTime(p.Values[0]),"yyyy-MM-ddTHH:mm:ss.fff") + "'";
break;
}
case ParameterDataType.String:
{
//if ((p.Values[0].Length < 2) || ("0x" != p.Values[0].Substring(0, 2)))
//{
paramstr = "'" + p.Values[0].Replace("'", "''") + "'";
//}
//else //special handling for binary
//{
// paramstr = p.Values[0];
//}
break;
}
case ParameterDataType.Boolean:
{
paramstr = Convert.ToInt32(0 == string.Compare("true", p.Values[0], true, CultureInfo.InvariantCulture)).ToString();
break;
}
default:
{
paramstr = p.Values[0].ToString();
break;
}
}
}
else if (Globals.UserSuppliedReportParameters.ContainsKey(paramname))
{ // user supplied a param value on the command line
paramstr = Globals.UserSuppliedReportParameters[paramname];
}
else
{ // param has no value
paramstr = "NULL";
}
break;
}
qrytext = qrytext.Replace("@" + paramname, paramstr);
//if (-1 != qrytext.IndexOf(qpname + ',', StringComparison.InvariantCultureIgnoreCase))
//{
// qrytext = qrytext.Replace(qpname + ',', paramstr);
//}
//else if (-1 != qrytext.IndexOf("@" + paramname, StringComparison.InvariantCultureIgnoreCase))
//{
// qrytext = qrytext.Replace("@" + paramname, paramstr);
//}
//else if (-1 != qrytext.IndexOf(paramname, StringComparison.InvariantCultureIgnoreCase))
//{
// qrytext = qrytext.Replace(paramname, paramstr);
//}
//else //No param token in query text
//{
// qrytext += " " + paramstr + ",";
//}
}
////Trim off trailing comma if there is one
//if (',' == qrytext[qrytext.Length - 1])
// qrytext = qrytext.Substring(0, qrytext.Length - 1);
return qrytext;
}