Database neutral .NET application

Question

Many product developers want to write a .NET application which will work seamlessly with any popular RDBMS like SQL server, oracle, DB2 , MySql. If we use the Data application block it dynamically picks the database driver (OracleClient, SQLClient or OleDBClient) based on configuration.

However, all databases have their own flavours of SQL. There are subtle differences which prevent one SQL code base to be used universally - the function names are different, the way dates are handled are different, the way Identity columns handled are different and so on.

One could use a third party product from Data Direct to write DB neutral code using predefined escape sequences.

Are there any tools or products from Microsoft which addresses this problem ?

Answer 1

The Microsoft ADO.NET Entity Framework is an Object-Relational Mapping framework from Microsoft which is designed to let you write code which is (somewhat) DB-agnostic. All major DBMS's have support for the Entity Framework, although for many (e.g. MySQL, Postgres) you'll need to download something separate from your .NET Framework install.

Note: I have yet to work on a single large project using an O/RM tool where there wasn't at least one case where we had to shell out to native SQL-- either for DB-specific functionality or for performance reasons (needed to write a query in a particular way to get a good query plan). But a good O/RM can work most of the time to prevent having to write SQL.

That said, the current version of the Entity Framework is pretty limited in a number of dimensions-- other O/RM frameworks like NHibernate, SubSonic, etc. are much more mature, where "mature" means support a deeper range of DBMS features without having to shell out to native SQL, wider community support, better performance, etc.

This is my polite way of saying that, for a real-world, relatively complex project, I don't think that the current version of the Entity Framework is the best way to go relative to the other O/RM tools available.

Apparently, the upcoming .NET 4.0/VS2010 version of the Microsoft Entity Framework is much improved, so the statement above may not be true next year. The new Entity Framework adds support for DDL, for example, so you can create tables and indexes in a DBMS-independent way. Also, the .NET 4.0 Entity Framework is leveraging a cool feature called T4 Templates, which is a neat feature to auto-generate code at build time, which is important for things like O/RM wrappers which need to be kept in sync with an external DB schema-- and which should yield better (aka pre-compiled) performance than do O/RM tools which don't know the types of database columns until runtime.

Answer 2

As I've understood you are looking for tool for writing RDBMS-independent SQL, but not an ORM. This is very actual problem solved by different ORM vendors in different ways. I just can mention Xtensive SQL DOM tool, that was developed for their ORM - DataObjects.Net. It is an abstraction over SQL allowing to write queries using full-featured DOM model. At this moment SQL DOM is not promoted separately from DataObjects.Net, but it is an open source product and you surely can ask its authors about it. As far as I know it is successfully used in some third-party projects.

Answer 3

Not directly from Microsoft. Those tools are commonly named Object-Relational Mappers or short ORM. NHibernate, for instance, is a common and widely used ORM that is very sure to fit your needs. Of course there are other commercial products like Telerik OpenAccess ORM, DevExpress XPO or RemObjects DataAbstract which will help you to be database agnostic.