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's 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 Managed C++ Application project named OledbschemaTest.
2. Paste the following code into the OledbschemaTest.cpp file to replace the default code:

   #include "stdafx.h"
   
   #using <mscorlib.dll>
   #using <System.dll>
   #using <System.Data.dll>
   
   using namespace System;
   using namespace System::Data;
   using namespace System::Data::OleDb;
   
   // This is the entry point for this application.
   #ifdef _UNICODE
   int wmain(void)
   #else
   int main(void)
   #endif
   {
   	OleDbConnection *cn = new OleDbConnection();
   	OleDbCommand *cmd = new OleDbCommand();
   	DataTable *schemaTable;
   	OleDbDataReader *myReader;
   	DataRow *myField;
   	DataColumn *myProperty;
   
   	// 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 (int i=0; i < schemaTable->Rows->Count - 1; i++)
   	{
   		myField = schemaTable->Rows->Item[i];
   
   		// For each property of the field...
   		for(int j=0; j< schemaTable->Columns->Count -1 ;j++)
   		{
   			myProperty = schemaTable->Columns->Item[j];
   
   			// Display the field name and value.
   			Console::WriteLine(" {0} = {1}", myProperty->ColumnName, 
                           myField->Item[myProperty]->ToString());
   		}
   		Console::WriteLine();
   
   		// Pause.
   		Console::ReadLine();
   	}
   
   	// Always close the DataReader and connection.
   	myReader->Close();
   	cn->Close();
       return 0;
   
   }

3. Modify the parameters of the ConnectionString property to properly connect to your SQL Server computer.
4. Press the F5 key to compile and to run the project. Notice that the properties of each field are listed in the Console window.
5. 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. In Visual Studio .NET, create a new Managed C++ Application project named SqlschemaTest.
2. Paste the following code into the SqlschemaTest.cpp file to replace the default code:

   #include "stdafx.h"
   
   #using <mscorlib.dll>
   #using <System.dll>
   #using <System.Data.dll>
   
   using namespace System;
   using namespace System::Data;
   using namespace System::Data:SqlClient;
   
   // This is the entry point for this application.
   #ifdef _UNICODE
   int wmain(void)
   #else
   int main(void)
   #endif
   {
   	SqlConnection *cn = new SqlConnection();
   	SqlCommand *cmd = new SqlCommand();
   	DataTable *schemaTable;
   	SqlDataReader *myReader;
   	DataRow *myField;
   	DataColumn *myProperty;
   
   	// Open a connection to the SQL Server Northwind database.
   	cn->ConnectionString = "Data Source=server;User Id=login;
                                  Password=Password1;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 (int i=0; i < schemaTable->Rows->Count - 1; i++)
   	{
   		myField = schemaTable->Rows->Item[i];
   
   		// For each property of the field...
   		for(int j=0; j< schemaTable->Columns->Count -1 ;j++)
   		{
   			myProperty = schemaTable->Columns->Item[j];
   
   			// Display the field name and value.
   			Console::WriteLine(" {0} = {1}", myProperty->ColumnName, 
                           myField->Item[myProperty]->ToString());
   		}
   		Console::WriteLine();
   
   		// Pause.
   		Console::ReadLine();
   	}
   
   	// Always close the DataReader and connection.
   	myReader->Close();
   	cn->Close();
   	return 0;
   }
   					

3. Modify the parameters of the ConnectionString property to properly connect to your SQL Server computer.
4. Press F5 to compile and to run the project. Notice that the properties of each field are listed in the Console window.
5. Press ENTER key to scroll through the list, end the console application, and return to the IDE.

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 GetOleDbSchemaTable method of the OleDbConnection object to retrieve schema information, click the article number below to view the article in the Microsoft Knowledge Base: