I've been on a real software philosophy kick lately. Here's more of my thoughts (which are subject to change).
I've spent alot of my time trying to develop methodologies and code to help me write database independent code. Invariably, I run across problems, some caused by a db with a crappy internal type system (Oracle), some caused by a broken unification model (ADO.net), and others caused by poorly implemented providers.
I ran across a heated discussion on another blog (which I can't find now, or I'd reference it) on whether database independent code should be something to give up on. After all, even though they compete for business, Oracle was designed for something very different than SqlServer, or MySQL was designed for. They each have unique features, different SQL syntax, stored procedure concepts, etc that keep there from being a universal API for database access.
So there are two common approaches to take (excluding giving up):
- Implement to the lowest common denominator. Solutions for this exist already. ODBC, JDBC, OLEDB, all fit this model.
- Move the unification layer inside your application, so you are implementing a unified API for your application. This allows you to focus on the functionality you need and use object oriented techniques and patterns to reduce redundancy and increase maintainability.
I first approached ADO.net as the answer to #1, for which it is woefully inadequate. I now realize it is an enabler for #2, defining concepts that databases share (which make them databases). This gives you some commonality between implementations without forcing you to the lowest common denominator like previous attempts. Besides, most applications are targeted to a specific db anyway and don't benefit from that approach.
ADO.net is not without it problems, but viewing it in that light makes it a much more satisfying tool...for me anyway.