在VB.NET中检索UDT表结构 [英] Retrieve UDT table structure in VB.NET

问题描述

在寻找我的答案时，我看到了这个问题:ADO.NET 从 SQL Server 2012 检索 UDT 列? 但它仍然没有得到答复.简而言之，这是我的问题，但上下文如下.

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.

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

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

我的 UDT 表类型如下所示:

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)

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

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

说了这么多，问题是:

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

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:如果 1 的答案是NO"，当我使用 DataTable.Columns.Add() 来构建我的 UDT 结构时，它是否必须与UDT 本身?鉴于上述 TestResultstype 的 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:实现这一点的唯一方法是作为 CLR(公共语言运行时)程序集实现吗?

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

这篇关于在VB.NET中检索UDT表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！