如何设计和管理与多个数据库管理系统（例如 Oracle 和 MS SQL Server）兼容的 Web 应用程序的开发？
如果您不能使用NHibernate或EF等ORM，那么您如何在开发过程中维护数据库模式？
我现在的方法是在 SQL Server 上建立一个开发数据库，并在发布测试补丁之前将其移植到 Oracle（使用工具），以在两个 RDBMS 上测试软件。 （该工具还生成一个供应用程序用来升级数据库的文件）
这是一个好方法吗？ Visual Studio 的数据库项目怎么样？它是否是保留数据库架构的更好方法？
编辑：
这个问题不是关于设计应用程序的架构（我已经有了一个抽象的数据访问层），而是如何在开发过程中维护不同类型的 RDBMS 的数据库模式。
How do you design and manage the development of a web application that should be compatible with multiple database management system such as Oracle and MS SQL Server?
If you can't use ORM like NHibernate or EF, how do you maintain database schemas during the development?
My approach now is to have a development database on SQL Server and to port it to Oracle (with a tool) just before releasing a test patch, to test the software on both rdbms. (The tool also generates a file used by the application to upgrade the database)
Is it a good approach? What about a database project of Visual Studio, could it be a better way to keep my db schema?
EDIT:
This question is not about designing the architecture of the application (I have already an abstract data access layer), but how to maintain database schemas for different kinds of rdbms during the development.
发布评论
评论（3）
我认为关键是确保您坚持标准 SQL 语法。 MS SQL Server 附带 Transact SQL (T-SQL)，它是 ISO 标准 SQL，这意味着它具有不属于标准 SQL 正式一部分的额外语法。
SQLZoo是一个很好的网站，可以让你比较不同数据库的语法支持。您会发现，您日常使用的大多数语法在大多数数据库之间都是相同的，但存在一些怪癖。找到它们的最佳方法是检查每个环境中的每个查询，然后将它们签入源代码管理。
您使用数据库项目的想法是一个很好的想法。它将允许您快速将更改部署到多个数据库并自动测试它们。
I think the key to this is to make sure that you stick to standard SQL syntax. MS SQL Server ships with Transact SQL (T-SQL) which is a super-set of ISO standard SQL, meaning that it has extra syntax which is not officially part of standard SQL.
SQLZoo is a good site that allows you to compare the syntax support of different databases. You will find that most of the syntax that you use from day to day will be the same between most DBs, but there are a few quirks out there. The best way to find them is to check each of your queries in each environment and then check them into source control.
Your idea to use a database project is a good one. It will allow you to deploy your changes to multiple databases quickly and test them automatically.
模型驱动架构 (MDA)：使用通用数据库建模工具来设计数据库架构。您定义表/关系/主键/等。以通用方式，然后让设计者生成必要的 SQL 脚本（大多数支持输出到各种数据库）。当您更改数据库模型时，该工具将生成必要的 SQL 代码来更新数据库，或者从头开始生成。这些工具还有助于生成文档并协助数据库版本控制等...
我使用 上下文数据库设计师，对工具和价格非常满意。 Enterprise Architect 看起来也是一个出色的工具，具有生成和逆向工程的能力代码。
Model-driven architecture (MDA): use a generic Database modelling tool to design your database schema. You define the tables/relationships/primary keys/etc. in a generic fashion and then have the designer generate the necessary SQL script (most support output to a variety of databases). As you change the DB model, the tool will generate the necessary SQL code to update the database, or generate it from scratch. The tools also assist in generating documentation and assisting with database versioning, amongst many other things...
I use Context Database Designer and am exceptionally happy with the tool and price. Enterprise Architect also looks like an excellent tool, with the ability generate and reverse-engineer code.
实际上，处理此问题的唯一方法是将数据库访问与核心应用程序分离，以便您可以自定义每个数据库的访问代码。换句话说，这正是 Nhibernate 和 EF 所做的。如果你不能使用这些工具之一，你仍然会以任何方式编写一个有效的工具。这可能很有趣，但也会占用大量时间。所以我会问一些关于为什么不能使用 ORM 的严肃问题。
Realistically the only way to handle this is to decouple your database access from you core application so that you can customize the access code for each database. In other words, exactly what Nhibernate and EF do. If you cannot use one of these tools you will still effective end up writing one any way. Which can be fun and interesting, but will also take up a large amount of time. So I'd be asking some serious questions about why you cannot use an ORM.