Get structure of UDT tables in VB.NET
While looking for an answer I saw this question: ADO.NET Get 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 Parameters (TVP) that I want to call from VB.NET (now 3.5, but I'm not married to that). I used SqlCommandBuilder.DeriveParameters
to define 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 DeriveParameters()
fills cmd1.Parameters
in as expected and I can also identify two types of UDT tables: Parameters.TypeName
indicates that they are, in fact, types of UDT tables - this code:
Debug.Print("parameter [" & p.ParameterName & "]: SqlDbType=[" & _
p.SqlDbType().ToString() & "]: TypeName=[" & p.TypeName() & "])
Productivity:
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, questions:
1: Is there a way to CHECK columns of UDT table types? All the examples I have studied will use DataTable.Columns.Add()
to add COLUMNS to the data before passing them as parameters to a stored procedure. Ideally, I would like to use a value Parameter.TypeName
to retrieve the structure of the UDT.
2: If the answer to 1 is "NO" when I use it DataTable.Columns.Add()
to build the structure of my UDT, should it be in ONE ORDER like the UDT itself? Given the UDT definition for TestResultstype
above, it's okay 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: The only way to do it is to implement it as a CLR (Common Language Runtime) assembly?
source to share
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
source to share