Getting data from multiple databases

I am working on an application that will need to communicate with many different applications running on different database platforms. I will know the table schema before runtime but I won't know the database platform (MS SQL 200X, Oracle 9i, 10g, etc, MySQL 4.0.1, 5.x, etc, sybase, etc) until ru开发者_JAVA技巧ntime.

It's my understanding that each of these systems have a slightly different dialect. Do I need to use nhibernate to handle the differences when connecting to these systems or can I use ADO.NET and pass raw SQL strings (select * from table)?

---

If you only need to use ANSI SQL statements, which should be implemented by all of the databases then yes, you can just use ADO.NET.

In my experience the main problem with database-agnostic code is the use of surrogate keys, like sequences or autonumber fields, as all databases implement these differently.

If you do need to use features that differ across databases then I don't think that it is reason enough to go to an object relational mapper like NHibernate - only do that if you have other reasons to do so. You can implement your own handling of syntax differences by generating different SQL for different databases easily enough.

---

SQL should be standardized for all dbs but they don't all use the same syntax so it really depends on what SQL you're calling. For example, SQL Server uses TOP while Oracle uses rownum. Even if they're all DDL, some syntactically differences between DBMSes can be an issue.

If select * from table is all you want, then there shouldn't be a problem, other than performance hits.