What SqlDbType enumeration should I use when my column is the Geography type? I'm using MS SQL Server 2008 R2.
This is what I'm looking for specifically:
// ADO.net - what do I use for the SqlDbType when it's defined
// as Geography in the stored proc
SqlCommand command = new SqlCommand();
command.CommandText = "dbo.up_Foobar_Insert";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@SomeGeographyType", SqlDbType.????);
SqlGeography is implemented as a CLR user defined type by SQL Server, so you can do something a little like:
SqlGeography geo = // Get the geography from somewhere...
using (SqlCommand command =
new SqlCommand(@"dbo.up_Foobar_Insert", connection))
command.Parameters.Add(new SqlParameter("@Point", geo) { UdtTypeName = "Geography" });
command.ExecuteNonQuery();
}
If it is a desktop application you've got it quite a bit easier. There is a good example at the Code Project of an SQL Geometry viewer that will help for both desktop or web.
You need to reference Microsoft.SqlServer.Types.dll, found at SQL Server Install/100/SDK/Assemblies to use SQLGeometry or SQLGeography directly.
Update
Try this:
//define parameter
command.Parameters.Add("@shape", SqlDbType.NVarChar);
//
//code in between omitted
//
//set value of parameter
command.Parameters["@shape"].Value = feature.Geometry.AsText();
When I tried to use SqlDbType.NVarChar I received and error
Failed to convert parameter value from Geography to a String
What resolved this problem for me was to use SqlDbType.Udt
var pgeo = cmd.Parameters.Add("@GeoLocation", SqlDbType.Udt);
pgeo.UdtTypeName = "Geography";
And then later, in a loop, I set the value of the parameter:
var ss = new SqlString(objValue.ToString());
var sc = new SqlChars(ss);
var geocode = SqlGeography.STPointFromText(sc, 4326);
cmd.Parameters["@GeoLocation"].Value = geocode;
Note: this requires Microsoft.SqlServer.Types and System.Data.SqlTypes
