How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual C# .NET (309681)


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 Q309681
For a Microsoft Visual Basic .NET version of this article, see 309488.

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 GetOleDbSchemaTable method of the OleDbConnection object in ADO.NET to retrieve database schema information. Schema information in a data source includes databases or catalogs that are available from the data source, tables, and views in a database, as well as constraints that exist and so on. Schema information in a table includes primary keys, columns, and autonumber fields.

Note that no method is equivalent to GetOleDbSchemaTable when you use a SqlClient.SqlConnection object. The SQL Server .NET Data Provider exposes backend schema information through stored procedures and informational views. For more information about views and stored procedures that are available through Microsoft SQL Server, see the Transact-SQL reference in the MSDN Library.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
  • Visual Studio .NET
  • ADO.NET fundamentals and syntax
back to the top

GetOleDbSchemaTable Method of the OleDbConnection Object

The OLE DB .NET Data Provider uses the GetOleDbSchemaTable method of the OleDbConnection object to expose schema information. GetOleDbSchemaTable returns a DataTable that is populated with the schema information.

The first argument of GetOleDbSchemaTable is the schema parameter, an OleDbSchemaGuid argument that identifies which schema information to return (such as tables, columns, and primary keys). The second argument is an Object array of restrictions to filter the rows that are returned in the schema DataTable (for example, you may specify restrictions for table name, type, owner, and /or schema).

back to the top

OleDbSchemaGuid Members

The OleDbSchemaGuid argument specifies the type of schema table for the GetOleDbSchemaTable method. Some of the OleDbSchemaGuid members include the following:
  • Columns
  • Foreign keys
  • Indexes
  • Primary keys
  • Tables
  • Views
For a complete list of the OleDbSchemaGuid members, refer to the "OleDbSchemaGuid Members" Web site in the References section.

back to the top

Restrictions

Restrictions are an Object array of filter values, each of which corresponds to a DataColumn in the resulting DataTable. The OleDbSchemaGuid argument determines the relevant restrictions. For example, when you specify an OleDbSchemaGuid of tables, the array of restrictions is as follows:

{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}

To review the available restrictions, click any of the OleDbSchemaGuid members at the following Microsoft Web site:

OleDbSchemaGuid Members
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbschemaguidmemberstopic.asp

When you pass values in the restriction array, include the Visual C# .NET null keyword for array elements that do not contain values. For example, if you want to retrieve table schemas, use OleDbSchemaGuid.Tables. However, if you specify tables, this also returns aliases, synonyms, views, and other related objects. Thus, if you want to filter out all objects except tables, use a restriction of TABLE for TABLE_TYPE. You can use null for TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME because you are not filtering on these objects: 
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
              new Object[] {null, null, null, "TABLE"});
back to the top

Returned DataTable

The DataTable that the GetOleDbSchemaTable method returns has a row for each object that meets the OleDbSchemaGuid type and the restriction criteria. The DataTable has a column for each of the restriction columns, which is followed by additional schema information based on the OleDbSchemaGuid field.

For example, when you use the following code, each row in the returned DataTable is a database table: 
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
              new Object[] {null, null, null, "TABLE"});
The columns that are returned in the DataTable are the restriction columns (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE), which are followed by the additional schema columns of TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, and DATE_MODIFIED.

To obtain this list of column names (that is, the field descriptors, such as TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME), you can use the ordinal position of the columns. Note that the Columns array is zero-based: 
for (int i = 0; i < schemaTable.Columns.Count; i++) {
   Console.WriteLine(schemaTable.Columns[i].ToString());
}
To obtain the values in each column (that is, the actual table names, such as Categories, Customers, and Employees), you can use the ordinal position in the ItemArray of the row. Note that the ItemArray is zero-based: 
for (int i = 0; i < schemaTable.Rows.Count; i++) {
   Console.WriteLine(schemaTable.Rows[i].ItemArray[2].ToString());
}
back to the top

Create Sample That List Tables in a Database

The following sample lists tables in the SQL Server Northwind database.

