By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,589 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,589 IT Pros & Developers. It's quick & easy.

.NET database inspection API?

P: n/a
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.

Doing a little browsing in MSDN, I see that the abstract representation of
a database appears to be the DataSet class. I also see that I can use
"data adapter" classes (e.g. OdbcDataAdapter) to connect a particular
database to a DataSet. Because for example DataSet.Tables returns a
collection of tables in the database, I thought this might be useful.

However, it looks to me as though the connection can only be made with
some advance knowledge of the database. It appears that to get a data
adapter hooked up to a database requires the use of a Select operation
(e.g. SQL SELECT), which in turn would imply you already know from which
table or tables you're selecting.

In the past I've written a simple database browsing utility that just uses
regular SQL statements with the appropriate connection, command, reader,
etc. objects, along with specific knowledge about the structure of the
database. By doing queries on the known standard tables in the database
that themselves describe the structure of the database, I was able to
retrieve the sort of information I wanted.

But a) that sort of approach seemed pretty specific to the exact kind of
database (e.g. I was working with an actual SQL database and server,
whereas now I'm working on doing a similar thing with a .MDB file accessed
by the appropriate ODBC driver) and b) I would much rather not have to
write the query statements directly in the first place, but rather just
call appropriate methods or properties on .NET classes to retrieve the
information in a more general way.

I hope the above makes sense. I do very little work with databases
anyway, and so please forgive me if my terminology or philosophy seems a
little "off". :)

I know from the messages that get posted here that lots of other people
are using databases on a daily basis. I'm hoping that this question will
translate and someone will have some helpful advice to offer.

Thanks! :)
Pete
Jun 27 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.

Doing a little browsing in MSDN, I see that the abstract representation
of a database appears to be the DataSet class. I also see that I can
use "data adapter" classes (e.g. OdbcDataAdapter) to connect a
particular database to a DataSet. Because for example DataSet.Tables
returns a collection of tables in the database, I thought this might be
useful.

However, it looks to me as though the connection can only be made with
some advance knowledge of the database. It appears that to get a data
adapter hooked up to a database requires the use of a Select operation
(e.g. SQL SELECT), which in turn would imply you already know from which
table or tables you're selecting.

In the past I've written a simple database browsing utility that just
uses regular SQL statements with the appropriate connection, command,
reader, etc. objects, along with specific knowledge about the structure
of the database. By doing queries on the known standard tables in the
database that themselves describe the structure of the database, I was
able to retrieve the sort of information I wanted.

But a) that sort of approach seemed pretty specific to the exact kind of
database (e.g. I was working with an actual SQL database and server,
whereas now I'm working on doing a similar thing with a .MDB file
accessed by the appropriate ODBC driver) and b) I would much rather not
have to write the query statements directly in the first place, but
rather just call appropriate methods or properties on .NET classes to
retrieve the information in a more general way.

I hope the above makes sense. I do very little work with databases
anyway, and so please forgive me if my terminology or philosophy seems a
little "off". :)

I know from the messages that get posted here that lots of other people
are using databases on a daily basis. I'm hoping that this question
will translate and someone will have some helpful advice to offer.
If you can get an OLE DB provide you can use OleDbConnection
GetOleDbSchemaTable.

A lot of databases support the INFORMATION_SCHEMA views.

But there are no true database independent way of doing it.

Arne
Jun 27 '08 #2

P: n/a
On 2008-04-26, Peter Duniho <Np*********@nnowslpianmk.comwrote:
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.

Doing a little browsing in MSDN, I see that the abstract representation of
a database appears to be the DataSet class. I also see that I can use
"data adapter" classes (e.g. OdbcDataAdapter) to connect a particular
database to a DataSet. Because for example DataSet.Tables returns a
collection of tables in the database, I thought this might be useful.

However, it looks to me as though the connection can only be made with
some advance knowledge of the database. It appears that to get a data
adapter hooked up to a database requires the use of a Select operation
(e.g. SQL SELECT), which in turn would imply you already know from which
table or tables you're selecting.

