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?
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