/// <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);
}
