4.5.1 Using GetSchema on a Connection

The GetSchema() method of the connection object can be used to retrieve schema information about the database currently connected to. The schema information is returned in the form of a DataTable. The schema information is organized into a number of collections. Different forms of the GetSchema() method can be used depending on the information required. There are three forms of the GetSchema() method:

• GetSchema() - This call will return a list of available collections.

• GetSchema(String) - This call returns information about the collection named in the string parameter. If the string “MetaDataCollections” is used then a list of all available collections is returned. This is the same as calling GetSchema() without any parameters.

• GetSchema(String, String[]) - In this call the first string parameter represents the collection name, and the second parameter represents a string array of restriction values. Restriction values limit the amount of data that will be returned. Restriction values are explained in more detail in the Microsoft .NET documentation.

Collections

The collections can be broadly grouped into two types: collections that are common to all data providers, and collections specific to a particular provider.

Common Collections.  The following collections are common to all data providers:

• MetaDataCollections

• DataSourceInformation

• DataTypes

• Restrictions

• ReservedWords

Provider-Specific Collections.  The following are the collections currently provided by Connector/NET, in addition to the common collections shown previously:

• Databases

• Tables

• Columns

• Users

• Foreign Keys

• IndexColumns

• Indexes

• Foreign Key Columns

• UDF

• Views

• ViewColumns

• Procedure Parameters

• Procedures

• Triggers

C# Code Example.  A list of available collections can be obtained using the following code:

using System;
using System.Data;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace ConsoleApplication2
{
    class Program
    {
        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("============================");
            }
        }
        static void Main(string[] args)
        {
            string connStr = "server=localhost;user=root;database=world;port=3306;password=******";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                DataTable table = conn.GetSchema("MetaDataCollections");
                //DataTable table = conn.GetSchema("UDF");
                DisplayData(table);
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("Done.");
        }
    }
}

Further information on the GetSchema() method and schema collections can be found in the Microsoft .NET documentation.