Datatable containing SqlGeometry is causing stored procedure execution to fail... Why?

Question

I'm trying to save a series of SqlGeometry values to a SQL Server 2008 database.

Basically I have a tabletype in a SQL Server stored procedure which looks like this:

CREATE TYPE [dbo].[TableType_Example] AS TABLE
(
    [SpatialID] [bigint] NOT NULL,
    [RecordID] [bigint] NOT NULL,
    [geom] [geometry] NOT NULL
)

I then build a datatable in C# and send it like this:

public static bool SaveSpatialDataElements(long recordID, List<BOSpatial> featureList)
{
        //Setup features datatable
        DataTable dtFeatures = new DataTable();
        dtFeatures.Columns.Add("SpatialID", typeof(SqlInt64));
        dtFeatures.Columns.Add("RecordID", typeof(SqlInt64));
        dtFeatures.Columns.Add("geom", typeof(SqlGeometry));

        foreach(var curFeature in featureList)
        {
            object[] curRowObjects = new object[dtFeatures.Columns.Count];
            curRowObjects[0] = curFeature.SpatialID;
            curRowObjects[1] = recordID;

            using (var reader = new StringReader(curFeature.ToGML()))
            {
                using (var xmlreader = new XmlTextReader(reader))
                {
                    curRowObjects[2] = SqlGeometry.GeomFromGml(new SqlXml(xmlreader), 0);
                }
            }

            DataRow row = dtFeatures.NewRow();
            row.ItemArray = curRowObjects;
            dtFeatures.Rows.Add(row);
        }

        DbConn conn = new DbConn();
        conn.Connect();
        conn.ExecuteStoredProcedure(false, "USP_tblSpatialLocation_Update", recordID, dtFeatures);
        conn.Disconnect();

        return true;
    }

This works fine for all my other datatables but this one contains a SqlGeometry column and it falls over with the error message:

An exception of type 'System.ArgumentException' occurred in System.Data.dll but was not handled in user code

Additional information: The type of column 'geom' is not supported. The type is 'SqlGeometry'

This doesn't make any sense to me as that datatype seems supported by what I'm reading in the documentation.

Any thoughts?

Edit:

The comments below and the article I've linked: https://viswaug.wordpress.com/2008/09/29/inserting-spatial-data-in-sql-server-2008/ seem to suggest that I need to change the datattype of SqlGeometry to SqlDbType.Udt. Sadly as I'm using a datatable I've got no way of defining UdtTypeName = “GEOMETRY”; as this is set on the parameter.

Bidentate

SQL doesn't know what a SqlGeometry is. All parameters to SQL have to be types SQL knows how to handle. – Autoerotism

What documentation have you read that says that "SqlGeometry" is supported? Also what do you do in ExecuteStoredProcedure with the datatable? – However

ExecuteStoredProcedure is just some generic code I've written which allows me to call all my stored procs in a similar fashion, it just creates the SqlCommand and fills the parameters. I'm obviously getting confused by MSSQL 2008 Geometry and the C# SqlGeometry types... I had assumed they were treated as the same thing like the other ones, SqlInt64 and bigint for example – Bidentate

Look into nuget.org/packages/Microsoft.SqlServer.Types. However for spatial data I usually use EntityFramework or pass coordinate information to the stored procedure as a string and let SQL Server do all the conversions internally. – Nollie

Thankyou for your input ElPresidente but I'm already using SqlGeometry from Microsoft.SqlServer.Types which is why I was confused that it wasn't working. Passing the coordinate information as a string seems sensible... I'm really not sure why I'm not just doing that! It will avoid a lot of hassle converting to/from the various types or bothering with GML in the first place. – Bidentate

Answer 1

Since making a short comment on your question, I've had chance to fully play around with the options. It appears that at present (even trying .NET 4.6 and SQL 2014) you cannot set SqlGeography OR SqlGeometry as the typeof() parameter when defining a column for a DataTable. For absolute clarity, you can do it in .NET and even populate it, but you then cannot then pass that table as a TVP to a Stored Procedure.

There are two options.

Option 1. Pass the value in WKT format.

Define your table-type as follows.

CREATE TYPE [dbo].[WKT_Example] AS TABLE
(
    [geom] [varchar](max) NOT NULL
)

Then define your Stored Procedure as follows.

CREATE PROCEDURE [dbo].[BulkInsertFromWKT]

    @rows [dbo].[WKT_Example] READONLY

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Table1]
        ([SpatialData])
    SELECT
        geometry::STGeomFromText(R.[SpatialData], 4326)
    FROM
        @rows R;

END

Define your .NET DataTable as follows:

DataTable wktTable = new DataTable();
wktTable.Columns.Add("SpatialData", typeof(string));

Populate it as follows:

for (int j = 0; j < geometryCollection.Count; j++)
{
    System.Data.SqlTypes.SqlString wkt = geometryCollection[j].STAsText().ToSqlString();

    wktTable.Rows.Add(wkt.ToString());
}

Option 2. Pass the value in WKB format.

Define your table-type as follows.

CREATE TYPE [dbo].[WKB_Example] AS TABLE
(
    [geom] [varbinary](max) NOT NULL
)

