dotConnect for DB2 Documentation
In This Topic
    Retrieving MetaData
    In This Topic
    This topic is applicable only for full .NET Framework.

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

    public virtual abstract DataTable GetSchema();

    
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). The third field is not used in dotConnect for DB2. It is always zero. 

    public virtual abstract DataTable GetSchema(
   string collectionName
);

    
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 the list of users on the server. 

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

    
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. These restrictions specify which subset of the collection will be returned. The restrictions can include, for example, the database name (in this case, only collection elements belonging to this database will be returned) or the mask for the name of collection elements (only the elements satisfying this mask will be returned). The quantity and description of restrictions allowed for each metadata collection are represented in the table here. You may also retrieve it by calling GetSchema("Restrictions"). If the second parameter is null/Nothing, it is ignored.

    Instead of specifying the metadata collection name as a string constant, you may use members of System.Data.DbMetaDataCollectionNames and Devart.Data.DB2.DB2MetadataCollectionNames as the first GetSchema argument values. The members of these classes are the string fields, each field stores the corresponding metadata collection name. It is recommended to use these fields rather than manually input the collection names manually as the string constants because in case of using these fields, you will find misspellings at compile-time, and intellisense will show you all the available metadata collection names.

    GetSchema Method Reference

    The following table provides detailed information on metadata collections that can be retrieved using the GetSchema method, and restrictions that can be applied for them. Some collections may be not supported in older server versions. If you try to get metadata for unsupported collection you will get exception with message "Collection not defined".

    Collection Name

    		 Number of restrictions

    Remarks
    MetaDataCollections 0

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

    Lists restrictions for each metadata collection.
    ReservedWords 0

    Lists all reserved words used in the server.
    DataSourceInformation 0

    Returns the information about the data source, associated with this DB2Connection instance.
    DataTypes 0

    Lists data types supported by DB2 server.
    Catalogs 0

    Lists databases on the DB2 server.
    Schemas 0

    Lists schemas on the DB2 server.
    Tables 4

    GetSchema("Tables") returns the list of all tables (and/or views) on the server that you have access to.

    • The first restriction for this collection is name of a database.
    • The second restriction for this collection is name of a schema. If specified, the method returns all tables within the schema.
    • The third restriction is table name.
    • The third restriction is table type. Can be 'TABLE' or 'VIEW' or 'ALIAS'.
    TablePrivileges 3

    GetSchema("Views") returns the list of table privileges on the server that you have access to.

    • The first restriction for this collection is name of a database.
    • The second restriction for this collection is name of a schema. If specified, the method returns the table privileges within the schema.
    • The third restriction is the name of the table.
    Columns 4

    Returns the list of columns, their type and some extra information.
    GetSchema("Columns") returns the list of all columns in all databases on the server you have access to.

    • Restricted by database name, the method returns all columns in the specified database.
    • Restricted by schema name, the method returns all columns in the specified schema.
    • The second restriction is a name of a table that GetSchema method should search in.
    • At last, you can specify column name.
    Column Privileges 4

    Returns the list of column privileges.
    GetSchema("Column Privileges") returns the list of column privileges in all databases on the server you have access to.

    • Restricted by database name, the method returns all column privileges for the specified database.
    • Restricted by schema name, the method returns all column privileges for the specified schema.
    • The third restriction is a name of a table that GetSchema method should search in.
    • At last, you can specify column name.
    Indexes 5

    Returns the list of indexes and their details.

    • The first restriction is name of a database.
    • The second restriction is name of a schema the indexes belongs to.
    • The third restriction is name of a table that uses the index.
    • The fourth restriction defines whether to return information for unique indexes only. Can be true or false.
    • The last restriction is name of schema the table belongs to.
    Functions 3

    Returns the list of functions on the server. The following restrictions may be specified:

    • Database name;
    • Schema name;
    • Function name.
    Procedures 4

    Returns the list of procedures on the server. The following restrictions may be specified:

    • Database name;
    • Schema name;
    • Module name;
    • Procedure name.
    Function Parameters 4

    Returns the list of function arguments. The following restrictions may be specified:

    • Database name;
    • Schema name;
    • Procedure name;
    • Argument name. 
    Procedure Parameters 5

    Returns the list of procedure arguments. The following restrictions may be specified:

    • Database name;
    • Schema name;
    • Module name;
    • Procedure name;
    • Argument name. 
    PrimaryKeys 4

    Returns the list of primary keys on the server. The following restrictions may be specified:

    • Database name;
    • Schema name;
    • Table name.
    ForeignKeys 6

    Returns the list of foreign keys on the server. The following restrictions may be specified:

    • Primary key database name;
    • Primary key schema name;
    • Primary key table name.
    • Foreign key database name;
    • Foreign key schema name;
    • Foreign key table name.

    Samples

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

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

    
Dim tableName As String = "DEPT"
Dim restrictions() As String = {"SAMPLE", "TEST", 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. 

    static void GetTableInfo(DB2Connection db2Connection, string tableName)
{
  db2Connection.Open();
  DataTable myDataTable = db2Connection.GetSchema("Columns", new string[] { 
            "SAMPLE", "TEST", 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();
  }
  db2Connection.Close();
}

    
Public Sub GetTableInfo(ByVal db2Connection As DB2Connection, ByVal tableName As String)
  db2Connection.Open()
  Dim restrictions() As String = {"SAMPLE", "TEST", tableName}
  Dim myDataTable As DataTable = db2Connection.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
  db2Connection.Close()
End Sub

    Also you can get a metadata of query result set using the GetSchemaTable method of DB2DataReader and the ShemaTable property of DB2DataTable classes.

    See Also

    DB2Connection Class | DbConnectionBase.GetSchema Method

     

     

    © 2013 - 2024 Devart. All Rights Reserved.

    [email protected]