How To Retrieve Column Schema Using the DataReader GetSchemaTable Method and Visual Basic .NET

Article translations Article translations
Article ID: 310108 - View products that this article applies to.
This article was previously published under Q310108
Expand all | Collapse all

On This Page

SUMMARY

This article demonstrates how to use the GetSchemaTable method of the DataReader object in ADO.NET to retrieve column schema information. Another name for a column's schema is its field properties. Column schema information includes the following information about the column:
  • Name
  • Data type
  • Size
  • Whether the column is a Primary Key field
  • Whether the column is an Autonumber (AutoIncrement) field
The GetSchemaTable method returns a DataTable property that contains the column schema for a DataReader. The DataTable contains one row for each field in the resultset. Each column maps to a property of the field in the resultset. The ColumnName property of the DataTable column is the name of the field's property, such as the ColumnName, DataType, ColumnSize, IsKeyColumn, or IsAutoIncrement property. The value of the DataTable column is the value of the field property, such as the FirstName value for the ColumnName property.

Note To get the primary key information that includes whether a field is part of a primary key and whether it is an AutoIncrement field, you must set the CommandBehavior value of the DataReader to CommandBehavior.KeyInfo.

You can use the GetSchemaTable method with either the OLE DB .NET Provider or the SQL .NET Provider. The OleDbDataReader.GetSchemaTable method maps to the OLE DB IColumnsRowset::GetColumnsRowset method. The SqlDataReader.GetSchemaTable method does not use an OLE DB Provider layer.

Of note, unless you explicitly use the GetSchemaTable method, the DataReader does not return column schema. Also, if you use GetSchemaTable to retrieve the column schema, you cannot update the DataReader. The DataReader always retrieves a read-only, forward-only stream of data from a database.

When to Use the GetSchemaTable Method

  • The SqlConnection object does not support retrieving SQL Server schema information in a method analogous to the OleDbConnection object's GetOleDbSchemaTable method. The GetSchemaTable method of the SqlDataReader class provides a straightforward way to obtain column schema information from SQL Server.
  • Although the OleDbConnection object's GetOleDbSchemaTable method can return database, table, and column schema information, you may find that the GetSchemaTable method of the DataReader object is easier to use if you want to retrieve only column schema information.
  • You can use the GetSchemaTable method to create a new DataTable based on an existing DataTable property's schema while you customize the column names and other column attributes. For sample code that demonstrates how to use GetSchemaTable to define a new table, refer to the "Visual Studio Samples: Fitch and Mather 7.0 Run SQL Query" topic in the Microsoft Visual Studio .NET Online Help documentation.

Retrieve Column Schema with the OLE DB .NET Provider

This sample lists the schema information for the columns (field properties) of the Employees table in the SQL Server Northwind database.

Note that when you use the OLE DB .NET Provider, you use the GetSchemaTable method of the OleDbDataReader object.
  1. Start Visual Studio .NET, and then create a new Visual Basic Console Application project. Module1.vb is created by default.
  2. Open the Code window for Module1. Paste the following code at the top of the Code window above the Module declaration:
    Imports System.Data
    Imports System.Data.OleDb
    					
  3. In the Code window, paste the following code in the Sub Main procedure:
    Dim cn As New OleDbConnection()
    Dim cmd As New OleDbCommand()
    Dim schemaTable As DataTable
    Dim myReader As OleDbDataReader
    Dim myField As DataRow
    Dim myProperty As DataColumn
    
    'Open a connection to the SQL Server Northwind database.
    cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=login;" & _
                          "Password=password;Initial Catalog=Northwind"
    cn.Open()
    
    'Retrieve records from the Employees table into a DataReader.
    cmd.Connection = cn
    cmd.CommandText = "SELECT * FROM Employees"
    myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
    
    'Retrieve column schema into a DataTable.
    schemaTable = myReader.GetSchemaTable()
    
    'For each field in the table...
    For Each myField In schemaTable.Rows
        'For each property of the field...
         For Each myProperty In schemaTable.Columns
            'Display the field name and value.
             Console.WriteLine(myProperty.ColumnName & " = " & myField(myProperty).ToString())
         Next
         Console.WriteLine()
    
         'Pause.
         Console.ReadLine()
    Next
    
    'Always close the DataReader and Connection objects.
    myReader.Close()
    cn.Close()
    					
  4. Modify the parameters of the ConnectionString property to properly connect to your SQL Server computer.
  5. Press the F5 key to compile and to run the project. Notice that the properties of each field are listed in the Console window.
  6. Press ENTER to scroll through the list, end the console application, and return to the Integrated Development Environment (IDE).

Retrieve Column Schema with the SQL .NET Provider

This sample lists the schema information for the columns (field properties) of the Employees table in the SQL Server Northwind database.

Note that when you use the SQL .NET Provider, you use the GetSchemaTable method of the SqlDataReader object.
  1. Start Visual Studio .NET, and then create a new Visual Basic Console Application project. Module1.vb is created by default.
  2. Open the Code window for Module1. Paste the following code in the top of the Code window above the Module declaration:
    Imports System.Data
    Imports System.Data.SqlClient
    					
  3. In the Code window, paste the following code in the Sub Main procedure:
    Dim cn As New SqlConnection()
    Dim cmd As New SqlCommand()
    Dim schemaTable As DataTable
    Dim myReader As SqlDataReader
    Dim myField As DataRow
    Dim myProperty As DataColumn
    
    'Open a connection to the SQL Server Northwind database.
    cn.ConnectionString = "Data Source=server;User Id=login;" & _
                          "Password=password;Initial Catalog=Northwind;"
    cn.Open()
    
    'Retrieve records from the Employees table into a DataReader.
    cmd.Connection = cn
    cmd.CommandText = "SELECT * FROM Employees"
    myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
    
    'Retrieve column schema into a DataTable.
    schemaTable = myReader.GetSchemaTable()
    
    'For each field in the table...
    For Each myField In schemaTable.Rows
      'For each property of the field...
       For Each myProperty In schemaTable.Columns
           'Display the field name and value.
           Console.WriteLine(myProperty.ColumnName & " = " & myField(myProperty).ToString())
       Next
       Console.WriteLine()
    
       'Pause.
       Console.ReadLine()
    Next
    
    'Always close the DataReader and Connection objects.
    myReader.Close()
    cn.Close()
    					
  4. Modify the parameters of the ConnectionString property to properly connect to your SQL Server computer.
  5. Press F5 to compile and to run the project. Notice that the properties of each field are listed in the Console window.
  6. Press ENTER key to scroll through the list, end the console application, and return to the IDE.

REFERENCES

For more information about the DataReader and the GetSchemaTable method, see the following topics in the Visual Studio .NET Online Help documentation:
Retrieving Data Using the DataReader
http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

IDataReader.GetSchemaTable Method
http://msdn.microsoft.com/en-us/library/system.data.idatareader.getschematable.aspx

NOTE: This topic includes a complete list of the columns in the returned DataTable.

OleDbDataReader.GetSchemaTable Method
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getschematable.aspx

SqlDataReader.GetSchemaTable Method
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable.aspx
For additional information about using the OleDbConnection object's GetOleDbSchemaTable method to retrieve schema information, click the article number below to view the article in the Microsoft Knowledge Base:
309488 How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual Basic .NET

Properties

Article ID: 310108 - Last Review: July 1, 2004 - Revision: 3.3
APPLIES TO
  • Microsoft ADO.NET 1.1
  • Microsoft ADO.NET 1.0
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
Keywords: 
kbhowto kbsqlclient kbsystemdata KB310108

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com