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 MyDirect .NET retrieves the metadata for you.

With MyDirect .NET you can take advantage of a very 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 databases, tables, columns, indexes, users, stored procedures and functions, user-defined procedures, and reserved words. The method is introduced in CoreLab.Common.DbConnection type (for .NET Framework 1.x), or System.Data.Common.DbConnection (for .NET Framework 2).

To illustrate capabilities of the feature, we have prepared MetaData sample. Please refer to the sample to learn the functionality in a quick glance.

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

When calling the GetSchema method, you can pass all or few arguments. In the latter case, some default values are assumed, if they were not specified explicitly. The default value of database restriction is "mysql", while restriction table is "user". Note that even if "Database" key is included in connection string, it does not affect GetSchema method. Some collections are not supported in older server versions. If you try ro get metadata for unsupported collection you will get exception with message "Collection not defined".

Collection Name	Number of restrictions	Remarks
Arguments	2	Returns list of arguments for stored procedures and functions on the server. Restrict it by database name and procedure name.
Columns	3	Returns list of columns, their type and some extra information. GetSchema("Columns") returns list of columns in the table "user" in database "mysql". Restricted by database name, GetSchema returns columns of table "user" if it can be found in the database. The second restriction is name of a table that GetSchema method should search in. At last, you can set column name pattern as described in "Tables" collection.
Databases	1	Lists all databases on the server. When restricted by name mask, returns all databases that match the mask.
DatasourceInformation	0	Returns information about the data source.
DataTypes	0	Returns information about data types supported by the data source.
ForeignKeyColumns	2	Returns list of columns used by foreign keys in the database. Restrict it with database name and table name.
ForeignKeys	3	Returns list of columns that participate in foreign keys. The first restriction for this collection is name of a database. The second restriction is table name mask. The third restriction is the key pattern.
Functions	2	Returns list of stored functions on the server. Restrict it by database name and function name.
IndexColumns	4	Returns list of indexed columns in the database, their type and some extra information. Restrict it with database name, table name, index name pattern, and column name pattern.
Indexes	3	Returns list of indexes and their details. The first restriction is database name, the second is table name. The last restriction is the index name pattern.
MetaDataCollections	0	Returns this list. Same as using GetSchema() method without parameters.
PrimaryKeys	2	Returns list of columns that participate in primary keys. The first restriction for this collection is name of a database. The second restriction is table name.
Procedures	2	Returns list of stored procedures on the server. Restrict it by database name and procedure name.
ReservedWords	0	Lists all reserved words used in the server.
Restrictions	0	Returns list of possible restrictions and their default values for the metadata collections.
Tables	2	GetSchema("Tables") returns list of tables in "mysql" database. The first restriction for this collection is name of a database. If specified, the method returns all tables within the database. The second restriction is table name mask. You can use wildcards '%' (any number of characters) and '_' (one character) to retrieve names of tables that match the mask.
Triggers	2	Returns list of triggers in a database. The first restriction for this collection is database name pattern. The second restriction is trigger name pattern.
UDFs	1	Returns information about user-defined functions on the server. You can specify name of the function as restriction.
UniqueKeys	2	Returns list of columns that participate in unique keys. The first restriction for this collection is name of a database. The second restriction is table name.
UserPrivileges	1	Lists all users and their privileges on the server. When restricted by username, returns information about specific user.
Users	1	Lists all users on the server. When restricted by username, returns information about specific user.
ViewColumns	3	Returns list of columns used by views in the database. Restrict it with database name, table name and column name.
Views	2	GetSchema("Views") returns list of views in "mysql" database. The first restriction for this collection is name of a database. The second restriction is view name mask.

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[] { "Test", tableName }).Rows.Count > 0)
{
Console.WriteLine("Table " + tableName + " exists in the database.");
}

[Visual Basic]

Dim tableName As String = "dept"
Dim restrictions() As String = {"Test", tableName}
If (myDbConnection.GetSchema("Tables", restrictions).Rows.Count > 0) Then
  Console.WriteLine("Table " + tableName + " exists in the database.")
End If

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

[C#]

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

[Visual Basic]

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

The following sample demonstrates how to generate SQL CREATE TABLE statement basing on metadata retrieved with GetSchema method. The generated script will work with all database management systems that support ANSI standard. Only column name and type are included in the script.

[C#]

static void GetCreateTable(MySqlConnection myDbConnection, string tableName)
{
  //Open the connection
  myDbConnection.Open();
  //Fill DataTable with columns information
  DataTable myDataTable = myDbConnection.GetSchema("Columns", new string[] { "Test", 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[0] + " " + myRow[1];
    //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
  myDbConnection.Close();
}

[Visual Basic]

Public Sub GetCreateTable(ByVal myDbConnection As MySqlConnection, ByVal tableName As String)
  'Open the connection
  myDbConnection.Open()
  Dim restrictions() As String = {"Test", tableName}
  'Fill DataTable with columns information
  Dim myDataTable As DataTable = myDbConnection.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(0) & " " & myRow(1)
    '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
  myDbConnection.Close()
End Sub

Language

Retrieving Metadata

How To Use

GetSchema Method Reference

Samples

See Also