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); } } }