In the past I've written a simple database browsing utility that just uses
regular SQL statements with the appropriate connection, command, reader,
etc. objects, along with specific knowledge about the structure of the
database. By doing queries on the known standard tables in the database
that themselves describe the structure of the database, I was able to
retrieve the sort of information I wanted.

But a) that sort of approach seemed pretty specific to the exact kind of
database (e.g. I was working with an actual SQL database and server,
whereas now I'm working on doing a similar thing with a .MDB file accessed
by the appropriate ODBC driver) and b) I would much rather not have to
write the query statements directly in the first place, but rather just
call appropriate methods or properties on .NET classes to retrieve the
information in a more general way.

I hope the above makes sense. I do very little work with databases
anyway, and so please forgive me if my terminology or philosophy seems a
little "off". :)

I know from the messages that get posted here that lots of other people
are using databases on a daily basis. I'm hoping that this question will
translate and someone will have some helpful advice to offer.

Thanks! :)
Pete
Yes, there are ways to inspect the database. You might want to start
with this article:

http://msdn2.microsoft.com/en-us/lib...4934(ide).aspx

While there are certain metadata collections that all providers share,
there are also provider specific collectons...

http://msdn2.microsoft.com/en-us/lib...54969(id).aspx

So for example - I have a piece of code I use to automatically generate
data entities from tables/views and stored procedures. This utility
generates code that complies to a specific in-house framework. We
connect to an oracle database - so, to get all the tables in a given
schema might look like:
using (OracleConnection connection = new OracleConnection (connectionString))
{
connection.Open();
using (DataTable tables = connection.GetSchema ("Tables", new string[] {schemaName, null}))
{
foreach (DataRow tableInfo in tables.Rows)
{
Console.WriteLine (tableInfo["TABLE_NAME"]);
}
}
}

Actually, the code for SQLServer is pretty identicle (I actually adapted
the sqlserver version to use with oracle) - except you use SqlConnection.

If I wanted to know the columns in a field, you could use schema
collections as well - or you can use the DataAdapter.FillSchema method
instead... It depends on the kind of information you need.

There are other useful tools, such as the various typed
ConnectionStringBuilder classes for parsing and building syntactically
correct connections strings, etc.

I hope this is enough to get you started.

--
Tom Shelton
Jun 27 '08 #3

P: n/a
Arne Vajhøj wrote:
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.

I hope the above makes sense. I do very little work with databases
anyway, and so please forgive me if my terminology or philosophy seems
a little "off". :)

I know from the messages that get posted here that lots of other
people are using databases on a daily basis. I'm hoping that this
question will translate and someone will have some helpful advice to
offer.

If you can get an OLE DB provide you can use OleDbConnection
GetOleDbSchemaTable.
It's pretty buggy in a lot of cases unfortunately. Like on Sqlserver,
it's better to use the ADO.NET 2.0 schema retrieval mechanism than to
use oledb for for example pk retrieval, which fields are identity etc.
A lot of databases support the INFORMATION_SCHEMA views.
I wished that was true ;). Not a lot of databases implement all views,
similar to that almost no database implements the SQL standards to which
INFORMATION_SCHEMA belongs to in full

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#)
------------------------------------------------------------------------
Jun 27 '08 #4

P: n/a
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#)
------------------------------------------------------------------------
Jun 27 '08 #5

P: n/a
Frans Bouma [C# MVP] wrote:
Arne Vajhøj wrote:
>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.

I hope the above makes sense. I do very little work with databases
anyway, and so please forgive me if my terminology or philosophy
seems a little "off". :)

I know from the messages that get posted here that lots of other
people are using databases on a daily basis. I'm hoping that this
question will translate and someone will have some helpful advice to
offer.

If you can get an OLE DB provide you can use OleDbConnection
GetOleDbSchemaTable.

