Retrieve UDT table structure in VB.NET

Question

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:

cmd1 = New SqlCommand()
cmd1.Connection = oCn2 ' assume properly connected to database
cmd1.CommandText = "uspInsertTestData"
cmd1.CommandType = CommandType.StoredProcedure
SqlCommandBuilder.DeriveParameters(cmd1)

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
AS
BEGIN
-- ... stuff
END

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() & "])

Yields:

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?

score 2 · Accepted Answer · answered Oct 28 '14 at 13:46

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

        ada.Fill(res)

        Return res
    End Using

End Function

An interesting note - using sql_variant in the UDT table does not work when trying to populate from VB.NET. I had to change the UDT to be a varchar(50), then use CONVERT(ResultValue, sql_variant) when loading into the table. — Keith Kibler, Oct 29 '14 at 11:39
@KeithKibler Yes, [it would appear so](http://stackoverflow.com/q/14148301/11683). — GSerg, Oct 29 '14 at 16:26

Retrieve UDT table structure in VB.NET

1 Answers1

Linked