title: How to retrieve column data types from query?
body: <p>I would like to retrieve data types of columns returned from (any) SQL query (using OLE DB). My assumption was, that calling <code>GetSchemaTable</code> method on <code>OleDbDataReader</code> object provides this info in <code>ProviderType</code> column. To transform this number to actual name, I search that value in DataTypes schema table.</p>
  
  <p>My code looks following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>DataItem table is defined like this (SQL Server 2012):</p>
  
  <pre class="lang-sql prettyprint-override"><code>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]
  </code></pre>
  
  <p>Here is actual output:</p>
  
  <p><code>
  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
  </code></p>
  
  <p>My problem is that there are multiple provider types with the same <code>ProviderDbType</code> value and I'm unable to choose the correct one. For example for column <code>NvarcharValue</code>, I expect to get <code>nvarchar</code> type. But <code>date</code>, <code>time</code>, <code>datetime2</code>, <code>datetimeoffset</code> and <code>nvarchar</code> have all the same value <code>202</code>. What am I doing wrong?</p>
  
  <p><strong>Edit:</strong>
  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 <code>INFORMATION_SCHEMA</code> (or <code>sys.columns</code> and <code>sys.types</code> on SQL Server). I modified the code accordingly to make it more obvious.</p>