It's pretty buggy in a lot of cases unfortunately. Like on
Sqlserver, it's better to use the ADO.NET 2.0 schema retrieval mechanism
than to use oledb for for example pk retrieval, which fields are
identity etc.
OK. I will take you word for it.
>A lot of databases support the INFORMATION_SCHEMA views.

I wished that was true ;). Not a lot of databases implement all
views, similar to that almost no database implements the SQL standards
to which INFORMATION_SCHEMA belongs to in full
My experience is that, if they are there, then they are also good enough
for the simple purposes like getting table names, column names and
column types.

Obviously peoples purposes may differ.

Arne
Jun 27 '08 #6

P: n/a
Responses inline:

"Peter Duniho" <Np*********@nnowslpianmk.comwrote in message
news:op***************@petes-computer.local...
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.

Doing a little browsing in MSDN, I see that the abstract representation of
a database appears to be the DataSet class. I also see that I can use
"data adapter" classes (e.g. OdbcDataAdapter) to connect a particular
database to a DataSet. Because for example DataSet.Tables returns a
collection of tables in the database, I thought this might be useful.
That's not accurate: DataSet.Tables is a collection of DataTable objects
that exist within the DataSet, and _not_ in the database. DataTable objects
inherently have absolutely nothing to do with the tables in the database.
Consequently, a DataSet does not automatically reflect the table structures
or relationships in the underlying database. If in a particular application
(e.g., yours), the DataTable objects do in fact map directly to underlying
tables in the database it's only because you explicitly caused that 1:1
mapping to happen.

For illustration purposes, you can populate a DataTable (or DataSet) from a
stored procedure that contains a select statement that JOINs multiple base
tables to return a single result set. That single result set is represented
by a single DataTable in your application, or a single DataTable within your
DataSet. Note that you can have a DataTable without having it also contained
within a DataSet. If your stored procedure contains multiple SELECT
statements (i.e. returns multiple result sets), then each result set becomes
a different DataTable in your DataSet.
>
However, it looks to me as though the connection can only be made with
some advance knowledge of the database. It appears that to get a data
adapter hooked up to a database requires the use of a Select operation
(e.g. SQL SELECT), which in turn would imply you already know from which
table or tables you're selecting.
True. And the way you would go about accomplishing this would very much be
database dependent (unfortunately). The reason is that the meta data within
each database (THIS is the stuff you are after) is stored differently for
each database product, and even changes between versions for a given
database (e.g., SQL Server).
In the past I've written a simple database browsing utility that just uses
regular SQL statements with the appropriate connection, command, reader,
etc. objects, along with specific knowledge about the structure of the
database. By doing queries on the known standard tables in the database
that themselves describe the structure of the database, I was able to
retrieve the sort of information I wanted.
What you did in the past is how you'd have to do it this time around, too.
In particular, the "specific knowledge about the structure of the
database" - if it is to be automated - would have to be retrieved from the
meta data tables implemented in each database you are interested in working
with. As others responding to your OP have pinted out, this metadata is
stored very differently amongst database products.

But a) that sort of approach seemed pretty specific to the exact kind of
database (e.g. I was working with an actual SQL database and server,
whereas now I'm working on doing a similar thing with a .MDB file accessed
by the appropriate ODBC driver) and b) I would much rather not have to
write the query statements directly in the first place, but rather just
call appropriate methods or properties on .NET classes to retrieve the
information in a more general way.
And, to quote Austin Powers, "... and I'd like to have a solid gold toilet
seat, but it's just not in the cards baby!..."

No sarcasm intended... just pointing out how unlikely this capability is,
given what it would take to accomplish - which is that it would have to have
specific knowledge of every database product's meta data.
I hope the above makes sense. I do very little work with databases
anyway, and so please forgive me if my terminology or philosophy seems a
little "off". :)

