广度优化搜索实例之解决方案功能搜索

搜索某一定点名词的N层上位词及下位词

业务逻辑层 

 using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using ProI.Search.Entity;
using ProI.Search.DAL;
using ProI.Search.Utilities.Security;
using ProI.Search.Utilities;
using System.IO;
using System.Configuration;

namespace ProI.Search.BLL
{
    public class SolutionIndex
    {
        SearchEngine engine =null;
        SolutionDAL dal=new SolutionDAL();
        CryptoHelper cryptHelper = new CryptoHelper();
        static String rootDir = ConfigurationManager.AppSettings["SolutionSourceDir"];
        static Dictionary<String, string> sourceFileList = null;


        /// <summary>
        /// Initial SolutionIndexer
        /// </summary>
        /// <param name="engURI"></param>
        /// <param name="chnURI"></param>
        public SolutionIndex(String engURI,String chnURI)
        {
            if (engine == null)
                engine = new SearchEngine(engURI,chnURI);
            engine.setEngDBURI(engURI);
            engine.setChnDBURI(chnURI);
        }
        
        /// <summary>
        /// Create Index
        /// </summary>
        /// <param name="langType"></param>
        /// <param name="infos"></param>
        public void indexInfo(int langType, List<SolutionEntity> infos)
        {
            engine.indexInfo(langType, getXML(infos));
        }
        

        /// <summary>
        /// Search Info
        /// </summary>
        /// <param name="langType"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<SolutionEntity> searchInfo(int langType, string query)
        {
            return getEntity(engine.searchInfo(langType, query));
        }

        public void delIndexInfo(int langType, string query)
        {
            engine.delIndexInfo(langType, query);
        }
        /// <summary>
        /// XML to list Of SolutionEntity list Converter
        /// </summary>
        /// <param name="xml"></param>
        /// <returns></returns>
        public List<SolutionEntity> getEntity(string xml)
        {
            List<SolutionEntity> list = new List<SolutionEntity>();
            XElement doc = XElement.Parse(xml);
            IEnumerable<XElement> docList = doc.Element("result").Elements("doc");
            #region 遍例XML Node
            foreach (XElement docElement in docList)
            {
                SolutionEntity entity = new SolutionEntity();
                foreach (XElement str in docElement.Elements("str"))
                {
                    String name = str.Attribute("name").Value;
                    String value = str.Value;
                    switch (name)
                    {
                        case "id":
                            entity.solutionID = value;
                            break;
                        case "name":
                            entity.name = value;
                            break;
                        case "introduction":
                            entity.introduction = value;
                            break;
                        case "description":
                            entity.description = value;
                            break;
                        case "additionalInfo":
                            entity.additionalInfo = value;
                            break;
                        case "specificResource":
                            entity.specificResource = value;
                            break;
                        case "techniqueDomain":
                            entity.techniqueDomain = value;
                            break;
                        case "reference":
                            entity.reference = value;
                            break;
                        case "langType":
                            entity.langType = int.Parse(value);
                            break;
                    }

                }
            #endregion
                SolutionEntity tempEntity = dal.GetSolutionEntity(entity);
                tempEntity.html = tempEntity.html!=null?cryptHelper.GetDecryptedValue(tempEntity.html):"";//Decrypt html
                //tempEntity.sourceURI = rootDir + tempEntity.sourceURI;
                tempEntity.sourceURI =tempEntity.sourceURI!=null? getSolutionResFile(rootDir, tempEntity.sourceURI):"";
                list.Add(entity);
            }
            return list;
        }
       /// <summary>
       /// SolutionEntity list to XML Converter
       /// </summary>
       /// <param name="infos"></param>
       /// <returns></returns>
        public String getXML(List<SolutionEntity> infos)
        {
            StringBuilder sbuilder = new StringBuilder("<add>");
            foreach (SolutionEntity info in infos)
            {
                sbuilder.Append("<doc>");

                sbuilder.Append("<field name=\"id\">");
                sbuilder.Append(info.solutionID);
                sbuilder.Append("</field>");

                sbuilder.Append("<field name=\"name\">");
                sbuilder.Append(info.name);
                sbuilder.Append("</field>");

                sbuilder.Append("<field name=\"introduction\">");
                sbuilder.Append(info.introduction);
                sbuilder.Append("</field>");

                sbuilder.Append("<field name=\"description\">");
                sbuilder.Append(info.description);
                sbuilder.Append("</field>");

                sbuilder.Append("<field name=\"additionalInfo\">");
                sbuilder.Append(info.additionalInfo);
                sbuilder.Append("</field>");

                sbuilder.Append("<field name=\"specificResource\">");
                sbuilder.Append(info.specificResource);
                sbuilder.Append("</field>");

                sbuilder.Append("<field name=\"reference\">");
                sbuilder.Append(info.reference);
                sbuilder.Append("</field>");

                sbuilder.Append("<field name=\"techniqueDomain\">");
                sbuilder.Append(info.techniqueDomain);
                sbuilder.Append("</field>");

                sbuilder.Append("<field name=\"langType\">");
                sbuilder.Append(info.langType);
                sbuilder.Append("</field>");

                sbuilder.Append("</doc>");
            }
            sbuilder.Append("</add>");
            return sbuilder.ToString();
        }

