Schema Discovery

The API Profile supports standard ADO.NET schemas to access profile metadata.

Retrieving the Table Listing

The Tables schema collection lists all tables in the database. To retrieve the Tables schema collection, call the GetSchema method of the APIConnection class.

C#

String connectionString = "Profile=<Path to Profile>;ProfileSettings=<Profile Configuration Settings>";

using (APIConnection conn = new APIConnection(connectionString)) {
  conn.Open();
  DataTable databaseSchema = conn.GetSchema("Tables");
  foreach (DataRow row in databaseSchema.Rows) {
    Console.WriteLine(row["TABLE_NAME"]);
  }
}

Columns Returned

The Tables schema collection returns the following columns.

Column Name	Data Type	Description
TABLE_CATALOG	System.String	The database that contains the table.
TABLE_SCHEMA	System.String	The schema that contains the table.
TABLE_NAME	System.String	The table name.
TABLE_TYPE	System.String	The table type.

Retrieving Column Metadata

Call the GetSchema method of the APIConnection class to retrieve the Columns or ViewColumns schema collections. You can restrict results by table name, as shown in the example below.

C#

string connectionString = "Profile=<Path to Profile>;ProfileSettings=<Profile Configuration Settings>";

using (APIConnection conn = new APIConnection(connectionString)) {
  conn.Open();
  DataTable databaseSchema = conn.GetSchema("Columns", new string[] {"NorthwindOData"});
  foreach (DataRow column in databaseSchema.Rows) {
    Console.WriteLine(column["COLUMN_NAME"]);
    Console.WriteLine(column["IS_KEY"]);
    Console.WriteLine(column["DATA_TYPE"]);
  }
}

Columns Returned

The Columns and ViewColumns schema collections and DataTables returned from the query contain the following columns.

Column Name	Data Type	Description
TABLE_CATALOG	System.String	The database containing the table.
TABLE_SCHEMA	System.String	The schema containing the table.
TABLE_NAME	System.String	The table containing the column.
COLUMN_NAME	System.String	The column name.
ORDINAL_POSITION	System.Int32	The sequence number of the column.
COLUMN_DEFAULT	System.String	The default value of the column.
IS_NULLABLE	System.String	Whether the column allows null values. This value is YES or NO.
DATA_TYPE	System.String	The column data type.
CHARACTER_MAXIMUM_LENGTH	System.Int32	The maximum length in characters of a column with character data.
NUMERIC_PRECISION	System.Int32	The maximum number of digits allowed for numeric data.
NUMERIC_SCALE	System.Int32	The maximum column scale or the number of digits to the right of the decimal point in numeric data.
DATETIME_PRECISION	System.Int32	Returns the precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.
CHARACTER_SET_NAME	System.String	The name of the character set for a column with character data.
COLUMN_COMMENT	System.String	A brief description of the column.
IS_KEY	System.Boolean	Whether the column is the primary key of the table referenced by TABLE_NAME.
IS_READONLY	System.Boolean	Whether the column is read-only.
PROVIDER_TYPE	System.Type	Indicates the appropriate data type dependent on the language you are executing in.