使用客户端应用程序安装数据库的工具或最佳实践
我想出了两种使用 WPF 应用程序安装本地数据库的方法：
- 创建数据库备份，然后通过 SMO
- 脚本在安装程序中恢复数据库安装并通过 ADO 或 osql.exe 执行它
我需要提供升级路径，而不是仅仅删除当前安装的数据库。
还有其他更灵活的方法吗？有更好/更容易管理的工具吗？
I have come up with 2 methods for installing a local database with my WPF application:
- Create a backup of the DB, then restore in the installer via SMO
- Script the database install and execute it via ADO or osql.exe
I'm required to provide an upgrade path, rather than just dropping the currently installed databases.
Are there other methods that are more flexible? Are there better/easier to manage tools?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您看过 Sql Server Compact 3.5 SP1 吗？安装只是复制数据库文件并引用项目中的程序集的问题。
http://www.microsoft.com/sqlserver/2008/en/us /compact.aspx
升级路径到底是什么意思？相同应用程序和数据库的未来新版本？
Have you looked at Sql Server Compact 3.5 SP1? The install is just a question of copying the database files and referencing the assemblies in your project.
http://www.microsoft.com/sqlserver/2008/en/us/compact.aspx
What do you mean exactly with an upgrade path? Future newer versions of the same application and database?
我见过的支持增量升级的方法都是维护一组“更改脚本”。对于初始安装，您可以将已知的“干净”备份恢复到新数据库，也可以使用 SQL 脚本或 ORM 工具将架构投影到 DMBS 中。 NHibernate 能够在给定映射配置的情况下生成模式，并且您可以从安装程序内部设置此行为。
安装数据库后，您必须增量更新。这通常意味着为数据库的每次更改创建一个脚本，并将它们包含在安装包中，并使用一个“运行程序”（一个简单的批处理过程）来旋转脚本并针对数据库执行它们。如果它们依赖于顺序，请确保您的文件结构考虑到这一点（但通常最好在必须先执行一个脚本时合并两个脚本）。还要确保更改不是“破坏性的”；例如，向表中添加一列不能涉及删除和重新创建表，即使代码会更少。更改还必须是可重复的或不可重复的，因为您可能必须两次包含相同的脚本。这可能意味着跟踪您已执行的脚本的名称，和/或让每个脚本在执行之前检测到它所做的更改尚未进行。
请记住，如果您使用视图、存储过程或 SQL 函数，则可以随意删除和重新创建它们，而不会影响数据。
最后，请确保在执行更新之前关闭或删除与正在更新的数据库的所有连接，以避免出现不可预测的行为。
The methods I've seen that support incremental upgrades are all something along the lines of maintaining a set of "change scripts". For an initial install, you can restore a known "clean" backup to a new DB, or you can use a SQL script or an ORM tool to project a schema into the DMBS. NHibernate has the ability to generate a schema given a mapping configuration, and you can set this behavior up from inside an installer.
Once the DB is installed, you have to update incrementally. That generally means creating a script for each change to the DB, and including them in the install package, with a "runner" (a simple batch process) that will spin through the scripts and execute them against the DB. If they're order-dependent, make sure your file structure takes that into account (but it's usually best to combine two scripts when one must be executed first). Also make sure that the change is not "destructive"; for instance, adding a column to a table cannot involve dropping and recreating the table, even if it would be less code. The change must also be either repeatable or non-repeating, as you're likely going to have to include the same script twice. This can mean keeping track of names of scripts you've executed, and/or by having each script detect that a change it makes has not already been made before executing it.
Keep in mind that if you use views, stored procs or SQL functions, these can be dropped and recreated at will without affecting data.
Lastly, make sure that all connections to a DB being updated are closed or dropped before executing updates to avoid unpredictable behavior.
对于初始安装，请恢复数据库。始终随应用程序提供当前版本的数据库。
对于更新要求，您需要一份更改脚本列表，每个新版本一个。我喜欢 RoR 方法，其中数据库包含版本。
这样可以更轻松地检查数据库所在的版本，并应用其后的所有更改脚本。
For the initial install do a restore of the db. Always ship the current version of the db with the app.
For the update requirement you need to a list of change scripts, one for each new version. I like the RoR approach where the db includes the version.
This makes it easier to just check which version the db is in, and applied all the change scripts after it.