OleDbSchemaGuid.Tables returns those tables (including views) that are accessible to a given log on. If you specify an Object array of {null, null, null, "TABLE"}, you filter to include only a TABLE_TYPE of TABLE. You then list the table name (TABLE_NAME) of each row in the returned schema table.
  1. Start Visual Studio .NET.
  2. Create a new Visual C# Console Application project. Class1.cs is added to the project by default.
  3. Open the Code window for Class1. Paste the following code into the top of the Code window, above the namespace declaration:
    using System.Data;
using System.Data.OleDb;
  4. In the Code window, paste the following code into the Main function:
    OleDbConnection cn = new OleDbConnection();
DataTable schemaTable;

//Connect to the Northwind database in SQL Server.
//Be sure to use an account that has permission to list tables.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;
                       Password=<strong password>;Initial Catalog=Northwind";
cn.Open();

//Retrieve schema information about tables.
//Because tables include tables, views, and other objects,
//restrict to just TABLE in the Object array of restrictions.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
              new Object[] {null, null, null, "TABLE"});

//List the table name from each row in the schema table.
for (int i = 0; i < schemaTable.Rows.Count; i++) {
   Console.WriteLine(schemaTable.Rows[i].ItemArray[2].ToString());
}

//Explicitly close - don't wait on garbage collection.
cn.Close();

//Pause
Console.ReadLine();
  5. Modify the ConnectionString to connect to your SQL Server computer with an account that has permission to list tables in the Northwind database.
  6. Press the F5 key to compile and run the project. Notice that the tables are listed in the Console window.
  7. Press ENTER to end the Console application and return to the Integrated Development Environment (IDE).
back to the top

Create Sample That Retrieves Schema for a Table

The following sample lists schema information for the Employees table in the SQL Server Northwind database.

OleDbSchemaGuid.Tables returns those tables (including views) that are accessible to a given log on. If you specify an Object array of {null, null, "Employees", "TABLE"}, you filter to include only a table named Employees. You then list the schema information for the returned schema table.
  1. Create a new Visual C# Console Application project. Class1.cs is added to the project by default.
  2. Open the Code window for Class1. Paste the following code into the top of the Code window, above the namespace declaration:
    using System.Data;
using System.Data.OleDb;
  3. In the Code window, paste the following code into the Main function:
    OleDbConnection cn = new OleDbConnection();
DataTable schemaTable;

//Connect to the Northwind database in SQL Server.
//Be sure to use an account that has permission to retrieve table schema.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;
                      Password=<strong password>;Initial Catalog=Northwind";
cn.Open();

//Retrieve schema information about the Employees table.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
              new Object[] {null, null, "Employees", "TABLE"});

//List the schema info for the Employees table
//in the format Field Descriptor : Field Value.
for(int i = 0; i < schemaTable.Columns.Count; i++) {
   Console.WriteLine(schemaTable.Columns[i].ToString() + " : " + 
                     schemaTable.Rows[0][i].ToString());
}

//Explicitly close - don't wait on garbage collection.
cn.Close();

//Pause
Console.ReadLine();
  4. Modify the ConnectionString to connect to your SQL Server computer with an account that has permission to retrieve the Employees table schema.
  5. Press F5 to compile and run the project. Notice that the tables are listed in the Console window.
  6. Press ENTER to end the Console application and return to the IDE.
back to the top

Create Sample That Lists Columns in a Table

The following sample lists the names of columns in the Employees table in the SQL Server Northwind database.

OleDbSchemaGuid.Columns returns those columns in tables and views that are accessible to a given log on. If you specify an Object array of {null, null, "Employees", null}, you filter to include only the columns for the Employees table.
  1. Create a new Visual C# Console Application project. Class1.cs is added to the project by default.
  2. Open the Code window for Class1. Paste the following code into the top of the Code window, above the namespace declaration:
    using System.Data;
using System.Data.OleDb;
  3. In the Code window, paste the following code into the Main function:
    OleDbConnection cn = new OleDbConnection();
DataTable schemaTable;

//Connect to the Northwind database in SQL Server.
//Be sure to use an account that has permission to list the columns in the Employees table.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;
                       Password=<strong password>;Initial Catalog=Northwind";
cn.Open();

//Retrieve schema information about columns.
//Restrict to just the Employees TABLE.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, 
              new Object[] {null, null, "Employees", null});

//List the column name from each row in the schema table.
for (int i = 0; i < schemaTable.Rows.Count; i++) {
   Console.WriteLine(schemaTable.Rows[i].ItemArray[3].ToString());
}

