C# 将字符串作为几何图形保存到数据库

Question

现在我们正在获取字符串形式的几何数据，我们需要将其作为 Geometry 类型保存到 SQL DB 中。

目前，我正在尝试做类似这样的事情

String shape = "POLYGON((0 0, 150 0, 150 150, 0 150, 0 0))" ;
String insertSQL = "INSERT INTO x (shape) values (@shape)";

SqlCommand cmd = new SqlCommand(insertSql, sqlConnection);
cmd.CommandType = System.Data.Text;

cmd.Parameters.AddWithValue("@shape", "geometry::Parse("+shape+")");

cmd.ExecuteNonQuery();

每次执行查询时都会出现错误:

{"A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":

System.FormatException: 24114: The label geometry::Parse('POL in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only).

System.FormatException:

at Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel(String input)at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s).

The statement has been terminated."}

有没有一种方法可以在不导入 SQL SERVER 类型包的情况下本地完成此操作？或者是实现此目的的唯一方法是包含 Nuget 包并从那里开始？

Answer 1

SQL 查询中的参数用于防止 SQL 注入(inject) - 这意味着您在查询参数中包含的任何内容都不会被视为数据以外的任何内容，不会对其进行求值，因此geometry::Parse 将不会运行。尝试这样的事情:

String shape = "POLYGON((0 0, 150 0, 150 150, 0 150, 0 0))" ;
String insertSQL = "INSERT INTO x (shape) values (geometry::Parse(@shape))";

SqlCommand cmd = new SqlCommand(insertSql, sqlConnection);
cmd.CommandType = System.Data.Text;

cmd.Parameters.AddWithValue("@shape", shape);

cmd.ExecuteNonQuery();