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

Article translations Article translations
Article ID: 309488 - View products that this article applies to.
This article was previously published under Q309488
Expand all | Collapse all

On This Page

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.

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

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

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.

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/en-us/library/system.data.oledb.oledbschemaguid_members.aspx
When you pass values in the restriction array, include the Visual Basic .NET Nothing 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 Nothing for TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME because you are not filtering on these objects:
 MySchemaTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                   New Object() {Nothing, Nothing, Nothing, "TABLE"})
				

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:
MySchemaTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                   New Object() {Nothing, Nothing, Nothing, "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 i = 0 To schemaTable.Columns.Count - 1
   Console.WriteLine(schemaTable.Columns(i).ToString)
Next i
				
To obtain the values in each column (that is, the actual table names, such as Categories, Customers, and Employees), you can use the item name in the DataRow. Note that the Rows are zero-based:
For i = 0 To schemaTable.Rows.Count - 1
   Console.WriteLine(schemaTable.Rows(i)!TABLE_NAME.ToString)
Next i
				
You can also use the ordinal position of the item in the row to obtain the values. Note that the Item array is also zero-based:
For i = 0 To schemaTable.Rows.Count - 1
   Console.WriteLine(schemaTable.Rows(i).Item(2).ToString)
Next i
				

Create Sample That Lists 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 {Nothing, Nothing, Nothing, "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 Basic Console Application project. Module1.vb is added to the project by default.
  3. Open the Code window for Module1. Paste the following code into the top of the Code window, above the Module declaration:
       Imports System.Data
       Imports System.Data.OleDb
    					
  4. In the Code window, paste the following code into the Sub Main procedure.

    Note You must change User ID <username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
            Dim cn As New OleDbConnection()
            Dim schemaTable As DataTable
            Dim i As Integer
    
            '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() {Nothing, Nothing, Nothing, "TABLE"})
    
            'List the table name from each row in the schema table.
            For i = 0 To schemaTable.Rows.Count - 1
                Console.WriteLine(schemaTable.Rows(i)!TABLE_NAME.ToString)
            Next i
    
            '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).

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 {Nothing, Nothing, "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 Basic Console Application project. Module1.vb is added to the project by default.
  2. Open the Code window for Module1. Paste the following code into 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 into the Sub Main procedure.

    Note You must change User ID <username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
            Dim cn As New OleDbConnection()
            Dim schemaTable As DataTable
            Dim i As Integer
    
            '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 the Employees table.
            schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                          New Object() {Nothing, Nothing, "Employees", "TABLE"})
    
            'List the schema info for the Employees table
            'in the format Field Descriptor : Field Value.
            For i = 0 To schemaTable.Columns.Count - 1
                Console.WriteLine(schemaTable.Columns(i).ToString & " : " & _
                   schemaTable.Rows(0).Item(i).ToString)
            Next i
    
            '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 schema information is listed in the Console window.
  6. Press ENTER to end the Console application and return to the IDE.

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 {Nothing, Nothing, "Employees", Nothing}, you filter to include only the columns for the Employees table.
  1. Create a new Visual Basic Console Application project. Module1.vb is added to the project by default.
  2. Open the Code window for Module1. Paste the following code into 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 into the Sub Main procedure.

    Note You must change User ID <username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
            Dim cn As New OleDbConnection()
            Dim schemaTable As DataTable
            Dim i As Integer
    
            '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() {Nothing, Nothing, "Employees", Nothing})
    
            'List the column name from each row in the schema table.
            For i = 0 To schemaTable.Rows.Count - 1
                Console.WriteLine(schemaTable.Rows(i)!COLUMN_NAME.ToString)
            Next i
    
            '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.

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 Basic Console Application project. Module1.vb is added to the project by default.
  2. Open the Code window for Module1. Paste the following code into 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 into the Sub Main procedure.

    Note You must change User ID <username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
            Dim cn As New OleDbConnection()
            Dim schemaTable As DataTable
    
            '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 (zero-based) in the row are catalog, schema, and table.
            'The fourth item is the primary key.
            Console.WriteLine(schemaTable.Rows(0).Item(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).Item(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.

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/en-us/library/system.data.oledb.oledbschemaguid.columns.aspx
For more information, refer to the following topics in the .NET Framework SDK documentation:
Obtaining Schema Information from a Database
http://msdn.microsoft.com/en-us/library/kcax58fh.aspx

OleDbConnection.GetOleDbSchemaTable Method
http://msdn.microsoft.com/en-us/library/system.data.idatareader.getschematable.aspx

OleDbSchemaGuid.Tables Field
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid.tables.aspx

OleDbSchemaGuid.Columns Field
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid.columns.aspx

OleDbSchemaGuid.Primary_Keys Field
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid.primary_keys.aspx

Properties

Article ID: 309488 - Last Review: July 1, 2004 - Revision: 3.3
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
Keywords: 
kbhowtomaster kbsystemdata KB309488

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com