/// <summary> /// 添加对象类型关系,包括实例的关系 /// </summary> /// <param name="list"></param> /// <returns></returns> public int AddRelationModel(List <RelTypeModel> list) { List <Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity> sqlList = new List <Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity>(); foreach (var item in list) { Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); StringBuilder strInsertSql = new StringBuilder(); strInsertSql.Append(" INSERT INTO RELTYPE( "); strInsertSql.Append(" RTID,RT,REMARK,BOTID1,BOTID2) "); strInsertSql.Append(" VALUES (:RTID,:RT,:REMARK,:BOTID1,:BOTID2) "); OracleParameter[] parameters = { new OracleParameter("RTID", OracleDbType.Varchar2, 36), new OracleParameter("RT", OracleDbType.Varchar2, 36), new OracleParameter("REMARK", OracleDbType.Varchar2, 100), new OracleParameter("BOTID1", OracleDbType.Varchar2, 36), new OracleParameter("BOTID2", OracleDbType.Varchar2, 36) }; parameters[0].Value = item.Rtid; parameters[1].Value = item.RT; parameters[2].Value = item.Title; parameters[3].Value = item.Botid1; parameters[4].Value = item.Botid2; sqlEntity.Sqlstr = strInsertSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } return(DBUtility.OracleDBHelper.OracleHelper.ExecuteSql(sqlList) == true ? 1 : 0); }
/// <summary> /// 删除对象关系类型 /// </summary> /// <param name="reltype"></param> /// <returns></returns> public int Delete(RelTypeModel reltype) { List <Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity> sqlList = new List <Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity>(); StringBuilder DelRelationSql = new StringBuilder(); DelRelationSql.Append(" DELETE RELATION "); DelRelationSql.Append(" WHERE RTID IN "); DelRelationSql.Append(" (SELECT RTID FROM RELTYPE "); DelRelationSql.Append(" WHERE RT=:RT) "); StringBuilder DelRelTypeSql = new StringBuilder(); DelRelTypeSql.Append(" DELETE RELTYPE "); DelRelTypeSql.Append(" WHERE RT=:RT"); OracleParameter[] parameters = { new OracleParameter("RT", OracleDbType.Varchar2, 36) }; parameters[0].Value = reltype.RT; Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity DelRelationEntity = new DBUtility.OracleDBHelper.SQLEntity(); DelRelationEntity.Sqlstr = DelRelationSql.ToString(); DelRelationEntity.Oracleparameter = parameters; Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity DelRelTypeEntity = new DBUtility.OracleDBHelper.SQLEntity(); DelRelTypeEntity.Sqlstr = DelRelTypeSql.ToString(); DelRelTypeEntity.Oracleparameter = parameters; sqlList.Add(DelRelationEntity); sqlList.Add(DelRelTypeEntity); return(DBUtility.OracleDBHelper.OracleHelper.ExecuteSql(sqlList) == true ? 1 : 0); }
/// <summary> /// 保存3GX数据到数据库中 /// </summary> /// <param name="boExModelList"></param> /// <param name="replaceOrLeave"></param> /// <returns></returns> private int Save3GX(List <GeoFeature> ftList, string replaceOrLeave = null) { int i = 0; int iInsertCount = 0; //记录添加条数 int iUpdateCount = 0; //记录修改条数 bool IsInsert = true; OracleDBHelper.SQLEntity sqlEntity; OracleParameter[] parameters; StringBuilder strSql = new StringBuilder(); foreach (GeoFeature bo in ftList) { subErr = new SubmissionError(); List <OracleDBHelper.SQLEntity> sqlList = new List <OracleDBHelper.SQLEntity>(); bool isBoExist = false; GeoFeature seachFt = GetBoListByName(bo.NAME, bo.FT); if (seachFt != null) { isBoExist = true; //对象存在则需要把带入库的对象id都换成库中的该对象id bo.BOID = seachFt.BOID; } else { bo.BOID = System.Guid.NewGuid().ToString(); } #region 添加对象 //存在且保留 if (isBoExist && replaceOrLeave.ToUpper() == "UNCHANGE") { ; } //覆盖 else { strSql = new StringBuilder(); //对象不存在 if (!isBoExist) { strSql.Append(" INSERT INTO BO( "); strSql.Append(" NAME,BOID,BOTID) "); strSql.Append(" VALUES (:NAME,:BOID,:BOTID)"); IsInsert = true; } //对象存在 else { strSql.Append(" UPDATE BO SET "); strSql.Append(" NAME=:NAME "); strSql.Append(" WHERE BOID=:BOID AND BOTID= :BOTID "); IsInsert = false; } parameters = new OracleParameter[] { new OracleParameter("NAME", OracleDbType.Varchar2, 50), new OracleParameter("BOID", OracleDbType.Varchar2, 36), new OracleParameter("BOTID", OracleDbType.Varchar2, 36) }; parameters[0].Value = bo.NAME; parameters[1].Value = bo.BOID == null?System.Guid.NewGuid().ToString() : bo.BOID; //根据FT查找对象的BOTID没用找到时,添加会出现异常 parameters[2].Value = DBUtility.OracleDBHelper.OracleHelper.ExecuteQueryText <string>(string.Format(" SELECT BOTID FROM OBJECTTYPE WHERE FT='{0}'", bo.FT)).FirstOrDefault(); if (parameters[2].Value == null) { subErr.BOName = bo.NAME; subErr.Error = bo.NAME + "对应的对象类型不存在"; result.TotalBO = ftList.Count; result.UpdatedBO = iUpdateCount; result.FailedBO = ListsubErr.Count + 1; result.InsertedBO = iInsertCount; result.Errors.Add(subErr); continue; } sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } #endregion #region 添加坐标数据 bool isGeometryExist = false; //如果对象存在,需要判断坐标是否存在,只要该对象存在一组坐标,就认为该对象坐标存在 if (isBoExist) { bo.GeometryList[0].BOID = bo.BOID; isGeometryExist = GeometryeExist(bo.GeometryList[0]); } //坐标且保留 if (isGeometryExist && replaceOrLeave.ToUpper() == "UNCHANGE") { ; } //覆盖 else { //坐标存在,先删除 if (isGeometryExist) { strSql = new StringBuilder(); strSql.Append(" DELETE FROM GEOMETRY "); strSql.Append(" WHERE BOID =:BOID "); parameters = new OracleParameter[] { new OracleParameter("BOID", OracleDbType.Varchar2, 36) }; parameters[0].Value = bo.BOID; sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } foreach (Geometry geometry in bo.GeometryList) { strSql = new StringBuilder(); strSql.Append(" INSERT INTO GEOMETRY( "); strSql.Append(" BOID,NAME,GEOMETRY,SOURCEDB)"); strSql.Append(" VALUES (:BOID,:NAME,MDSYS.SDO_GEOMETRY(:GEOMETRY,4326),:SOURCEDB)"); parameters = new OracleParameter[] { new OracleParameter("BOID", OracleDbType.Varchar2, 36), new OracleParameter("NAME", OracleDbType.Varchar2, 100), new OracleParameter("GEOMETRY", OracleDbType.Clob), new OracleParameter("SOURCEDB", OracleDbType.Varchar2, 100) }; parameters[0].Value = bo.BOID == null?System.Guid.NewGuid().ToString() : bo.BOID; parameters[1].Value = geometry.NAME == null?geometry.GEOMETRY.Split('(')[0] : geometry.NAME; parameters[2].Value = geometry.GEOMETRY; parameters[3].Value = geometry.SOURCEDB; sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } } #endregion #region 添加属性数据 foreach (Property property in bo.PropertyList) { strSql = new StringBuilder(); bool isPropertyExist = false; if (isBoExist) { property.BOID = bo.BOID; isPropertyExist = PropertyExist(property); } //该条属性存在且保留,跳过 if (isPropertyExist && replaceOrLeave.ToUpper() == "UNCHANGE") { ; } else { if (!isPropertyExist) { strSql.Append(" INSERT INTO PROPERTY( "); strSql.Append(" MD,MDSOURCE,BOID,NS)"); strSql.Append(" VALUES (:MD,:MDSOURCE,:BOID,:NS)"); } else if (isPropertyExist && replaceOrLeave.ToUpper() != "UNCHANGE") { strSql.Append(" UPDATE PROPERTY SET "); strSql.Append(" MD=:MD ,MDSOURCE=:MDSOURCE "); strSql.Append(" WHERE BOID=:BOID AND NS=:NS"); } parameters = new OracleParameter[] { new OracleParameter("MD", OracleDbType.XmlType), new OracleParameter("MDSOURCE", OracleDbType.Varchar2, 50), new OracleParameter("BOID", OracleDbType.Varchar2, 36), new OracleParameter("NS", OracleDbType.Varchar2, 50) }; parameters[0].Value = property.MD; parameters[1].Value = property.MdSource; parameters[2].Value = bo.BOID; parameters[3].Value = property.NS; sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } } #endregion #region 添加别名 foreach (AliasName aliasName in bo.AliasNameList) { strSql = new StringBuilder(); bool isAliasNameExist = false; if (isBoExist) { aliasName.BOID = bo.BOID; isAliasNameExist = AliasNameExist(aliasName); } //该条属性存在且保留,跳过 if (isAliasNameExist && replaceOrLeave.ToUpper() == "UNCHANGE") { ; } else { strSql = new StringBuilder(); if (!isAliasNameExist) { strSql.Append(" INSERT INTO ALIASNAME( "); strSql.Append(" NAME,BOID,APPDOMAIN)"); strSql.Append(" VALUES (:NAME,:BOID,:APPDOMAIN)"); } else if (isAliasNameExist && replaceOrLeave.ToUpper() != "LEAVE") { strSql.Append(" UPDATE ALIASNAME SET "); strSql.Append(" NAME=:NAME "); strSql.Append(" WHERE BOID=:BOID AND APPDOMAIN=:APPDOMAIN"); } parameters = new OracleParameter[] { new OracleParameter("NAME", OracleDbType.Varchar2, 50), new OracleParameter("BOID", OracleDbType.Varchar2, 36), new OracleParameter("APPDOMAIN", OracleDbType.Varchar2, 50) }; parameters[0].Value = aliasName.NAME; parameters[1].Value = aliasName.BOID; parameters[2].Value = aliasName.APPDOMAIN; sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } } #endregion try { //存在需要执行的sql语句 if (sqlList.Count > 0) { i = i + (DBUtility.OracleDBHelper.OracleHelper.ExecuteSql(sqlList) == true ? 1 : 0); if (IsInsert == true) { iInsertCount++; } else { iUpdateCount++; } } } catch (Exception ex) { //记录入库错误信息 subErr.BOName = bo.NAME; subErr.Error = "空间数据有误!"; result.Errors.Add(subErr); //continue; //记录完毕 throw ex; } //构造需要返回的SubmissionResult result.TotalBO = ftList.Count; result.UpdatedBO = iUpdateCount; result.FailedBO = ListsubErr.Count; result.InsertedBO = iInsertCount; //result.Errors.AddRange(ListsubErr); //结束 } return(i); }
/// <summary> /// 修改对象关系类型 /// </summary> /// <param name="list"></param> /// <param name="oldRT"></param> /// <returns></returns> public int UpdateRelTypeByRt(List <RelTypeModel> list, string oldRT) { //1.根据RT查询系统中所有的对象类型 2.对比修改、添加、删除 IList <ObjTypeRelationModel> RelationList = GetRelationListByrt(oldRT); List <Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity> sqlList = new List <Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity>(); //修改关系类型的实例 foreach (var item in list) { ObjTypeRelationModel seachModel = (from p in RelationList where p.Botid1 == item.Botid1 && p.Botid2 == item.Botid2 && p.Rt == oldRT select p).FirstOrDefault(); if (seachModel != null) { #region 修改部分 Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity sqlUpdateEntity = new DBUtility.OracleDBHelper.SQLEntity(); StringBuilder UpdateRelTypeSql = new StringBuilder(); UpdateRelTypeSql.Append(" UPDATE RELTYPE "); UpdateRelTypeSql.Append(" SET TITLE=:TITLE,RT=:RT "); UpdateRelTypeSql.Append(" WHERE RTID=:RTID "); OracleParameter[] parameters = { new OracleParameter("TITLE", OracleDbType.Varchar2, 36), new OracleParameter("RT", OracleDbType.Varchar2, 36), new OracleParameter("RTID", OracleDbType.Varchar2, 36) }; parameters[0].Value = item.Title; parameters[1].Value = item.RT; parameters[2].Value = item.Rtid; sqlUpdateEntity.Sqlstr = UpdateRelTypeSql.ToString(); sqlUpdateEntity.Oracleparameter = parameters; sqlList.Add(sqlUpdateEntity); #endregion } else { #region 新增部分 Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity sqlAddEntity = new DBUtility.OracleDBHelper.SQLEntity(); StringBuilder strInsertSql = new StringBuilder(); strInsertSql.Append(" INSERT INTO RELTYPE( "); strInsertSql.Append(" RTID,RT,TITLE,BOTID1,BOTID2) "); strInsertSql.Append(" VALUES (:RTID,:RT,:TITLE,:BOTID1,:BOTID2) "); OracleParameter[] parameters = { new OracleParameter("RTID", OracleDbType.Varchar2, 36), new OracleParameter("RT", OracleDbType.Varchar2, 36), new OracleParameter("TITLE", OracleDbType.Varchar2, 100), new OracleParameter("BOTID1", OracleDbType.Varchar2, 36), new OracleParameter("BOTID2", OracleDbType.Varchar2, 36) }; parameters[0].Value = System.Guid.NewGuid().ToString(); parameters[1].Value = item.RT; parameters[2].Value = item.Title; parameters[3].Value = item.Botid1; parameters[4].Value = item.Botid2; sqlAddEntity.Sqlstr = strInsertSql.ToString(); sqlAddEntity.Oracleparameter = parameters; sqlList.Add(sqlAddEntity); #endregion } } #region 除部分 foreach (var item in RelationList) { RelTypeModel seachModel = (from p in list where p.Botid1 == item.Botid1 && p.Botid2 == item.Botid2 && item.Rt == oldRT select p).FirstOrDefault(); if (seachModel == null) { //删除对象关系实例 Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity sqlDelEntity = new DBUtility.OracleDBHelper.SQLEntity(); StringBuilder DelRelationSql = new StringBuilder(); DelRelationSql.Append(" DELETE RELATION "); DelRelationSql.Append(" WHERE RTID =(SELECT RTID FROM RELTYPE WHERE BOTID1 =:BOTID1 AND BOTID2=:BOTID2 AND RT=:RT) "); OracleParameter[] parameters = { new OracleParameter("BOTID1", OracleDbType.Varchar2, 36), new OracleParameter("BOTID2", OracleDbType.Varchar2, 36), new OracleParameter("RT", OracleDbType.Varchar2, 50) }; parameters[0].Value = item.Botid1; parameters[1].Value = item.Botid2; parameters[2].Value = oldRT; sqlDelEntity.Sqlstr = DelRelationSql.ToString(); sqlDelEntity.Oracleparameter = parameters; sqlList.Add(sqlDelEntity); //删除类型关系实例 Jurassic.GeoFeature.DBUtility.OracleDBHelper.SQLEntity Entity = new DBUtility.OracleDBHelper.SQLEntity(); StringBuilder delRelTypeSql = new StringBuilder(); delRelTypeSql.Append(" DELETE RELTYPE "); delRelTypeSql.Append(" WHERE RTID =(SELECT RTID FROM RELTYPE WHERE BOTID1 =:BOTID1 AND BOTID2=:BOTID2 AND RT=:RT) "); OracleParameter[] parameters1 = { new OracleParameter("BOTID1", OracleDbType.Varchar2, 36), new OracleParameter("BOTID2", OracleDbType.Varchar2, 36), new OracleParameter("RT", OracleDbType.Varchar2, 50) }; parameters1[0].Value = item.Botid1; parameters1[1].Value = item.Botid2; parameters1[2].Value = oldRT; Entity.Sqlstr = delRelTypeSql.ToString(); Entity.Oracleparameter = parameters1; sqlList.Add(Entity); } } #endregion return(DBUtility.OracleDBHelper.OracleHelper.ExecuteSql(sqlList) == true ? 1 : 0); }