A View Inside My Head

Jason's Random Thoughts of Interest


Using SQL Server Spatial Objects as ADO.NET Parameter Values

I've previously mentioned that the SQL Server 2008 Spatial data types are freely available for use in your .NET applications, regardless of whether you have SQL Server 2008 or not.  This allows you to incorporate some powerful spatial capabilities right into your application. 

(Look for "Microsoft SQL Server System CLR Types" on this page: http://www.microsoft.com/downloads/details.aspx?FamilyID=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en )

However, in most usage scenarios, there will come a time when you have an instance of a SQL Server spatial object in your .NET application, and need to commit it to your SQL Server 2008 database.  How would you do this, without losing fidelity or resorting to serialization of the object to WKT first?

The solutions is to create a Parameter object of type System.Data.SqlDbType.Udt.  Then set the UdtTypeName parameter to the SQL Server-recognized type name (i.e., for SqlGeometry, you would simply use Geometry).

The following code demonstrates executing an UPDATE statement that sets the value of a Spatial field to a newly constructed object.

using (SqlConnection conn = new SqlConnection("Server=.;Integrated Security=true;Initial Catalog=scratch")) { using (SqlCommand cmd = new SqlCommand("UPDATE fe_2007_us_zcta500 SET Boundary=@boundary WHERE id=@id", conn)) { SqlParameter id = cmd.Parameters.Add("@id", System.Data.SqlDbType.Int); SqlParameter boundary = cmd.Parameters.Add("@boundary", System.Data.SqlDbType.Udt); boundary.UdtTypeName = "geometry"; SqlGeometry geom = SqlGeometry.Parse("POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))"); boundary.Value = geom; id.Value = 123; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } }