public void Query(XmlNode Query)
{
string timingToken = "Anonymous Query";
if (Timing != null)
{
if (Query.Attributes["name"] != null)
{
var queryname = Query.Attributes["name"].Value;
timingToken = String.Format("ProjectFlxDB.DB.DatabaseQuery {0}", queryname);
}
Timing.Start(timingToken);
}
XmlElement elm = null;
string _commandtext = null;
string xpath = null;
if (_database.State != ConnectionState.Open)
{
_database.Open();
}
InitializeCommand();
// command timeout
if (Query.Attributes["script-timeout"] != null)
{
_command.CommandTimeout = int.Parse(Query.Attributes["script-timeout"].Value);
}
_querynode = Query;
try
{
// command type
xpath = "command/type";
elm = (XmlElement)Query.SelectSingleNode(xpath);
switch (elm.InnerText)
{
case "StoredProcedure":
_command.Parameters.Clear();
_command.CommandType = CommandType.StoredProcedure;
break;
case "Select":
case "SQL":
_command.CommandType = CommandType.Text;
break;
}
// command text
switch (_command.CommandType)
{
case (CommandType.Text):
xpath = "command/text";
elm = (XmlElement)Query.SelectSingleNode(xpath);
_commandtext = Regex.Replace(elm.InnerText, @"^[ \t]+", "", RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace);
_commandtext = Regex.Replace(_commandtext, @"(\r\n)", " ", RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace);
break;
default:
xpath = "command/name";
elm = (XmlElement)Query.SelectSingleNode(xpath);
_commandtext = elm.InnerText;
_command.Parameters.Clear();
break;
}
// command parameters - in only
xpath = "parameters/parameter[not(@inout='out')]";
XmlNodeList nodes = Query.SelectNodes(xpath);
foreach (XmlNode node in nodes)
{
// validate input
if (validInput(node))
{
if (_command.CommandType == CommandType.Text)
{
string replaceparam = "[" + node.Attributes["name"].Value + "]";
_commandtext = _commandtext.Replace(replaceparam, node.InnerText);
}
else
{
elm = (XmlElement)node;
if (!elm.IsEmpty)
{
int size = 0;
if (!String.IsNullOrEmpty(elm.GetAttribute("size")))
{
size = Convert.ToInt32(elm.GetAttribute("size"));
}
AddParameter(elm.GetAttribute("name"), elm.InnerText, elm.GetAttribute("type"), elm.GetAttribute("inout"), size);
}
}
}
}
// did all input values validate?
if (!_isValid)
{
throw new ProjectException("Invalid Formatting Exception", _handler);
}
// command parameters - output only (are required)
xpath = "parameters/parameter[@inout='out']";
nodes = Query.SelectNodes(xpath);
foreach (XmlNode node in nodes)
{
if (_command.CommandType == CommandType.Text)
{
string replaceparam = "[" + node.Attributes["name"].Value + "]";
_commandtext.Replace(replaceparam, node.InnerText);
}
else
{
elm = (XmlElement)node;
int size = 0;
if (!String.IsNullOrEmpty(elm.GetAttribute("size")))
{
size = Convert.ToInt32(elm.GetAttribute("size"));
}
AddParameter(elm.GetAttribute("name"), elm.InnerText, elm.GetAttribute("type"), elm.GetAttribute("inout"), size);
}
}
_command.CommandText = _commandtext;
// prepare result xml document
XmlNode importnode;
XmlNode newElm;
_xmresult = new XmlDocument();
_xmresult.LoadXml("<results><schema/></results>");
_xmresult.DocumentElement.SetAttribute("name", Query.Attributes["name"].Value);
if (!String.IsNullOrEmpty(_sqlProjName))
{
_xmresult.DocumentElement.SetAttribute("ProjectSqlFile", _sqlProjName);
}
importnode = _xmresult.ImportNode(Query, true);
_xmresult.SelectSingleNode("results/schema").AppendChild(importnode);
// execute query
int scalar = 0;
int rows = 0;
xpath = "command/action";
elm = (XmlElement)Query.SelectSingleNode(xpath);
switch (elm.InnerText)
{
case ("Scalar"):
var obj = _command.ExecuteScalar();
int.TryParse(obj == null ? "0" : obj.ToString(), out scalar);
_scalar = scalar;
if (scalar > 0)
{
rows = 1;
}
_rowsaffected = rows;
// set result in xml
elm = (XmlElement)_xmresult.SelectSingleNode("results");
newElm = _xmresult.CreateElement("result");
newElm.InnerText = Convert.ToString(_rowsaffected);
elm.AppendChild(newElm);
break;
case ("Result"):
var cachekey = cacheKeyHelper(_xmresult);
var cachedBuilder = GetCache(cachekey);
if (cachedBuilder != null && _cachingEnabled)
{
pushToTree(cachedBuilder);
}
else
{
SqlDataReader dr = null;
try
{
// TODO: move open DB here (or similar) to avoid open db when cache results
dr = _command.ExecuteReader();
BuildResults(dr);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
}
break;
case ("NonQuery"):
default:
rows = _command.ExecuteNonQuery();
_rowsaffected = rows;
// set result in xml
elm = (XmlElement)_xmresult.SelectSingleNode("results");
newElm = _xmresult.CreateElement("result");
newElm.InnerText = Convert.ToString(_rowsaffected);
elm.AppendChild(newElm);
break;
}
// set output parameter results on result xml document
xpath = "results/schema/query/parameters/parameter[@inout='out' or @inout='inout']";
nodes = _xmresult.SelectNodes(xpath);
foreach (XmlNode node in nodes)
{
node.InnerText = _command.Parameters[node.Attributes["name"].InnerText].Value.ToString();
}
}
catch (SqlException handledSql)
{
if (handledSql.Message.Contains(" duplicate key "))
{
if (QuiteUniqueConstraints == false)
{
throw handledSql;
}
}
else
{
throw handledSql;
}
}
catch (Exception unhandled)
{
ProjectExceptionArgs args = new ProjectExceptionArgs("Sorry, we handled an exception. The problem has been sent to MSO Admin", "mso.Utility.DB.DatabaseQuery", "Query " + _commandtext, null, SeverityLevel.Critical, LogLevel.Event);
throw new ProjectException(args, unhandled);
}
finally
{
if (Timing != null)
{
Timing.Stop(timingToken);
}
}
}
