Retrieving Schema Metadata with the GetSchema Method

Retrieving Schema Metadata with the GetSchema Method

Applications use the GetSchema method of the Connection object to retrieve Schema Metadata about a data provider and/or data source. Each provider implements a number of Schema collections, including the five standard metadata collections.

•

MetaDataCollections Schema Collections

•

DataSourceInformation Schema Collection

•

DataTypes Collection

•

ReservedWords Collection

•

Restrictions Collection

Additional collections are specified and must be supported to return Schema information from the data provider.

See Additional Schema Collections for details about the other collections supported by the data providers.

NOTE: Refer to the .NET Framework documentation for additional background functional requirements, including the required data type for each ColumnName.

MetaDataCollections Schema Collections

The MetaDataCollections schema collection is a list of the schema collections available to the logged in user. The MetaDataCollection can return the supported columns described in Table 50 in any order.

Table 50

Columns Returned by the MetaDataCollections Schema Collection

The name of the collection to pass to the GetSchema method to return the collection.

NumberOfRestrictions

The number of restrictions that may be specified for the collection.

NumberOfIdentifierParts

The number of parts in the composite identifier/data base object name.

DataSourceInformation Schema Collection

The DataSourceInformation schema collection can return the supported columns, described in Table 51, in any order. Note that only one row is returned.

Table 51

ColumnNames Returned by the DataSourceInformation Collection

CompositeIdentifierSeparatorPattern

The regular expression to match the composite separators in a composite identifier.

DataSourceProductName

The name of the product accessed by the data provider.

DataSourceProductVersion

Indicates the version of the product accessed by the data provider, in the data source’s native format.

DataSourceProductVersionNormalized

A normalized version for the data source. This allows the version to be compared with String.Compare().

GroupByBehavior

Specifies the relationship between the columns in a GROUP BY clause and the non-aggregated columns in the select list.

The host to which the data provider is connected.

Indicates whether non-quoted identifiers are treated as case sensitive.

IdentifierPattern

A regular expression that matches an identifier and has a match value of the identifier.

OrderByColumnsInSelect

Specifies whether columns in an ORDER BY clause must be in the select list. A value of true indicates that they are required to be in the Select list, a value of false indicates that they are not required to be in the Select list.

ParameterMarkerFormat

A format string that represents how to format a parameter.

ParameterMarkerPattern

A regular expression that matches a parameter marker. It will have a match value of the parameter name, if any.

ParameterNameMaxLength

The maximum length of a parameter name in characters.

ParameterNamePattern

A regular expression that matches the valid parameter names.

QuotedIdentifierCase

Indicates whether quoted identifiers are treated as case sensitive.

QuotedIdentifierPattern

A regular expression that matches a quoted identifier and has a match value of the identifier itself without the quotation marks.

StatementSeparatorPattern

A regular expression that matches the statement separator.

StringLiteralPattern

A regular expression that matches a string literal and has a match value of the literal itself.

SupportedJoinOperators

Specifies the types of SQL join statements that are supported by the data source.

DataTypes Collection

Table 52 describes the supported columns of the DataTypes schema collection. The columns can be returned in any order.

Table 52

ColumnNames Returned by the DataTypes Collection

The length of a non-numeric column or parameter refers to either the maximum or the length defined for this type by the data provider.

Format string that represents how to add this column to a data definition statement, such as CREATE TABLE.

CreateParameters

The creation parameters that must be specified when creating a column of this data type. Each creation parameter is listed in the string, separated by a comma in the order they are to be supplied.

For example, the SQL data type DECIMAL needs a precision and a scale. In this case, the creation parameters should contain the string "precision, scale".

In a text command to create a DECIMAL column with a precision of 10 and a scale of 2, the value of the CreateFormat column might be DECIMAL({0},{1})" and the complete type specification would be DECIMAL(10,2).

The name of the .NET Framework type of the data type.

IsAutoIncrementable

Specifies whether values of a data type are auto-incremented.

true: Values of this data type may be auto-incremented.

false: Values of this data type may not be auto-incremented.

Specifies whether the data type is the best match between all data types in the data store and the .NET Framework data type indicated by the value in the DataType column.

true: The data type is the best match.

false: The data type is not the best match.

IsCaseSensitive

Specifies whether the data type is both a character type and case-sensitive.

true: The data type is a character type and is case-sensitive.

false: The data type is not a character type or is not case-sensitive.

IsConcurrencyType

true: The data type is updated by the database every time the row is changed and the value of the column is different from all previous values.

false: The data type is not updated by the database every time the row is changed.

true: Columns of this data type created by the data definition language (DDL) will be of fixed length.

false: Columns of this data type created by the DDL will be of variable length.

IsFixedPrecisionScale

true: The data type has a fixed precision and scale.

false: The data type does not have a fixed precision and scale.

IsLiteralsSupported

true: The data type can be expressed as a literal.

false: The data type cannot be expressed as a literal.

true: The data type contains very long data. The definition of very long data is provider-specific.

false: The data type does not contain very long data.

true: The data type is nullable.

false: The data type is not nullable.

true: The data type contains very long data. The definition of very long data is provider-specific.

false: The data type does not contain very long data.

IsSearchableWithLike

true: The data type can be used with the LIKE predicate.

false: The data type cannot be used with the LIKE predicate.

true: The data type is unsigned.

false: The data type is signed.

The prefix applied to a given literal.

The suffix applied to a given literal.

If the type indicator is a numeric type, this is the maximum number of digits allowed to the right of the decimal point.

Otherwise, this is DBNull.Value.

If the type indicator is a numeric type, this is the minimum number of digits allowed to the right of the decimal point.

Otherwise, this is DBNull.Value.

The provider-specific type value that should be used when specifying a parameter's type.

The provider-specific data type name.

ReservedWords Collection

This schema collection exposes information about the words that are reserved by the database to which the data provider is connected. Table 53 describes the columns that the data provider supports.

Table 53

ReservedWords Schema Collection

Provider-specific reserved words.

Restrictions Collection

The Restrictions schema collection exposes information about the restrictions supported by the data provider that is currently connected to the database. Table 54 describes the columns returned by the data providers. The columns can be returned in any order.

The PSQL ADO.NET Data Provider uses standardized names for restrictions. If the data provider supports a restriction for a Schema method, it always uses the same name for the restriction.

The case sensitivity of any restriction value is determined by the underlying database, and can be determined by the IdentifierCase and QuotedIdentifierCase values in the DataSourceInformation collection (see DataSourceInformation Schema Collection).

Table 54

ColumnNames Returned by the Restrictions Collection

The name of the collection to which the specified restrictions apply.

RestrictionName

The name of the restriction in the collection.

RestrictionDefault

RestrictionNumber

The actual location in the collection restrictions for this restriction.

Specifies whether the restriction is required.

See Additional Schema Collections for the restrictions that apply to the each of the additional supported schema collections.