Insert feature data into SQL

Topics: Data Access, SharpMap v0.9 / v1.x, Web Controls
Jun 24, 2011 at 10:36 AM
Edited Jun 24, 2011 at 10:51 AM

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

Here's my code:

SharpMap.Data. FeatureDataSet ds = new SharpMap.Data.FeatureDataSet();
........
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.

Please help!

Here's the stack trace:

[ArgumentException: No mapping exists from object type SharpMap.Geometries.Polygon to a known managed provider native type.]
   System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen) +1919491
   System.Data.SqlClient.SqlParameter.GetMetaTypeOnly() +4872901
   System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +17
   System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) +203
   System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) +237
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   _Default.imgMap_Click(Object sender, ImageClickEventArgs e) in c:\ASL\ASL02\Default.aspx.cs:167
   System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +108
   System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +118
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Coordinator
Jun 24, 2011 at 9:42 PM

Hello tarana,

you want to set-up your SqlServerGeometry from a Well-Known-Binary representation. Therefore you need to convert your SharpMap.Geometry into just that.
Change your second parameter declaration to

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

Jun 24, 2011 at 11:28 PM
Edited Jun 24, 2011 at 11:33 PM

Hi FObermaier,

I changed the cmd.Parameters. as you suggested

Stiil I am getting an error at Execute Query(); System.Data.SqlClient.SqlException: Invalid object name table1

The table does exist.

Stack Trace:

 [SqlException (0x80131904): Invalid object name 'table1'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   _Default.imgMap_Click(Object sender, ImageClickEventArgs e) in c:\ASL\ASL02\Default.aspx.cs:180
   System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +108
   System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +118
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Coordinator
Jun 25, 2011 at 8:21 AM

the error does not seem to be related to the geometry conversion though. Maybe providing schema (dbo?) helps

SharpMap v2 has functioning updateable providers from where you can pull some code:

http://code.google.com/p/sharpmapv2/source/browse/trunk/SharpMap.Data.Providers/DbProviderBase/SpatialDbProviderBase.cs
http://code.google.com/p/sharpmapv2/source/browse/trunk/SharpMap.Data.Providers/MsSqlServer2008/MsSqlServer2008Provider.cs

Hth FObermaier