A datatable containing SqlGeometry causes the stored procedure to fail ... Why?

Question

I am trying to save a series of values SqlGeometryin a SQL Server 2008 database.

Basically, I have a table type in a SQL Server stored procedure that looks like this:

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

Then I create a datatable in C # and post 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 data, but there is a column in this SqlGeometryand it crashes with an error message:

An exception of type "System.ArgumentException" occurred in System.Data.dll, but was not processed in the user code.

Additional Information: The column type "geom" is not supported. Type "SqlGeometry"

, , , , .

?

Edit:

, : https://viswaug.wordpress.com/2008/09/29/inserting-spatial-data-in-sql-server-2008/, , , SqlGeometry SqlDbType.Udt. , datatable, UdtTypeName = "GEOMETRY";, .

Answer 1

, . , ( .NET 4.6 SQL 2014) SqlGeography OR SqlGeometry typeof() DataTable. .NET , .

.

1. WKT.

.

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

.

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

.NET DataTable :

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

:

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

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

2. WKB.

.

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

.

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

.NET DataTable :

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

:

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

:

SqlParameter :

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

SRID 4326 . , , , Geography, , .

, , WKB . , WKB 45-65% , WKT . .

, UdtTypeName "" / "", , [] []. .

Answer 2

, , - , .

SqlGeometries # ( ), WKT.

, . , , , . WKT Geometries .

:

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

Proc:

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

#:

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

. SqlGeometry # , WKB, , .

Answer 3

, . , , , , , , .

:
, (TVP) SQL- sproc
SQL TVP

, , WKT, , DB .