DacFxを使って（C#で書いたコードを動かして）SQL Serverの差分更新スクリプトを取得する

Visual StudioのアドオンSQL Server Data Tools（以下SSDT）の機能に「スキーマ比較」ツールがあります。これは大雑把に以下のような機能を持っています。

任意の "SQL Server データベースプロジェクト（以下DBプロジェクト）" / "（オンライン状態の）データベース" / "dacpacファイル" 間でスキーマの比較を行い差異を抽出する
抽出した差異を解消するための更新スクリプトを生成する

これらと同等の操作はコマンドラインツールのSqlPackage.exeでも可能であり、これらの基盤となっているDacFxを利用すれば.NETのコードで上記の操作を記述・実行することが可能です。

（多分こんなことやる／やりたい人はそうそうそういないと思うのですが自分はまた今後もやりそうな気がするので、完全に自分用のメモです）

差分の抽出

利用するのは DacFxに含まれる Microsoft.SqlServer.Dac.Compare.SchemaComparison クラスです。

docs.microsoft.com

このクラスは、dacpacを指す SchemaCompareDacpacEndpoint またはオンラインのデータベースを指す SchemaCompareDatabaseEndpoint をソースおよびターゲットとして比較する機能を持っています。つまり、比較対象はdacpacファイルとオンライン状態のデータベースのいずれかの組み合わせとなります。もちろんdacpac同士やデータベース同士での比較だけではなくdacpacとデータベース間での比較もできます。

（なお、SSDTのスキーマ比較機能のようにDBプロジェクトを比較対象にしたい場合は何らかの方法でDBプロジェクトからdacpacを生成した上で比較する必要がある、という事になります。その場合は以前書いたように、Microsoft.Build.Evaluation 名前空間のあれこれを利用すればdacpacの生成処理自体も.NETのコードで記述することが可能です）

例えば以下のようなコード新旧のバージョンのdacpacを比較して差分を抽出するできました。

var newVersion = new SchemaCompareDacpacEndpoint(@"C:\some_database_v1.1.dacpac"));
var oldVersion = new SchemaCompareDacpacEndpoint(@"C:\some_database_v1.0.dacpac"));
var comparison = new SchemaComparison(newVersion, oldVersion);

var compareResult = comparison.Compare(); // 戻り値はSchemaComparisonResult型

結果は SchemaComparisonResult型 で戻されます。例えば IEnumerable<SchemaDifference>型の Differences プロパティには差分のオブジェクトの情報が列挙されますので、この中身を見ることで差分の種類やオブジェクトの名前などを取得することができます（ただし若干直感と反するようなプロパティ名かもしれない）。

foreach(var d in compareResult.Differences)
{
  // UpdateAction : 更新種別（Add or Change or Delete）
  // Name : オブジェクトの種別 (Table or Procedure or .... )
  // SourceObject.Name / TargetObject.Name : オブジェクト名
  Console.WriteLine($"更新種別:{d.UpdateAction}, オブジェクトタイプ:{d.Name}, 対象オブジェクト名:{(d.SourceObject?.Name) ?? (d.TargetObject?.Name)}");
}

更新用T-SQLスクリプトの取得

上記までのコードで比較はできましたが、この状態では差分を抽出しただけでありまだ更新用T-SQLスクリプトは生成されていません。これはGenerateScriptメソッドで生成できます。 GenerateScriptメソッドを実行すると戻り値としてSchemaCompareScriptGenerationResultが得られ、SchemaCompareScriptGenerationResultのScriptプロパティにT-SQLスクリプトが格納されています。

雑にファイルに保存するならこんな感じでしょうか。

var generateResult = compareResult.GenerateScript("Hoge"); // 引数database name は任意の文字列。更新スクリプト中の USE [Hoge] などで使われる。
using (var sw = new System.IO.StreamWriter(@"C:\Update-Schema.sql", false, Encoding.UTF8))
{
  sw.Write(generateResult.Script);
  sw.Flush();
}

そのほか、Compareの第二引数側（古いバージョンのスキーマ側）がSchemaCompareDatabaseEndpointすなわちオンラインのデータベースの場合、GenerateScriptメソッドの代わりにPublishChangesToTargetメソッドを実行することで直接データベースを更新することができます。その際に実行されたT-SQLスクリプトはSchemaComparePublishResult型で返却されるPublishChangesToTargetの戻り値のScriptプロパティに格納されています。

まとめ

以上の通り、比較的簡単にSQL Serverのデータベースおよびdacpac間でスキーマ比較を行い差分更新用T-SQLスクリプトを生成できました。

……できましたが、オフィシャルのドキュメントが『ドキュメントコメントから生成されたシグネチャやメンバが書いてあるだけの極シンプルなAPIリファレンス』以上でも以下でもなく、利用方法などについての説明はほぼないためAPIリファレンスとインテリセンスであたりをつけつつ（基本的に同じことができるはずの）SqlPackage.exeのコマンドリファレンスを見たりおださん（@shinsukeoda）に教えてもらったサンプルコードを眺めてみたりと基本的に手さぐりだったので意外と時間がかかりました。

このほか、特定のオブジェクトを除外した更新スクリプトの生成にも無駄に時間を食ってしまったりしたのですが、またそのうち気が向いたら書いておこうと思います。