private static XmlDocument getXmlRecords(string connection, XmlDocument searchIds, string contentType, DeletedOptions deleted)
{
if (connection == null || connection.Length <= 0)
{
throw new Exception("missing or invalid connection string");
}
if (searchIds == null)
{
throw new Exception("missing or invalid searchIds Xml");
}
if (contentType.Length <= 0)
{
throw new Exception("contentType must be specified");
}
XmlDocument xdOutput = new XmlDocument {
XmlResolver = null
};
string rootNode = contentType + "s";
// no IDs, so save time by aborting now
if (!searchIds.DocumentElement.HasChildNodes)
{
xdOutput.LoadXml(string.Format("<{0}/>", rootNode));
return(xdOutput);
}
try
{
StringBuilder sbXml = new StringBuilder();
using (SqlCommand oCmd = new SqlCommand("getXmlRecords", new SqlConnection(connection)))
{
oCmd.CommandType = CommandType.StoredProcedure;
using (XmlNodeReader xnr = new XmlNodeReader(searchIds))
{
SqlXml sx = new SqlXml(xnr);
oCmd.Parameters.Add("@ids", SqlDbType.Xml);
oCmd.Parameters["@ids"].Value = sx;
}
oCmd.Parameters.AddWithValue("@contentType", contentType);
oCmd.Parameters.AddWithValue("@version", null);
oCmd.Parameters.AddWithValue("@deleted", deleted);
oCmd.Connection.Open();
System.Data.SqlClient.SqlDataReader oRdr = oCmd.ExecuteReader();
if (!oRdr.HasRows)
{
sbXml.Append("<" + rootNode + "/>");
}
else
{
sbXml.Append("<" + rootNode + ">");
while (oRdr.Read())
{
System.Data.SqlTypes.SqlXml sx = oRdr.GetSqlXml(0);
//XmlReader xr = sx.CreateReader();
//xr.Read();
sbXml.Append(sx.Value);
}
sbXml.Append("</" + rootNode + ">");
oRdr.Close();
oCmd.Connection.Close();
}
}
xdOutput.LoadXml(sbXml.ToString());
return(xdOutput);
}
catch (Exception ex)
{
throw ex;
}
}
public XmlDocument Retrieve(string connection, string id, string contentType, DeletedOptions deleted)
{
return(getXmlRecord(connection, id, contentType, deleted));
}
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 getXmlRecord(string connection, string keyId, string contentType, DeletedOptions deleted)
{
if (connection.Length <= 0)
{
throw new Exception("missing or invalid connection string");
}
if (keyId.Length <= 0)
{
throw new Exception("missing or invalid keyID");
}
if (contentType.Length <= 0)
{
throw new Exception("contentType must be specified");
}
try
{
XmlDocument xdOutput = new XmlDocument {
XmlResolver = null
};
StringBuilder sb = new StringBuilder();
using (SqlCommand oCmd = new SqlCommand("getXmlRecord", new SqlConnection(connection)))
{
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.AddWithValue("@id", keyId);
oCmd.Parameters.AddWithValue("@contentType", contentType);
oCmd.Parameters.AddWithValue("@deleted", deleted);
oCmd.Connection.Open();
System.Data.SqlClient.SqlDataReader oRdr = oCmd.ExecuteReader();
if (!oRdr.HasRows)
{
return(null);
}
while (oRdr.Read())
{
System.Data.SqlTypes.SqlXml sx = oRdr.GetSqlXml(0);
//XmlReader xr = sx.CreateReader();
//xr.Read();
sb.Append(sx.Value);
}
oRdr.Close();
oCmd.Connection.Close();
}
xdOutput.LoadXml(sb.ToString());
return(xdOutput);
}
catch (Exception ex)
{
throw ex;
}
}
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;
}
}
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);
}
public void Delete(SqlTransaction oTran, string id, string contentType, DeletedOptions deleted, string deletedBy)
{
deleteXmlRecord(oTran, id, contentType, deleted, deletedBy);
}
private static bool deleteXmlRecord(SqlTransaction oTran, string keyId, string contentType, DeletedOptions deleted, string updatedBy)
{
bool retVal = false;
try
{
using (SqlCommand oCmd = new SqlCommand("deleteXmlContent", oTran.Connection))
{
oCmd.Transaction = oTran;
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.AddWithValue("@id", keyId);
oCmd.Parameters.AddWithValue("@deleted", deleted);
oCmd.Parameters.AddWithValue("@updatedBy", updatedBy);
retVal = oCmd.ExecuteNonQuery() != 0;
}
}
catch (Exception ex)
{
throw ex;
}
return(retVal);
}
public void Delete(string connection, string id, string contentType, DeletedOptions deleted, string deletedBy)
{
deleteXmlRecord(connection, id, contentType, deleted, deletedBy);
}
public XmlDocument Search(string connection, string xPathFormat, string[] queryValues, string contentType, string rootNode, bool rootsOnly, DeletedOptions deleted)
{
return(searchXmlRecord(connection, xPathFormat, queryValues, contentType, rootNode, rootsOnly, deleted));
}
public XmlDocument Search(string connection, NameValueCollection qryTerms, string contentType, string rootNode, bool rootsOnly, SqlFullTextConjunctionType conjunction, DeletedOptions deleted)
{
return(searchXmlRecord(connection, qryTerms, contentType, rootNode, rootsOnly, conjunction, deleted));
}
public XmlDocument Search(string connection, string query, string contentType, string rootNode, bool rootsOnly, DeletedOptions deleted)
{
return(searchXmlRecord(connection, query, contentType, rootNode, rootsOnly, deleted));
}
public XmlDocument Retrieve(string connection, XmlDocument SearchIds, string contentType, DeletedOptions deleted)
{
return(getXmlRecords(connection, SearchIds, contentType, deleted));
}
