C#と諸々 SQL Server 管理オブジェクト (SMO) を利用してデータベースを管理する

[ NAgile ] TDD

2009/01/05 18:15

実践!ソフトウェアアーキテクチャ VisualStudioとASP.NETによる業務システム開発方法

この本でユニットテスト時に SMO を使ってテスト用のデータベースを構築しているのを見て、僕も取り入れてみたので自分のためにメモ。
# 本に載ってる方法とは少し違う方法。

ユニットテストだけでなく、インストーラのカスタム動作でも使えるので便利。

まず、データベース管理用のクラスライブラリプロジェクトを作成。ここでは Hoge.Databases と名付けることにする。

次に、全てのテーブルを (再) 作成するためのクエリーファイルをプロジェクト内に作成。ビルドアクションは "埋め込まれたリソース" に設定。ここでは Create.sql と名付けることにする。

SET ANSI_NULLS ON
GO

-- テーブルを削除。子テーブルを先に削除すること。
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table2]') AND type in (N'U'))
    DROP TABLE [dbo].[Table2]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))
    DROP TABLE [dbo].[Table1]
GO

-- テーブルを作成。親テーブルを先に作成すること。
CREATE TABLE [dbo].[Table1]
(
    [PrimaryKey] [bigint] IDENTITY(1,1) NOT NULL,
    [Id] [nvarchar](255) NOT NULL,
    [Name] [nvarchar](255) NOT NULL,

    CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([PrimaryKey]),
    CONSTRAINT [IX_Table1_Id] UNIQUE NONCLUSTERED ([Id])
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table2]
(
    [PrimaryKey] [bigint] IDENTITY(1,1) NOT NULL,
    [Table1PrimaryKey] [bigint] NOT NULL,
    [Value] [nvarchar](255) NOT NULL,

    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ([PrimaryKey]),
    CONSTRAINT [FK_Table2_Table1] FOREIGN KEY([Table1PrimaryKey])
        REFERENCES [dbo].[Table1] ([PrimaryKey])
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ON [PRIMARY]
GO

次に、SMO を使ってデータベースを管理するクラスを作成。

using System;
using System.IO;
using System.Reflection;
using Microsoft.SqlServer.Management.Smo;

namespace Hoge.Databases
{
    /// <summary>
    /// Hoge データベースを管理します。
    /// </summary>
    public sealed class HogeDatabase
    {
        #region Static Members

        /// <summary>
        /// Hoge データベースに含めるテーブルを生成するための SQL クエリーファイルのリソースパスを取得します。
        /// </summary>
        private const string CreateScriptResourcePath = "Hoge.Databases.Create.SQL";

        /// <summary>
        /// 指定したリソースパスに含まれるリソースを文字列として読み出します。
        /// </summary>
        /// <param name="resourcePath">リソースパス。</param>
        /// <returns></returns>
        static private string ReadResourceText(string resourcePath)
        {
            Assembly thisAssembly = Assembly.GetExecutingAssembly();
            using (Stream resourceStream = thisAssembly.GetManifestResourceStream(resourcePath))
            using (StreamReader resourceReader = new StreamReader(resourceStream))
            {
                return resourceReader.ReadToEnd();
            }
        }

        #endregion

        #region Constructors

        /// <summary>
        /// HogeDatabase クラスの新しいインスタンスを初期化します。
        /// </summary>
        /// <param name="server">SQL Server のインスタンス名。</param>
        /// <param name="database">データベース名。</param>
        public HogeDatabase(string instanceName, string databaseName)
        {
            this._server = new Server(instanceName);
            this._database = new Database(this._server, databaseName);
        }

        #endregion

        #region Fields

        /// <summary>
        /// SQL Server のインスタンスの管理オブジェクトを取得します。
        /// </summary>
        private readonly Server _server;

        /// <summary>
        /// データベースの管理オブジェクトを取得します。
        /// </summary>
        private readonly Database _database;

        #endregion

        #region Properties

        /// <summary>
        /// Hoge データベースが存在するかどうかを示す値を取得します。
        /// </summary>
        /// <returns>Hoge データベースが存在するかどうかを示す値。</returns>
        public bool Exists
        {
            get
            {
                this._database.Refresh();
                return (this._database.State == SqlSmoState.Existing);
            }
        }

        #endregion

        #region Methods

        /// <summary>
        /// Hoge データベース、各テーブルを作成し、ASPNET アカウントをログイン登録します。
        /// </summary>
        /// <exception cref="InvalidOperationException">データベースは既に存在します。</exception>
        public void Create()
        {
            this.CreateDatabase();
            this.CreateTables();
            this.CreateLoginForAspnet();
        }

        /// <summary>
        /// Hoge データベースを作成します。
        /// </summary>
        /// <exception cref="InvalidOperationException">データベースは既に存在します。</exception>
        public void CreateDatabase()
        {
            if (this.Exists)
            {
                throw new InvalidOperationException("データベースは既に存在します。");
            }
            this._database.Refresh();
            this._database.Create();
        }

        /// <summary>
        /// Hoge データベースの各テーブルを作成します。
        /// テーブルが既に存在する場合は、テーブルを削除してから作成を行います。
        /// </summary>
        /// <exception cref="InvalidOperationException">データベースは存在しません。</exception>
        public void CreateTables()
        {
            if (!this.Exists)
            {
                throw new InvalidOperationException("データベースは存在しません。");
            }
            string createScript = ReadResourceText(CreateScriptResourcePath);
            this._database.ExecuteNonQuery(createScript);
        }

        /// <summary>
        /// 結果を返さない SQL コマンドを実行します。
        /// </summary>
        /// <param name="sqlCommand">SQL コマンド。</param>
        /// <exception cref="InvalidOperationException">データベースは存在しません。</exception>
        public void ExecuteNonQuery(string sqlCommand)
        {
            if (!this.Exists)
            {
                throw new InvalidOperationException("データベースは存在しません。");
            }
            this._database.ExecuteNonQuery(sqlCommand);
        }

        /// <summary>
        /// Hoge データベースを削除します。
        /// </summary>
        /// <exception cref="InvalidOperationException">データベースは存在しません。</exception>
        public void Drop()
        {
            if (!this.Exists)
            {
                throw new InvalidOperationException("データベースは存在しません。");
            }
            this._server.Refresh();
            this._server.KillDatabase(this._database.Name);
            System.Data.SqlClient.SqlConnection.ClearAllPools();
        }

        /// <summary>
        /// IIS 5.1 上に配置された ASP.NET アプリケーションから DB にアクセス (R/W) できるよう、
        /// ASPNET アカウントをログイン登録します。
        /// </summary>
        public void CreateLoginForAspnet()
        {
            if (!this.Exists)
            {
                throw new InvalidOperationException("データベースは存在しません。");
            }

            string name = string.Format(@"{0}\{1}", Environment.MachineName, "aspnet");
            this._server.Refresh();
            if (!this._server.Logins.Contains(name))
            {
                Login login = new Login(this._server, name);
                login.LoginType = LoginType.WindowsUser;
                login.Create();
            }

            this._database.Refresh();
            User user = new User(this._database, name);
            if (!this._database.Users.Contains(name))
            {
                user.Login = name;
                user.Create();
            }
            if (!user.IsMember("db_datareader"))
            {
                user.AddToRole("db_datareader");
            }
            if (!user.IsMember("db_datawriter"))
            {
                user.AddToRole("db_datawriter");
            }
        }

        #endregion
    }
}

ユニットテストクラスでの使用例
private const string InstanceName = @"localhost\sqlexpress";
private const string DatabaseName = "HogeDatabase_Test";

private HogeDatabase _testDatabase;

[TestFixtureSetUp]
public void CreateDatabase()
{
    this._testDatabase = new HogeDatabase(InstanceName, DatabaseName);
    if (this._testDatabase.Exists)
    {
        this._testDatabase.Drop();
    }
    this._testDatabase.Create();
}

[SetUp]
public void CreateTables()
{
    this._testDatabase.CreateTables();
}

[TestFixtureTearDown]
public void DropDatabase()
{
    if (this._testDatabase.Exists)
    {
        this._testDatabase.Drop();
    }
}

private void InsertTable1(string id, string name)
{
    const string insertQueryFormat = "INSERT INTO [dbo].[Table1] ([Id], [Name]) VALUES ('{0}', '{1}')";
    string insertQuery = string.Format(insertQueryFormat, id.Replace("'", "''"), name.Replace("'", "''"));

    this._testDatabase.ExecuteNonQuery(insertQuery);
}

[Test]
public void テスト１()
{
    for (int i = 0; i < 3; i++)
    {
        InsertTable1(i.ToString(), string.Format("Test {0:000}", i));
    }

    // テストコード
}

// 2009/01/14
ユニットテストの SetUp と TearDown で DB の作成・削除を実行すると、2 度目以降の DB 作成に失敗してしまうため、DB の作成・削除は TestFixtureSetUp と TestTearDown で行い、テーブルの再作成だけ SetUp で行うように、HogeDatabase クラスとユニットテストを変更。
HogeDatabase クラスの実装方法も全体的に変更。
ついでに ASPNET アカウントの R/W 権限を登録するメソッドも追加。(IIS 5.1 のみ正常に機能する)

// 2009/02/02
テストケースが複数あって DB の作成・削除が2度行われる場合、SqlConnection のプールが残ってしまう関係でエラーが発生する。そのため、DB の削除時に ClearAllPools メソッドを呼び出すよう修正。
参考 : DBバックアップ→DBリストア→テーブル内容参照時にException - Insider.NET