Re: .NET database inspection API?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Peter Duniho wrote:
Is there a straightfoward API in .NET that allows for inspection of a database? That is, to look at the structure of the database, without knowing anything in advance about it? For example, retrieving a list of tables in the database.

Depends on what you want to know. If you just want to know the tables and their columns, it might be possible, using DbProviderFactory to obtain the factory for the db elements at hand, and then use these elements, e.g. command, connection, to obtain the schema of the database connected, using DbConnection.GetSchema(). Unfortunately, the design of this is pretty shortsighted, in that it doesn't force one way to obtain meta data with the same resultsets onto EVERY ado.net provider in .net. So there are ado.net providers, which do implement GetSchema but the returned tables with meta-data are specific to their db. This means that it's hard to write db generic code for this.

On top of that, the more detailed meta-data can be db specific too. For example, oracle uses sequences and synonyms for everything. Access doesn't, sqlserver 2005 has synonyms but not sequences. Synonym retrieval for oracle works very different than it does for sqlserver 2005. etc. etc.

So it gets dirty pretty quickly. In the end, you'll end up with a lot of db specific code, wrapped in a driver per db.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
.

Relevant Pages

  • Re: Help - Timing Logic
    ... server application, both of which ran on the same box. ... the client applications and 'lodging' them in the database. ... Another part of the server application was dedicated to retrieving messages ... commit transaction ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Trying to use *.ini file as a database record
    ... > manipulating a database file. ... using the APIs to retrieve data and populate a MSflexgrid ... > is a magnitude slower than retrieving that info from a user-defined random ... If you like the ini file, you might consider reading the whole thing in at once ...
    (comp.lang.basic.visual.misc)
  • Re: Should I use frontpage or other web authoring tool
    ... Of course retrieving a page from the database takes longer then retrieving a page from the file system, ... The ability to edit in FrontPage is fantastic, so powerful, so easy etc. ... Doing this will possibly cause problems for you down the road because FrontPage will save the page changes to SharePoint's content database instead of the actual file, and if you ever need to modify the files on the web server, it will not affect the pages that you opened and edited in FrontPage. ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Is OOP really appropriate for PHP?
    ... all my objects reside in RAM. ... to think about storing and retrieving them, ... You might say, "in my MySQL database", ... You can store objects or object data in a session. ...
    (comp.lang.php)
  • Re: store object in database
    ... > while retrieving, i am getting a class cast exception when i am trying ... As far as I know, mysql is not an OO database, so you can't store objects ...
    (comp.lang.java.help)