Microsoft KB Archive/310108

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

Article ID: 310108

Article Last Modified on 7/1/2004

-

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

-



This article was previously published under Q310108



For a Microsoft Visual C# .NET version of this article, see 310107.

For a Microsoft Visual C++ .NET version of this article, see 309683.

This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.OleDb

IN THIS TASK
 SUMMARY

When to Use the GetSchemaTable MethodRetrieve Column Schema with the OLE DB .NET ProviderRetrieve Column Schema with the SQL .NET Provider

 REFERENCES



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 &quot;Visual Studio Samples: Fitch and Mather 7.0 Run SQL Query&quot; 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.  Start Visual Studio .NET, and then create a new Visual Basic Console Application project. Module1.vb is created by default.  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   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 = &quot;Provider=SQLOLEDB;Data Source=server;User ID=login;&quot; & _ &quot;Password=password;Initial Catalog=Northwind&quot; cn.Open

'Retrieve records from the Employees table into a DataReader. cmd.Connection = cn cmd.CommandText = &quot;SELECT * FROM Employees&quot; 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 & &quot; = &quot; & myField(myProperty).ToString) Next Console.WriteLine

'Pause. Console.ReadLine Next

'Always close the DataReader and Connection objects. myReader.Close cn.Close  Modify the parameters of the ConnectionString property to properly connect to your SQL Server computer.</li> Press the F5 key to compile and to run the project. Notice that the properties of each field are listed in the Console window.</li> Press ENTER to scroll through the list, end the console application, and return to the Integrated Development Environment (IDE).</li></ol>

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. <ol> Start Visual Studio .NET, and then create a new Visual Basic Console Application project. Module1.vb is created by default.</li>  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 </li>  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 = &quot;Data Source=server;User Id=login;&quot; & _ &quot;Password=password;Initial Catalog=Northwind;&quot; cn.Open

'Retrieve records from the Employees table into a DataReader. cmd.Connection = cn cmd.CommandText = &quot;SELECT * FROM Employees&quot; 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 & &quot; = &quot; & myField(myProperty).ToString) Next Console.WriteLine

'Pause. Console.ReadLine Next

'Always close the DataReader and Connection objects. myReader.Close cn.Close </li> Modify the parameters of the ConnectionString property to properly connect to your SQL Server computer.</li> Press F5 to compile and to run the project. Notice that the properties of each field are listed in the Console window.</li> Press ENTER key to scroll through the list, end the console application, and return to the IDE.</li></ol>

back to the top