CODEWITHSUNDEEP
c#sql-serverc#-4.0
George
George

Reputation: 295

C# geography column in datatable

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?

Upvotes: 3

Views: 3618

Answers (2)

George
George

Reputation: 295

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

Upvotes: 7

Nico
Nico

Reputation: 12683

Each column should be of a similar type to Decimal(9,6) where you have a length of 9 and a precision of 6. Thus allowing for the maximum value could be 180°

If you want to get really picky it actually could be.

Latitude: Decimal(8,6) (maximum of 90° to 0 north or south of the equator)

Longitude: Decimal(9,6) (maximum of 180° to -180°)

Decimal format in C#

###.######

If you were to combine them together, you will need to use a nvarchar(25) (it could be less than 25, but it leaves room for copy with formatting). As for the exact bulkcopy statement, you will need to provide a sample of the data.

Upvotes: 0

Related Questions