Queries for Sql Server and Oracle

Question

Hi All, I'm developing an asp.net application with Database factory pattern which allows the application to support both Sql Server and Oracle. I've created an abstract class that has the methods common to Sql Server and Oracle, like the CreateConnection and CreateCommand methods. This class is implemented by SqlServer and Oracle classes. Now, is there an easy way to write in-line sql queries with parameters common to both Sql Server and Oracle. I mean, I understand that we use "@" symbol in Sql Server and ":" in Oracle for parameters. Just for this reason, I'm writing queries twice in each of the class. Is there a way to write such queries common to both the databases? (or interpret the parameters from one common query?)

Thanks.

Answer 1

The only way to write one query that will work for both Oracle and Sql Server is to use only the syntax that is common to both platforms. Once you use features that are different between the two languages (like parameters or joins), you either have to write two different queries or hack together a "translator" class that converts a query from one platform to the other.

I've done a lot of this type of programming (database-agnostic software), and with .Net a relatively pain-free way of doing this is to write your main application to work entirely with ADO.Net DataTables/DataSets, with a wrapper class that handles generating the DataTables from either Oracle or Sql Server tables under-the-hood, and also handles persisting changes made to the DataTables back into Oracle or Sql Server. This approach isolates your DB-specific code in one place, although it's not necessarily a viable approach if the data your application needs access to is large.

Answer 2

You could write some kind of translator, but I would suggest that in some cases you'll need to write db-specific code for performance reasons anyway, so you'll have to put up with the maintenance burden of two versions of some queries.

Answer 3

Pardon my ignorance here, but can't something like an ORM (object relational mapper) work for both SQL and Oracle?

Answer 4

What is the point of using ORACLE and not using all its non standard functions (analytics, pivots etc) ? ORACLE is a powerful tool. Other DBs have there own strenght also, so why use the lowest common denominator just to be able to work on ALL of them? You will just lose in performance.

Just pick one DB, and use it fully with all its functionalities !

Answer 5

I had similar requirements, to support both Sql Server and Oracle, and summarized my two years of experience with such problems in these articles:

Writing ANSI Standard SQL is not practical.

Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.