Access schema info for query (similar to GetOleDbSchemaTable option)

Hi All,

Till recently we were using the following code to retreive schema information of columns of tables

Dim schemaTable = connection.GetOleDbSchemaTable( _
          System.Data.OleDb.OleDbSchemaGuid.Columns, _
          New Object() {Nothing, Nothing, tableName, Nothing})


Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query.

Now my question is if I were to get a query like the following :

SELECT 
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]

then how can I retrieve the schema information of only the columns present in the query.

(PS.. Its possible that i might get a query with multiple tables with joints)

September 10th, 2015 10:40am

Hi Rohan,

Based on my limited knowledge on .NET, you can rely on the SqlDataReader.GetSchemaTable Method. It returns a DataTable that describes the column metadata of the SqlDataReader.


>(PS.. Its possible that i might get a query with multiple tables with joints)
It is possible. Check the columns BaseColumnName, BaseTableName in the table returned by the method. You can find the columns from different joined tables.

If you have any feedback on our support, you can click here.

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 2:27am

Hi Eric,

Thanks for the reply.

With the help of your answer I was able to solve the problem by using the code bellow

	Dim oleCm As New OleDbCommand(tableName, connection)
        schemaTable = oleCm.ExecuteReader().GetSchemaTable

	connection.Close()
        Dim s As String = ""
        schemaTable.Columns.Add("OleDbDataType", Type.GetType("System.String"))
        schemaTable.Columns.Add("CLRDataType", Type.GetType("System.String"))

        For Each row As DataRow In schemaTable.Rows
            Dim t As OleDbType = CType(row("ProviderType"), OleDbType)
            row("OleDbDataType") = t.ToString()
            row("CLRDataType") = row("DataType").ToString()
        Next

The problem is instead of getting pure sql types i'm getting oledbtypes like 'VarWChar' instead of 'VarChar',etc..

Also i'm not able to get BaseTableName and BaseCatalogue name.

Do i have to change anything in the code above to get those properties as well ?

September 11th, 2015 3:54am

Hi Rohan,

It's glad that my reply helped.

To get BaseTableName and BaseCatalogName, get the schemaTable as below.

schemaTable = oleCm.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable()


Regarding the type question, Both "VarWChar" and "VarChar" are OleDbType, as the description says "A variable-length, null-terminated stream of Unicode characters ", so the column should be a nvarchar type in your dat

Free Windows Admin Tool Kit Click here and download it now
September 12th, 2015 1:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics