Retrieve UDT table structure in VB.NET

In searching for my answer, I saw this question:
ADO.NET Retrieve UDT Columns from SQL Server 2012? but it remains unanswered. In short, this is my question, but the context is below.

I have a SQL Server 2012 stored procedure that takes two table-valued parameters (TVPs) which I want to call from VB.NET (3.5 now, but I am not married to this). I have used SqlCommandBuilder.DeriveParameters to determine the parameters of my stored procedure:

  • T-SQL - Get runduration from a restore job
  • SQL Server View Primary Key
  • SQL Server CE 4.0 with multiple connections across seperate processes
  • Multiplicity constraint violated Entity framework 5
  • SqlDependency causes error in other application
  • SQL Server split CSV into multiple rows
  • cmd1 = New SqlCommand()
    cmd1.Connection = oCn2 ' assume properly connected to database
    cmd1.CommandText = "uspInsertTestData"
    cmd1.CommandType = CommandType.StoredProcedure

    My Stored procedure looks like this:

    ALTER Procedure [dbo].[uspInsertTestData]
            @Processor varchar(20),
            @TRes [dbo].[TestResultsType] READONLY,
            @EXSetup ExtraTestSetupType READONLY,
            @ErrNum int OUTPUT,
            @ErrString varchar(300) OUTPUT
    -- ... stuff

    And my UDT table types look like this:

    CREATE TYPE [dbo].[TestResultsType] AS TABLE(
        [ResultValue] [sql_variant] NULL,
        [ResultInfo] [varchar](50) NULL,
        [ResultUnits] [char](20) NULL,
        [PassedTest] [varchar](15) NULL,
        [TestName] [varchar](30) NULL,
        [TestTypeName] [varchar](50) NULL,
        [MinLimit] [float] NULL,
        [MaxLimit] [float] NULL,
        [UUTTemperature] [float] NULL)
    CREATE TYPE [dbo].[ExtraTestSetupType] AS TABLE(
        [FieldName] [varchar](50) NULL,
        [FieldValue] [varchar](50) NULL,
        [Units] [char](20) NULL)

    The call to DeriveParameters() populates cmd1.Parameters as expected, and I can identify the two UDT table types as well – the Parameters.TypeName indicate that they are, in fact, UDT table types – this code:

    Debug.Print("parameter [" & p.ParameterName & "]: SqlDbType=[" & _
          p.SqlDbType().ToString() & "]: TypeName=[" & p.TypeName() & "])


    parameter [@Processor]: SqlDbType=[VarChar]: TypeName=[]
    parameter [@TRes]: SqlDbType=[Structured]: TypeName=[LMUTesterData.dbo.TestResultsType]
    parameter [@EXSetup]: SqlDbType=[Structured]: TypeName=[LMUTesterData.dbo.ExtraTestSetupType]
    parameter [@ErrNum]: SqlDbType=[Int]: TypeName=[]
    parameter [@ErrString]: SqlDbType=[VarChar]: TypeName=[]

    All that said, the questions are:

    1: Is there a way to DERIVE the columns of the UDT table types? All the examples I have studied will use DataTable.Columns.Add() to add the COLUMNS to the datatables before passing them as parameters to stored procedure. Ideally, I would like to use the Parameter.TypeName value to retrieve the structure of the UDT.

    2: If the answer to 1 is “NO”, when i use DataTable.Columns.Add() to build the structure of my UDT, does it have to be in the SAME ORDER as the UDT itself? Given the UDT definition for TestResultstype above, is it OK to do:

    DataTable dataTable = new DataTable("TestResultsType"); 
    dataTable.Columns.Add("ResultUnits", GetType(string)); 
    dataTable.Columns.Add("ResultInfo", GetType(string)); 
    dataTable.Columns.Add("ResultValue", GetType(float)); 
    dataTable.Columns.Add("TestTypeName", GetType(string)); 
    dataTable.Columns.Add("UUTTemperature", GetType(float)); 
    dataTable.Columns.Add("MinLimit", GetType(float)); 
    dataTable.Columns.Add("MaxLimit", GetType(float)); 

    3: Is the only way to do this is to implement as CLR (Common Language Runtime) assembly?

  • Using Entity Framework to migrate database structure
  • Recommendation system experimentation
  • How to transpose CSV string into rows?
  • Dynamic month year columns per date range
  • T-Sql function to convert a varchar - in this instance someone's name - from upper to title case?
  • Can't add more then one user to the table. Identity Insert is set to off
  • One Solution collect form web for “Retrieve UDT table structure in VB.NET”

    Private Function DataTableForServerType(ByVal Connection As SqlConnection, ByVal ServerTypeName As String) As DataTable
        Dim SanitisedQualifiedTypeName As String
        Using cmd = New SqlCommand()
            cmd.Connection = Connection
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "select top (1) quotename(schema_name([schema_id]), '[') + N'.' + quotename([name], '[') from [sys].[types] where [name] = parsename(@type_name,1) and [schema_id] = isnull(schema_id(parsename(@type_name,2)), [schema_id]) and [is_table_type] = 1;"
            cmd.Parameters.Add("@type_name", SqlDbType.NVarChar, 128).Value = ServerTypeName
            SanitisedQualifiedTypeName = CType(cmd.ExecuteScalar(), String)
            If String.IsNullOrEmpty(SanitisedQualifiedTypeName) Then
                Throw New Exception(String.Format("Table type '{0}' does not exist or you don't have permission.", ServerTypeName))
            End If
        End Using
        Using ada = New SqlDataAdapter("declare @t " & SanitisedQualifiedTypeName & "; select * from @t;", Connection)
            Dim res As New DataTable
            Return res
        End Using
    End Function
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.