使用 DacFx 公共模型和 System.Reflection 参数化 SSDT 数据库项目 .dacpac 文件中的架构名称
5.00/5 (3投票s)
如何在部署前以编程方式更改数据库项目中的模式名称
引言
Visual Studio 的数据库项目（也称为 SQL Server Data Tools，SSDT）是一个极其有用的数据库开发工具。它允许在项目构建期间进行代码验证、语法和引用完整性检查、数据库版本控制等等。
但是，当然，它也有其局限性。例如，在对象名称中使用变量的用处非常有限。如果您使用项目引用，则可以在 4 部分对象名称中的服务器名称和数据库名称中使用变量。
CREATE VIEW Sales.[FactEvent] AS
SELECT *
FROM
[$(SourceServer)].[$(SalesDatabase)].[dbo].[FactEvent]
但是您不能在架构名称、表名称或视图名称等中使用变量。这总是会产生错误 71502“未解析的对象引用”。
幸运的是，有一种方法可以克服这个限制。在接下来的文章中，我将解释如何编辑 SSDT 创建的数据库包（*.dacpac 文件），如何在其中重命名架构，并将更新后的包部署到 SQL Server。
我们将使用 DacFx 公共模型解析数据库包，并将其内部的数据库模型视为对象层次结构；我们将使用反射来导航和修改此层次结构。
所有代码均用 C# 编写。
背景：DacFx 公共模型
DacFx 公共模型是 Data-tier Application Framework 的 API。Data-tier Application Framework 只是 SSDT 内部使用的对象模型的另一个名称。在此模型中，每个表、视图、存储过程、SELECT 语句等都表示为复杂层次结构中的一个对象。例如，DacFx 公共模型中简单的 SELECT 语句“select * from OldSchema.t1;”在 Visual Studio 调试器中看起来是这样的：
每个 SQL 语句都会生成不同的对象层次结构。我们需要一种通用的方法来递归地遍历它，查找具有特定架构名称的对象，并对其进行更新。如果层次结构中有多个此类对象（例如，多个表的 JOIN 或 UNION），我们需要找到所有这些对象。我们正在寻找的对象可能嵌套在彼此内部：由表创建的视图、执行另一个存储过程的存储过程等。我们需要一种可靠的方法来解决所有这些情况。为此，我们将使用 System.Reflection。
背景：System.Reflection
反射是一个 API，它允许程序操作自身的元数据。例如，反射允许我们分析类实例，获取其所有 public 属性和方法的列表，更改特定名称属性的值，调用方法等。
private void DoSomething(dynamic obj)
{
foreach (var property in obj.GetType().
GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
// Recursively analyse object model to find specific objects
}
}
使用反射导航 DacFx 模型
我们将使用以下算法来导航 DacFx 模型：
- 如果当前对象具有是 DacFx 对象的
public属性，则递归地为每个属性运行此算法。 - 如果当前对象是集合，则对集合中的每个项运行此算法。
- 如果当前对象具有架构，并且其名称等于
oldSchema，则将其更新为newSchema。 - 如果当前对象是硬编码文本（例如“
IF OBJECT_ID (N'OldSchema.ufn_SalesByStore', N'IF') IS NOT NULL”）- 将文本分割为以点分隔的单词。
- 对于每个单词，如果它等于
oldSchema，则将其更新为newSchema。
var propertyValue = index == -1 ?
property.GetValue(obj) : property.GetValue(obj, new object[] { index });
//If we have a collection, then iterate through its elements.
bool isCollection = false;
if (property.PropertyType.GetInterface("System.Collections.IEnumerable") != null)
isCollection = true;
string currentPropertyName = property.Name;
string currentPropertyType = property.PropertyType.Name;
string currentPropertyTypeNamespace = property.PropertyType.Namespace;
if (currentPropertyTypeNamespace == propertyTypeNamespace ||
currentPropertyType == "String" || isCollection)
{
if (isCollection && !(propertyValue is string) && propertyValue != null)
foreach (var item in propertyValue)
Set(item, oldValue, newValue, propertyTypeNamespace,
propertyHierarchy + "." + currentPropertyName);
//If field name and type matches, then set.
else
if (currentPropertyName == "Value" &&
currentPropertyType == "String" && propertyValue == oldValue
&& (propertyHierarchy.EndsWith("MultiPartIdentifier.Item")
|| propertyHierarchy.EndsWith("SchemaIdentifier")
|| propertyHierarchy.EndsWith("CreateSchemaStatement.Name")))
property.SetValue(obj, newValue);
else
if (currentPropertyName == "Value" && currentPropertyType == "String"
&& propertyHierarchy.EndsWith("Predicate.Expression.Parameters"))
ProcessStringLiteral(propertyValue, property, obj, oldValue, newValue);
else
if (currentPropertyTypeNamespace == propertyTypeNamespace)
Set(propertyValue, oldValue, newValue, propertyTypeNamespace,
propertyHierarchy + "." + currentPropertyName);
}
为什么 Visual Studio Deployment Contributor 不起作用
我最初的想法是创建一个 Visual Studio Deployment Contributor – 一个自定义的 VS 插件，用于补充部署过程。当 SSDT 生成部署脚本时，Deployment Contributor 可以在脚本执行于服务器之前对其进行修改。可以使用相同的方法（DacFx + Reflection）来解析和修改脚本，更改架构名称。
但是这种方法存在一个关键问题。部署脚本基于源 SSDT 数据库模型和目标数据库模型之间的比较。此比较发生在部署之前，因此会使用未更改的架构名称进行比较。如果目标模型和源模型已具有表 OldSchema.t1 的相同定义，那么此表将根本不会包含在部署脚本中，因此也不会创建表 NewSchema.t1。另一方面，如果目标中不存在表 OldSchema.t1，但存在表 NewSchema.t1，则部署尝试将因错误“指定的对象已存在”而失败。
因此，我们需要在部署和模型比较之前修改源模型。我尝试使用 Visual Studio Build Contributor 来做到这一点。与 Deployment Contributor 类似，它补充了项目构建过程。不幸的是，Build Contributor 对 SSDT 数据库模型具有只读访问权限。它无法修改模型 – 只能收集有关模型的信息，例如用于自定义日志记录。
因此，在部署之前修改数据库模型的唯一方法是编辑数据库包文件（*.dacpac），并在 Visual Studio 部署周期之外完全进行。然后可以使用 SqlPackage.exe 命令行实用程序将修改后的包部署到 SQL Server。
解析数据库包文件
从 .dacpac 文件加载模型非常容易：
TSqlModel modelFromDacpac = TSqlModel.LoadFromDacpac(fileName,
new ModelLoadOptions(DacSchemaModelStorageType.Memory, loadAsScriptBackedModel: true));
在此处指定选项 loadAsScriptBackedModel: true 很重要。指定此选项后，模型中的每个对象都会加载一个自动生成的 SQL 脚本。方法 TsqlModel.DeleteObjects 以 SQL 脚本名称作为参数；因此，没有脚本的对象无法从模型中删除。
模型加载后，我们可以获取其中用户定义对象的列表。在将每个对象分析为类层次结构之前，我们需要将其从 TSqlObject 转换为 TSqlStatement。没有原生优雅的方法可以做到这一点，所以我们必须使用一个技巧：从 TsqlObject 中提取 SQL 脚本，并在该脚本之上构建 TsqlStatement。为了更新模型，我们将进行反向转换。
可能会有诱惑，只需在此步骤中使用 string.Replace() 等函数更新 SQL 脚本，而不使用 DacFx 类层次结构。但是，如果这样做，我们可能会面临很多意外的错误：某些对象名称可能包含架构名称作为子字符串；某些对象名称可能与架构名称重叠等等。如果我们使用对象模型，我们将能够正确处理所有这些情况。
List<TSqlObject> sourceModelObjects = new List<TSqlObject>();
foreach (TSqlObject tso in model.GetObjects(DacQueryScopes.UserDefined))
sourceModelObjects.Add(tso);
for (int i = 0; i < sourceModelObjects.Count; i++)
{
Console.WriteLine(DateTime.Now.ToString("T") +
" Processing object " + (i + 1).ToString() + " of " +
sourceModelObjects.Count.ToString() + ": " +
sourceModelObjects[i].ObjectType.Name + " " + sourceModelObjects[i].Name.ToString());
SourceInformation sourceInfo = sourceModelObjects[i].GetSourceInformation();
string oldObjectScript = string.Empty;
sourceModelObjects[i].TryGetScript(out oldObjectScript);
string newObjectScript = string.Empty;
try
{
TSql120Parser parser = new TSql120Parser(false);
IList<ParseError> errors;
TSqlScript fragment = (TSqlScript)parser.Parse(new StringReader(oldObjectScript), out errors);
TSqlStatement stmt = fragment.Batches[0].Statements[0];
ReflectionUtils.SubstituteSchemaName(stmt, oldSchema,
newSchema, "Microsoft.SqlServer.TransactSql.ScriptDom");
Sql120ScriptGenerator sg = new Sql120ScriptGenerator();
sg.GenerateScript(fragment, out newObjectScript);
}
catch (Exception ex) { }
if (oldObjectScript != newObjectScript && newObjectScript != string.Empty)
{
model.DeleteObjects(sourceInfo.SourceName);
model.AddObjects(newObjectScript);
}
}
将更新后的模型保存到文件几乎和加载它一样简单。
using (DacPackage dacPackage = DacPackage.Load(fileName,
DacSchemaModelStorageType.Memory,
FileAccess.ReadWrite))
{
DacPackageExtensions.UpdateModel(dacPackage, modelFromDacpac, null);
}
使用 SqlPackage.exe 实用程序部署修改后的 .dacpac 文件
SqlPackage.exe 命令行实用程序是 SQL Server 安装的一部分。它可以在 %Program Files (x86)\Microsoft SQL Server\120\DAC\bin\ 中找到（取决于 SQL Server 的版本）。为了实现部署自动化，我使用了 PowerShell 脚本来修改 .dacpac，然后进行部署。
$SqlPackageLocation = [string] ($Settings.DeploymentSettings.Setting | ?
{ $_.name -eq "SqlPackageLocation" }).value
$DacpacFile = [string] ($Settings.DeploymentSettings.Setting | ?
{ $_.name -eq " DacpacFile" }).value
$Profile = [string] ($Settings.DeploymentSettings.Setting | ?
{ $_.name -eq "Profile" }).value
$SchemaSubstitute = [System.Convert]::ToBoolean
(($Settings.DeploymentSettings.Setting | ?
{ $_.name -eq "SchemaSubstitute" }).value)
$SchemaSubstituteCmdLocation = [string] ($Settings.DeploymentSettings.Setting | ?
{ $_.name -eq "SchemaSubstituteCmdLocation" }).value
$SchemaSubstituteParams = """$DacpacFile"""
foreach ($nodeXML1 in ($Settings.DeploymentSettings.Setting | ?
{ $_.name -eq "SchemaSubstitutePairs" }).SchemaSubstitutePair)
{
$SchemaSubstituteParams = $SchemaSubstituteParams + " " +
$nodeXML1.OldSchema + " " + $nodeXML1.NewSchema
}
if ($SchemaSubstitute) {
write-host "Substituting schema with parameters " $SchemaSubstituteParams
& $SchemaSubstituteCmdLocation $SchemaSubstituteParams
}
echo "Deploy"
& $SqlPackageLocation /Action:Publish /SourceFile:$DacpacFile /Profile:$Profile
此脚本使用的配置文件如下所示：
<DeploymentSettings>
<Setting name="SqlPackageLocation"
value="C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe"/>
<Setting name="DacpacFile" value="Sample.Database.dacpac"/>
<Setting name="Profile" value="Sample.Database.publish.xml"/>
<Setting name="SchemaSubstitute" value="True" />
<Setting name="SchemaSubstituteCmdLocation" value="SchemaSubstituteCmd.exe" />
<Setting name="SchemaSubstitutePairs">
<SchemaSubstitutePair OldSchema="dbo" NewSchema="TestSchema" />
</Setting>
</DeploymentSettings>
结论
此应用程序展示了如何更新 SSDT 创建的数据库包的模型。除了参数化架构名称之外，这项技术还有许多其他潜在的用例：更改表名和列名，动态添加新对象（如索引或默认约束）等。
致谢
- 为多租户解决方案中的 SSDT 数据库项目参数化架构名称 - 本文及其源代码被用作本文的起点。
- 本文 是一个很好的 DacFx 公共模型教程。
- C# 中的反射教程