/// <summary> /// 添加对象类型关系,包括实例的关系 /// </summary> /// <param name="list"></param> /// <returns></returns> public int AddRelationModel(List <RelTypeModel> list) { List <Jurassic.GeoFeature.DBUtility.SqlServerDBHelper.SQLEntity> sqlList = new List <Jurassic.GeoFeature.DBUtility.SqlServerDBHelper.SQLEntity>(); foreach (var item in list) { Jurassic.GeoFeature.DBUtility.SqlServerDBHelper.SQLEntity sqlEntity = new DBUtility.SqlServerDBHelper.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) "); SqlParameter[] parameters = { new SqlParameter("@RTID", SqlDbType.VarChar, 36), new SqlParameter("@RT", SqlDbType.VarChar, 36), new SqlParameter("@REMARK", SqlDbType.VarChar, 100), new SqlParameter("@BOTID1", SqlDbType.VarChar, 36), new SqlParameter("@BOTID2", SqlDbType.VarChar, 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.Sqlparameter = parameters; sqlList.Add(sqlEntity); } return(DBUtility.SqlServerDBHelper.ExecuteSql(sqlList) == true ? 1 : 0); }
/// <summary> /// 删除对象关系类型 /// </summary> /// <param name="reltype"></param> /// <returns></returns> public int Delete(RelTypeModel reltype) { List <Jurassic.GeoFeature.DBUtility.SqlServerDBHelper.SQLEntity> sqlList = new List <Jurassic.GeoFeature.DBUtility.SqlServerDBHelper.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"); SqlParameter[] parameters = { new SqlParameter("@RT", SqlDbType.VarChar, 36) }; parameters[0].Value = reltype.RT; DBUtility.SqlServerDBHelper.SQLEntity DelRelationEntity = new DBUtility.SqlServerDBHelper.SQLEntity(); DelRelationEntity.Sqlstr = DelRelationSql.ToString(); DelRelationEntity.Sqlparameter = parameters; DBUtility.SqlServerDBHelper.SQLEntity DelRelTypeEntity = new DBUtility.SqlServerDBHelper.SQLEntity(); DelRelTypeEntity.Sqlstr = DelRelTypeSql.ToString(); DelRelTypeEntity.Sqlparameter = parameters; sqlList.Add(DelRelationEntity); sqlList.Add(DelRelTypeEntity); return(DBUtility.SqlServerDBHelper.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; SqlServerDBHelper.SQLEntity sqlEntity; SqlParameter[] parameters; StringBuilder strSql = new StringBuilder(); foreach (GeoFeature bo in ftList) { subErr = new SubmissionError(); List<SqlServerDBHelper.SQLEntity> sqlList = new List<SqlServerDBHelper.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 SqlParameter[] { new SqlParameter("NAME",SqlDbType.VarChar,50), new SqlParameter("BOID",SqlDbType.VarChar,36), new SqlParameter("BOTID",SqlDbType.VarChar,36) }; parameters[0].Value = bo.NAME; parameters[1].Value = bo.BOID == null ? System.Guid.NewGuid().ToString() : bo.BOID; //根据FT查找对象的BOTID没用找到时,添加会出现异常 parameters[2].Value = DBUtility.SqlServerDBHelper.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.SqlServerDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Sqlparameter = 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 SqlParameter[] { new SqlParameter("BOID",SqlDbType.VarChar, 36) }; parameters[0].Value = bo.BOID; sqlEntity = new DBUtility.SqlServerDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Sqlparameter = 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,GEOGRAPHY::STGeomFromText(@GEOMETRY, 4326),@SOURCEDB)"); parameters = new SqlParameter[]{ new SqlParameter("BOID",SqlDbType.VarChar,36), new SqlParameter("NAME",SqlDbType.VarChar,100), new SqlParameter("GEOMETRY", SqlDbType.VarChar), new SqlParameter("SOURCEDB",SqlDbType.VarChar,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.SqlServerDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Sqlparameter = 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 SqlParameter[] { new SqlParameter("MD", SqlDbType.Xml), new SqlParameter("MDSOURCE",SqlDbType.VarChar,50), new SqlParameter("BOID",SqlDbType.VarChar,36), new SqlParameter("NS",SqlDbType.VarChar,50) }; parameters[0].Value = property.MD; parameters[1].Value = property.MdSource; parameters[2].Value = bo.BOID; parameters[3].Value = property.NS; sqlEntity = new DBUtility.SqlServerDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Sqlparameter = 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 SqlParameter[]{ new SqlParameter("NAME",SqlDbType.VarChar,50), new SqlParameter("BOID",SqlDbType.VarChar,36), new SqlParameter("APPDOMAIN",SqlDbType.VarChar,50) }; parameters[0].Value = aliasName.NAME; parameters[1].Value = aliasName.BOID; parameters[2].Value = aliasName.APPDOMAIN; sqlEntity = new DBUtility.SqlServerDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Sqlparameter = parameters; sqlList.Add(sqlEntity); } } #endregion try { //存在需要执行的sql语句 if (sqlList.Count > 0) { i = i + (DBUtility.SqlServerDBHelper.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.SqlServerDBHelper.SQLEntity> sqlList = new List <Jurassic.GeoFeature.DBUtility.SqlServerDBHelper.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.SqlServerDBHelper.SQLEntity sqlUpdateEntity = new DBUtility.SqlServerDBHelper.SQLEntity(); StringBuilder UpdateRelTypeSql = new StringBuilder(); UpdateRelTypeSql.Append(" UPDATE RELTYPE "); UpdateRelTypeSql.Append(" SET TITLE=@TITLE,RT=@RT "); UpdateRelTypeSql.Append(" WHERE RTID=@RTID "); SqlParameter[] parameters = { new SqlParameter("@TITLE", SqlDbType.VarChar, 36), new SqlParameter("@RT", SqlDbType.VarChar, 36), new SqlParameter("@RTID", SqlDbType.VarChar, 36) }; parameters[0].Value = item.Title; parameters[1].Value = item.RT; parameters[2].Value = item.Rtid; sqlUpdateEntity.Sqlstr = UpdateRelTypeSql.ToString(); sqlUpdateEntity.Sqlparameter = parameters; sqlList.Add(sqlUpdateEntity); #endregion } else { #region 新增部分 Jurassic.GeoFeature.DBUtility.SqlServerDBHelper.SQLEntity sqlAddEntity = new DBUtility.SqlServerDBHelper.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) "); SqlParameter[] parameters = { new SqlParameter("@RTID", SqlDbType.VarChar, 36), new SqlParameter("@RT", SqlDbType.VarChar, 36), new SqlParameter("@TITLE", SqlDbType.VarChar, 100), new SqlParameter("@BOTID1", SqlDbType.VarChar, 36), new SqlParameter("@BOTID2", SqlDbType.VarChar, 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.Sqlparameter = 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.SqlServerDBHelper.SQLEntity sqlDelEntity = new DBUtility.SqlServerDBHelper.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) "); SqlParameter[] parameters = { new SqlParameter("@BOTID1", SqlDbType.VarChar, 36), new SqlParameter("@BOTID2", SqlDbType.VarChar, 36), new SqlParameter("@RT", SqlDbType.VarChar, 50) }; parameters[0].Value = item.Botid1; parameters[1].Value = item.Botid2; parameters[2].Value = oldRT; sqlDelEntity.Sqlstr = DelRelationSql.ToString(); sqlDelEntity.Sqlparameter = parameters; sqlList.Add(sqlDelEntity); //删除类型关系实例 Jurassic.GeoFeature.DBUtility.SqlServerDBHelper.SQLEntity Entity = new DBUtility.SqlServerDBHelper.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) "); SqlParameter[] parameters1 = { new SqlParameter("@BOTID1", SqlDbType.VarChar, 36), new SqlParameter("@BOTID2", SqlDbType.VarChar, 36), new SqlParameter("@RT", SqlDbType.VarChar, 50) }; parameters1[0].Value = item.Botid1; parameters1[1].Value = item.Botid2; parameters1[2].Value = oldRT; Entity.Sqlstr = delRelTypeSql.ToString(); Entity.Sqlparameter = parameters1; sqlList.Add(Entity); } } #endregion return(DBUtility.SqlServerDBHelper.ExecuteSql(sqlList) == true ? 1 : 0); }