OT: automatically discovering data source structure

Brian Criswell posted on Friday, January 19, 2007

I am looking at building a sort of ORM into my metadata creator.  To this end, I would like to find something that would allow me to point it at a data source and it would automatically figure out the tables, foreign keys, stored procedures, XML schema, or whatever else defines that data source's structure.  I am thinking about look at NHibernate, but am not sure how to get it to premptively define tables without them being defined in an XML file.  CodeSmith has a library that allows you to browse a database's structure, but I would prefer to not force a user of my tool to have a CodeSmith license.  The MicroSoft enterprise library has a database class that I will probably also look into.  Does anyone have any suggestions?

ajj3085 replied on Friday, January 19, 2007

Well, its not hard to do it yourself.  Basically you select from the table you want using a data reader, using the SchemaOnly and KeyInfo parameters.  Then you use the GetTableSchema method on the dr, and look at the data it returns. 

Brian Criswell replied on Friday, January 19, 2007

ajj3085:

Well, its not hard to do it yourself.  Basically you select from the table you want using a data reader, using the SchemaOnly and KeyInfo parameters.  Then you use the GetTableSchema method on the dr, and look at the data it returns. 

Which is fantastic if you know the tables already.  Any insights into a method to do that for more than one ADO.NET provider from a single code base?

Brian Criswell replied on Saturday, January 20, 2007

ajj3085:

Well, its not hard to do it yourself.  Basically you select from the table you want using a data reader, using the SchemaOnly and KeyInfo parameters.  Then you use the GetTableSchema method on the dr, and look at the data it returns. 

Thanks for the idea.  I did some searching and found that you can get the schema from a OleDbConnection.
http://my.execpc.com/~gopalan/dotnet/ado_net/ado.net_retrieving_database_metadata.html
So I will do some investigating to see how well discovery of tables, keys and such works.  Cheers.

ajj3085 replied on Monday, January 22, 2007

select * from sysobjects where type = 'u'

Brian Criswell replied on Monday, January 22, 2007

Yes, but as far as I know that only works for SQL Server.  Anyway, I am pleased to report that your initial suggestion put me on the right track.  The example that I linked to in my previous post did exactly what I wanted.  It allowed me to get the database, table, column, etc. information without having to write provider specific code (aside from the connection string).

ajj3085 replied on Monday, January 22, 2007

Opps, I forgot you were trying to be non-sql server specific.  Glad the suggestion was still useful though.

Q Johnson replied on Friday, January 19, 2007

You may want to check out the MyMeta functionality that comes with the free code generator MyGeneration.  It works great within MyGeneration templates (which can be written in C# and VB.NET), so it should likely work in what you're trying to build.

Brian Criswell replied on Friday, January 19, 2007

Q Johnson:

You may want to check out the MyMeta functionality that comes with the free code generator MyGeneration.  It works great within MyGeneration templates (which can be written in C# and VB.NET), so it should likely work in what you're trying to build.

Thanks, I think something like that is what I am looking for.  I will look into it.

EDIT: Doh!  It looks like the license prohibits linking to the .dlls.  So the best I will be able to do is generate the tables structure and then consume some metadata objects in my application.