Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Cubrid Metadata Support


The CUBRID .NET 8.4 Driver adds support for databases metadata. Most of these methods are implemented in the CUBRIDSchemaProvider class:

public DataTable GetDatabases(string[] filters)
public DataTable GetTables(string[] filters)
public DataTable GetViews(string[] filters)
public DataTable GetColumns(string[] filters)
public DataTable GetIndexes(string[] filters)
public DataTable GetIndexColumns(string[] filters)
public DataTable GetExportedKeys(string[] filters)
public DataTable GetCrossReferenceKeys(string[] filters)
public DataTable GetForeignKeys(string[] filters)
public DataTable GetUsers(string[] filters)
public DataTable GetProcedures(string[] filters)
public static DataTable GetDataTypes()
public static DataTable GetReservedWords()
public static String[] GetNumericFunctions()
public static String[] GetStringFunctions()
public DataTable GetSchema(string collection, string[] filters)

Let’s see some examples of getting metadata information from a CUBRID connection.

Get the list of Tables in the current database:

CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetTables(new string[] { "%" });
Debug.Assert(dt.Columns.Count == 3);
Debug.Assert(dt.Rows.Count == 10);
Debug.Assert(dt.Rows[0][0].ToString() == "demodb");
Debug.Assert(dt.Rows[0][1].ToString() == "demodb");
Debug.Assert(dt.Rows[0][2].ToString() == "stadium");

Get the list of Foreign Keys in a table:

CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetForeignKeys(new string[] { "game" });
Debug.Assert(dt.Columns.Count == 9);
Debug.Assert(dt.Rows.Count == 2);
Debug.Assert(dt.Rows[0][0].ToString() == "athlete");
Debug.Assert(dt.Rows[0][1].ToString() == "code");
Debug.Assert(dt.Rows[0][2].ToString() == "game");
Debug.Assert(dt.Rows[0][3].ToString() == "athlete_code");
Debug.Assert(dt.Rows[0][4].ToString() == "1");
Debug.Assert(dt.Rows[0][5].ToString() == "1");
Debug.Assert(dt.Rows[0][6].ToString() == "1");
Debug.Assert(dt.Rows[0][7].ToString() == "fk_game_athlete_code");
Debug.Assert(dt.Rows[0][8].ToString() == "pk_athlete_code");

Get the list of Indexes in a table:

CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetIndexes(new string[] { "game" });

Debug.Assert(dt.Columns.Count == 9);
Debug.Assert(dt.Rows.Count == 5);

Debug.Assert(dt.Rows[3][2].ToString() == "pk_game_host_year_event_code_athlete_code"); //verify index name
Debug.Assert(dt.Rows[3][4].ToString() == "True"); //Is it a primary key?

Remember there is a lot more information in the data provider documentation in the ADO.NET API Manual.

Here is a complete example that shows how to get the users, views, columns, tables, foreign keys or indexes using the ADO.NET Driver:

using CUBRID.Data.CUBRIDClient;
using System.Diagnostics;
using System.Data;

namespace MetadataExample
{
    class Program
    {
        static void Main(string[] args)
        {
            CUBRIDConnectionStringBuilder sb = new CUBRIDConnectionStringBuilder("localhost", "demodb", "public", "", "33000");
            using (CUBRIDConnection conn = new CUBRIDConnection(sb.GetConnectionString()))
            {
                conn.Open();

                CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);

                //get tables example
                using (DataTable dt = schema.GetTables(new string[] { "%" }))
                {
                    Debug.Assert(dt.Columns.Count == 3);
                    Debug.Assert(dt.Rows.Count == 10);

                    Debug.Assert(dt.Rows[0][0].ToString() == "demodb");
                    Debug.Assert(dt.Rows[0][1].ToString() == "demodb");
                    Debug.Assert(dt.Rows[0][2].ToString() == "stadium");
                }

                //get columns example
                using (DataTable dt = schema.GetColumns(new string[] { "game" }))
                {
                    Debug.Assert(dt.Columns.Count == 11);
                    Debug.Assert(dt.Rows.Count == 7);

                    Debug.Assert(dt.Rows[0][3].ToString() == "host_year");
                    Debug.Assert(dt.Rows[1][3].ToString() == "event_code");
                }

                //get users example
                using (DataTable dt = schema.GetUsers(null))
                {
                    Debug.Assert(dt.Columns.Count == 1);
                    Debug.Assert(dt.Rows.Count >= 2);

                    Debug.Assert(dt.Rows[0][0].ToString().ToUpper() == "DBA");
                    Debug.Assert(dt.Rows[1][0].ToString().ToUpper() == "PUBLIC");
                }

                //get views example
                using (DataTable dt = schema.GetViews(null))
                {
                    Debug.Assert(dt.Columns.Count == 3);
                    Debug.Assert(dt.Rows.Count == 0);
                }

                //get foreign keys example
                using (DataTable dt = schema.GetForeignKeys(new string[] { "game" }))
                {
                    Debug.Assert(dt.Columns.Count == 9);
                    Debug.Assert(dt.Rows.Count == 2);

                    Debug.Assert(dt.Rows[0][0].ToString() == "athlete");
                    Debug.Assert(dt.Rows[0][1].ToString() == "code");
                    Debug.Assert(dt.Rows[0][2].ToString() == "game");
                    Debug.Assert(dt.Rows[0][3].ToString() == "athlete_code");
                    Debug.Assert(dt.Rows[0][4].ToString() == "1");
                    Debug.Assert(dt.Rows[0][5].ToString() == "1");
                    Debug.Assert(dt.Rows[0][6].ToString() == "1");
                    Debug.Assert(dt.Rows[0][7].ToString() == "fk_game_athlete_code");
                    Debug.Assert(dt.Rows[0][8].ToString() == "pk_athlete_code");

                    Debug.Assert(dt.Rows[1][0].ToString() == "event");
                    Debug.Assert(dt.Rows[1][1].ToString() == "code");
                    Debug.Assert(dt.Rows[1][2].ToString() == "game");
                    Debug.Assert(dt.Rows[1][3].ToString() == "event_code");
                    Debug.Assert(dt.Rows[1][4].ToString() == "1");
                    Debug.Assert(dt.Rows[1][5].ToString() == "1");
                    Debug.Assert(dt.Rows[1][6].ToString() == "1");
                    Debug.Assert(dt.Rows[1][7].ToString() == "fk_game_event_code");
                    Debug.Assert(dt.Rows[1][8].ToString() == "pk_event_code");
                }

                //get indexes example
                using (DataTable dt = schema.GetIndexes(new string[] { "game" }))
                {
                    Debug.Assert(dt.Columns.Count == 9);
                    Debug.Assert(dt.Rows.Count == 5);

                    Debug.Assert(dt.Rows[3][2].ToString() == "pk_game_host_year_event_code_athlete_code"); //Index name
                    Debug.Assert(dt.Rows[3][4].ToString() == "True"); //Is PK?
                }

                conn.Close();
            }
        }
    }
}

comments powered by Disqus
Page info
viewed 965 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 2 years ago by
View revisions
Share this article