コード例 #1
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
        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;
            }
        }
コード例 #2
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
 public XmlDocument Retrieve(string connection, string id, string contentType, DeletedOptions deleted)
 {
     return(getXmlRecord(connection, id, contentType, deleted));
 }
コード例 #3
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
        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));
        }
コード例 #4
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
        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;
            }
        }
コード例 #5
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
        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;
            }
        }
コード例 #6
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
        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);
        }
コード例 #7
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
 public void Delete(SqlTransaction oTran, string id, string contentType, DeletedOptions deleted, string deletedBy)
 {
     deleteXmlRecord(oTran, id, contentType, deleted, deletedBy);
 }
コード例 #8
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
        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);
        }
コード例 #9
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
 public void Delete(string connection, string id, string contentType, DeletedOptions deleted, string deletedBy)
 {
     deleteXmlRecord(connection, id, contentType, deleted, deletedBy);
 }
コード例 #10
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
 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));
 }
コード例 #11
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
 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));
 }
コード例 #12
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
 public XmlDocument Search(string connection, string query, string contentType, string rootNode, bool rootsOnly, DeletedOptions deleted)
 {
     return(searchXmlRecord(connection, query, contentType, rootNode, rootsOnly, deleted));
 }
コード例 #13
0
ファイル: SqlDataAccess.cs プロジェクト: jpotisch/restnet
 public XmlDocument Retrieve(string connection, XmlDocument SearchIds, string contentType, DeletedOptions deleted)
 {
     return(getXmlRecords(connection, SearchIds, contentType, deleted));
 }