The forgotten methods in the ADO.NET API

In our projects we consistently found that we needed some slight additional functionality from that provided. And we needed the same thing in project after project. So we added this in a library that provides it for the top 5 databases as well as ODBC/OleDb.

We are now releasing it as open source (The Kailua Project). Not GPL or anything like that - just it's free to use however you wish.

This library is intended to be as lightweight as possible while providing a consistent API for ADO.NET connectors. I myself call it the forgotten methods in the ADO.NET API. If you want something more than this take a look at Hibernate.

Every bit of functionality in here we need for our products. Some of our products need more than others but we were very careful to only add what is absolutely needed. We do not use all the DbType columns in databases but we did try to add testing for all types.

What this adds is the following:

• Return all metadata for the DB
  • table/view/column names, descriptions
  • column data type
  • tables PK:FK relationships
  • stored procedures – parameter info
• Return the primary key on an insert for auto-generated primary keys.
• Have a standard syntax to select the "top N" rows.
• A timestamp construct to match the TIMESTAMP in Sql Server and adding a GetTimestamp() method to handle it.
• Create connection strings from a server, database, username, and password.
• Run a DDL script on the database.
• Launch the program used to administer the database.
• A DataReader wrapper to work around bugs in the DB2 DataReader.

We use the IDb... interfaces as the basis instead of the Db... classes. There are good arguments either way but we found it easier to do this working off the interfaces. We created the WRVendor class instead of extending DbProviderFactory because we are returning IDb... interfaces rather than Db... classes. I am curious as to why Microsoft had DbProviderFactory return Db... instead of IDb... - but they did.

For Sql Server, ODBC, and OleDb it statically links to the connectors as those are part of .NET. For all others (DB2, MySql, Oracle, & PostgreSQL as of this time), it gets it's classes from DbProviderFactories so that it builds and runs fine if those connectors are not installed (although obviously cannot call them if they are not installed).

For other vendors, it has the WrUnknown classes which by definition cannot provide full functionality. They do as best as they can but if they could do all of this, this library would not be needed.

Comments

[ben logan]

I hope I don't forget! I like the timestamp constuct, looking forward to playing around with it. thanks!