//Explicitly close - don't wait on garbage collection.
cn.Close();

//Pause
Console.ReadLine();
  4. Modify the ConnectionString to connect to your SQL Server computer with an account that has permission to list columns in the Employees table.
  5. Press F5 to compile and run the project. Notice that the columns from the Employees table are listed in the Console window.
  6. Press ENTER to end the Console application and return to the IDE.
back to the top

Create Sample That Lists Primary Keys in a Table

The following sample lists the primary keys of the Employees table in the SQL Server Northwind database and of the Employee table in the SQL Server Pubs database.

OleDbSchemaGuid.Primary_Keys returns those primary keys in a catalog that are accessible to a given log on. In this sample, the OleDbConnection is to SQL Server but not to a specific SQL Server database: 
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;
                       Password=<strong password>;"
Instead, this code specifies the Northwind or Pubs databases as the TABLE_CATALOG in the restriction array. This code specifies the table owner, "dbo", for the TABLE_SCHEMA restriction. In addition, this code specifies the table names for the TABLE_NAME restriction.

To obtain the primary key of the Employees table in the Northwind database, you use an Object array of {"Northwind", "dbo", "Employees"}: 
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, 
              new Object[] {"Northwind", "dbo", "Employees"});
To obtain the primary key of the Employee table in the Pubs database, you use an Object array of {"Pubs", "dbo", "Employee"}: 
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, 
              new Object[] {"Pubs", "dbo", "Employee"});
To create the sample, follow these steps:
  1. Create a new Visual C# Console Application project. Class1.cs is added to the project by default.
  2. Open the Code window for Class1. Paste the following code into the top of the Code window, above the namespace declaration:
    using System.Data;
using System.Data.OleDb;
  3. In the Code window, paste the following code into the Main function:
    OleDbConnection cn = new OleDbConnection();
DataTable schemaTable;

//Connect to SQL Server.
//Be sure to use an account that has permissions to list primary keys
//in both the Northwind and Pubs databases.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;
                       Password=<strong password>;";
cn.Open();

//Retrieve schema information about primary keys.
//Restrict to just the Employees TABLE in the Northwind CATALOG.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, 
              new Object[] {"Northwind", "dbo", "Employees"});

//List the primary key for the first row in the schema table.
//The first three items in the ItemArray in the row are catalog, schema, and table.
//The fourth item is the primary key.
Console.WriteLine(schemaTable.Rows[0].ItemArray[3].ToString());

//Retrieve primary key for the Employee TABLE in the Pubs CATALOG.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, 
              new Object[] {"Pubs", "dbo", "Employee"});

//List the primary key for the first row in the schema table.
Console.WriteLine(schemaTable.Rows[0].ItemArray[3].ToString());

//Explicitly close - don't wait on garbage collection.
cn.Close();

//Pause
Console.ReadLine();
  4. Modify the ConnectionString to connect to your SQL Server computer with an account that has sufficient permissions to list the primary keys.
  5. Press F5 to compile and run the project. Notice that the primary keys from the Employees table in the Northwind database and the Employee table in the Pubs database are listed in the Console window.
  6. Press ENTER to end the Console application and return to the IDE.
back to the top

REFERENCES

For a complete list of the OleDbSchemaGuid members, refer to the following topic in the Microsoft .NET Framework Software Development Kit (SDK) documentation. For a list of available restrictions, click any of the OleDbSchemaGuid members:

OleDbSchemaGuid Members
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbschemaguidmemberstopic.asp

For more information, refer to the following topics in the .NET Framework SDK documentation:

Obtaining Schema Information from a Database
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingschemainformationfromdatabase.asp

OleDbConnection.GetOleDbSchemaTable Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbconnectionclassgetoledbschematabletopic.asp

OleDbSchemaGuid.Tables Field
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbschemaguidclasstablestopic.asp

OleDbSchemaGuid.Columns Field
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbschemaguidclasscolumnstopic.asp

OleDbSchemaGuid.Primary_Keys Field
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbschemaguidclassprimary_keystopic.asp

back to the top
Modification Type:MinorLast Reviewed:7/1/2004
Keywords:kbHOWTOmaster kbSystemData KB309681 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.