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);
}
private static XmlDocument searchXmlRecord(string connection, string query, string contentType, string rootNode, bool rootsOnly, DeletedOptions deleted)
{
if (connection == null || connection.Length <= 0)
{
throw new Exception("missing or invalid connection string");
}
if (query == null)
{
query = string.Empty;
}
if (contentType.Length == 0)
{
throw new Exception("contentType must be specified");
}
// HACK
if (deleted == RestNet.Data.DeletedOptions.all)
{
throw new Exception("return deleted and non-deleted (all) not implemented");
}
try
{
query = RestNet.Utilities.SearchableString(query);
Ewbi.FullTextSearch ftsHelper = new Ewbi.FullTextSearch(query, Ewbi.FullTextSearchOptions.None);
string SQL = "searchXmlContent";
StringBuilder sb = new StringBuilder();
XmlDocument xdOutput = new XmlDocument {
XmlResolver = null
};
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("@query", ftsHelper.NormalForm);
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
sb.AppendFormat("<{0} query=\"{1}\">", rootNode, query);
//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());
// save the query
xdOutput.DocumentElement.Attributes.Append(xdOutput.CreateAttribute("query"));
xdOutput.DocumentElement.Attributes["query"].Value = query;
xdOutput.DocumentElement.Attributes.Append(xdOutput.CreateAttribute("results"));
xdOutput.DocumentElement.Attributes["results"].Value = xdOutput.DocumentElement.ChildNodes.Count.ToString();
return(xdOutput);
}
catch (Exception ex)
{
throw ex;
}
}
