/// <summary> /// 获取DBINFO基本信息 /// </summary> /// <returns></returns> public DbInfo GetDbInfo() { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT DBSERNAME,CRS,CSPARAM FROM DBInfo "); return(SqlServerDBHelper.ExecuteQueryText <DbInfo>(strSql.ToString()).FirstOrDefault()); }
/// <summary> /// 根据业务对象类型和过滤条件获取对象列表 /// </summary> /// <param name="bot"></param> /// <param name="filter"></param> /// <returns></returns> public BOCollection GetBOListByType(string bot, string filter) { BOCollection list = new BOCollection(); StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT DISTINCT T.BOID, T.NAME, T1.BOT FROM "); strSql.Append(" BO T,"); strSql.Append(" OBJECTTYPE T1 "); //属性过滤条件不为空 if (!string.IsNullOrEmpty(filter)) { strSql.Append(" ,PROPERTY PROPERTY "); } strSql.Append(string.Format(" WHERE T1.BOT = '{0}' ", bot)); strSql.Append(" AND T.BOTID = T1.BOTID "); //属性过滤条件不为空 if (!string.IsNullOrEmpty(filter)) { strSql.Append(" AND PROPERTY.BOID = T.BOID "); } if (!string.IsNullOrEmpty(filter)) { strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(filter, bot).ToString() + ")"); } list.AddRange(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString())); return(list); }
public IList <TypeClassTree> GetList() { List <TypeClassTree> list = new List <TypeClassTree>(); StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT T1.ID AS ID,T1.NAME AS NAME ,'0' AS PID,'' AS FT,"); strSql.Append(" T1.ISUSERDEFINE ,'' AS SHAPE,'' AS USEGEOMETRY,'TypeClass' AS TYPE,'' AS ENAME FROM OBJTYPECLASS T1 "); strSql.Append(" UNION "); strSql.Append(" SELECT O.BOTID AS ID,O.BOT AS NAME,T.ID AS PID,O.FT,O.ISUSERDEFINE,"); strSql.Append(" O.SHAPE,O.USEGEOMETRY,'ObjType' AS TYPE,O.NAME AS ENAME FROM OBJECTTYPE O JOIN OBJTYPECLASS T ON T.ID=O.ID "); list.AddRange(SqlServerDBHelper.ExecuteQueryText <TypeClassTree>(strSql.ToString())); return(list); }
/// <summary> /// 根据对象ID查询业务对象 /// </summary> /// <param name="boid">对象ID</param> /// <returns></returns> public BO GetBOById(string boid) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT T.BOID,T.NAME,T1.BOT FROM BO T "); strSql.Append(" LEFT JOIN OBJECTTYPE T1 "); strSql.Append(" ON T.BOTID=T1.BOTID "); strSql.Append(" WHERE T.BOID =@BOID "); SqlParameter[] parameters = { new SqlParameter("BOID", SqlDbType.VarChar, 36) }; parameters[0].Value = boid; return(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString(), parameters).FirstOrDefault()); }
/// <summary> /// 根据业务对象名称、对象类型查询对象ID /// </summary> /// <param name="name">业务对象名称</param> /// <param name="bot">对象类型</param> /// <returns></returns> public BO GetBOByName(string name, string bot) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT * FROM BO B"); strSql.Append(" LEFT JOIN OBJECTTYPE T "); strSql.Append(" ON B.BOTID=T.BOTID "); strSql.Append(" WHERE B.NAME =@NAME "); strSql.Append(" AND T.BOT=@BOT "); SqlParameter[] parameters = { new SqlParameter("NAME", SqlDbType.VarChar, 50), new SqlParameter("BOT", SqlDbType.VarChar, 50) }; parameters[0].Value = name; parameters[1].Value = bot; return(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString(), parameters).FirstOrDefault()); }
/// <summary> /// 根据应用场景和过滤条件查询业务对象。通过对象的参数集进行过滤,返回符合条件的对象列表 /// </summary> /// <param name="bot">业务对象类型</param> /// <param name="wktBBox">空间范围</param> /// <param name="filte">过滤条件</param> /// <returns></returns> public BOCollection GetBOListByFilter(string bot, string wktBBox, string filte) { //sdo_point_type(坐下坐标,右上坐标)。 BOCollection list = new BOCollection(); StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT DISTINCT T.BOID, T.NAME, T1.BOT "); strSql.Append(" FROM "); if (!string.IsNullOrEmpty(wktBBox)) { strSql.Append(" V_GEOMETRY A,"); } strSql.Append(" BO T,"); strSql.Append(" OBJECTTYPE T1 "); //属性过滤条件不为空 if (!string.IsNullOrEmpty(filte)) { strSql.Append(" ,PROPERTY PROPERTY "); } strSql.Append(string.Format(" WHERE T1.BOT = '{0}' ", bot)); if (!string.IsNullOrEmpty(wktBBox)) { //strSql.Append(" GEOMETRY A,"); strSql.Append(" AND A.BOID = T.BOID "); } strSql.Append(" AND T.BOTID = T1.BOTID "); //属性过滤条件不为空 if (!string.IsNullOrEmpty(filte)) { strSql.Append(" AND PROPERTY.BOID = T.BOID "); } //空间范围不为空 if (!string.IsNullOrEmpty(wktBBox)) { strSql.Append(string.Format(" AND ( A.GEOMETRY.STWithin(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 or ", wktBBox)); strSql.Append(string.Format(" A.GEOMETRY.STIntersects(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 ) ", wktBBox)); } if (!string.IsNullOrEmpty(filte)) { strSql.Append(" AND " + "( " + MongoJsonToSql.JsonToSql(filte, bot).ToString() + " )"); } list.AddRange(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString())); return(list); }
/// <summary> /// 根据业务对象ID和业务域查询业务对象别名 /// </summary> /// <param name="boid">业务对象ID</param> /// <param name="appdomains">业务域</param> /// <returns></returns> public AliasCollection GetBOAliasByID(string boid, params string[] appdomains) { AliasCollection aliasColl = new AliasCollection(); foreach (var item in appdomains) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT * FROM ALIASNAME "); strSql.Append(" WHERE BOID =@BOID AND APPDOMAIN=@APPDOMAIN"); SqlParameter[] parameters = { new SqlParameter("BOID", SqlDbType.VarChar, 36), new SqlParameter("APPDOMAIN", SqlDbType.VarChar, 50) }; parameters[0].Value = boid; parameters[1].Value = item; List <Alias> list = SqlServerDBHelper.ExecuteQueryText <Alias>(strSql.ToString(), parameters); aliasColl.AddRange(list); } return(aliasColl); }
/// <summary> /// 获取参数的数据类型 /// </summary> /// <param name="ft"></param> /// <param name="ns"></param> /// <param name="paraName"></param> /// <returns></returns> public string GetPropertyDataType(string ns, string paraName, string bot) { string dataType = string.Empty; StringBuilder strSql = new StringBuilder(); SqlParameter[] parameters; strSql.Append(" SELECT x.MD.value('(/PropertySet/P[@n=\"" + paraName.Trim() + "\"]/@t)[1]','varchar(10)') as datatype"); strSql.Append(" FROM objtypeproperty X, "); strSql.Append(" objecttype t "); strSql.Append(" WHERE t.BOTID=x.BOTID "); strSql.Append(" AND t.BOT= @BOT "); strSql.Append(" AND X.NS = @NS "); parameters = new SqlParameter[] { new SqlParameter("BOT", SqlDbType.VarChar, 50), new SqlParameter("NS", SqlDbType.VarChar, 50) }; parameters[0].Value = bot.Trim(); parameters[1].Value = ns.Trim(); dataType = SqlServerDBHelper.ExecuteQueryText <string>(strSql.ToString(), parameters).FirstOrDefault(); if (string.IsNullOrEmpty(dataType)) { strSql = new StringBuilder(); strSql.Append(" SELECT x.MD.value('(/PropertySet/P[@n=\"" + paraName.Trim() + "\"]/@t)[1]','varchar(10)') as datatype"); strSql.Append(" FROM property X, "); strSql.Append(" objecttype t,bo "); strSql.Append(" WHERE bo.boid=x.boid and bo.BOTID=t.BOTID "); strSql.Append(" AND t.BOT= @BOT "); strSql.Append(" AND X.NS = @NS "); parameters = new SqlParameter[] { new SqlParameter("BOT", SqlDbType.VarChar, 50), new SqlParameter("NS", SqlDbType.VarChar, 50), }; parameters[0].Value = bot.Trim(); parameters[1].Value = ns.Trim(); dataType = SqlServerDBHelper.ExecuteQueryText <string>(strSql.ToString(), parameters).FirstOrDefault(); } return(dataType); }
public NearBOCollection GetNearBOByBo(decimal distince, string bot, string filter, string strSqlGetGeo) { NearBOCollection list = new NearBOCollection(); StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT DISTINCT B.BOID, T.NAME, O.BOT, SUBSTRING(B.SPACELOCATION,0,3800) AS SPACELOCATION, B.DIST DISTINCE "); strSql.Append(" FROM (SELECT A.BOID, A.NAME, A.GEOMETRY.STAsText() AS SPACELOCATION, "); strSql.Append(string.Format(" ({0}).STDistance(A.GEOMETRY ) AS DIST ", strSqlGetGeo)); strSql.Append(" FROM V_GEOMETRY A "); //过滤条件 if (!string.IsNullOrEmpty(filter)) { strSql.Append(" ,PROPERTY PROPERTY "); strSql.Append(" WHERE PROPERTY.BOID = A.BOID "); strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(filter, bot).ToString() + ")"); strSql.Append(" AND "); } else { strSql.Append(" WHERE "); } strSql.Append(string.Format(" ({0}).STDistance(A.GEOMETRY )<{1} ", strSqlGetGeo, distince)); strSql.Append(" ) B "); strSql.Append(" LEFT JOIN BO T "); strSql.Append(" ON B.BOID = T.BOID "); strSql.Append(" LEFT JOIN OBJECTTYPE O "); strSql.Append(" ON O.BOTID = T.BOTID "); //对象类型 if (!string.IsNullOrEmpty(bot)) { strSql.Append(string.Format(" WHERE O.BOT = '{0}'", bot)); } list.AddRange(SqlServerDBHelper.ExecuteQueryText <NearBO>(strSql.ToString())); return(list); }
/// <summary> /// 根据对象ID获取属性数据 /// </summary> /// <param name="boid"></param> /// <returns></returns> public static List <Property> GetPropertyByBoid(string boid) { return(SqlServerDBHelper.ExecuteQueryText <Property>(string.Format("SELECT NS,T.MD MD FROM PROPERTY T WHERE T.BOID = '{0}'", boid))); }
/// <summary> /// 根据对象ID获取空间坐标数据 /// </summary> /// <param name="boid"></param> /// <returns></returns> public static List <Geometry> GetGeometryByBoid(string boid) { return(SqlServerDBHelper.ExecuteQueryText <Geometry>(string.Format("SELECT NAME,T.GEOMETRY.STAsText() GEOMETRY,SOURCEDB FROM GEOMETRY T WHERE T.BOID = '{0}'", boid))); }
/// <summary> /// 根据对象ID获取别名 /// </summary> /// <param name="boid"></param> /// <returns></returns> public static List <AliasName> GetAliasNameByBoid(string boid) { return(SqlServerDBHelper.ExecuteQueryText <AliasName>(string.Format("SELECT APPDOMAIN,NAME FROM ALIASNAME WHERE BOID = '{0}'", boid))); }
/// <summary> /// 获取对象类型集合 /// </summary> /// <returns></returns> public List <string> GetBOTList() { return(SqlServerDBHelper.ExecuteQueryText <string>("SELECT BOT FROM OBJECTTYPE ")); }
/// <summary> /// 根据条件获取3GX数据 /// </summary> /// <param name="filter"></param> /// <returns></returns> public XmlDocument GetFeatures(FeatureFilter filter) { string sqlWhere = string.Empty; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT DISTINCT BO.*,OBJECTTYPE.BOT,OBJECTTYPE.FT FROM BO,OBJECTTYPE"); if (!string.IsNullOrEmpty(filter.BBox)) { strSql.Append(",v_Geometry "); } if (filter.Filter != null) { strSql.Append(" ,PROPERTY PROPERTY "); strSql.Append(" WHERE BO.BOTID = OBJECTTYPE.BOTID "); strSql.Append(" AND PROPERTY.BOID = BO.BOID "); strSql.Append(string.Format(" AND OBJECTTYPE.FT='{0}' ", filter.FT)); strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID "); } else { strSql.Append(string.Format(" WHERE OBJECTTYPE.FT='{0}' ", filter.FT)); strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID "); } //对象名称 if (filter.BOs != null && filter.BOs.Count > 0) { string bos = string.Empty; for (int i = 0; i < filter.BOs.Count; i++) { if (i == filter.BOs.Count - 1) { bos += "'" + filter.BOs[i].Trim() + "'"; } else { bos += "'" + filter.BOs[i].Trim() + "',"; } } strSql.Append(string.Format(" AND BO.NAME in {0} ", bos)); } //空间范围和crs 未启用坐标范围 if (!string.IsNullOrEmpty(filter.BBox)) { strSql.Append(" AND v_Geometry.BOID=BO.BOID "); strSql.Append(string.Format(" AND ( v_Geometry.GEOMETRY.STWithin(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 or ", filter.BBox)); strSql.Append(string.Format(" v_Geometry.GEOMETRY.STIntersects(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 ) ", filter.BBox)); } //属性 if (filter.Filter != null) { JObject jObj = JObject.Parse(filter.Filter.ToString()); string bot = SqlServerDBHelper.ExecuteQueryText <string>(string.Format("SELECT BOT FROM OBJECTTYPE WHERE FT='{0}'", filter.FT)).FirstOrDefault(); strSql.Append(" AND " + "( " + MongoJsonToSql.JsonToSql(jObj.ToString(), bot).ToString() + " )"); } DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); List <GeoFeature> ftList = new List <GeoFeature>(); foreach (DataRow row in dt.Rows) { GeoFeature ft = new GeoFeature(); ft.BOID = row["Boid"].ToString(); ft.BOT = row["BOT"].ToString(); ft.FT = row["FT"].ToString(); ft.NAME = row["Name"].ToString(); ft.AliasNameList = Comm.GetAliasNameByBoid(ft.BOID); ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID); ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID); ftList.Add(ft); } return(GGGXParse.ConvertFT.FeatureToGGGX(ftList)); }
/// <summary> /// 递归查询 /// </summary> /// <param name="model"></param> /// <returns></returns> private List <TreeBO> GetTreeBONode(BOTreeNodeTemplate nodeTemplate, List <TreeBO> treeBOList, BORelation relation) { foreach (var bo in treeBOList) { StringBuilder strSql = new StringBuilder(); if (string.IsNullOrEmpty(relation.ToString()) || relation == BORelation.Space) { strSql.Append(" SELECT T.BOID, T.NAME, T1.BOT,"); strSql.Append("'" + bo.BOID + "'" + " AS PARENTBOID "); strSql.Append(" FROM BO T, OBJECTTYPE T1, GEOMETRY T3 "); //过滤条件 #region if (!string.IsNullOrEmpty(nodeTemplate.Filter)) { strSql.Append(" ,PROPERTY PROPERTY "); strSql.Append(" WHERE PROPERTY.BOID = T.BOID "); JObject jObj = JObject.Parse(nodeTemplate.Filter); JToken strFilter = jObj["filter"]; strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(strFilter.ToString(), bo.BOT).ToString() + ")"); strSql.Append(" AND T.BOTID = T1.BOTID "); strSql.Append(" AND T.BOID = T3.BOID "); } else { strSql.Append(" WHERE T.BOTID = T1.BOTID "); strSql.Append(" AND T.BOID = T3.BOID "); } #endregion //空间包含的子对象 strSql.Append(" AND SDO_FILTER(t3.GEOMETRY, "); strSql.Append(" (SELECT T2.GEOMETRY "); strSql.Append(" FROM GEOMETRY T2 "); strSql.Append(string.Format(" WHERE T2.BOID ='{0}'), ", bo.BOID)); strSql.Append(" 'QUERYTYPE=WINDOW') = 'TRUE' "); } else { strSql.Append(" SELECT T5.BOID, T5.NAME, T6.BOT, "); strSql.Append("'" + bo.BOID + "'" + " AS PARENTBOID "); strSql.Append(" FROM RELATION T4, BO T5, OBJECTTYPE T6 "); //过滤条件 #region if (!string.IsNullOrEmpty(nodeTemplate.Filter)) { strSql.Append(" ,PROPERTY PROPERTY "); strSql.Append(" WHERE PROPERTY.BOID = T5.BOID "); strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(nodeTemplate.Filter, bo.BOT).ToString() + ")"); strSql.Append(" AND T4.RTID = (SELECT T.RTID "); } else { strSql.Append(" WHERE T4.RTID = (SELECT T.RTID "); } #endregion strSql.Append(" FROM RELTYPE T "); strSql.Append(" JOIN OBJECTTYPE T1 "); strSql.Append(" ON T.BOTID1 = T1.BOTID "); strSql.Append(" JOIN OBJECTTYPE T2 "); strSql.Append(" ON T.BOTID2 = T2.BOTID "); strSql.Append(string.Format(" AND T1.BOT = '{0}' ", bo.BOT)); strSql.Append(string.Format(" AND T2.BOT = '{0}') ", nodeTemplate.BOT)); strSql.Append(" AND T4.BOID2 = T5.BOID "); strSql.Append(string.Format(" AND T4.BOID1 = '{0}' ", bo.BOID)); strSql.Append(" AND T5.BOTID = T6.BOTID "); } //过滤条件未添加 if (nodeTemplate.BOs != null && nodeTemplate.BOs.Count() > 0) { if (nodeTemplate.BOs != null && nodeTemplate.BOs.Count() > 0) { string strBos = string.Empty; for (int i = 0; i < nodeTemplate.BOs.Count(); i++) { if (i == nodeTemplate.BOs.Count() - 1) { strBos += "'" + nodeTemplate.BOs[i].Trim() + "'"; } else { strBos += "'" + nodeTemplate.BOs[i].Trim() + "',"; } } strSql.Append(string.Format(" AND T5.NAME IN ({0})", strBos)); strSql.Append(string.Format(" AND T6.BOT = '{0}' ", nodeTemplate.BOT)); } } else { strSql.Append(string.Format(" AND T6.BOT = '{0}' ", nodeTemplate.BOT)); } List <TreeBO> boList = SqlServerDBHelper.ExecuteQueryText <TreeBO>(strSql.ToString()); listTemp.AddRange(boList); if (nodeTemplate.Children != null) { foreach (var children in nodeTemplate.Children) { GetTreeBONode(children, boList, nodeTemplate.Relation); } } } return(listTemp); }
/// <summary> /// 根据业务对象ID获取指定BO的父节点、下级节点、兄弟节点、相邻节点(父节点、兄弟节点和下级节点)和子树。返回的节点中不包括自己 /// </summary> /// <param name="template">树模板</param> /// <returns></returns> public TreeBOCollection GetBOTree(BOTreeTemplate template) { TreeBOCollection list = new TreeBOCollection(); foreach (var item in template) { string strBos = string.Empty; TreeBO treeBo = new TreeBO(); if (item.BOs != null && item.BOs.Count() > 0) { for (int i = 0; i < item.BOs.Count(); i++) { if (i == item.BOs.Count() - 1) { strBos += "'" + item.BOs[i].Trim() + "'"; } else { strBos += "'" + item.BOs[i].Trim() + "',"; } } } StringBuilder strSql = new StringBuilder(); SqlParameter[] parameters; strSql.Append(" SELECT T.BOID, T1.BOT, T.NAME, '' AS ParentBOID "); strSql.Append(" FROM BO T, OBJECTTYPE T1 "); //过滤条件 #region if (!string.IsNullOrEmpty(item.Filter)) { strSql.Append(" ,PROPERTY PROPERTY "); strSql.Append(" WHERE PROPERTY.BOID = T.BOID "); strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(item.Filter, item.BOT).ToString() + ")"); strSql.Append(" AND T.BOTID = T1.BOTID "); } else { strSql.Append(" WHERE T.BOTID = T1.BOTID "); } #endregion if (item.BOs != null && item.BOs.Count() > 0) { strSql.Append(string.Format(" AND T.NAME IN({0}) ", strBos)); strSql.Append(" AND T1.BOT =@BOT "); } else { strSql.Append(" AND T1.BOT =@BOT "); } parameters = new SqlParameter[] { new SqlParameter("BOT", SqlDbType.VarChar, 100) }; parameters[0].Value = item.BOT; List <TreeBO> treeBOList = new List <TreeBO>(); //父节点 treeBOList = SqlServerDBHelper.ExecuteQueryText <TreeBO>(strSql.ToString(), parameters); list.AddRange(treeBOList); if (item.Children != null) { foreach (BOTreeNodeTemplate nodeTemplate in item.Children) { list.AddRange(GetTreeBONode(nodeTemplate, treeBOList, item.Relation)); } } } return(list); }