SqlBulkCopy DataTable with WellKnownText spatial data column

Question

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

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

Into a SQL database with the following columns:

• "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: The given value of type String from the data source cannot be converted to type udt of the specified target column.

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?

Answer 1

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

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.

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

Good luck!