Adding geometry data to a SQL Server 2008 database with C#

If you are already using SQL Server 2008, you might be aware that one of the new interesting features is the support for spatial data and coordinates. More specifically, the database supports new data types called "geometry" and "geography", and you can also use these directly from C#. I've found that although the data types itself are nicely documented for instance on SQL Server Books Online (BOL) on MSDN, C# code examples are scarcely available.

Thus, I wanted to briefly share a simple code example on how to add a single geometry point to a SQL Server 2008 database table, which contains a field called "point1" defined as a "geometry" type. In this case, the code that you could use would be:

using Microsoft.SqlServer.Types;
...
int x = 12;
int y = 23;
SqlGeometry geom = SqlGeometry.Point(x, y, 0);
string sql = "INSERT INTO [mytable] " +
"([point1]) VALUES (@point1)";
SqlConnection conn = ...
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter param = cmd.Parameters.
  AddWithValue("@point1", geom);
param.UdtTypeName = "geometry";
conn.Open();
int rows = cmd.ExecuteNonQuery();

I'm presently preparing a longer article on the topic for Developer.com, so stay tuned. The article should be published around late September/early October.