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

用于脚本化 SQL Server 数据库对象的库， 并附有示例

Kanasz Robert
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.93/5 (138投票s)

2011 年 10 月 9 日

CPOL

13分钟阅读

viewsIcon

251584

downloadIcon

13406

本文介绍了一个用于脚本化 SQL Server 数据库对象的库，并提供了如何使用该库的示例。

引言

几个月前，我写了一篇关于比较数据库的文章。在那篇文章中，我描述了使用 SMO 的方法。使用 SMO 生成脚本非常容易，但存在一些性能问题。在我的案例中，比较两个大约有 2500 个对象的数据库需要将近 4 分钟。发表那篇文章后，我决定创建自己的脚本库，经过 3 个月，我几乎完成了一个版本。在本文中，我将描述该库的原理，并通过两个示例向您展示如何使用它。第一个示例是关于比较数据库架构，第二个是关于文档生成工具。 

背景

该库使用动态管理视图 (DMV) 来获取数据库对象的信息。这种方法非常快。通过这些信息，对象集合会被填充。当您想使用该库时，第一步是创建一个接受连接字符串作为参数的 ObjectDb 对象。接下来，您必须创建一个 ScriptingOptions 对象，该对象指定您要脚本化的对象类型，最后一步是调用 FetchObjects 方法，该方法接受 ScriptingOptions 参数。当对象获取完成后，您可以通过访问 ObjectDb 对象的集合来访问已脚本化的对象。以下列表表示支持的数据库对象：

  • 表 
  • 索引
  • Ddl 触发器
  • Dml 触发器
  • CLR 触发器
  • 存储过程
  • 视图
  • 应用程序角色
  • 数据库角色
  • 用户
  • Assemblies
  • 聚合
  • 默认值
  • 同义词
  • XML 架构集合
  • 消息类型
  • 合同
  • 分区函数
  • 服务队列
  • 全文目录
  • 全文停止列表
  • 全文索引
  • 服务
  • 代理优先级
  • 分区方案
  • 远程服务绑定
  • 规则
  • 路由
  • 模式
  • SQL 用户定义函数
  • CLR 用户定义函数
  • 用户定义数据类型 
  • 用户定义类型
  • 用户定义表类型

ObjectHelper 项目包含位于项目文件夹中的主要类、位于 DBObjectType 文件夹中的数据库对象类以及位于 SQL 文件夹中对脚本生成很重要的 SQL 语句。

注意： 此库只能用于兼容级别为 90 (MS SQL Server 2005) 或 100 (MS SQL Server 2008) 的数据库。  

使用代码

如我在上一章提到的，ObjectHelper 项目包含几个主要类。其中第一个是 BaseDbObject 类，它是所有数据库对象类的基类。

    public class BaseDbObject
    {
        public string Name{get;set;}
        public long ObjectId{get;set;}
        public string Description { get; set; }
        public DateTime CreateDate { get; set; }
        public DateTime ModifyDate { get; set; }
    }

下一个主要类是 FetchEventArgs 类，它在对象被脚本化时用作事件类。

    public class FetchEventArgs : EventArgs
    {
        public BaseDbObject DbObject;

        public FetchEventArgs(BaseDbObject obj)
        {
            DbObject = obj;
        }
    }

最重要的是 ObjectDB 类。该类获取有关数据库对象的所有信息，并根据 ScriptingOptions 类创建脚本。该类有一个接受连接字符串参数的构造函数。此连接字符串用于连接到数据库。它还有一个名为 ObjectFetched 的事件，该事件在对象被获取时（数据被收集且数据库对象类使用此数据被填充）触发。

    public delegate void ObjectFetchedEventHandler(object sender, FetchEventArgs e);

    public class ObjectDb
    {
        readonly string _connString;
        private SqlDatabase _sqlDatabase;
        Hashtable _hsResultSets = new Hashtable();

        public ObjectDb(string connString)
        {
            _connString = connString;
            _tables = new List();
        }

        public event ObjectFetchedEventHandler ObjectFetched;

        protected virtual void OnObjectFetched(FetchEventArgs e)
        {
            if (ObjectFetched != null)
            {
                ObjectFetched(this,e);
            }
        }

        ...

    }