I know from the messages that get posted here that lots of other people
are using databases on a daily basis. I'm hoping that this question will
translate and someone will have some helpful advice to offer.
To see how close you can get to your objective with the .NET
Framework-provided classes, look at the DataAdapter class's .FillSchema
method.

Note that there is no such thing as a DataAdapter class that you can work
with, because DataAdapter classes are database product-specific. So, you'll
need to look up SqlDataAdapter (used for connecting to a SQL Server
database), or OleDbDataAdapter, or OracleDataAdapter.

In any case, the .FillSchema and similar methods might get you close to what
you want. If they do, then all your utility would need to "know" is which of
those 3 types of databases you are targeting.

Note that while you can use OleDbDataAdapter to connect to [almost] any
database out there, the generic OleDb drivers they encapsulate provide only
the most generic of connectivity to the target database, and may not provide
the schema information you are after... in which case you would do better to
use the particular DataAdapter that targets the target database most
specifically.

One final note about ADO.NET to think about is that it's classes are divided
into two categories: (1) the connected objects, and (2) the disconnected
objects. The "connected objects" are those that connect to a specific data
store and must have data store-specific knowledge, like a connection string,
correct data provider etc (e.g., SqlConnection, SqlCommand, SqlDataAdapter,
SqlDataReader). The disconnected objects are completely oblivious to the
source of the data that they work with. They don't know or care about the
actual data source, and that source may even be your hard-coding of their
population. Examples include the DataSet and DataTable objects/collections.
HTH
-Cramer

Jun 27 '08 #7

P: n/a
Cramer wrote:
"Peter Duniho" <Np*********@nnowslpianmk.comwrote in message
>However, it looks to me as though the connection can only be made with
some advance knowledge of the database. It appears that to get a data
adapter hooked up to a database requires the use of a Select operation
(e.g. SQL SELECT), which in turn would imply you already know from which
table or tables you're selecting.

True. And the way you would go about accomplishing this would very much be
database dependent (unfortunately). The reason is that the meta data within
each database (THIS is the stuff you are after) is stored differently for
each database product, and even changes between versions for a given
database (e.g., SQL Server).
That is how it is.

It is not how it needs to be.

The JDBC API provides a database independent way of doing it. There are
defined methods in the interfaces and the JDBC driver writers has to
implement that using whatever methods are appropriate for the database.

ADO.NET could have done something similar.
>But a) that sort of approach seemed pretty specific to the exact kind of
database (e.g. I was working with an actual SQL database and server,
whereas now I'm working on doing a similar thing with a .MDB file accessed
by the appropriate ODBC driver) and b) I would much rather not have to
write the query statements directly in the first place, but rather just
call appropriate methods or properties on .NET classes to retrieve the
information in a more general way.

And, to quote Austin Powers, "... and I'd like to have a solid gold toilet
seat, but it's just not in the cards baby!..."

No sarcasm intended... just pointing out how unlikely this capability is,
given what it would take to accomplish - which is that it would have to have
specific knowledge of every database product's meta data.
That should not be a problem.

The ADO.NET provider is database specific anyway.

Arne
Jun 27 '08 #8

P: n/a
On Sun, 27 Apr 2008 15:06:46 -0700, Arne Vajhøj <ar**@vajhoej.dkwrote:
[...]
> True. And the way you would go about accomplishing this would very
much be database dependent (unfortunately). The reason is that the meta
data within each database (THIS is the stuff you are after) is stored
differently for each database product, and even changes between
versions for a given database (e.g., SQL Server).

That is how it is.

It is not how it needs to be.

The JDBC API provides a database independent way of doing it. There are
defined methods in the interfaces and the JDBC driver writers has to
implement that using whatever methods are appropriate for the database.
Hmmm...well, that's interesting. In my case, I don't actually _need_
general-purpose database support, at least not at the moment. It just
seemed that the more generally the application could be written at the
outset, the less likely there'd be some maintenance issue in the future,
should the database format change.

I know the database format (Access .MDB files). And I don't _have_ to do
this in .NET. Java would be okay, if that turned out to be a better
general-purpose solution.

