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