此类包含数据库对象的泛型列表。
public List FullTextIndexes {get { return _fullTextIndexes;}}
public List Dependencies {get{return _dependencies;}}
public List Assemblies {get{return _assemblies;}}

...

public List UserDefinedTypes {get{return _userDefinedTypes;}}
public List UserDefinedTableTypes {get{return _userDefinedTableTypes;}}
此类中的 FetchObjects 方法用于获取对象。它接受 ScriptingOptions 参数。根据 ScriptingOptions，使用 ScriptGenerator 类生成用于检索数据库对象信息的 SQL 语句。这是一个演示如何准备表对象脚本的示例：
if (so.Tables)
{
    sql.Append("SELECT COUNT(*) FROM sys.tables;");
    sql.AppendLine();
    ResultSets.Add("TableCount", resultSetCount++);
    sql.Append(GetResourceScript("ObjectHelper.SQL.Tables_" + so.ServerMajorVersion + ".sql"));
    sql.AppendLine();
    ResultSets.Add("TableCollection", resultSetCount++);

    if (so.DataCompression)
    {
        if (so.ServerMajorVersion >= 10)
        {
            sql.Append(GetResourceScript("ObjectHelper.SQL.TableDataCompression_" + so.ServerMajorVersion + ".sql"));
            sql.AppendLine();
            ResultSets.Add("TableDataCompressionCollection", resultSetCount++);
        }
        else
        {
            so.DataCompression = false;
        }
    }
    if (so.DefaultConstraints)
    {
        sql.Append(GetResourceScript("ObjectHelper.SQL.DefaultConstraints.sql"));
        sql.AppendLine();
        ResultSets.Add("DefaultConstraintCollection", resultSetCount++);
    }
    if (so.CheckConstraints)
    {
        sql.Append(GetResourceScript("ObjectHelper.SQL.CheckConstraints.sql"));
        sql.AppendLine();
        ResultSets.Add("CheckConstraintCollection", resultSetCount++);
    }
    if (so.ForeignKeys)
    {
        sql.Append(GetResourceScript("ObjectHelper.SQL.ForeignKeys.sql"));
        sql.AppendLine();
        ResultSets.Add("ForeignKeyCollection", resultSetCount++);
        ResultSets.Add("ForeignKeyColumnCollection", resultSetCount++);
    }
    sql.Append(GetResourceScript("ObjectHelper.SQL.Columns_" + so.ServerMajorVersion + ".sql"));
    sql.AppendLine();
    ResultSets.Add("ColumnCollection", resultSetCount++);
}
在这种情况下，ScriptingOptions 由一个名为 so 的对象表示。如果 so 对象的 Tables 属性为 true，则将表的 SQL 语句追加到 sql StringBuilder 变量。表脚本存储在 Tables_X.sql 文件中，这些文件作为嵌入式资源存储在 SQL 文件夹中。文件名中的 X 表示 SQL Server 的版本。目前支持两个版本（90 代表 SQL Server 2005，100 代表 SQL Server 2010）。您可以看到 SQL 语句是动态生成的，因为只执行由 ScriptingOptions 指定的对象脚本。之后，执行此动态 SQL 语句，检索数据并获取对象。每次获取对象时，都会触发 ObjectFetched 事件。
if (so.Tables)
{
    DataTable dtTables = ds.Tables[int.Parse(_hsResultSets["TableCollection"].ToString())];
    foreach (DataRow drTable in dtTables.Rows)
    {
        var table = new Table();
        table.AnsiNullsStatus = bool.Parse(drTable["AnsiNullsStatus"].ToString());
        table.ChangeTrackingEnabled = bool.Parse(drTable["ChangeTrackingEnabled"].ToString());
        table.Description = drTable["Description"].ToString();
        …
        OnObjectFetched( new FetchEventArgs(table));
    }
}

