locked
SQL Server 2k8.User Defined Table Type. Mapping between DbType 'Structured' and Type 'System.Object'

    Question

  •  

    Hi.


    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:

    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3873851&SiteID=1

     

    So now I am trying to just ADO as suggested here:

    http://wiki.evident.nl/Default.aspx?Page=Table-valued%20parameters&AspxAutoDetectCookieSupport=1

     

    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();

    geographyBuilder.SetSrid(4326);

    geographyBuilder.BeginGeography(OpenGisGeographyType.Point);

    geographyBuilder.BeginFigure(1,2);

    geographyBuilder.EndFigure();

    geographyBuilder.EndGeography();

    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);

    sqlConn.Open();

    int result = sqlCmd.ExecuteNonQuery ();

    sqlConn.Close();

    }

    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 ();

    Regards
    Steve
    Tuesday, March 17, 2009 6:44 PM