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?)
我正在开发一个带有数据库工厂模式的asp.net应用程序,它允许应用程序同时支持Sql Server和Oracle。我创建了一个抽象类,它具有Sql Server和Oracle共有的方法,比如CreateConnection和CreateCommand方法。该类由SqlServer和Oracle类实现。现在,是否有一种简单的方法来编写带有Sql Server和Oracle通用参数的内联sql查询。我的意思是,我知道我们在Sql Server中使用“@”符号,在Oracle中使用“:”作为参数。正是因为这个原因,我在每个班级写两次查询。有没有办法编写这两个数据库共有的查询? (或解释一个常见查询中的参数?)
Thanks.
5 个解决方案
#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.
编写一个适用于Oracle和Sql Server的查询的唯一方法是仅使用两个平台共有的语法。一旦使用两种语言之间不同的功能(如参数或连接),您就必须编写两个不同的查询,或者将一个“转换器”类混合在一起,将一个查询从一个平台转换为另一个平台。
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.
我已经完成了很多这种类型的编程(数据库无关的软件),而使用.Net这样做的一个相对简单的方法就是将你的主应用程序编写为完全使用ADO.Net DataTables / DataSets,处理器类,用于处理从引擎盖下的Oracle或Sql Server表生成DataTables,还处理对DataTables进行的持久更改,返回Oracle或Sql Server。这种方法可以在一个地方隔离特定于数据库的代码,但如果应用程序需要访问的数据很大,则不一定是可行的方法。
#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.
您可以编写某种翻译器,但我建议在某些情况下,出于性能原因需要编写特定于db的代码,因此您将不得不忍受某些查询的两个版本的维护负担。
#3
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.
使用ORACLE并且不使用所有非标准功能(分析,枢轴等)有什么意义? ORACLE是一个强大的工具。其他数据库也有自己的实力,那么为什么要使用最低的公分母才能使用它们呢?你只会失去表现。
Just pick one DB, and use it fully with all its functionalities !
只需选择一个数据库,并充分利用它的所有功能!
#4
Pardon my ignorance here, but can't something like an ORM (object relational mapper) work for both SQL and Oracle?
请原谅我的无知,但不能像ORM(对象关系映射器)那样适用于SQL和Oracle吗?
#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:
我有类似的要求,支持Sql Server和Oracle,并总结了我在这些文章中遇到这些问题的两年经验:
Writing ANSI Standard SQL is not practical.
编写ANSI标准SQL是不实际的。
Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.
认为ANSI标准SQL在数据库之间是完全可移植的?再想想。
#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.
编写一个适用于Oracle和Sql Server的查询的唯一方法是仅使用两个平台共有的语法。一旦使用两种语言之间不同的功能(如参数或连接),您就必须编写两个不同的查询,或者将一个“转换器”类混合在一起,将一个查询从一个平台转换为另一个平台。
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.
我已经完成了很多这种类型的编程(数据库无关的软件),而使用.Net这样做的一个相对简单的方法就是将你的主应用程序编写为完全使用ADO.Net DataTables / DataSets,处理器类,用于处理从引擎盖下的Oracle或Sql Server表生成DataTables,还处理对DataTables进行的持久更改,返回Oracle或Sql Server。这种方法可以在一个地方隔离特定于数据库的代码,但如果应用程序需要访问的数据很大,则不一定是可行的方法。
#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.
您可以编写某种翻译器,但我建议在某些情况下,出于性能原因需要编写特定于db的代码,因此您将不得不忍受某些查询的两个版本的维护负担。
#3
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.
使用ORACLE并且不使用所有非标准功能(分析,枢轴等)有什么意义? ORACLE是一个强大的工具。其他数据库也有自己的实力,那么为什么要使用最低的公分母才能使用它们呢?你只会失去表现。
Just pick one DB, and use it fully with all its functionalities !
只需选择一个数据库,并充分利用它的所有功能!
#4
Pardon my ignorance here, but can't something like an ORM (object relational mapper) work for both SQL and Oracle?
请原谅我的无知,但不能像ORM(对象关系映射器)那样适用于SQL和Oracle吗?
#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:
我有类似的要求,支持Sql Server和Oracle,并总结了我在这些文章中遇到这些问题的两年经验:
Writing ANSI Standard SQL is not practical.
编写ANSI标准SQL是不实际的。
Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.
认为ANSI标准SQL在数据库之间是完全可移植的?再想想。