title: Datatable containing SqlGeometry is causing stored procedure execution to fail... Why?
body: <p>I'm trying to save a series of <code>SqlGeometry</code> values to a SQL Server 2008 database.</p>
  
  <p>Basically I have a tabletype in a SQL Server stored procedure which looks like this:</p>
  
  <pre><code>CREATE TYPE [dbo].[TableType_Example] AS TABLE
  (
      [SpatialID] [bigint] NOT NULL,
      [RecordID] [bigint] NOT NULL,
      [geom] [geometry] NOT NULL
  )
  </code></pre>
  
  <p>I then build a datatable in C# and send it like this:</p>
  
  <pre><code>public static bool SaveSpatialDataElements(long recordID, List&lt;BOSpatial&gt; featureList)
  {
          //Setup features datatable
          DataTable dtFeatures = new DataTable();
          dtFeatures.Columns.Add("SpatialID", typeof(SqlInt64));
          dtFeatures.Columns.Add("RecordID", typeof(SqlInt64));
          dtFeatures.Columns.Add("geom", typeof(SqlGeometry));
  
          foreach(var curFeature in featureList)
          {
              object[] curRowObjects = new object[dtFeatures.Columns.Count];
              curRowObjects[0] = curFeature.SpatialID;
              curRowObjects[1] = recordID;
  
              using (var reader = new StringReader(curFeature.ToGML()))
              {
                  using (var xmlreader = new XmlTextReader(reader))
                  {
                      curRowObjects[2] = SqlGeometry.GeomFromGml(new SqlXml(xmlreader), 0);
                  }
              }
  
              DataRow row = dtFeatures.NewRow();
              row.ItemArray = curRowObjects;
              dtFeatures.Rows.Add(row);
          }
  
          DbConn conn = new DbConn();
          conn.Connect();
          conn.ExecuteStoredProcedure(false, "USP_tblSpatialLocation_Update", recordID, dtFeatures);
          conn.Disconnect();
  
          return true;
      }
  </code></pre>
  
  <p>This works fine for all my other datatables but this one contains a <code>SqlGeometry</code> column and it falls over with the error message:</p>
  
  <blockquote>
    <p>An exception of type 'System.ArgumentException' occurred in
    System.Data.dll but was not handled in user code</p>
    
    <p>Additional information: The type of column 'geom' is not supported. 
    The type is 'SqlGeometry'</p>
  </blockquote>
  
  <p>This doesn't make any sense to me as that datatype seems supported by what I'm reading in the documentation.</p>
  
  <p>Any thoughts?</p>
  
  <p>Edit:</p>
  
  <p>The comments below and the article I've linked: <a href="https://viswaug.wordpress.com/2008/09/29/inserting-spatial-data-in-sql-server-2008/" rel="noreferrer">https://viswaug.wordpress.com/2008/09/29/inserting-spatial-data-in-sql-server-2008/</a> seem to suggest that I need to change the datattype of <code>SqlGeometry</code> to <code>SqlDbType.Udt</code>. Sadly as I'm using a datatable I've got no way of defining <code>UdtTypeName = “GEOMETRY”;</code> as this is set on the parameter.</p>