SQL Insert Data

Question

am trying to insert a feature data - polygon into SQL 2008. ID-type:nvarchar, geom=>type: geometry

Here's my code:

foreach (FeatureDataRow dsRow in ds.Tables[0])
{
  string ID = (string)dsRow["Name"];
  SharpMap.Geometries.Polygon geom = (SharpMap.Geometries.Polygon)dsRow.Geometry;  

   SqlConnection con = new SqlConnection(MapHelper.GetSQLConnectionString());
   string cmdStr = "INSERT INTO table1 (ID, geom) VALUES (@ID, geometry::STGeomFromWKB(@geom))";
   SqlCommand cmd = new SqlCommand(cmdStr, con);

   //Store parameters with values to the collection
   cmd.Parameters.AddWithValue("ID", ID);
   cmd.Parameters.AddWithValue("geom", geom); //.DBType ???

   con.Open();
   cmd.ExecuteNonQuery();
  con.Close();
}

I get an error on cmd.ExecuteQuery :No mapping exists from object type SharpMap.Geometries.Polygon to a known managed provider native type

I don't know what to set for .DBTYPE for the geom parameter.

Answer 1

You could change your code to:

cmd.Parameters.AddWithValue("geom", geom.AsBinary());

All geometry objects in SharpMap implement the AsBinary() and AsText() methods, which will return the binary/text representation of the geometry. In your insert statement, you are using geometry::STGeomFromWKB(), which expects binary input. Hence, pass the byte array returned by AsBinary method as the parameter's value.

The DbType obviously would be VarBinary.

Answer 2

According to here: http://msdn.microsoft.com/en-us/library/bb933882(v=SQL.105).aspx

It sounds like the type is varbinary(max). Which would imply DbType.VarBinary

Of course, I've never used the spatial types yet.

Answer 3

You should use the type of the argument that geometry::STGeomFromWKB() takes.