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

All replies


    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;


    fooItems.Columns.Add("FooId", typeof(long));

    fooItems.Columns.Add("Location", typeof(SqlGeography));

    fooItems.Rows.Add(0, geography);


    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
  •  Hi 
        To pass a Geography type to a store procedure via .Net you need to add one more line of code: param.UdtTypeName = "Geography";

    you will need to do this before you call sqlCmd.ExecuteNonQuery ();

    Tuesday, March 17, 2009 6:44 PM