SQL Server 2k8.User Defined Table Type. Mapping between DbType 'Structured' and Type 'System.Object'
I created a stored procedure which takes a user defined table type as a parameter. When i drag the stored procedure via the visual studio designer into the dbml I get the following error:
Error 105 DBML1005: Mapping between DbType 'Structured' and Type 'System.Object' in Parameter 'FooItems' of Function 'dbo.FooInsert' is not supported. 0 0
Is this just not supported?
Do I just need to use ADO as specified in this article: http://wiki.evident.nl/Default.aspx?Page=Table-valued%20parameters&AspxAutoDetectCookieSupport=1?
What is the recommended approach here?Tuesday, November 11, 2008 10:03 PM
I researched further, it appears that what I am doing is not supported yet. No LINQ To SQL and Table Parameters.
This post confirms:
So now I am trying to just ADO as suggested here:
only now I have an additional problem... one of my columns in the table parameter is a SqlGeography type. When I try to use ADO I get another error saying that the column type is not supported: SqlGeography.
Code below. Is my only option then to pass the geography as an nvarchar and parse in db if I want to include it as a column in a udtt?
var fooItems = new DataTable();
var geographyBuilder = new SqlGeographyBuilder();
var geography = new SqlGeography();
geography = geographyBuilder.ConstructedGeography;
using (SqlConnection sqlConn = new SqlConnection(Settings.DBConnectionString))
var sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "FooInsert";
SqlParameter param = sqlCmd.Parameters.AddWithValue("@FooItems", fooItems);
int result = sqlCmd.ExecuteNonQuery ();
}Wednesday, November 12, 2008 2:37 AM