C# geography column in datatable

Question

I have a datatable in C#, and I want to add a column to store latitude and longitude coordinates in a geography format to bulkcopy in SQL Server after that.

In what format should I create the datacolumn for this?

Answer 1

We have to use the reference DLL that is located under "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll":

using Microsoft.SqlServer.Types;

After that, we can create the column in datatable, store some data and successfully send them to SQL Server via bulkcopy.

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

DataRow newRow = datatable.NewRow();
newRow["Geom"] = SqlGeometry.Point(lat, lon, 4326);

datatable.Rows.Add(newRow);

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