C# Save String To Database As Geometry-CodePudding

Right now we are getting the geometry data as a string, and we need to save it to our SQL DB as a Geometry type.

Currently, I'm trying to do something that looks like this

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();

Every time the query executes I get an error:

{"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."}

Is there a way to accomplish this natively without importing the SQL SERVER Types package? Or is the only way to achieve this to include the Nuget package and go from there?

CodePudding user response：

Parameters in SQL queries are used to prevent SQL injection - that means nothing you include in a parameter to your query will be treated as anything other than data, it won't be evaluated, so geometry::Parse will not run. Try something like this:

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();