mortware mortware - 2 months ago 18
C# Question

SqlBulkCopy DataTable with WellKnownText spatial data column

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

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!