none
ADO.Net Table Value Parameters -- Passing a (simple) UDT To a TVP using IEnumerator <SqlDataRecord> RRS feed

  • Question

  • Passing a (simple) UDT To a TVP using IEnumerator <SqlDataRecord>

    I have a User Defined Table Type that I am using as a TVP. All the members of the User Defined Table Type are primitive SQL data types. However, one column is a User Defined Type, say,

    CREATE TYPE [udtCustom] FROM varchar NULL

    (it's just a simple abstraction for consistence and easy of refactoring when declaring different columns and parameters etc.)

    Here's my type: CREATE TYPE myCustomType AS TABLE(

    -- other columns here

    [ColumnName] [udtCustom] NULL,

    -- other columns here

    );

    In C#, what should be the data type passed to SqlMetaData method when creating a SqlDataRecord object (to pass an IEnumerable to SqlDbType.Structured)?

    Should I use this constructor

    SqlMetaData Constructor (String, SqlDbType)

    and pass a primitive type

    new SqlMetaData ("ColumnName", SqlDbType.NVarChar, 7)

    or, should I use this constructor

    SqlMetaData Constructor (String, SqlDbType, Type, String)?

    If so, what should be the type of the third parameter, Type new SqlMetaData ("ColumnName", SqlDbType.Udt, [what goes here?], "udtCustom")?

    Thanks in advance!

    Tuesday, March 28, 2017 1:35 AM

All replies

  • Hi MatrixArchitect,

    you could use

    new SqlMetaData ("ColumnName", SqlDbType.NVarChar, 7)

    The following document provide a complete code for your reference.

    http://mikescode.info/2013/08/12/using-table-valued-parameters-to-efficiently-pass-lists-into-sql-server-stored-procedures/

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 28, 2017 9:20 AM
    Moderator
  • Hi Cole,

    Thank you for your response.

    The approach you suggested works and is what I am using right now.

    However, the problem with this approach is that if the definition of UDT changes on SQL server, especially if the field is narrowed, the C# code creating parameter would also need to be updated; which is not hard to do per se. But remembering to do so is. :) It get's worse if similar params are strewn all over code.

    I was hoping to find a more elegant solution where we have a custom type representing the UDT on SQL server that needs to be updated/refreshed in only one location, ideally be regenerating the custom type from SQL server UDT, and referencing this custom type while creating params.

    Thanks again for your response!

    Friday, March 31, 2017 9:42 PM