示例

现在，让我们看一个基本示例。在下面的示例中，我将向您展示如何使用此库。第一步是创建一个 ObjectDb 对象并将连接字符串作为参数传递。接下来，您必须创建一个 ScriptingOptions 对象并指定您要脚本化的对象类型。您还可以指定其他选项，例如是否脚本化排序规则、标识等。请记住，您必须设置 ServerMajorVersion。这一点很重要，因为根据此版本生成对象脚本。您可以为 ObjectFetched 事件设置事件处理程序来监视当前正在获取的对象。最后一步是调用 FetchObject 方法，该方法接受 ScriptingOptions 参数。如果您想获取表的脚本，只需遍历 ObjectDb 对象的 Tables 属性并调用对象的 Script 方法。

var objDb = new ObjectDb("server='ANANAS\\ANANAS2009';Trusted_Connection=true;multipleactiveresultsets=false; Initial Catalog='AdventureWorks2008R2'");
var so = new ScriptingOptions { Tables = true, ServerMajorVersion = 10 };
objDb.ObjectFetched += ObjectFetched;
objDb.FetchObjects(so);
foreach (var table in objDb.Tables)
{
    Console.WriteLine("--------------------[" + table.Name + "]--------------------");
    Console.WriteLine(table.Script(so));
}

static void ObjectFetched(object sender, FetchEventArgs e)
{
    Console.WriteLine("Fetched: " + e.DbObject.Name);
}

数据库比较工具

main.jpg

下一个演示如何使用此库的示例是数据库比较工具。有时，当开发人员处理大型系统时，数据库对象会出现不一致。我曾多次遇到这个问题，因此我决定创建一个允许我比较数据库对象的工具。在我以前的文章中，我创建了一个基于 SMO 的工具，但这种方法非常慢。我修改了这个项目，使用了我自己的脚本库，该工具的性能得到了显著提高。DBCompare 项目由 5 个屏幕组成：Login、MDIMain、ObjectCompare、ObjectFetch 和 ScriptView。它还使用一个名为 DiffereceEngine 的外部组件，该组件用作脚本比较的基类。有关该类的更多信息可以在 此处找到。

登录屏幕

login_screen.jpg

登录屏幕用于创建数据库连接。它有两个选项卡。第一个选项卡用于输入服务器、身份验证和数据库名称等连接信息。

scriptingoptions_screen.jpg

在第二个选项卡中，您可以指定脚本选项。在这里，您可以选择要脚本化的对象类型以及其他选项，例如是否脚本化标识、排序规则等。以下是支持的选项列表：

