locked
Getting Table Column Names RRS feed

  • Question

  • I am looking for the best way to identify the column names of a database table at runtime, especially with using as little memory and time as possible because it is possible that this file will grow quite large. Ideally, I would get just the column names back with no data.

    I can do this using a datareader and filling a dataset- it occured to me to create a query that would return a single row and access the resulting dataset to get the column names. This still seems like a waste of steps, is it possible just to get these names directly?

    Thanks all!

     

     

    Thursday, August 3, 2006 7:29 PM

Answers

  • you have a few different choices...if you need the data also I would just iterate through the datacolumns collection of the datatable....if all you need is schema info then:

    Public Overrides Function GetSchema(ByVal collectionName As String) As System.Data.DataTable

    Member of: System.Data.OleDb.OleDbConnection

    Summary:

    Returns schema information for the data source of this System.Data.OleDb.OleDbConnection using the specified string for the schema name.

    Parameters:

    collectionName: Specifies the name of the schema to return. 

    Return Values:

    A System.Data.DataTable that contains schema information.

    Thursday, August 3, 2006 8:01 PM

All replies

  • you have a few different choices...if you need the data also I would just iterate through the datacolumns collection of the datatable....if all you need is schema info then:

    Public Overrides Function GetSchema(ByVal collectionName As String) As System.Data.DataTable

    Member of: System.Data.OleDb.OleDbConnection

    Summary:

    Returns schema information for the data source of this System.Data.OleDb.OleDbConnection using the specified string for the schema name.

    Parameters:

    collectionName: Specifies the name of the schema to return. 

    Return Values:

    A System.Data.DataTable that contains schema information.

    Thursday, August 3, 2006 8:01 PM
  • The fastest way is to just quering directly the database schema.

    For example for Sql Server, this means to query the INFORMATION_SCHEMA.COLUMNS view, like for example

    Select column_name From INFORMATION_SCHEMA.COLUMNS Where table_name = '...' And table_catalog = '...' And table_schema = '...'

    For other databases there are similar methods

    Friday, August 4, 2006 2:52 AM
  • Hi Guy

    If you want the  schema information of a table even before loading the table you can also do as follow: (VB Code)

    Public Function GetSchemaTable(ByVal Command as DbCommand) As DataTable
        

         ' DbCommand mast have been built ans has its Connection property set
         With DbCommand
              Dim DataReader As DbDataReader = Nothing
              Try
                    DataReader = .ExecuteReader(CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo)
                    Return DataReader.GetSchemaTable
               Finally
                    ' Must ALWAYS close any opened reader
                    If DataReader IsNot Nothing AndAlso Not DataReader.IsClosed Then DataReader.Close()
              End Try
         End With

    End Function

    This retrieves you all the informations needed about each column of the table

    Cheers!

     

    Saturday, August 5, 2006 9:43 AM