SqlGeometry parameter error in DataTable translated to TVP

Question

I am trying to pass in a TVP datatable containing geometric polygons. For some reason, my geometry objects are being rejected before the query is even run with the following error: System.ArgumentException: The type of column 'Coordinates' is not supported. The type is 'SqlGeometry'

The TVP I'm using is as follows:

CREATE TYPE [prop].[ShapeTableType] AS TABLE(
    [Coordinates] [geometry] NULL,
    [Inclusive] [bit] NULL,
    [Radius] [decimal](7, 2) NULL,
    [ShapeType] [varchar](16) NULL
)
GO

and the code for populating it is as follows:

Create datatable:

var dataTable = new DataTable();
dataTable.Columns.Add("Coordinates", typeof(SqlGeometry));
dataTable.Columns.Add("Inclusive", typeof(bool));
dataTable.Columns.Add("Radius", typeof(decimal));
dataTable.Columns.Add("ShapeType", typeof(string));

Create SqlParameter:

var parameter = new SqlParameter(parameterName, SqlDbType.Structured)
            {
                TypeName = "prop.ShapeTableType",
                Value = dataTable
            };
_parameters.Add(parameter);

foreach (var shape in shapes)
        {
            var polygon = shape as Polygon;

            if (polygon != null)
            {
                var geoPolygon = GetGeometryBuilder(polygon.Coordinates).ConstructedGeometry;
                if (geoPolygon == null) continue;
                dataTable.Rows.Add(geoPolygon, polygon.IsInclusive, DBNull.Value, "polygon");
            }
        }

Method to extract geography object from a list of lat/long:

static SqlGeometryBuilder GetGeometryBuilder(IEnumerable<PointF> points)
        {
            SqlGeometryBuilder builder = new SqlGeometryBuilder();
            builder.SetSrid(4326);
            builder.BeginGeometry(OpenGisGeometryType.Polygon);
            var firstPoint = points.First();
            builder.BeginFigure(firstPoint.X, firstPoint.Y);
            foreach (PointF point in points)
            {
                // check if any of the points equal the first point. If so, do not add them as we'll do this manually at the end
                // to ensure the polygon is closed properly.
                if (point != firstPoint)
                {
                    builder.AddLine(point.X, point.Y);
                }
            }
            builder.AddLine(firstPoint.X, firstPoint.Y);
            builder.EndFigure();
            builder.EndGeometry();
            return builder;
        }

I am beginning to worry that the type 'SqlGeometry' is not allowed in DataTable objects, but I have not found any documentation explicitly stating this.

SqlGeometry parameter error in DataTable translated to TVP

0 Answers0