如何从查询中检索列数据类型？

I would like to retrieve data types of columns returned from (any) SQL query (using OLE DB). My assumption was, that calling GetSchemaTable method on OleDbDataReader object provides this info in ProviderType column. To transform this number to actual name, I search that value in DataTypes schema table.

我想检索从(任何)SQL查询返回的列的数据类型(使用OLE DB)。我的假设是,在OleDbDataReader对象上调用GetSchemaTable方法在ProviderType列中提供此信息。要将此数字转换为实际名称,我在DataTypes架构表中搜索该值。

My code looks following:

我的代码如下:

Private Sub Test()

    Dim cs = "Provider=SQLOLEDB;Initial Catalog=TestDb;Data Source=127.0.0.1;User ID=XXX;Password=XXX"
    Dim schemaTable As DataTable
    Dim dataTypesTable As DataTable

    Using conn = New OleDbConnection(cs)

        conn.Open()

        Using command = conn.CreateCommand()
            command.CommandText = "SELECT *, 42 AS Foo, CURRENT_TIMESTAMP AS Bar FROM DataItem"

            Using reader = command.ExecuteReader(CommandBehavior.SchemaOnly And CommandBehavior.KeyInfo)
                schemaTable = reader.GetSchemaTable()
            End Using
        End Using
        dataTypesTable = conn.GetSchema("DataTypes")

    End Using

    For Each row As DataRow In schemaTable.Rows

        Dim name = row.Field(Of String)("ColumnName")
        Dim providerType = row.Field(Of Int32)("ProviderType")

        Dim types = dataTypesTable.Rows.OfType(Of DataRow).
                    Where(Function(r) r.Field(Of Int32)("ProviderDbType") = providerType).
                    Select(Function(r) r.Field(Of String)("TypeName"))

        Console.WriteLine($"Column: {name}, Provider type: {providerType}, Types: {String.Join(", ", types)}")
    Next
End Sub

DataItem table is defined like this (SQL Server 2012):

DataItem表定义如下(SQL Server 2012):

