包含SqlGeometry的数据表导致存储过程执行失败.为什么？

Question

我正在尝试将一系列SqlGeometry值保存到Server 2008数据库中。

基本上，我在Server存储过程中有一个表类型，如下所示：

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

然后，我在C#中构建了一个数据表，并按如下方式发送：

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;
    }

这对于我的所有其他数据数据都很好，但是这个数据包含一个SqlGeometry列，它会出现错误消息：

“System.ArgumentException”类型的异常发生在System.Data.dll中，但未在用户代码中处理 附加信息:不支持列“geom”的类型。类型为“SqlGeometry”

这对我来说没有任何意义，因为我在文档中看到的内容似乎支持这种数据类型。

有什么想法吗？

编辑：

下面的评论和我链接的文章：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和Sql2014)，在为DataTable定义列时，不能将SqlGeography或SqlGeometry设置为typeof()参数。为了绝对清晰起见，您可以在.NET中完成它，甚至可以填充它，但是这样就不能将该表作为TVP传递给存储过程。

有两种选择。

选项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;

我在地理工作中留下了4326的SRID。您可以将它更改为您想要的任何内容--实际上，如果您使用的是Geography，我建议将它作为第二个参数，以使您具有灵活性。

此外，如果性能很关键，您会发现使用WKB更好。我的测试发现，WKB在WKT所用时间的45%到65%之间完成。这将因数据的复杂性和设置的不同而有所不同。

当存储过程的参数为几何或地理类型时，您在将参数的UdtTypeName指定为“几何”/“地理”时发现的信息是正确的。它不适用于TVP。

Answer 2

把它放在这里作为一个替代答案，以防其他人遇到这种情况，并发现它有用，也为了完整性，包括尽可能多的这些信息在一个地方。

如果在您的SqlGeometries代码中根本没有使用c# (我只是在c#代码中包含了一个纯粹用于发送数据库值的库)，那么只使用WKT似乎更有效。

要做到这一点，最明确的方法就是乔恩·贝拉米( Jon )在上面被接受的答案中所暗示的那样。然而，有一种较短的方式似乎表现得更好。基本上，数据库将隐式地将有效的WKT值转换为存储的proc中的几何图形。

示例：

表型

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

C#代码：

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

只是为了澄清。如果您的数据将在您的SqlGeometry代码中采用c#格式，那么按照Jon的建议，使用WKB格式会更快。

Answer 3

我还试图找到一种扩展datatable的方法，但没有成功。但是，我确实找到了一个很好的选择，使用自定义迭代，它可能比datatable更快，而且更灵活。

查看以下链接：

详细说明如何使用自定义迭代器将表值param (TVP)传递给SQL server sproc。

在TVP中使用SQL几何的示例

值得注意的是，这种方法比WKT方法占用的DB更少，因为它不需要DB将输入转换成可用的格式。