索引
聚集索引 获取或设置一个布尔属性值，该值指定是否将定义聚集索引的语句包含在生成的脚本中。
全文索引 获取或设置一个布尔属性值，该值指定是否将全文索引包含在生成的脚本中。
非聚集索引 获取或设置一个布尔属性值，该值指定是否将非聚集索引包含在生成的脚本中。
杂项
聚合 获取或设置一个布尔属性值，该值指定是否将聚合函数包含在脚本化对象列表中。
脚本 ANSI NULLS 获取或设置一个布尔属性值，该值指定是否脚本化 ANSI NULLS。
脚本依赖项 获取或设置一个布尔属性值，该值指定是否包含依赖项。
脚本带引号的标识符 获取或设置一个布尔属性值，该值指定是否脚本化带引号的标识符。
同义词 获取或设置一个布尔属性值，该值指定是否将同义词包含在脚本化对象列表中。
XML 架构集合 获取或设置一个布尔属性值，该值指定是否将 XML 架构集合包含在脚本化对象列表中。
可编程性
Assemblies 获取或设置一个布尔属性值，该值指定是否将程序集包含在脚本化对象列表中。
CLR 用户定义函数 获取或设置一个布尔属性值，该值指定是否将 CLR 用户定义函数包含在脚本化对象列表中。
默认值 获取或设置一个布尔属性值，该值指定是否将默认值包含在脚本化对象列表中。
规则 获取或设置一个布尔属性值，该值指定是否将规则包含在脚本化对象列表中。
SQL 用户定义函数 获取或设置一个布尔属性值，该值指定是否将 SQL 用户定义函数包含在脚本化对象列表中。
存储过程 获取或设置一个布尔属性值，该值指定是否将存储过程包含在脚本化对象列表中。
视图 获取或设置一个布尔属性值，该值指定是否将视图包含在脚本化对象列表中。
安全
应用程序角色 获取或设置一个布尔属性值，该值指定是否将应用程序角色包含在脚本化对象列表中。
模式 获取或设置一个布尔属性值，该值指定是否将架构包含在脚本化对象列表中。
用户 获取或设置一个布尔属性值，该值指定是否将用户包含在脚本化对象列表中。
Service Broker
代理优先级 获取或设置一个布尔属性值，该值指定是否将代理优先级包含在脚本化对象列表中。
消息类型 获取或设置一个布尔属性值，该值指定是否将消息类型包含在脚本化对象列表中。
远程服务绑定 获取或设置一个布尔属性值，该值指定是否将远程服务绑定包含在脚本化对象列表中。
路由 获取或设置一个布尔属性值，该值指定是否将路由包含在脚本化对象列表中。
服务队列 获取或设置一个布尔属性值，该值指定是否将服务队列包含在脚本化对象列表中。
服务 获取或设置一个布尔属性值，该值指定是否将服务包含在脚本化对象列表中。
合同 获取或设置一个布尔属性值，该值指定是否将契约包含在脚本化对象列表中。
存储
数据库角色 获取或设置一个布尔属性值，该值指定是否将数据库角色包含在脚本化对象列表中。
全文目录路径 获取或设置一个布尔属性值，该值指定是否将全文目录路径包含在脚本化对象列表中。
全文目录 获取或设置一个布尔属性值，该值指定是否将全文目录包含在脚本化对象列表中。
全文停止列表 获取或设置一个布尔属性值，该值指定是否将全文停止列表包含在脚本化对象列表中。
分区函数 获取或设置一个布尔属性值，该值指定是否将分区函数包含在脚本化对象列表中。
分区方案 获取或设置一个布尔属性值，该值指定是否将分区方案包含在脚本化对象列表中。
表格
检查约束 获取或设置一个布尔属性值，该值指定是否将检查约束包含在表约束列表中。
排序 获取或设置一个布尔属性值，该值指定是否将 Collation 子句包含在生成的脚本中。
数据压缩 获取或设置一个布尔属性值，该值指定是否将 DATA_COMRESSION 子句包含在生成的脚本中。
默认约束 获取或设置一个布尔属性值，该值指定是否将默认约束包含在表约束列表中。
外键 获取或设置一个布尔属性值，该值指定是否将通过强制声明性引用完整性在外键中定义的依赖关系包含在脚本中。
无文件流 获取或设置一个对象，该对象指定在生成脚本时创建 VarBinaryMax 列时是否包含 FILESTREAM_ON 子句。
无标识 获取或设置一个布尔属性值，该值指定是否将标识属性种子和增量的定义包含在生成的脚本中。
主键 获取或设置一个布尔属性值，该值指定是否将主键约束包含在表约束列表中。
脚本表 获取或设置一个布尔属性值，该值指定是否将表包含在脚本化对象列表中。
唯一约束 获取或设置一个布尔属性值，该值指定是否将唯一约束包含在表约束列表中。
触发器
CLR 触发器 获取或设置一个布尔属性值，该值指定是否将 CLR 触发器包含在脚本化对象列表中。
DDL 触发器 获取或设置一个布尔属性值，该值指定是否将 DDL 触发器包含在脚本化对象列表中。
DML 触发器 获取或设置一个布尔属性值，该值指定是否将 DML 触发器包含在脚本化对象列表中。
Types
用户定义数据类型 获取或设置一个布尔属性值，该值指定是否将用户定义数据类型包含在脚本化对象列表中。
用户定义表类型 获取或设置一个布尔属性值，该值指定是否将用户定义表类型包含在脚本化对象列表中。
用户定义类型 获取或设置一个布尔属性值，该值指定是否将用户定义类型包含在脚本化对象列表中。

