通过 C# 和 ADO.NET 更改列排序规则

今天我们深入探讨了在 SQL Server 中，排序规则是如何困扰我的，当时我试图让代码生成功能在一个与原始数据库不同的数据库上工作。我们还将探讨我是如何解决这个问题的（通过一些炫酷的 C# 和 WPF 代码）。

问题背景

首先，让我们先来看一下全局图景，这样我们都能理解我为什么会遇到这个问题。在我们构建的应用程序中，我们有多个数据库来存储我们的 LOB（大对象）数据。根据用户的授权，他/她可能拥有访问一个或多个这些数据库的权限（有时也可能只拥有部分数据库的访问权限）。因此，我们需要一个中央数据存储来保存关于安全、完整安装以及 LOB 数据库的位置和其中数据的信息。我们将这个数据存储称为 Repository，并规定任何需要跨越 LOB 数据库边界的数据都应该存储在 Repository 中。

到目前为止，一切都还不错。项目早期阶段，我们构建了一个基于 Entity Framework 的 WCF 服务，它允许我们以通用方式对 LOB 数据库执行任何数据操作。为了实现这一点，我们构建了一个代码生成器来生成所需的所有业务类。我们为服务使用的代码生成过程基于我们使用自动生成的 EF 模型的事实。换句话说，EF 模型直接描绘了 SQL Server 数据模型。因此，我们可以从 SQL Server 中提取元数据来为我们的代码生成过程提供数据。为此，我们的数据库管理员编写了一个脚本，从 SQL Server（使用系统视图）中提取元数据，并将其插入到一个单独的数据库中，我们用它来驱动代码生成。

到目前为止，仍然没有问题。现在我们想做的是复制我们用于访问 LOB 数据库的所有代码，并基于这些代码构建一个 WCF 服务来访问我们的 Repository，因此，我需要根据 Repository 数据库中的模型生成业务类。通常情况下，我们通过 Visual Studio 2008 中的 SQL Server 2005 项目来创建和更新数据库模型，并进行模式比较。
然而，多个用户会向此元数据添加数据，在这种情况下，我们倾向于使用备份来分发这些数据，而这正是我在准备从 Repository 添加元数据时所做的。

问题

恢复元数据数据库后，问题就开始出现了。我需要修改用于从数据库提取元数据的存储过程，以更改源数据库名称，使其能够访问 repository 数据库。当我运行 `alter procedure` 语句更新存储过程时，我遇到了几个排序规则冲突错误。原来，我从 Repository 数据库获取的备份是在一个与我常用的排序规则不同的排序规则下创建的。更具体地说，Repository 数据库是用 `Latin1_General_CI_AS`（我的本地默认值）创建的，而元数据数据库是用 `SQL_Latin1_General_CP1_CI_AS` 创建的。

为了解决这个问题，我需要逐一检查每个列并更改排序规则。因为从 Repository 访问的唯一数据在系统视图中，而您无法（也不想）更改它们的排序规则，所以我必须更改元数据数据库上的排序规则。我不想手动执行此操作，而且考虑到这种情况可能会更频繁地发生，我认为最好写一个小工具来为我处理这个问题。下面是它的屏幕截图

基本上，它允许您输入 SQL 实例名称，然后它会检索该实例的数据库列表，并检索所有可用的排序规则来填充两个 `combobox`。一旦您选择了一个数据库和至少一个源排序规则，您就可以点击“查找列”按钮来检索具有该源排序规则的所有列。在选择目标排序规则并取消选中您不想更改的列之后，您可以点击“更改排序规则”按钮，它将触发一个 `alter table` / `alter column` 查询来尝试为您完成此操作。

我想给这个工具增色的是直接在 ADO.NET 上使用了一些查询。这是一段包含这些查询的代码片段

private const string GetAllDatabasesSqlCommand = "select name from sys.databases";
private const string GetAllCollationsSqlCommand = _
	"select name from ::fn_helpcollations()";
private const string GetAllColumnNamesSqlCommand = _
	"select o.name, c.name, t.name, c.max_length, c.is_nullable"
+ " from sys.columns c"
+ " left join sys.objects o on c.object_id=o.object_id"
+ " left join sys.types t on c.system_type_id = t.system_type_id"
+ " where c.collation_name=@collation_name";

第一个查询检索所有数据库名称。这显然只在连接到 master 数据库时有效。
第二个查询返回所有排序规则名称。这可以在任何数据库上执行（它应该始终返回相同的列表）。
最后一个查询查找使用特定排序规则的所有列。结果集包括表名、列名、类型名、最大长度以及列是否可以包含 `NULL`。这些是生成用于更改排序规则的 `alter table alter column` 语句所需的所有信息。

我之前从未用过的东西是 `SqlConnectionStringBuilder` 类，用于动态创建我的连接字符串。这是我为此使用的代码

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
connectionStringBuilder.DataSource = hostnameTextBox.Text;
connectionStringBuilder.InitialCatalog = MasterDatabaseName;
connectionStringBuilder.IntegratedSecurity = true;

正如您所看到的，使用这个类非常简单。我之前从未用过的另一件事是 `SqlConnection` 类上的 `ChangeDatabase` 方法。这非常有用，因为我可以让应用程序在整个流程中保持一个连接，并快速切换数据库。

我已将代码 在此处 上传供您欣赏。请注意，这个工具是匆忙完成的，所以大部分代码都在主窗口的后端代码中，而且大部分代码写得不太好，但其中也包含了一些不错的设计理念。

我希望您再次感到满意。我知道我感到很满意。