CREATE TABLE [dbo].[DataItem](
    [Id] [uniqueidentifier] NOT NULL,
    [NvarcharValue] [nvarchar](50) NOT NULL,
    [NvarcharNullValue] [nvarchar](50) NULL,
    [DateValue] [date] NOT NULL,
    [DateNullValue] [date] NULL,
    [TimeValue] [time](7) NOT NULL,
    [TimeNullValue] [time](7) NULL,
    [DatetimeValue] [datetime] NOT NULL,
    [DatetimeNullValue] [datetime] NULL,
    [SmallintValue] [smallint] NOT NULL,
    [SmallintNullValue] [smallint] NULL,
    [IntValue] [int] NOT NULL,
    [IntNullValue] [int] NULL,
    [BigintValue] [bigint] NOT NULL,
    [BigintNullValue] [bigint] NULL,
    [RealValue] [real] NOT NULL,
    [RealNullValue] [real] NULL,
    [FloatValue] [float] NOT NULL,
    [FloatNullValue] [float] NULL,
    [NumericValue] [numeric](10, 3) NOT NULL,
    [NumericNullValue] [numeric](10, 3) NULL,
    [BitValue] [bit] NOT NULL,
    [BitNullValue] [bit] NULL,
    [ImageValue] [image] NOT NULL,
    [ImageNullValue] [image] NULL,
    [VarbinaryValue] [varbinary](50) NOT NULL,
    [VarbinaryNullValue] [varbinary](50) NULL,
    [GeometryNullValue] [geometry] NULL,
    [GeographyNullValue] [geography] NULL,
    [NvarcharMaxNullValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_DataItem] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Here is actual output:

这是实际输出:

Column: Id, Provider type: 72, Types: uniqueidentifier Column: NvarcharValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: NvarcharNullValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: DateValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: DateNullValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: TimeValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: TimeNullValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: DatetimeValue, Provider type: 135, Types: smalldatetime, datetime Column: DatetimeNullValue, Provider type: 135, Types: smalldatetime, datetime Column: SmallintValue, Provider type: 2, Types: smallint Column: SmallintNullValue, Provider type: 2, Types: smallint Column: IntValue, Provider type: 3, Types: int Column: IntNullValue, Provider type: 3, Types: int Column: BigintValue, Provider type: 20, Types: bigint Column: BigintNullValue, Provider type: 20, Types: bigint Column: RealValue, Provider type: 4, Types: real Column: RealNullValue, Provider type: 4, Types: real Column: FloatValue, Provider type: 5, Types: float Column: FloatNullValue, Provider type: 5, Types: float Column: NumericValue, Provider type: 131, Types: decimal, numeric Column: NumericNullValue, Provider type: 131, Types: decimal, numeric Column: BitValue, Provider type: 11, Types: bit Column: BitNullValue, Provider type: 11, Types: bit Column: ImageValue, Provider type: 205, Types: image Column: ImageNullValue, Provider type: 205, Types: image Column: VarbinaryValue, Provider type: 204, Types: varbinary Column: VarbinaryNullValue, Provider type: 204, Types: varbinary Column: GeometryNullValue, Provider type: 205, Types: image Column: GeographyNullValue, Provider type: 205, Types: image Column: NvarcharMaxNullValue, Provider type: 203, Types: ntext, xml Column: Foo, Provider type: 3, Types: int Column: Bar, Provider type: 135, Types: smalldatetime, datetime

列:Id,提供者类型:72,类型:uniqueidentifier列:NvarcharValue,提供者类型:202,类型:日期,时间,日期时间2,日期时间偏移,nvarchar列:NvarcharNullValue,提供者类型:202,类型:日期,时间,日期时间2,日期时间偏移,nvarchar列:DateValue,提供者类型:202,类型:日期,时间,日期时间2,日期时间偏移,nvarchar列:DateNullValue,提供者类型:202,类型:日期,时间,日期时间2,日期时间偏移,nvarchar列:TimeValue,提供者类型:202 ,类型:日期,时间,datetime2,datetimeoffset,nvarchar列:TimeNullValue,提供者类型:202,类型:date,time,datetime2,datetimeoffset,nvarchar列:DatetimeValue,提供者类型:135,类型:smalldatetime,datetime列:DatetimeNullValue,提供者类型:135,类型:smalldatetime,datetime列:SmallintValue,提供者类型:2,类型:smallint列:SmallintNullValue,提供者类型:2,类型:smallint列:IntValue,提供者类型:3,类型:int列:IntNullValue,提供者类型:3,类型s:int列:BigintValue,提供者类型:20,类型:bigint列:BigintNullValue,提供者类型:20,类型:bigint列:RealValue,提供者类型:4,类型:实列:RealNullValue,提供者类型:4,类型: real Column:FloatValue,Provider type:5,Types:float Column:FloatNullValue,Provider type:5,Types:float Column:NumericValue,Provider type:131,Types:decimal,numeric Column:NumericNullValue,Provider type:131,Types: decimal,numeric列:BitValue,提供程序类型:11,类型:位列:BitNullValue,提供程序类型:11,类型:位列:ImageValue,提供程序类型:205,类型:image列:ImageNullValue,提供程序类型:205,类型: image列:VarbinaryValue,提供者类型:204,类型:varbinary列:VarbinaryNullValue,提供者类型:204,类型:varbinary列:GeometryNullValue,提供者类型:205,类型:image列:GeographyNullValue,提供者类型:205,类型:image列:NvarcharMaxNullValue,Provider类型:203,类型:ntext,xml列:Foo,提供者类型:3,类型:int列:Bar,提供者类型:135,类型:smalldatetime,datetime

My problem is that there are multiple provider types with the same ProviderDbType value and I'm unable to choose the correct one. For example for column NvarcharValue, I expect to get nvarchar type. But date, time, datetime2, datetimeoffset and nvarchar have all the same value 202. What am I doing wrong?

我的问题是有多个提供程序类型具有相同的ProviderDbType值,我无法选择正确的提供程序类型。例如,对于列NvarcharValue,我希望得到nvarchar类型。但是date,time,datetime2,datetimeoffset和nvarchar都具有相同的值202.我做错了什么?

Edit: Just for clarification, I would like to get types of all returned values, not only columns of one particular table. Therefore I cannot simply query INFORMATION_SCHEMA (or sys.columns and sys.types on SQL Server). I modified the code accordingly to make it more obvious.

编辑:为了澄清,我想得到所有返回值的类型,而不仅仅是一个特定表的列。因此,我不能简单地查询INFORMATION_SCHEMA(或SQL Server上的sys.columns和sys.types)。我相应地修改了代码以使其更加明显。

1 个解决方案

#1

I hope this helps. Here is an example script to identify all the columns datatype and other information in a specific database in SQL SERVER

我希望这有帮助。下面是一个示例脚本,用于标识SQL SERVER中特定数据库中的所有列数据类型和其他信息

SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY c.OBJECT_ID;