        String getSolutionResFile(string dir, string soultionID)
        {
            String tempID=null;
            try
            {
                if (SolutionIndex.sourceFileList == null)
                {
                    lock (this)
                    {
                        if (SolutionIndex.sourceFileList == null)
                        {
                            SolutionIndex.sourceFileList = new Dictionary<string, string>();
                            DirectaryHelper dirhelper = new DirectaryHelper();
                            List<FileInfo> fileList = dirhelper.getFileList(new DirectoryInfo(dir));
                            if (fileList == null || fileList.Count == 0)
                            {
                                throw new Exception("Solution Resource DB初始化失败！");
                            }
                            foreach (FileInfo file in fileList)
                            {
                                SolutionIndex.sourceFileList.Add(file.Name.Substring(0, file.Name.IndexOf("_")), file.Name);
                            }
                        }
                    }
                }
                tempID = SolutionIndex.sourceFileList[soultionID];

            }
            catch (Exception e)
            {
                if (e.Message.Contains("初始化失败"))
                    throw e;
                else
                    throw new Exception("获取Solution flash资源失败！ solutionID:" + soultionID);
            }
            return tempID;
            
        }

        /// <summary>
        /// solution func search
        /// </summary>
        /// <param name="verb"></param>
        /// <param name="objects"></param>
        /// <param name="level"></param>
        /// <param name="langType"></param>
        /// <returns></returns>
        public List<SolutionEntity> getSolutionListByFunc(string verb, string objects, int level,int langType)
        {
            List<SolutionEntity> list = dal.GetSolutionList(verb, objects, level,langType);
            foreach (SolutionEntity entity in list)
            {
                entity.sourceURI = getSolutionResFile(rootDir, entity.sourceURI);
            }

            sortSolutionList(list, objects, level,langType);
            return list;
        }

