using NetTopologySuite.Features; using NetTopologySuite.Geometries; using NetTopologySuite.IO; using System.Data.Common; using System.Data; using System.Runtime.Intrinsics.X86; using System.Windows.Media; using WxMetro.Entity; using System.Collections.Generic; namespace WxMetro { public class Class1 { public static Polygon aaa() { WKTReader reader = new WKTReader(); var point = reader.Read("Point(0 0)") as Point; var line = reader.Read("LineString(1 -1,1 4)") as LineString; var polygon = reader.Read("Polygon((0 0,0 1, 1 1,1 0,0 0))") as Polygon; return polygon; } public static FeatureCollection ReadShpFile(string pathName) { FeatureCollection featureCollection = new FeatureCollection(); var gfactory = GeometryFactory.Floating; ShapefileDataReader dataReader = new ShapefileDataReader(pathName, gfactory); while (dataReader.Read()) { Feature feature = new Feature { Geometry = dataReader.Geometry }; int length = dataReader.DbaseHeader.NumFields; var keys = new string[length]; for (var i = 0; i < length; i++) { keys[i] = dataReader.DbaseHeader.Fields[i].Name; } feature.Attributes = new AttributesTable(); for (var i = 0; i < length; i++) { var val = dataReader.GetValue(i); string value = val.ToString(); feature.Attributes.Add(keys[i], value); } featureCollection.Add(feature); } return featureCollection; } public static void testFreesql() { //后台管理库的表可以实体类查询 //var list = FreeSqlContext.Inst.fsql.Select<GIS_USER_VS_ORG>().Where(a => a.USER_ID == "61315360589").ToList(); //但SDE的图层使用 实体类查询有问题。只有采用下方sql 拼语句办法 //var zd = FreeSqlContext.Inst.fsql.Select<DBZD>().Where(a => a.ZDDM == "32xx6012JC00443").ToList(); //图形 cgk 的sql 语句查询办法 List<(string,string, string, DateTime?, string)> list1 = CgkContext.Inst.fsql.Select<object>().WithSql("select objectid, bdcdyh,zl,t.CREATED_DATE,sde.st_astext(SHAPE) as wkt from DBZD t where zddm='321204116012JC00443'") .ToList<(string,string, string, DateTime?,string)>("objectid,bdcdyh,zl,CREATED_DATE,wkt"); //("321204116012JC00443W00000000", "省市区溪镇里溪村三组45号", null, "POLYGON (( 40499420.18440000 3607093.85080000, 40499420.21680000 3607094.09860000, 40499431.35560000 3607092.66740000, 40499431.05780000 3607090.40900000, 40499435.30080000 3607089.85040000, 40499439.45040000 3607089.34520000, 40499439.59600000 3607090.66420000, 40499439.34540000 3607090.69060000, 40499439.44020000 3607091.58760000, 40499439.86740000 3607095.63320000, 40499440.39180000 3607100.55380000, 40499438.96540000 3607100.70580000, 40499435.96500000 3607101.09780000, 40499428.82560000 3607102.03040000, 40499426.27940000 3607102.36300000, 40499419.48480000 3607103.25060000, 40499418.90200000 3607098.78840000, 40499419.13340000 3607093.98800000, 40499420.18440000 3607093.85080000))") //或者字典 var dic = new Dictionary<string, object>(); dic.Add("OBJECTID", 36932); //要用大写 dic.Add("DJZQDM", "xxxx"); //以下这样修改shape字段不行的： //WKTReader r = new WKTReader(); //NetTopologySuite.Geometries.Geometry geom = r.Read(list1[0].Item5); //var geometryForWGS84 = GeometryFactory.Default.WithSRID(4326).CreateGeometry(geom); //dic.Add("SHAPE", geometryForWGS84); var ret = CgkContext.Inst.fsql.UpdateDict(dic).AsTable("DBZD").WherePrimary("OBJECTID").ExecuteAffrows(); /* 创建的存储过程 create or replace procedure UPDATE_SHAPE(v_t_name in varchar2, v_shp in varchar2, v_epsg_id in int, v_objectid in int, out_ret OUT int) Authid Current_User is v_sql VARCHAR2(2000) :=''; begin v_sql := 'update '||v_t_name||' SET SHAPE=sde.st_geomfromtext('''||v_shp||''','||v_epsg_id||') where objectid='||v_objectid; execute immediate v_sql; out_ret := sql%rowcount; DBMS_OUTPUT.PUT_LINE(v_sql); end; 执行： declare ret int:=-1; begin UPDATE_SHAPE( 'DBZD', 'POLYGON (( 40510751.44660000 3592248.35280000, 40510751.72460000 3592239.97740000, 40510752.00320000 3592233.27300000, 40510778.08700000 3592234.38860000, 40510783.43480000 3592234.61740000, 40510783.26740000 3592238.35980000, 40510783.01100000 3592244.09840000, 40510782.83120000 3592249.40260000, 40510770.51820000 3592249.03100000, 40510751.44660000 3592248.35280000))', 4528, 1,ret); DBMS_OUTPUT.PUT_LINE('aaaa '|| ret); end; */ DbParameter p2 = null; CgkContext.Inst.fsql.Ado.CommandFluent("UPDATE_SHAPE") .CommandType(CommandType.StoredProcedure) .CommandTimeout(60) .WithParameter("v_t_name", "DBZD") .WithParameter("v_shp", list1[0].Item5) .WithParameter("v_epsg_id", 4528) .WithParameter("v_objectid", 1) .WithParameter("out_ret", -1, p => { //(p as OracleParameter).OracleType = ...; p2 = p; //Output 参数 p.DbType = DbType.Int32; p.Direction = ParameterDirection.Output; }) .ExecuteNonQuery(); //.Query<T>() 或者 .ExecuteDataTable() 或者 ... Console.WriteLine(p2.Value); System.Diagnostics.Debug.WriteLine(ret); } } }
Freesql 使用 + NetTopologySuite 使用：
添加这些nuget 包：
XXgkContext: FreeSql的单例类
using Microsoft.Extensions.Configuration; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace WxMetro { public class CgkContext { private static readonly Lazy<CgkContext> lazy = new Lazy<CgkContext>(() => new CgkContext()); public static CgkContext Inst { get { return lazy.Value; } } private CgkContext() { var builder = new ConfigurationBuilder().AddJsonFile("appsettings.json", optional: false, reloadOnChange: true); IConfigurationRoot configuration = builder.Build(); var dbConnectionstring = configuration.GetConnectionString("cgk"); //"cgk2": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.x.x.7)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));Persist Security Info=True;User ID=xx;Password=xx;" //@"user id=xk;password=xk; data source=//xx.x.x7.x7:1521/orcl;Pooling=true;Min Pool Size=1" _fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.Oracle, dbConnectionstring) .UseAutoSyncStructure(true) //automatically synchronize the entity structure to the database .Build(); //be sure to define as singleton mode } private IFreeSql _fsql; public IFreeSql fsql { get { return _fsql; } } } }
