Passing SqlGeometry To Simple.Data Stored Procedure

Question

I am trying to pass a SqlGeometry type to a stored procedure using Simple.Data and SQL Server 2008. The stored procedure definition looks like this:

CREATE PROCEDURE [dbo].[spUpdateDamLocation]
(
  @DamID int,
  @Shape geometry
)

The call I am using for Simple.Data looks like this:

var db = Simple.Data.Database.OpenConnection(dbConn);
db.spUpdateDamLocation(DamID: damLocation.DamID, Shape: damLocation.Shape);

Where dbConn is a connection string, damLocation.DamID is an integer and damLocation.Shape is a SqlGeometry object.

The error I am getting is:

System.ArgumentException: UdtTypeName property must be set for UDT parameters.
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount,     Boolean inSchema, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Simple.Data.Ado.DbCommandExtensions.TryExecuteReader(IDbCommand command)
at Simple.Data.Ado.ProcedureExecutor.ExecuteReader(IDbCommand command)
at Simple.Data.Ado.ProcedureExecutor.Execute(IDictionary`2 suppliedParameters, IDbTransaction transaction)
at Simple.Data.Ado.ProcedureExecutor.Execute(IDictionary`2 suppliedParameters)
at Simple.Data.Ado.AdoAdapter.Execute(String functionName, IDictionary`2 parameters)
at Simple.Data.Database.ExecuteFunction(Object& result, ExecuteFunctionCommand command)
at Simple.Data.DataStrategy.TryInvokeFunction(String functionName, Func`1 getFunctionArguments, Object& result)
at Simple.Data.DataStrategy.TryInvokeMember(InvokeMemberBinder binder, Object[] args, Object& result)
at CallSite.Target(Closure , CallSite , Object , Int32 , SqlGeometry )
at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid3[T0,T1,T2](CallSite site, T0 arg0, T1 arg1, T2 arg2)

Is there a way to specify a specific UDT of the parameter in Simple.Data? If I were just using ADO.NET I would do something like this:

SqlParameter sqlParam = sqlCmd.Parameters.Add("@Shape", SqlDbType.UDT);
sqlParam.UdtTypeName = "geometry";
sqlParam.Value = myTypeOfSqlGeometry;

Im not near a computer right now, but you need to pass it to the proc by WKT/WKB rather than geometry, and recreate it as a geometry in the proc itself. Bit long winded, but it will work. — CatchingMonkey, Oct 23 '12 at 16:35

score 0 · Accepted Answer · answered Oct 23 '12 at 20:20

Simple.Data doesn't currently handle user-defined types at the moment. Issue 163 on github raised a similar question several months ago as encountering a UDT crashed the SqlSchemaProvider. The bug was fixed but it doesn't look like there has been any further development into supporting SqlGeometry, SqlGeography and the like.

A stored procedure, as your're trying would seem to be the best way to go, but as UDTs aren't explicitly supported, perhaps sending the SqlGeometry object as its WKT literal equivalent with a call to ToString() would work?

var db = Simple.Data.Database.OpenConnection(dbConn);
db.spUpdateDamLocation(DamID: damLocation.DamID, Shape: damLocation.Shape.ToString());

Also, With Simple.Data v1 just around the corner, I'd suggest opening a new issue for UDT support to be added in v1.1 so you don't need to go through such hoops in the future.

The solution you described is a good work around. I will open an issue like you suggested, because it would be nice to eventually handle UDT without any extra steps. — Sagebrush GIS, Oct 24 '12 at 14:26

Passing SqlGeometry To Simple.Data Stored Procedure

1 Answers1