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

With dotConnect for MySQL 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 System.Data.Common.DbConnection.

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

This article consists of the following sections:

• How To Use
• GetSchema Method Reference
• Samples

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 MySQL. 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 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. Their number can also be obtained from the return of the GetSchema() method. 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.MySql.MySqlMetadataCollectionNames 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. If the restriction name is not specified, a default value is used. For example, the default values for the database, table, and collection element name restrictions are "mysql", "user", and "%" accordingly. Note that the Database property of the connection string does not affect the GetSchema method, and the database restriction should be specified any time you are interested in some database other than "mysql". 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".

Samples

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

string tableName = "dept";
if (myDbConnection.GetSchema("Tables", new string[] { "Test", tableName }).Rows.Count > 0)
{
Console.WriteLine("Table " + tableName + " exists in the database.");
}

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.

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();
}

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.

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["Name"] + " " + myRow["DataType"];
   //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();
}

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("Name") & " " & myRow("DataType")
   '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

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

See Also

MySqlConnection Class | DbConnectionBase.GetSchema Method