Rather than switch newsgroups, I'll just ask the basic question here: is
there JDBC support for Access .MDB files? If so, is this support limited
to a specific platform or platforms (e.g. Windows), or have the drivers
been implemented wherever the Java run-time itself has been?

Cross-platform would be great. The specific application can be Windows
only if that turns out to be the least-expensive way to write it, but the
users would much prefer a cross-platform solution. If it's actually
easier to do it cross-platform, via Java, then that's an obvious win. :)

The fact is, if push came to shove, there's no real need to be able to
deal with an arbitrary database structure. At least for the moment, the
actual database structure is known and of course we can always open the
files in Access to find out the details of the structure if it should
change in the future. It would be sufficient to simply allow the user to
describe the database structure. But obviously it'd be easier for the
user if the application can present the existing structure to the user
instead.

I appreciate all the responses. The DbConnection.GetSchema() method
sounds the most promising, at least from the .NET point of view. However,
I'm intrigued by the possibility that Java might actually work better.
That would be amusingly ironic. :)

Thanks!

Pete
Jun 27 '08 #9

P: n/a
Peter Duniho wrote:
On Sun, 27 Apr 2008 15:06:46 -0700, Arne Vajhøj <ar**@vajhoej.dkwrote:
>[...]
>> True. And the way you would go about accomplishing this would very
much be database dependent (unfortunately). The reason is that the
meta data within each database (THIS is the stuff you are after) is
stored differently for each database product, and even changes
between versions for a given database (e.g., SQL Server).

That is how it is.

It is not how it needs to be.

The JDBC API provides a database independent way of doing it. There are
defined methods in the interfaces and the JDBC driver writers has to
implement that using whatever methods are appropriate for the database.

Hmmm...well, that's interesting. In my case, I don't actually _need_
general-purpose database support, at least not at the moment. It just
seemed that the more generally the application could be written at the
outset, the less likely there'd be some maintenance issue in the future,
should the database format change.

I know the database format (Access .MDB files). And I don't _have_ to
do this in .NET. Java would be okay, if that turned out to be a better
general-purpose solution.

Rather than switch newsgroups, I'll just ask the basic question here: is
there JDBC support for Access .MDB files? If so, is this support
limited to a specific platform or platforms (e.g. Windows), or have the
drivers been implemented wherever the Java run-time itself has been?

Cross-platform would be great. The specific application can be Windows
only if that turns out to be the least-expensive way to write it, but
the users would much prefer a cross-platform solution. If it's actually
easier to do it cross-platform, via Java, then that's an obvious win. :)
Java for Windows comes with a JDBC-ODBC bridge, which is a JDBC driver
that can delegate to any ODBC driver. It works with MS Access since
there is an ODBC driver for that.

But the JDBC-ODBC bridge is a piece of crap. It notoriously gives
problems. Among other things it is not thread safe. Microsoft should
have written a JDBC driver for Access like they did for SQLServer.

I don't think you can access MDB files on a non-Windows platform,
because the Jet DLL's necesarry to access MDB files are Windows
specific.
The fact is, if push came to shove, there's no real need to be able to
deal with an arbitrary database structure. At least for the moment, the
actual database structure is known and of course we can always open the
files in Access to find out the details of the structure if it should
change in the future. It would be sufficient to simply allow the user
to describe the database structure. But obviously it'd be easier for
the user if the application can present the existing structure to the
user instead.

I appreciate all the responses. The DbConnection.GetSchema() method
sounds the most promising, at least from the .NET point of view.
However, I'm intrigued by the possibility that Java might actually work
better. That would be amusingly ironic. :)
"better" is such a broad word. But the JDBC API has been designed from
the beginning to be completely database independent. For good and
for worse.

That includes a lot of database "exploration" see
http://java.sun.com/javase/6/docs/ap...eMetaData.html
for some of the info you can get.

Arne
Jun 27 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.