        void sortSolutionList(List<SolutionEntity> list, string objects, int level,int langType)
        {
            List<String> sortList = new List<String>();

            List<SolutionEntity> preciseCategory = new List<SolutionEntity>();
            List<SolutionEntity> generalCategory = new List<SolutionEntity>();
            List<SolutionEntity> specCategory = new List<SolutionEntity>();
            List<SolutionEntity> othersCategory = new List<SolutionEntity>();

            foreach (SolutionEntity entity in list)
            {
                sortList.Add(entity.solutionID);
            }

            string objectID = dal.GetObjectID(objects);

            List<String> upperSet = getupperSet(objectID, level);
            List<String> lowerSet = getlowerSet(objectID, level);
            //Console.WriteLine("Upper&Lower set size:" + (upperSet.Count + lowerSet.Count));
            //Console.WriteLine("SYN number:" + (upperSet.Count + lowerSet.Count)*5.0);

            String upperSetString = dal.getObjects(upperSet);
            String lowerSetString = dal.getObjects(lowerSet);

            foreach (SolutionEntity entity in list)
            {
                string objectValue=entity.objects;
                if (objectValue == objects)
                    preciseCategory.Add(entity);
                else if (upperSetString.IndexOf(objectValue) > 0)
                    generalCategory.Add(entity);
                else if (lowerSetString.IndexOf(objectValue) > 0)
                    specCategory.Add(entity);
                else
                    othersCategory.Add(entity);
            }

            list.Clear();
            list.AddRange(preciseCategory);
            list.AddRange(generalCategory);
            list.AddRange(specCategory);
            list.AddRange(othersCategory);
        }

        
        List<string> getupperSet(string objectID, int level)
        {
            List<string> lowerList = new List<string>();
            Queue<lowerIterm> lowerQ = new Queue<lowerIterm>();
            List<String> tempList = new List<string>();

            lowerQ.Enqueue(new lowerIterm() { objectID = objectID, level = 0 });

            while (lowerQ.Count > 0)//upper&euqal
            {
                lowerIterm item = lowerQ.Dequeue();
                if (item.level > level)
                    break;

                tempList = dal.getOntologySet(item.objectID, 1);
                if (tempList.Count > 0)
                {
                    foreach (string objid in tempList)
                    {
                        lowerQ.Enqueue(new lowerIterm() { objectID = objid, level = item.level + 1 });
                    }

                    lowerList.AddRange(tempList);
                }
            }

            return lowerList;
        }

        List<string> getlowerSet(string objectID, int level)
        {
            List<string> lowerList = new List<string>();
            Queue<lowerIterm> lowerQ = new Queue<lowerIterm>();
            List<String> tempList = new List<string>();

            lowerQ.Enqueue(new lowerIterm() { objectID = objectID, level = 0 });

            while (lowerQ.Count > 0)
            {
                lowerIterm item = lowerQ.Dequeue();
                if (item.level > level)//upper&equal
                    break;

                tempList = dal.getOntologySet(item.objectID, 0);
                if (tempList.Count > 0)
                {
                    foreach (string objid in tempList)
                    {
                        lowerQ.Enqueue(new lowerIterm() { objectID = objid, level = item.level + 1 });
                    }

                    lowerList.AddRange(tempList);
                }
            }

            return lowerList;

        }


    }
}

数据访问层调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ProI.Search.Entity;
using System.Data;
using System.Configuration;

namespace ProI.Search.DAL
{
    public class SolutionDAL
    {
        String localConnStr = ConfigurationManager.AppSettings["proIDBSource"];
        SqlConnection conn = null;

        public SolutionDAL()
        {
            conn = new SqlConnection(localConnStr);
        }

        SqlConnection getSqlConn()
        {
            return new SqlConnection(localConnStr);

        }