ObjectFetch 屏幕

objectfetch_screen.jpg

此屏幕是应用程序的第二重要部分。在这里，所有对象都被获取、脚本化然后进行比较。每个被获取的对象都存储在 ScriptedObject 对象中。

public class ScriptedObject
{
    public string Name="";
    public string Schema="";
    public string Type="";
    public DateTime DateLastModified;
    public string ObjectDefinition="";
    public Urn Urn; 
}

当所有对象都被获取和脚本化后，它们会被比较并存储在 DataTable 对象中，然后传递到 ObjectCompare 屏幕。

ObjectCompare 屏幕

此屏幕是项目的主要部分。在这里，您可以比较数据库对象并查看它们之间的差异。此屏幕有三个部分。在第一部分（左侧面板）中，您可以选择要比较的数据库对象类型。在第二部分（数据库对象列表）中，对象分为 4 部分：

  • 仅在 DB1 中存在的对象
  • 仅在 DB2 中存在的对象
  • 同时存在于两个数据库中且不同的对象
  • 同时存在于两个数据库中且相同的对象
object_list.jpg

在第三部分中，您可以查看数据库对象之间的差异。当您单击对象列表中的某个项目时，将比较并显示所选对象的脚本。

script_compare.jpg

所有对象都存储在 DataTable dbObjects 中，该表有六列：

  • ResultSet - 指定对象所属的组（仅在 DB1 中存在的对象 [1]，仅在 DB2 中存在的对象 [2]，...）。
  • Name - 数据库对象的名称。
  • Type - 数据库对象的类型。
  • Schema - 数据库对象所属的架构（并非所有对象都属于数据库架构）。
  • ObjectDefinition1 - 如果两个数据库具有相同名称和类型的对象但定义不同，则 ObjectDefinition1 存储源数据库对象的定义。
  • ObjectDefinition2 - 如果两个数据库具有相同名称和类型的对象但定义不同，则 ObjectDefinition2 存储目标数据库对象的定义。如果对象仅存在于其中一个数据库中，则此属性为空，对象的定义存储在 ObjectDefinition1 中。

数据库文档工具

dbdoc_main.jpg

这里是使用我的脚本库的另一个示例。这个小工具允许您创建数据库的 HTML 基本文档。目前，该工具为以下对象生成文档，但将来我会添加其余的对象。

  • 默认值
  • 消息类型
  • 合同
  • 服务队列
  • 服务
  • 代理优先级
  • 索引
  • 聚合
  • Assemblies
  • SQL 用户定义函数
  • CLR 用户定义函数
  • 存储过程
  • 视图
  • 用户定义类型
  • 用户定义表类型
  • 用户定义数据类型
  • 触发器
  • 表格
  • 全文索引
  • 全文目录
  • 全文停止列表

这个名为 DBDocumentation 的项目包含一个名为 Main 的屏幕。该屏幕有两个选项卡。在第一个选项卡中，您可以设置连接信息，例如服务器、凭据和数据库。

dbdoc_scriptingoptions.jpg

在第二个选项卡中，您可以选择要记录的对象类型，还可以设置要在文档中包含的信息类型。在生成文档之前，您必须指定将保存 HTML 文档的输出目录。该工具可以检索每个对象的依赖项列表并创建指向它们的交叉引用。

此处有一个工作示例。

documentaion.jpg

未来发展

将来，我将提高用于脚本化数据库对象的信息的收集性能。接下来，我将把剩余的对象添加到数据库文档工具中，并将此脚本库适配到新版本的 SQL Server。

历史 

  • 2011 年 10 月 9 日 - 发布原始版本。 
  • 2011 年 11 月 1 日 - 修改文章  

© . All rights reserved.