How to retrieve column data types from query?

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.

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):

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

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?

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.

---

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

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;

链接地址: http://www.djcxy.com/p/93858.html

上一篇: 如何从.net代码传递表值参数到存储过程

下一篇: 如何从查询中检索列数据类型？