        public void CloseSqlConn()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn.Close();
            }
        }

        public string GeneratorKnowledgeEntryId()
        {
            string result;
            using (SqlConnection con=getSqlConn())
            {
                var cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT MAX(CAST(solutionGroupID AS INT)) FROM dbo.SOLUTION WHERE CAST(solutionGroupID AS INT)>=80000";
                var sda = new SqlDataAdapter(cmd);
                var dt = new DataTable();
                sda.Fill(dt);
                if (dt.Rows[0][0]==DBNull.Value)
                {
                    result = "80001";
                }
                else
                {
                    result = ((int)dt.Rows[0][0] + 1) + "";
                }
            }
            return result;
        }

        public bool IsSolutionExist(string name)
        {
            using (SqlConnection con=getSqlConn())
            {
                var cmd = con.CreateCommand();
                cmd.CommandText = "SELECT solutionGroupID FROM dbo.SOLUTION WHERE name=@name AND CAST(solutionGroupID AS INT)>80000;";
                cmd.CommandType=CommandType.Text;
                cmd.Parameters.AddWithValue("@name", name);
                var sda = new SqlDataAdapter(cmd);
                var dt = new DataTable();
                sda.Fill(dt);
                con.Close();
                return dt.Rows.Count>0;
            }
        }

        public void AddSolutionEntity(SolutionEntity entity)
        {
            using (SqlConnection con = getSqlConn())
            {
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandText = "insert into Solution  VALUES (@solutionID,@name,@introduction,@description, @additionalInfo ,@specificResource,@techniqueDomain,@reference,@html, '"
                    + entity.sourceURI + "', " + entity.langType + ")";
                com.CommandType = CommandType.Text;
                Func<string, string> getParameter = str => str ?? "";
                var id = com.CreateParameter();
                id.ParameterName = "@solutionID";
                id.Value = entity.solutionID;
                com.Parameters.Add(id);

                var name = com.CreateParameter();
                name.ParameterName = "@name";
                name.Value = entity.name;
                com.Parameters.Add(name);

                var introduction = com.CreateParameter();
                introduction.ParameterName = "@introduction";
                introduction.Value = getParameter(entity.introduction);
                com.Parameters.Add(introduction);

                var description = com.CreateParameter();
                description.ParameterName = "@description";
                description.Value = getParameter(entity.description);
                com.Parameters.Add(description);

                var additionalInfo = com.CreateParameter();
                additionalInfo.ParameterName = "@additionalInfo";
                additionalInfo.Value = getParameter(entity.additionalInfo);
                com.Parameters.Add(additionalInfo);

                var specificResource = com.CreateParameter();
                specificResource.ParameterName = "@specificResource";
                specificResource.Value = getParameter(entity.specificResource);
                com.Parameters.Add(specificResource);

                //techniqueDomain
                var techniqueDomain = com.CreateParameter();
                techniqueDomain.ParameterName = "@techniqueDomain";
                techniqueDomain.Value = getParameter(entity.techniqueDomain);
                com.Parameters.Add(techniqueDomain);

                var reference = com.CreateParameter();
                reference.ParameterName = "@reference";
                reference.Value = getParameter(entity.reference);
                com.Parameters.Add(reference);

                var htmlPara = com.CreateParameter();
                htmlPara.ParameterName = "@html";
                htmlPara.Value = getParameter(entity.html);
                com.Parameters.Add(htmlPara);

                if (!(con.State == ConnectionState.Open))
                    con.Open();
                int i = Int32.Parse(com.ExecuteNonQuery().ToString());
            }

        }

        public SolutionEntity GetSolutionEntity(SolutionEntity entity)
        {
            using (SqlConnection con = getSqlConn())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select html,SourceURI from Solution where solutionGroupID=@solutionID and langType=@langType";
                //com.CommandText = "select html,SourceURI from Solution where solutionGroupID='03509' and langType=@langType";
                com.CommandType = CommandType.Text;

                var paraSolutionID = com.CreateParameter();
                paraSolutionID.ParameterName = "@solutionID";
                //paraSolutionID.Value = "03509";
                paraSolutionID.Value = entity.solutionID;
                com.Parameters.Add(paraSolutionID);

                var paralangType = com.CreateParameter();
                paralangType.ParameterName = "@langType";
                paralangType.Value = entity.langType;
                com.Parameters.Add(paralangType);

                com.Connection = con;
                if (!(con.State == ConnectionState.Open))
                    con.Open();
                SqlDataReader reader = com.ExecuteReader();
                if (reader.Read())
                {
                    string html = reader["html"].ToString();
                    string sourceURI = reader["SourceURI"].ToString();
                    entity.html = html;
                    entity.sourceURI = sourceURI;
                }

                return entity;
            }
        }


        public List<SolutionEntity> GetSolutionList(string verb, string objects, int level,int inlangType)
        {
            List<SolutionEntity> list = new List<SolutionEntity>();
            using (SqlConnection con = getSqlConn())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = @"select  B.udr_id as id,A.name,A.introduction,A.description,A.additionalInfo,A.specResource,A.techniqueDomain,A.html,A.reference,A.sourceURI,A.langType,B.object from solution A, solution_svpo B 
                                  where A.solutionGroupID=B.UDR_ID and  B.verb=@verb and A.langtype=@langType";
                com.CommandType = CommandType.Text;

                var paraVerb = com.CreateParameter();
                paraVerb.ParameterName = "@verb";
                paraVerb.Value = verb;
                com.Parameters.Add(paraVerb);

                var paralang = com.CreateParameter();
                paralang.ParameterName = "@langType";
                paralang.Value = inlangType;
                com.Parameters.Add(paralang);

                com.Connection = con;
                if (!(con.State == ConnectionState.Open))
                    con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    SolutionEntity entity = new SolutionEntity();
                    string id = reader["id"].ToString();
                    string html = reader["html"].ToString();
                    string sourceURI = reader["SourceURI"].ToString();
                    string name = reader["name"].ToString();
                    string introduction = reader["introduction"].ToString();
                    string description = reader["description"].ToString();
                    string additionalInfo = reader["additionalInfo"].ToString();
                    string specificResource = reader["specResource"].ToString();
                    string techniqueDomain = reader["techniqueDomain"].ToString();
                    string reference = reader["reference"].ToString();
                    int langType = int.Parse(reader["langType"].ToString());
                    string objectName = reader["object"].ToString();
                    entity.solutionID = id;
                    entity.name = name;
                    entity.introduction = introduction;
                    entity.techniqueDomain = techniqueDomain;
                    entity.additionalInfo = additionalInfo;
                    entity.specificResource = specificResource;
                    entity.html = html;
                    entity.sourceURI = sourceURI;
                    entity.langType = langType;
                    //for func 
                    entity.objects = objectName;
                    list.Add(entity);
                }
                return list;
            }
        }

        /// <summary>
        /// find upper/lower synwordsID
        /// </summary>
        /// <param name="objectID"></param>
        /// <param name="level"></param>
        /// <param name="ontoType">0:find children; 1:find parents</param>
        /// <returns>ObjectID set</returns>

        public List<String> getOntologySet(string objectID,int ontoType)
        {
            List<String> childSet = new List<string>();
            SqlConnection con = getSqlConn();
            SqlCommand com = new SqlCommand();
            try
            {
                if (ontoType == 0)
                    com.CommandText = "select parent_id,child_id  from synset_term_rel where parent_id=@id";
                else
                    com.CommandText = "select parent_id,child_id from synset_term_rel where child_id=@id";

                com.CommandType = CommandType.Text;
                var paraId = com.CreateParameter();
                paraId.ParameterName = "@id";
                paraId.Value = objectID;
                com.Parameters.Add(paraId);

                com.Connection = con;
                if (!(con.State == ConnectionState.Open))
                    con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    if (ontoType == 0)
                    {
                        childSet.Add(reader["child_id"].ToString());
                    }
                    else
                    {
                        childSet.Add(reader["parent_id"].ToString());
                    }

                }
            }
            catch (Exception ex)
            { }
            finally
            {
                con.Close();
                con.Dispose();
                con = null;
            }
            return childSet;
        }

        /// <summary>
        /// get Objects of functional search
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public String getObjects(List<String> ids)
        {
            StringBuilder childSet = new StringBuilder();
            SqlConnection con = getSqlConn();
            SqlCommand com = new SqlCommand();
            try
            {                
                com.CommandText = "select name from synset_term where termGroupID";

                StringBuilder idStr = new StringBuilder("(");
                foreach (String item in ids)
                {
                    idStr.Append(item + ",");
                }

                com.CommandText = com.CommandText + " in " + idStr.ToString().Substring(0, idStr.Length - 2) + ")";

                com.Connection = con;
                if (!(con.State == ConnectionState.Open))
                    con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    childSet.Append(reader["name"].ToString());
                }
            }
            catch (Exception ex)
            { }
            finally
            {
                con.Close();
                con.Dispose();
                con = null;

            }
            return childSet.ToString();
        }

        public string GetObjectID(string objectname)
        {
            string name = null;
            using (SqlConnection con = getSqlConn())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select top 1 termGroupID from synset_term where name =@name";
                com.CommandType = CommandType.Text;

                var paraName = com.CreateParameter();
                paraName.ParameterName = "@name";
                paraName.Value = objectname;
                com.Parameters.Add(paraName);

                com.Connection = con;
                if (!(con.State == ConnectionState.Open))
                    con.Open();
                SqlDataReader reader = com.ExecuteReader();
                if (reader.Read())
                {
                    name = reader["termGroupID"].ToString();
                }
            }
            return name;
        }

    }

    public class lowerIterm
    {
        public string objectID { get; set; }
        public int level { get; set; }
    }
}