I have used this string and tested it with string concatenation.But as you know it is not safe to use this to format an sql command.
SqlCommand param = new SqlCommand();
param.CommandText = "INSERT INTO Circle (Center_Point, Circle_Data) VALUES (geometry::STGeomFromText('POINT(@center_lat @center_lng)',0),geometry::STGeomFromText('POLYGON((@polygon))',0));";
param.Parameters.Add(new SqlParameter("@center_lat", center_lat));
param.Parameters.Add(new SqlParameter("@center_lng", center_lng));
param.Parameters.Add(new SqlParameter("@polygon", polygon));
I go to parametrize the string and get the following error:
System.Data.SqlClient.SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24141: A number is expected at position 17 of the input. The input has @center_lat.
Looks like it hasn't put the value into the string. but when I step through the code it does indeed hold the value.
What could be the problem?
Thanks
Thanks to Me.Name. I had to add the correct assemblies to the ASP.net project, which enabled me to set the UDT type correctly. Updated Code is below.
SqlCommand param = new SqlCommand();
SqlGeometry point = SqlGeometry.Point(center_lat,center_lng,0);
SqlGeometry poly = SqlGeometry.STPolyFromText(new SqlChars(new SqlString(polygon)),0);
param.CommandText = "INSERT INTO Circle (Center_Point, Circle_Data) VALUES (@point,@poly);";
param.Parameters.Add(new SqlParameter("@point", SqlDbType.Udt));
param.Parameters.Add(new SqlParameter("@poly", SqlDbType.Udt));
param.Parameters["@point"].UdtTypeName = "geometry";
param.Parameters["@poly"].UdtTypeName = "geometry";
param.Parameters["@point"].Value = point;
param.Parameters["@poly"].Value = poly;
For me, using MySQL geometry, I had to use the MySqlGeometry class rather than the SqlGeometry as in the answer from @SammyG
parameters.Add(new
{
...
MyGeometryObject = MySqlGeometry.Parse($"POINT({point.WGS84Lon} {point.WGS84Lat})").Value,
});
User contributions licensed under CC BY-SA 3.0