65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 SQL -> XML 对 SQL 数据库进行版本控制

HightechRider
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.87/5 (10投票s)

2008年6月26日

CPOL

4分钟阅读

viewsIcon

104142

downloadIcon

252

演示 SQL Server 管理对象、LINQ to XML 等。

引言

这个小型的控制台应用程序将 SQL Server 数据库的信息导出到 XML 文件。当您有一个版本控制系统（如 SVN 或 GIT）来管理所有源代码，但又缺少跟踪 SQL Server 中表更改的方法时，这将非常有用。

每当您进行数据库更改时，只需运行此代码，然后签入它生成的 XML 文件，或者将其作为 MS Build 任务的一部分，使其在持续集成服务器上每次构建时自动执行。

此实用程序还可以帮助确保您的开发环境、暂存环境和生产环境都具有相同的 SQL 表。只需为每个环境运行一次，然后比较文件以查看缺少的内容。由于 XML 文件包含了您所需的所有 T-SQL CREATE 脚本，因此可以轻松地将其复制并粘贴到 SQL Management Studio Express 中，以添加其他环境中缺少的任何内容。

该示例也旨在演示 SQL Server 管理对象和 LINQ to XML。

背景

有许多（昂贵的）工具可以管理您的 SQL 数据库并在环境之间同步它们，但如果您需要一个用于小型项目的简单工具，像这样的工具可能正是您所需要的，而且由于它完全是普通的 C# 代码，您可以轻松地将其构建到您的构建或部署项目中。有了它，您将能够记录数据库结构在每个阶段的变化以及何时对字段、索引和外键约束进行了更改。您的 SVN 日志将显示**表更改和代码更改**，从而更容易追究是谁搞砸了查看何时发生了什么更改。

该代码**不**会尝试将数据库的每个方面复制到 XML 文件中；如果您愿意，可以通过扩展它来做到这一点，但目前，我只需要一个快速的工具来同步我不同环境之间的数据库字段、索引和约束，而 SQL MO 是实现这一目标的简单方法。

使用代码

编译代码并通过命令行运行可执行文件，传递三个参数

DatabaseExport <databasename> "<connection string>" [<output file>]

例如，如果 *Exceptions* 是本地 SQL 实例上数据库的名称，您可以使用以下命令将其导出到 XML：

DatabaseExport Exceptions ".;Integrated Security=True;Pooling=False;"

结果可能看起来像这样……

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<!--Dump of database for .\SQLEXPRESS, database exceptions-->
<database version="1.0" name="exceptions">
  <table name="ExceptionRecord">
    <body name="ExceptionRecord"><![CDATA[SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ExceptionRecord](
    [ExceptionUID] [bigint] IDENTITY(1,1) NOT NULL,
    [Application] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DateOccurred] [datetime] NOT NULL,
    [ExceptionText] [nvarchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [StackTrace] [nvarchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Module] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Severity] [int] NOT NULL
) ON [PRIMARY]

]]></body>
    <index name="PK_Exception"><![CDATA[ALTER TABLE [dbo].[ExceptionRecord] 
                                  ADD CONSTRAINT [PK_Exception] PRIMARY KEY CLUSTERED 
(
    [ExceptionUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
       GNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
       ON [PRIMARY]
]]></index>
  </table>
  <table name="ExceptionRecordData">
    <body name="ExceptionRecordData"><![CDATA[SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ExceptionRecordData](
    [ExceptionDataUID] [bigint] IDENTITY(1,1) NOT NULL,
    [ExceptionUID] [bigint] NOT NULL,
    [Name] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Value] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

]]></body>
    <index name="PK_ExceptionData"><![CDATA[ALTER TABLE [dbo].[ExceptionRecordData] 
                              ADD  CONSTRAINT [PK_ExceptionData] PRIMARY KEY CLUSTERED 
(
    [ExceptionDataUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
      ON [PRIMARY]
]]></index>
  </table>
</database>

这实际上是我的 *Exceptions* 数据库，我通过它记录了我所有解决方案抛出的每个异常，以后也许会更多地介绍……

正如您所见，每个表都成为 XML 文件中的一个元素，并且创建该表的脚本位于 CDATA 部分。每个索引和外键约束也都有一个部分，其中包含创建它的脚本。

实现这一切的代码非常简单。SQL Server 管理对象为我们提供了一种发现数据库下的所有表、索引和其他对象的方法。例如，在这里，我们请求一个特定名称的数据库，然后请求它下面的表集合

Database db1 = server1.Databases[databaseName];
TableCollection tc1 = db1.Tables;

一旦我们有了数据库对象集合，我们就可以利用 LINQ 的强大功能来查询它们并从它们构建 XML 元素。这个简单的方法接受一个 SQL MO 集合和一个泛型参数 `T`，指定我们期望在该集合中找到的类型。一旦我们将集合的元素转换为 `T` 类型，我们就可以在它们之上构建一个查询，然后很容易从该查询生成我们想要的 `IEnumerable<XElement>` 序列。

注意我们如何对类型 `T` 设置泛型约束，指出它必须是 `NamedSmoObject`，并且必须支持 `IScriptable` 接口，该接口用于生成该对象的 T-SQL 脚本。

/// <summary>
/// Convert a collection of SQL Management Objects into XElements
/// </summary>
private static IEnumerable<XElement> XElementsFromCollection<T>(SmoCollectionBase collection)
    where T : NamedSmoObject, IScriptable
{
    var query =
       from namedObject in collection.Cast<T>()
       orderby namedObject.Name
       select new XElement(typeof(T).Name.ToLower(), 
                     new XAttribute("name", namedObject.Name), EmitScript(namedObject));

    return query;
}

有了这个方法，下面这一行代码就能完成 99% 的工作。它构建一个 XML 'table' 元素，其 `name` 属性等于 SQL 表的名称，然后在其中包含一个 `body` 元素，该元素包含一个用于创建该表的脚本的 CDATA 部分，然后是一系列索引和外键，每个都有其创建脚本的 CDATA 部分。

databaseElement.Add(new XElement("table",
    new XAttribute("name", table.Name),
    new XElement("body", new XAttribute("name", table.Name), EmitScript(table)),
    XElementsFromCollection<Index>(table.Indexes),
    XElementsFromCollection<ForeignKey>(table.ForeignKeys)

剩下的就是将其包装在 XML 文档中并将其写入文件。

XDocument root = new XDocument(new XDeclaration("1.0", "utf-8", "yes"),
    new XComment("Dump of database for " + server1.Name + ", database " + databaseName),
        databaseElement = new XElement("database",
        new XAttribute("version", "1.0"),
    new XAttribute("name", databaseName)
    ));

关注点

使用 LINQ to XML，原本需要 10 行代码的复杂 `foreach` 语句现在只需一行代码即可完成。例如，生成一个包含所有数据库名称并用逗号分隔的字符串可以通过以下方式实现：

string databases = "[" + string.Join(",", 
               server1.Databases.Cast<Database>().Select(d => d.Name).ToArray()) + "]";

有后续文章吗？

如果有人感兴趣，我可能会写一篇后续文章，展示如何使用一个简单的 GUI 来比较此 XML 文件与另一个数据库实例，在该 GUI 中，您可以单击以添加任何缺少的表、索引或外键约束。我现在正在处理这个“分支”，完成后，一旦我不再需要担心保持所有数据库表在两个开发机器和一个持续集成服务器之间同步，我就可以回到编写一些“有用”的代码了。

历史

First version.

© . All rights reserved.