How To Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C++ .NET (309683)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET (included with the .NET Framework 1.1)
  • Microsoft Visual C++ .NET (2002)
  • Microsoft Visual C++ .NET (2003)
This article was previously published under Q309683
For a Microsoft Visual Basic .NET version of this article, see 310108.
For a Microsoft Visual C# .NET version of this article, see 310107.

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

IN THIS TASK

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

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

Retrieving Data Using the DataReader
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

IDataReader.GetSchemaTable Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataIDataReaderClassGetSchemaTableTopic.asp

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

OleDbDataReader.GetSchemaTable Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbDataReaderClassGetSchemaTableTopic.asp

SqlDataReader.GetSchemaTable Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlDataReaderClassGetSchemaTableTopic.asp

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:

309488 How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual Basic .NET

back to the top
Modification Type:MinorLast Reviewed:6/30/2004
Keywords:kbDatabase kbHOWTOmaster kbSqlClient kbSystemData KB309683 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.