private static XmlDocument searchXmlRecord(string connection, NameValueCollection queryParams, string contentType, string rootNode, bool rootsOnly, SqlFullTextConjunctionType conjunction, DeletedOptions deleted) { if (queryParams == null || queryParams.Count == 0) { throw new Exception("No search parameters were provided."); } NameValueCollection oNvc = SqlXpathSearch.GenerateXpathStructsFromNVC(queryParams, conjunction); if (oNvc == null) { throw new Exception("Error generating search parameter collection."); } return(searchXmlRecord(connection, oNvc.Keys[0], oNvc[0].Split('|'), contentType, rootNode, rootsOnly, deleted)); }
private static XmlDocument searchXmlRecord(string connection, string xPathFormat, string[] queryValues, string contentType, string rootNode, bool rootsOnly, DeletedOptions deleted) { if (connection == null || connection.Length <= 0) { throw new Exception("missing or invalid connection string"); } if (xPathFormat == null || xPathFormat.Length == 0) { throw new Exception("missing or invalid xPath input"); } if (queryValues == null || queryValues.Length == 0) { throw new Exception("missing or invalid query values input"); } if (contentType.Length == 0) { throw new Exception("missing or invalid query parameters collection"); } // HACK if (deleted == RestNet.Data.DeletedOptions.all) { throw new Exception("return deleted and non-deleted (all) not implemented"); } string SQL = "searchXmlContentXpath"; StringBuilder sb = new StringBuilder(); XmlDocument xdOutput = new XmlDocument { XmlResolver = null }; // convert to lowercase and remove accents string[] cleanQueryValues = RestNet.Utilities.SearchableString(queryValues); string finalXPath = SqlXpathSearch.GenerateXpathFromFormat(xPathFormat, cleanQueryValues); System.Collections.Specialized.NameValueCollection ftsTerms = new NameValueCollection(); for (int x = 0; x < cleanQueryValues.Length; x++) { cleanQueryValues[x] = string.Format("\"{0}\"", cleanQueryValues[x]); } char[] tab = { '\t' }; string ftsRawSearch = string.Join(" or ", string.Join("\t", cleanQueryValues).Replace("\"\"", string.Empty).Split(tab, StringSplitOptions.RemoveEmptyEntries)); Ewbi.FullTextSearch ftsHelper = new Ewbi.FullTextSearch(ftsRawSearch, Ewbi.FullTextSearchOptions.None); using (SqlCommand oCmd = new SqlCommand(SQL, new SqlConnection(connection))) { oCmd.CommandType = CommandType.StoredProcedure; oCmd.Parameters.AddWithValue("@contentType", contentType); oCmd.Parameters.AddWithValue("@rootsOnly", rootsOnly); oCmd.Parameters.AddWithValue("@contains", ftsHelper.NormalForm); oCmd.Parameters.AddWithValue("@xPath", string.Empty); //finalXPath); oCmd.Parameters.AddWithValue("@deleted", deleted); oCmd.Connection.Open(); System.Data.SqlClient.SqlDataReader oRdr = oCmd.ExecuteReader(); if (!rootsOnly) { // SPROC RETURNS RECORD SET OF 1 COL WITH XML DOC IN EACH COLUMN // TODO: Escape quotes so this is valid XML sb.AppendFormat("<{0} query=\"{1}\">", rootNode, SqlXpathSearch.GenerateXpathFromFormat(xPathFormat, cleanQueryValues)); //xdOutput.AppendChild(xdOutput.CreateElement(rootNode)); while (oRdr.Read()) { sb.Append(oRdr.GetValue(0)); } sb.AppendFormat("</{0}>", rootNode); } else { // SPROC RETURNS WELL-FORMED XML DOC IN COL 0 WHICH MIGHT SPAN MULTIPLE ROWS while (oRdr.Read()) { sb.Append(oRdr.GetValue(0)); } } oRdr.Close(); oCmd.Connection.Close(); } if (sb.Length == 0) { sb.Append("<" + rootNode + "/>"); } xdOutput.LoadXml(sb.ToString()); RestNet.Logging.DebugFormat("SqlDataAccess.searchXmlRecord - {6} records\r\nSearch SQL: {0} @contentType='{1}', @rootsOnly='{2}', @contains='{3}', @xPath='{4}', @deleted={5}\r\nSearch Results:{7}", SQL, contentType, rootsOnly, ftsHelper.NormalForm, string.Empty, deleted, xdOutput.DocumentElement.ChildNodes.Count, xdOutput.OuterXml); return(xdOutput); }