Limitations

Each database platform has different capabilities (stored procedures, packages, different data types) and different metadata (some have standard INFORMATION_SCHEMA views, most have database-specific schema tables and commands).

.Net's ADO DbConnection.GetSchema method is a useful way of standardizing that information, although the collection names and column names vary. The DatabaseSchemaReader projects this into a single model that is common to many major database platforms. Where the ADO provider collections are insufficient, it adds extra calls for specific databases (ProviderSchemaReaders).

However, there are significant limitations to this approach. A database specific IDE like SqlServer Management Studio, Toad for Oracle or MySql Workbench will always be able to provide full, rich information. DatabaseSchemaReader is intended to be cross-platform and focus only on the standard common SQL database features.

Reading any database

Most ADO providers implement the DbConnection.GetSchema methods, but some don't. One that doesn't is Microsoft's SqlServer Ce 3.5, which throws a NotImplementedException (SqlServer Ce 4.0 implements GetSchema, and works well). Even when GetSchema is implemented, each provider returns different data - which is the reason that the DatabaseSchemaReader exists. Most of them return inadequate information on constraints- for instance, which columns are the primary key. DatabaseSchemaReader includes providerSchemaReaders which directly query the database system tables for this information.

DatabaseSchemaReader is tested with many common (and some uncommon) databases -

• SqlServer,
• Oracle (with Microsoft, ODP, Devart and DataDirect drivers) ,
• MySql,
• PostgreSql (with Npgsql and Devart drivers),
• SQLite,
• SqlServerCe 4,
• DB2 ,
• Firebird,
• Intersystems Cache
• Ingres
• Sybase AnyWhere (ASA)
• Sybase UltraLite
• Sybase ASE
• Access 97 and Access 2007
• VistaDB

Other databases will likely require changes to read the data correctly.

Database-specific datatypes

DbConnection.GetSchema includes a standard DataTypes collection which contains useful information for mapping database types to .Net types. In addition, DatabaseSchemaReader recognizes the common synonyms for standard SQL types (VARCHAR, VARCHAR2, VARYING CHAR and so on).

Most of the additional tools within the DatabaseSchemaReader have limitations with database specific types, especially things like UDTs, sparse columns, column groups etc. Use your database DBA tools for these tasks.

Database security

DatabaseSchemaReader will attempt to read the SQL of SqlServer and Oracle stored procedures. But these databases, or others, may be secured with permissions or encryption which prevents reading parts of their metadata. DatabaseSchemaReader cannot bypass these rules, and may fail to read your schema.

Inadequate metadata

Different ADO providers expose different metadata. The ODBC and OLE DB providers will always provide less information than the SqlServer and Oracle providers. The PostgreSql Npgsql provider has very little information, but the Devart.Data.PostgreSql provider has much better schema metadata.

Sometimes it is not the ADO provider that is giving less information - the database metadata is inadequate. MySql provides very poor metadata for stored procedure parameters (until MySql 6). The MySql provider uses SHOW CREATE and parses the text of the stored procedure trying to work out the parameters (if it wrongly parses the information, parameters will be missing). The Devart.Data.MySql provider doesn't return any information about parameters.