具有WellKnownText空间数据列的SqlBulkCopy DataTable [英] SqlBulkCopy DataTable with WellKnownText spatial data column

问题描述

我正在尝试批量复制DataTable，其中包含以下列:

I'm trying to bulk copy a DataTable which has the following columns:

• "ID"-System.Int32
• "Geom"-System.String

• "ID" - System.Int32
• "Geom" - System.String

进入具有以下列的SQL数据库:

Into a SQL database with the following columns:

• "Id"-int
• 形状"-geometry

• "Id" - int
• "Shape" - geometry

任何人都可以建议最好的方法吗?

Can anyone advise on the best way to do this?

一些测试代码是否有帮助...

Some test code if it helps...

DataTable dataTable = new DataTable();
dataTable.Columns.Add("ID", typeof(Int32));
dataTable.Columns.Add("Geom", typeof(String));

dataTable.Rows.Add(1, "POINT('20,20')");
dataTable.Rows.Add(1, "POINT('40,25')");
dataTable.Rows.Add(1, "POINT('60,30')");

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection);
sqlBulkCopy.DestinationTableName = "MySpatialDataTable";
sqlBulkCopy.WriteToServer(dataTable);

---

我的原始帖子无法解释执行上述操作会引发以下异常.

---

My original post failed to explain that performing the above causes the following Exception to be thrown.

InvalidOperationException:无法将数据源中String类型的给定值转换为指定目标列的udt类型.

InvalidOperationException: The given value of type String from the data source cannot be converted to type udt of the specified target column.

据此我假设SqlBulkCopy不了解geometry列类型，因此不知道如何从string转换为它.有人可以确认吗?

I'm assuming from this that SqlBulkCopy doesn't know about the geometry column type and therefore doesn't know how to convert to it from a string. Can anyone confirm this?

推荐答案

"Geom"列的类型必须为SqlGeometry，而不是字符串. Sql Server将期望用户定义的类型(UDT)用于插入对象上的几何列.这就是我要使用的:

Your "Geom" column needs to be type SqlGeometry, not a string. Sql Server will expect a user defined type (UDT) for a geometry column on an insert. This is what I would use:

DataTable dataTable = new DataTable();
dataTable.Columns.Add("ID", typeof(Int32));
dataTable.Columns.Add("Geom", typeof(SqlGeometry));

dataTable.Rows.Add(1, SqlGeometry.STGeomFromText("POINT('20,20')"));
dataTable.Rows.Add(2, SqlGeometry.STGeomFromText("POINT('40,25')"));
dataTable.Rows.Add(3, SqlGeometry.STGeomFromText("POINT('60,30')"));

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection);
sqlBulkCopy.DestinationTableName = "MySpatialDataTable";
sqlBulkCopy.WriteToServer(dataTable);

请注意，我们从您的字符串构造实际的SqlGeometry类型.批量插入将负责将其转换为SqlServer可以识别的二进制格式.

Note that we construct the actual SqlGeometry type from your string. The bulk insert will take care of converting it to a binary format that SqlServer will recognize.

此外，我不确定为什么要插入多个具有相同ID的记录(示例中所有ID为1).

Also, I'm not sure why you want to insert multiple records with the same ID (you have all ID 1 in your sample).

祝你好运！

这篇关于具有WellKnownText空间数据列的SqlBulkCopy DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！