Retrieve schema from a database RRS feed

  • Question

  • I suppose for the majority of the developers comes the time, they have to code against the unknown schema of a database. Possible reasons they do is due the need to create their own frameworks or create code generation programs. I am in the process of evaluating T4 text transformation engine in order to create code against a database. I do not wish to re-invent an ORM, rather that jumpstart one; create POCOs from the database schema, create necessary .config files, etc.

    In the quest of doing it, I've trying to find a successful "generic way" to retrieve a database schema for over a week now; specifying "generic way", I mean, I do not wish to code custom SQL code against every particular database, but use "pure" ADO.NET functions in order to retrieve a database's schema. I am aware of DbConnection.GetSchema() and DbDataReader.GetTableSchema() functions.

    I must admit, I am fascinated by DbDataReader.GetTableSchema(), and what it can do with a single SELECT statement; you can get a table's columns, the CLR type that match the data type of each column, if they can accept null values, if they are unique, and the primary keys of the table. I suppose we can thank datasets for this.

    On the contrast, I am disappointed with DbConnection.GetSchema(); there are not even enough metadata to find the columns that match the foreign keys constrains of a database. Every data provider, has a different number of metadata collections, and the same collections between the different database vendors, carry different columns and data. Meshing around with OleDbConnection.OleDbSchemaTable(), I found out that the data I needed, actually exist in the native provider, and that made me feel really bad.

    So, I ask: Is there something I miss in the big picture here? Why so "rich" on the table data, and so "poor" on the database data (database constrains) on the managed data providers, while the same databases, with native providers offer the same metadata? Are there other ways to retrieve database metadata, I don't know? I emphasize, I am no talking about SQL code, specific on every database, in order to do my job here...

    George J.

    MVP VSTO Athens, Greece
    Tuesday, January 20, 2009 12:12 AM

All replies

  • How about using GetOleDbSchemaTable?

    Keep in mind that vendors will implement native implementations to their database products (such as autoincrement type columns) so a single solution for all features simply wouldn't be possible.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 21, 2009 2:31 PM
  • It is not always possible to use OleDbConnection.OleDbSchemaTable(), instead of the managed provider. I don't install MySQL's OleDb provider any more, as the database's own tools do not need it, and the managed provider is just enough to create programs with .NET Framework.

    My point here, is how is possible to be so easy to get data for a table's columns DbDataReader.GetTableSchema() -  was it implemented in order to support datasets? - and no way to get data for the tables relations - DbDataReader.GetTableSchema() metadata collections are almost useless.

    George J.

    MVP VSTO Athens, Greece
    Wednesday, January 21, 2009 3:39 PM
  • Unfortunately I'm not aware of a better method. I know that you can retrieve foreign key information using GetSchema but I don't believe enough information is provided to determine table relationships.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, January 26, 2009 2:26 PM