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?
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: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!