Retrieving Metadata

Usually you have to dig through SQL references to find out how to get metadata information for specific DBMS. There are few servers that support same SQL commands. Sometimes the syntax differs slightly, sometimes a server does not support certain statement. Now you can forget about those problems because dotConnect for Oracle retrieves the metadata for you.

You can take advantage of the new useful feature - GetSchema method. It allows you to read server schema information without writing queries and parsing the output. All information you may want to obtain is brought to you by single function in easy-to-process format. You can get information on schemas, views, packages, tables, columns, indexes, users, procedures and functions, their arguments, and reserved words. The method is introduced in System.Data.Common.DbConnection.

This article consists of the following sections:

How To Use

GetSchema method is available in three overloads, each of them serves its own purpose. All overloads return System.Data.DataTable object that contains information about server elements.

[C#]

public virtual abstract DataTable GetSchema();

[Visual Basic]

Overloads Public Overridable MustOverride Function GetSchema() As DataTable

If you call the GetSchema method without parameters, or with single parameter "MetaDataCollections" (which is actually the same), the table object returned by the method will contain three columns. The first field of every row is a keyword allowed to be passed to the method (as collectionName argument). The second field is the number of restriction values for this keywords (passed through restrictionValues argument).

[C#]

public virtual abstract DataTable GetSchema(
   string collectionName
);

[Visual Basic]

Overloads Public Overridable MustOverride Function GetSchema( _
   ByVal collectionName As String _
) As DataTable

GetSchema with 1 argument returns general information about the collection queried. For example, GetSchema("Users") returns list of users on the server.

[C#]

public virtual abstract DataTable GetSchema(
   string collectionName,
   string[] restrictionValues
);

[Visual Basic]

Overloads Public Overridable MustOverride Function GetSchema( _
   ByVal collectionName As String, _
   ByVal restrictionValues() As String _
) As DataTable

In this overload first parameter is name of a collection, and second parameter is the array of restrictions to be applied when querying information. Quantity of elements in the array must be less or equal to the value that is returned by GetSchema() method in the second cell of the row that corresponds to the collection name. (Or from the table below, which is much more handy.) If the second argument is null (Nothing), the function behaves like the previous overload (that takes a single parameter).

GetSchema Method Reference

Collection Name

 Number of restrictions

Remarks
MetaDataCollections 0

  • Returns this list. Same as using GetSchema() method without parameters.
ReservedWords 0

  • Lists all reserved words used in the server.
Users 1

  • Lists all users on the server.
  • When restricted by username, returns information about specific user.
Tables 2

  • GetSchema("Tables") returns list of all tables on the server that you have access to.
  • The first restriction for this collection is name of a schema. If specified, the method returns all tables within the schema.
  • The second restriction is table name. Note that masks are not allowed in dotConnect for Oracle.
Views 2

  • GetSchema("Views") returns list of all views on the server that you have access to.
  • The first restriction for this collection is name of a schema. If specified, the method returns all views within the schema.
  • The second restriction is the name of the view.
Columns 3

  • Returns list of columns, their type and some extra information.
    GetSchema("Columns") returns list of all columns in all schemas of the table.
  • Restricted by schema name, the method returns all columns in the specified schema.
  • The second restriction is name of a table that GetSchema method should search in.
  • At last, you can specify column name.
Indexes 4

  • Returns list of indexes and their details.
  • The first restriction is name of a schema the indexes belongs to.
  • The second restriction is name of the index.
  • The third restriction is name of a table that uses the index.
  • The last restriction is name of schema the table belongs to.
IndexColumns 5

  • Returns information about columns included in indexes. The following restrictions may be specified:
  • Name of the schema for indexes;
  • Index name;
  • Name of the schema for tables;
  • Table name;
  • Column name.
Functions 2

  • Returns list of functions on the server. The following restrictions may be specified:
  • Schema name;
  • Function name.
Procedures 3

  • Returns list of procedures on the server. The following restrictions may be specified:
  • Schema name;
  • Package name;
  • Procedure name.
Arguments 4

  • Returns list of procedure and function arguments. The following restrictions may be specified:
  • Schema name;
  • Package name;
  • Procedure name;
  • Argument name. 
Synonyms 2

  • Returns list of synonyms on the server. The following restrictions may be specified:
  • Schema name;
  • Synonym name.
Sequences 2

  • Returns list of sequences on the server. The following restrictions may be specified:
  • Schema name;
  • Sequence name.
Packages 2

  • Returns list of packages on the server. The following restrictions may be specified:
  • Schema name;
  • Package name.
PackageBodies 2

  • Returns list of package bodies on the server that you have access to. The following restrictions may be specified:
  • Schema name;
  • Package name.
ForeignKeys 3

  • Returns list of foreign keys on the server. The following restrictions may be specified:
  • Schema name;
  • Key name;
  • Table name.
ForeignKeyColumns 3

  • Returns list of columns of foreign keys on the server. The following restrictions may be specified:
  • Schema name;
  • Key name;
  • Table name.
Triggers 2

  • Returns list of triggers on the server that you have access to. The following restrictions may be specified:
  • Schema name;
  • Trigger name.
Clusters 2

  • Returns list of clusters on the server that you have access to. The following restrictions may be specified:
  • Schema name;
  • Cluster name.

Samples

The following code fragment is an elegant way to detect existence of a table.

[C#]

string tableName = "DEPT";
if (myDbConnection.GetSchema("Tables", new string[] { "SCOTT", tableName }).Rows.Count > 0)
{
Console.WriteLine("Table " + tableName + " found.");
}

[Visual Basic]

Dim tableName As String = "DEPT"
Dim restrictions() As String = {"SCOTT", tableName}
If (myDbConnection.GetSchema("Tables", restrictions).Rows.Count > 0) Then
  Console.WriteLine("Table " + tableName + " found.")
End If

The next sample shows how to retrieve columns information from a table and render it to console.

[C#]

static void GetTableInfo(OracleConnection oraConnection, string tableName)
{
  oraConnection.Open();
  DataTable myDataTable = oraConnection.GetSchema("Columns", new string[] { 
            "SCOTT", tableName });
  for (int i = 0; i < myDataTable.Columns.Count; i++)
  {
    Console.Write(myDataTable.Columns[i].Caption + "\t");
  }
  Console.WriteLine();
  foreach (DataRow myRow in myDataTable.Rows)
  {
    foreach (DataColumn myCol in myDataTable.Columns)
    {
      Console.Write(myRow[myCol] + "\t");
    }
    Console.WriteLine();
  }
  oraConnection.Close();
}

[Visual Basic]

Public Sub GetTableInfo(ByVal oraConnection As OracleConnection, ByVal tableName As String)
  oraConnection.Open()
  Dim restrictions() As String = {"SCOTT", tableName}
  Dim myDataTable As DataTable = oraConnection.GetSchema("Columns", restrictions)
  Dim i As Int32
  For i = 0 To myDataTable.Columns.Count - 1
    Console.Write(myDataTable.Columns(i).Caption & Chr(9))
  Next
  Console.WriteLine()
  Dim myRow As DataRow
  Dim myCol As DataColumn
  For Each myRow In myDataTable.Rows
    For Each myCol In myDataTable.Columns
      Console.Write(myRow(myCol) & Chr(9))
    Next
    Console.WriteLine()
  Next
  oraConnection.Close()
End Sub

The following sample demonstrates how to generate SQL CREATE TABLE statement basing on metadata retrieved with GetSchema method. Only column name and type are included in the script.

[C#]

static void GetCreateTable(OracleConnection oraConnection, string tableName)
{
  //Open the connection
  oraConnection.Open();
  //Fill DataTable with columns information
  DataTable myDataTable = oraConnection.GetSchema("Columns", new string[] { 
                                                   "SCOTT", tableName });
  string queryText = "CREATE TABLE " + tableName + " (\n";
  string fieldLine;
  DataRow myRow;
  //For every row in the table
  for (int i = 0; i < myDataTable.Rows.Count; i++)
  {
    //Get column name and type
    myRow = myDataTable.Rows[i];
    fieldLine = myRow["Name"] + " " + myRow["DataType"];
    //Add column size if required
    if (Array.IndexOf(new string[] { 
	   "NUMBER", "VARCHAR", "VARCHAR2", "RAW" 
	 }, myRow["DataType"]) != -1)
    {
      fieldLine = fieldLine + "(" + myRow["Length"] + ")";
    }
    //Add coma or closing bracket
    if (i < myDataTable.Rows.Count - 1)
    {
      fieldLine = fieldLine + ",\n";
    }
    else
    {
      fieldLine = fieldLine + ")";
    }
    //Add new column to script
    queryText = queryText + fieldLine;
  }
  Console.WriteLine(queryText);
  //Close the connection
  oraConnection.Close();
}

[Visual Basic]

Public Sub GetCreateTable(ByVal oraConnection As OracleConnection, _ 
  ByVal tableName As String)
  'Open the connection
  oraConnection.Open()
  Dim restrictions() As String = {"SCOTT", tableName}
  'Fill DataTable with columns information
  Dim myDataTable As DataTable = oraConnection.GetSchema("Columns", restrictions)
  Dim queryText As String = "CREATE TABLE " + tableName & _
                            + " (" + System.Environment.NewLine
  Dim fieldLine As String
  Dim myRow As DataRow
  Dim i As Int32
  'For every row in the table
  For i = 0 To myDataTable.Rows.Count - 1
    'Get column name and type
    myRow = myDataTable.Rows(i)
    fieldLine = myRow("Name") & " " & myRow("DataType")
    'Add column size if required
    If (Array.IndexOf(New String() { _
	  "VARCHAR", "VARCHAR2", "RAW", "NUMBER"}, myRow("DataType")) <> -1) Then
      fieldLine = fieldLine & "(" & myRow("Length") & ")"
    End If
    'Add coma or closing bracket
    If (i < myDataTable.Rows.Count - 1) Then
      fieldLine = fieldLine + "," + System.Environment.NewLine
    Else
      fieldLine = fieldLine + ")"
    End If
    'Add new column to script
    queryText = queryText + fieldLine
  Next
  Console.WriteLine(queryText)
  'Close the connection
  oraConnection.Close()
End Sub