Datatable containing SqlGeometry is causing stored

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.

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.

Answer 2

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.

Answer 3

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.