sql-server - 在VB.NET中检索UDT表结构

在寻找我的答案时，我看到了这个问题:
ADO.NET Retrieve UDT Columns from SQL Server 2012? 但它仍未得到答复。简而言之，这是我的问题，但上下文如下。

我有一个 SQL Server 2012 存储过程，它需要两个表值参数 (TVP)，我想从 VB.NET(现在是 3.5，但我不喜欢这个)调用它们。我已经使用 SqlCommandBuilder.DeriveParameters 来确定我的存储过程的参数:

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

我的存储过程如下所示:

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

我的 UDT 表类型如下所示:

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)

对 DeriveParameters() 的调用按预期填充 cmd1.Parameters，我也可以识别两种 UDT 表类型 - Parameters.TypeName 表明它们实际上是 UDT 表类型 - 这段代码:

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=[]

说了这么多，问题是:

1:有没有办法DERIVE UDT表类型的列？我研究过的所有示例都将使用 DataTable.Columns.Add() 将 COLUMNS 添加到数据表中，然后再将它们作为参数传递给存储过程。理想情况下，我想使用 Parameter.TypeName 值来检索 UDT 的结构。

2:如果 1 的答案是“否”，当我使用 DataTable.Columns.Add() 构建我的 UDT 的结构时，它是否必须与 UDT 本身处于相同的顺序？鉴于上面 TestResultstype 的 UDT 定义，可以这样做:

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:这样做的唯一方法是实现为 CLR(公共(public)语言运行时)程序集吗？

最佳答案

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

关于sql-server - 在VB.NET中检索UDT表结构，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26608765/