Working with the GetSchema Methods

Article
11/03/2006

The Connection classes in each of the .NET Framework managed providers implement a GetSchema method which is used to retrieve schema information about the database that is currently connected, and the schema information returned from the GetSchema method comes in the form of a DataTable. The GetSchema method is an overloaded method that provides optional parameters for specifying the schema collection to return, and restricting the amount of information returned.

Specifying the Schema Collections

The first optional parameter of the GetSchema method is the collection name which is specified as a string.. There are two types of schema collections: common schema collections that are common to all providers, and specific schema collections which are specific to each provider.

You can query a .NET Framework managed provider to determine the list of supported schema collections by calling the GetSchema method with no arguments, or with the schema collection name "MetaDataCollections". This will return a DataTable with a list of the supported schema collections, the number of restrictions that they each support, and the number of identifier parts that they use.

For more information about the common schema collections, see Understanding the Common Schema Collections; for the provider-specific collections, see Understanding the Provider-Specific Schema Collections.

Retrieving Schema Collections Example

The following examples demonstrate how to use the GetSchema method of the .NET Framework Data Provider for the SQL Server SqlConnection class to retrieve schema information about all of the tables contained in the AdventureWorks sample database:

[Visual Basic]

Imports System.Data.SqlClient

Module Module1
   Sub Main()
      Dim connectionString As String = GetConnectionString()
      Using connection As New SqlConnection(connectionString)
         'Connect to the database then retrieve the schema information.
         connection.Open()
         Dim table As DataTable = connection.GetSchema("Restrictions")

         ' Display the contents of the table.
         DisplayData(table)
         Console.WriteLine("Press any key to continue.")
         Console.ReadKey()
      End Using
   End Sub

   Private Function GetConnectionString() As String
      ' To avoid storing the connection string in your code,  
      ' you can retrieve it from a configuration file.
      Return "Data Source=(local);Database=AdventureWorks;" _
         & "Integrated Security=SSPI;"
   End Function

   Private Sub DisplayData(ByVal table As DataTable)
      For Each row As DataRow In table.Rows
         For Each col As DataColumn In table.Columns
            Console.WriteLine("{0} = {1}", col.ColumnName, row(col))
         Next
         Console.WriteLine("============================")
      Next
   End Sub
End Module

[C#]

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
  static void Main(string[] args)
  {
  string connectionString = GetConnectionString();
  sing (SqlConnection connection = new SqlConnection(connectionString))
  {
   // Connect to the database then retrieve the schema information.
   connection.Open();
   DataTable table = connection.GetSchema("Tables");

   // Display the contents of the table.
   DisplayData(table);
   Console.WriteLine("Press any key to continue.");
   Console.ReadKey();
   }
 }

  private static string GetConnectionString()
  {
   // To avoid storing the connection string in your code,
   // you can retrieve it from a configuration file.
   return "Data Source=(local);Database=AdventureWorks;" +
      "Integrated Security=SSPI;";
  }

  private static void DisplayData(System.Data.DataTable table)
  {
     foreach (System.Data.DataRow row in table.Rows)
     {
        foreach (System.Data.DataColumn col in table.Columns)
        {
           Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
        }
     Console.WriteLine("============================");
     }
  }
}

Specifying the Restriction Values

The second optional parameter of the GetSchema method is the restrictions that are used to limit the amount of schema information returned, and it is passed to the GetSchema method as an array of strings. The position in the array determines the values that you can pass, and this is equivalent to the restriction number.

For example, the following table describes the restrictions supported by the "Tables" schema collection using the .NET Framework Data Provider for SQL Server:

Restriction Name	Restriction Default	Restriction Number
Catalog	TABLE_CATALOG	1
Owner	TABLE_SCHEMA	2
Table	TABLE_NAME	3
TableType	TABLE_TYPE	4

To use one of the restrictions of the "Tables" schema collection, simply create an array of strings with four elements, then place a value in the element that matches the restriction number. For example, to restrict the tables returned by the GetSchema method to only those tables owned by the "dbo" role, set the second element of the array to "dbo" before passing it to the GetSchema method.

Note

The restrictions collections for SqlClient and OracleClient have an additional ParameterName column. The restriction default column is still there for backwards compatibility, but is currently ignored. Parameterized queries rather than string replacement should be used to minimize the risk of an SQL injection attack when specifying restriction values.

Note

The number of elements in the array must be less than or equal to the number of restrictions supported for the specified schema collection else an ArgumentException will be thrown. There can be fewer than the maximum number of restrictions. The missing restrictions are assumed to be null (unrestricted).

You can query a .NET Framework managed provider to determine the list of supported restrictions by calling the GetSchema method with the name of the restrictions schema collection, which is "Restrictions". This will return a DataTable with a list of the collection names, the restriction names, the default restriction values, and the restriction numbers.

Restricting Schema Collections Example

The following examples demonstrate how to use the GetSchema method of the .NET Framework Data Provider for the SQL Server SqlConnection class to retrieve schema information about all of the tables contained in the AdventureWorks sample database, and to restrict the information returned to only those tables that are owned by the "dbo" role: