c# - 如何以编程方式验证、创建和更新 SQL 表结构？

Question

场景:

我有一个应用程序 (C#)，它需要一个由用户设置的 SQL 数据库和登录名。连接后，它会检查是否存在多个表，如果未找到则创建它们。

如果我发布依赖于新列的程序的新版本，我想通过让程序能够向这些表添加列来对此进行扩展。

问题:

以编程方式检查现有 SQL 表的结构并创建或更新它以匹配预期结构的最佳方法是什么？

我计划遍历所需列的列表，并在现有表不包含新列时更改它。我不禁想知道是否有不同或更好的方法。

条件:

以下是我的一些期望和 self 强加的规则:

• 较新版本的程序可能不再使用某些列，但会保留它们用于数据记录目的。换句话说，不会删除任何列。
• 必须保留表中的现有数据，因此不能简单地删除并重新创建表。
• 在所有情况下，新添加的列都允许空数据，因此通过使用默认空值来处理旧记录的数量。

示例:

这是一个示例表(因为视觉示例有帮助!):

id  datetime         sensor_name  sensor_status  x1    x2    x3    x41   20100513T151907  na019        OK             0.01  0.21  1.41  1.222   20100513T152907  na019        OK             0.02  0.23  1.45  1.52

然后，在新版本中，我可能想添加列 x5。 “x-columns”都是接受 null 的数据存储列。

编辑:

我更新了上面的示例表。它更像是一个日志而不是父表。所以传感器将重复出现在这个记录表中，并记录值。一个单独的父表包含关于传感器的地理信息和其他后勤信息，使我希望修改的表成为子表。

Answer 1

这是您正在考虑实现的一个非常麻烦的功能。我建议不要这样做，而是考虑使用第三方工具(例如 Red Gate 的 Sql Compare)来更改脚本:http://www.red-gate.com/products/SQL_Compare/index.htm

如果您有疑问，请考虑下载该软件的试用版并在两个具有一些重要差异的数据库上执行结构差异脚本。您将从结果中看出，此类操作的考虑远非简单。

解决此类问题的另一种方法是使用 EAV 模型重新设计您的数据库:http://en.wikipedia.org/wiki/Entity-attribute-value_model (枢轴动态添加行，因此永远不会改变结构。它有自己的问题，但非常灵活。)

(要使用 diff 工具，您必须拥有所有数据库版本的副本，并创建 diff 脚本，这些脚本将在新版本和升级时运行并执行。这本身就是一个巨大的维护问题。EAV是这样的事情的方式。它错误地得到了很多抨击，因为它不如传统的数据库结构那样高效，但我已经多次使用它并取得了巨大的成功。事实上，我有一个符合 HIPAA 标准的 EAV db (Sql Server 2000) 已经投入生产超过六年，其中几个 EAV 表包含数千万行，并且它仍然很强大，没有太大的放缓。当然，我们不会针对该数据库进行大量报告. 对于报告，我们有一个将数据扁平化为关系结构的导出。)