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

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.

back to the top

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.

back to the top

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

back to the top

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.

back to the top

REFERENCES

For more information about the DataReader and the GetSchemaTable method, see the following topics in the Visual Studio .NET Online Help documentation: 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: back to the top