Then define your Stored Procedure as follows.

CREATE PROCEDURE [dbo].[BulkInsertFromWKB]

    @rows [dbo].[WKB_Example] READONLY

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Table1]
        ([SpatialData])
    SELECT
        geometry::STGeomFromWKB(R.[SpatialData], 4326)
    FROM
        @rows R;

END

Define your .NET DataTable as follows:

DataTable wkbTable = new DataTable();
wkbTable.Columns.Add("SpatialData", typeof(System.Data.SqlTypes.SqlBytes));

Populate it as follows:

for (int j = 0; j < geometryCollection.Count; j++)
{
    wkbTable.Rows.Add(geographyCollection[j].STAsBinary());
}

Notes:

Define your SqlParameter as follows:

SqlParameter p = new SqlParameter("@rows", SqlDbType.Structured);
p.TypeName = "WKB_Example"; // The name of your table type
p.Value = wkbTable;

I've left an SRID of 4326 in from my geography work. You can change this to whatever you wish - and indeed if you're using Geography I would suggest making it a second parameter to give you flexibility.

Additionally, if performance is critical, you'll find using WKB better. My tests found that WKB completed in 45% to 65% of the time WKT took. This will vary by the complexity of your data and by your setup.

The information you found on specifying the parameter's UdtTypeName as "Geometry" / "Geography" is correct when your Stored Procedure has a parameter of type [Geometry] or [Geography]. It doesn't apply to TVPs.

Uncovered

Thanks very much for this! I'd ended up going down the wkt route as a stopgap until I'd found a better solution but now I've adapted it to something more permanent. (Got the db outputting wkt rather than sqlgeometries so I can do away with sql geo datatypes in my c# code altogether). – Bidentate

@Bidentate No problem! So you're storing the data spatially, but then converting to and from WKT for transmission between your .NET library and your database? I guess that's fine if your .NET code merely sends the WKT back to a caller in string format but if you're then converting back to SqlGeometry for processing in any way, it's not the most efficient route. That being said, a few milliseconds here and there may not be an issue for you!?!? – Uncovered

Basically to give you the complete picture I'm storing the data spatially for spatial queries but wanting it as geoJSON for working with a leaflet map when actually displaying the data. Translation wise it goes like this: Database -> WKT -> geoJSON -> WKT -> Database. I'm finding this more efficient than WKB because I'm not actually ever creating SqlGeomeries in my c# code anymore. Interestingly SQL seems to implicitly convert from WKT to Geometry if you pass it WKT but have the stored proc parameter (or table type column) as Geometry type. I'll post an example answer below – Bidentate

@Bidentate are you using EntityFramework as your data layer or a hand rolled queries? EF is mapping my Geography column to a DbGeography but I'm having a hard time converting it from GeoJson as I want a geography collection in my database. I can convert multiple polygons to a multipolygon but that's not what I want to do. – Knowland

Hand rolled queries I'm afraid. I've not dealt yet with SQL spatial types and entity framework. If you are using SQL Server and definately not going to change the database used in the project then you could try fudging the mapping in entity framework to do it as I have above. Not sure how that is going to work out for you though! – Bidentate

Answer 2

Putting this up here as an alternative answer just in case anyone else runs into this and finds it useful, and also for completeness to include as much of this information in once place as possible.

If you aren't using SqlGeometries at all in your c# code (I just had the library included purely for sending the database values) then it seems more efficient to just stick with WKT.

The most clear way to do this is as Jon Bellamy suggests in the accepted answer above. However there is a shorter way which seems to perform better. Basically the database will convert valid WKT values implicitly to Geometries in a stored proc.

Example:

Table Type

CREATE TYPE [dbo].[WKT_Example] AS TABLE
(
    [geom] Geometry NOT NULL
)

Stored Proc:

CREATE PROCEDURE [dbo].[BulkInsertFromWKB]
    @rows [dbo].[WKB_Example] READONLY
AS
BEGIN
    INSERT INTO [dbo].[Table1]
      ([SpatialData])
    SELECT
        geom
    FROM
        @rows
END

C# Code:

DataTable wkbTable = new DataTable();
wkbTable.Columns.Add("SpatialData", typeof(SqlString));
for (int j = 0; j < arrOfWKT.Count; j++)
{
    wkbTable.Rows.Add(arrOfWKT[j]);
}

Just to clarify. If your data is going to be in the format of SqlGeometry in your c# code anyway then you are faster to use the WKB format as suggested above by Jon Bellamy.

Bidentate

+1 for completeness! I've been using the SqlGeography type for a few years now and never would have thought to just pass it as WKT!!! – Uncovered

Answer 3

I also tried to figure out a way to extend the datatable, but didn't manage. However, I did find a good alternative using custom iterations which is probably faster than the datatable and is much more flexible.

checkout these links:
A detailed explanation of how to use custom iterators to pass a table valued param (TVP) to SQL server sproc
An example using SQL geometry in a TVP

I'ts worth noting that this approach has a lighter DB footprint than the WKT method because it doesn't require the DB to convert inputs to a usable format.

Helm