過去記事
Data Access Application Block
ここでは、データベースにアクセスするための機能を提供するData Access Application Blockについて説明します。Data Access Application Blockを使うと、データベースにアクセスするための定型的なコードを簡略化することができます。
Data Access Application Blockを使用するには、プロジェクトを作成してNuGetからEnterpriseLibrary.Dataをインストールします。
Data Access Application Blockを利用するには、Fluent APIを利用することも出来ますがログと同様に接続先のDBは構成ファイルで管理するのが一般的だと思うのでFluent APIは利用せずに構成ファイルで接続文字列を管理するようにします。
主な提供機能
Data Access Application Blockは、Databaseと呼ばれるクラスを使って各種データベースへのアクセスを行います。１メソッドでDataSetやDataTableへSQLを使ってデータを読み込むことや、DbReaderを取得することが出来ます。また、SQLからPOCOにデータをつめこむということも行えます。その他、ストアドプロシージャのDbCommandに対するサポート機能が用意されていますが、ここではその部分についての説明は省略します。詳しくはEnterprise Libraryのドキュメントを確認してください。
データベースの作成
ここでは、SQL Server Compact Edition 4.0に簡単なテーブルを定義してSQLを使ってデータを読み書きする方法を示します。DataSetやDataTableを利用する方法は下位互換のための機能だと思うので、ここではSQLからPOCOへデータを詰め込む機能に絞って説明を行います。
コンソールアプリケーションを新規作成しターゲットフレームワークを.NET Framework 4に変更します。そして、sample.sdfという名前でSQL Server Compact Edition 4.0のデータベースを作成します。作成したデータベースに下記の構造を持ったテーブルを作成します。
ID列は、下記のようにデータベースで自動的に採番されるようにします。
データは初期状態で3件登録しました。
構成ファイルの編集
構成ファイルは、Enterprise Libraryの構成ファイルを編集するツールを使用します。app.configをプロジェクトに追加して右クリックからEdit configuration fileを選択してツールを起動します。ツールを起動したらDatabase Settingsの箇所を下図のように、先ほど作成したSQL Server Compact Edition 4.0のデータベースに接続するようにSqlCeという名前（任意の名前で問題ありません）構成します。そして、デフォルトにSqlCeを設定して保存します。
データベースへのアクセス
データベースにアクセスするにはEnterprise LibraryのコンテナからDatabaseクラスを取得します。そしてIEnumerable
01. class Person 02. { 03. public long Id { get; set; } 04. public string Name { get; set; } 05. public int Age { get; set; } 06. } このクラスにデータを格納するコードを下記に示します。 01. // Enterprise LibraryのコンテナからDatabaseクラスのインスタンスを取得 02. var database = EnterpriseLibraryContainer.Current.GetInstance<Database>(); 03. // SQL文を発行してデータを格納 04. var people = database.ExecuteSqlStringAccessor<Person>( 05. "SELECT Id, Name, Age FROM PERSON ORDER BY Age DESC"); 06. // 結果を表示 07. foreach (var p in people) 08. { 09. Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", p.Id, p.Name, p.Age); 10. }
4行目〜5行目がデータを取得している箇所になります。このコードの実行結果を以下に示します。
Id: 3, Name: ohta, Age: 30 Id: 2, Name: kimura, Age: 20 Id: 1, Name: tanaka, Age: 10
パラメータつきのSQL文を実行する方法は下記のようになります。パラメータと引数を対応づけるには通常はDbCommandのParametersにDbParameterを追加しますが、SqlStringAccessorではIDbParameterMapperの実装クラスでパラメータと引数の対応付けを行います。Enterprise Libraryでは、特別な実装は用意されていないので利用者が要件にあった実装を行う必要があります。例えば@p1, @p2, @p3…のようなルールのパラメータに引数を当てはめるIDbParameterMapperは下記のような実装になります。
01. /// <summary> 02. /// @p1, @p2, @p3という名前の順番でパラメータをマッピングするパラメータマッパー 03. /// </summary> 04. class SequenceParameterMapper : IParameterMapper 05. { 06. /// <summary> 07. /// デフォルトインスタンス 08. /// </summary> 09. public static readonly IParameterMapper Default = new SequenceParameterMapper(); 10. 11. public void AssignParameters(DbCommand command, object[] parameterValues) 12. { 13. // 引数で渡された値をCommandParameterへ変換 14. var parameters = parameterValues 15. .Select((value, index) => 16. { 17. var p = command.CreateParameter(); 18. p.ParameterName = "p" + (index + 1); 19. p.Value = value; 20. return p; 21. }) 22. .ToArray(); 23. // コマンドにパラメータを追加 24. command.Parameters.AddRange(parameters); 25. } 26. }
AssignParametersメソッドでパラメータをDbCommandに設定しています。このクラスを使うとパラメータつきのSQL文は下記のように実行できます。
01. // Enterprise LibraryのコンテナからDatabaseクラスのインスタンスを取得 02. var database = EnterpriseLibraryContainer.Current.GetInstance<Database>(); 03. // SQL文を発行してデータを格納 04. var accessor = database.CreateSqlStringAccessor<Person>( 05. // パラメータつきのSQL文 06. "SELECT Id, Name, Age FROM PERSON WHERE NAME LIKE @p1 ORDER BY ID DESC", 07. // パラメータのマッピングルール 08. SequenceParameterMapper.Default); 09. // パラメータを指定して実行 10. var people = accessor.Execute("%mu%"); 11. // 結果を表示 12. foreach (var p in people) 13. { 14. Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", p.Id, p.Name, p.Age); 15. }
CreateSqlStringAccessorが返すDataAccessor
Id: 2, Name: kimura, Age: 20
Nameにmuを含むid:2のkimuraさんだけが抽出されていることが確認できます。
データの更新と明示的なトランザクション管理
データの更新はDbCommandを使って行います。コードは以下のようになります。
01. // Enterprise LibraryのコンテナからDatabaseクラスのインスタンスを取得 02. var database = EnterpriseLibraryContainer.Current.GetInstance<Database>(); 03. // コネクションを作成 04. using (var conn = database.CreateConnection()) 05. { 06. conn.Open(); 07. // トランザクションを開始 08. using (var tran = conn.BeginTransaction()) 09. { 10. // 登録対象のデータ 11. var p = new Person { Name = "hanami", Age = 100 }; 12. // コマンドをSQLから作成 13. var command = database.GetSqlStringCommand("INSERT INTO PERSON(NAME, AGE) VALUES(@p1, @p2)"); 14. // パラメータを追加 15. database.AddInParameter(command, "p1", DbType.String, p.Name); 16. database.AddInParameter(command, "p2", DbType.Int32, p.Age); 17. // トランザクションを指定してコマンドを実行 18. var count = database.ExecuteNonQuery(command, tran); 19. 20. // DB側でふられたIDを取得 21. var newId = database.ExecuteScalar(tran, CommandType.Text, "SELECT @@IDENTITY"); 22. // 登録件数と、登録時にふられたIDを表示 23. Console.WriteLine("inserted: {0}, newId: {1}", count, newId); 24. // コミット 25. tran.Commit(); 26. } 27. conn.Close(); 28. }
上記のコードは単純にINSERT文を実行するだけではなく、Data Access Application Blockにおけるコネクションの明示的な管理方法とトランザクションの明示的な管理方法を示しています。このように、DBに非依存なAPIを使ってトランザクションやコネクションの管理が簡単にできるようになっています。
TransactionScope
.NET Framework 2.0から導入されたTransactionScopeを使ったトランザクション管理にもData Access Application Blockは対応しています。何も考えずに使うと分散トランザクションに簡単に昇格してしまうことから嫌う人は多い機能(SQL Server 2008 と .NET Framework 2.0 SP1で緩和されてますが)ですが、Data Access Application BlockではTransactionScopeのトランザクションが存在している間は、Databaseクラスを経由して行った同じ接続文字列のコネクションをキャッシュしておいて、2重にコネクションが開かないように管理を行います。このためSQL Server 2008以降でなくても、分散トランザクションに昇格しにくくなっています。
まず、最初に分散トランザクションに対応していないSQL Server Compact Edition 4.0でTransactionScope内で2回コネクションを開いたときの挙動を下記に示します。
01. using (var tc = new TransactionScope()) 02. { 03. // 単純にTransactionScope内で2つのコネクションを開いて閉じる 04. var conn1 = new SqlCeConnection(ConfigurationManager.ConnectionStrings["SqlCe"].ConnectionString); 05. conn1.Open(); 06. conn1.Close(); 07. var conn2 = new SqlCeConnection(ConfigurationManager.ConnectionStrings["SqlCe"].ConnectionString); 08. conn2.Open(); 09. conn2.Close(); 10. }
上記のコードを実行すると下記のように例外になります。
ハンドルされていない例外: System.InvalidOperationException: 接続オブジェクトをトランザクション スコープに参加させることができません。 場所 System.Data.SqlServerCe.SqlCeConnection.Enlist(Transaction tx) 場所 System.Data.SqlServerCe.SqlCeConnection.Open() 場所 以下省略
Databaseクラスを使って上記のように複数コネクションを開くように見えるコードを記載してみます。
01. // Enterprise LibraryのコンテナからDatabaseクラスのインスタンスを取得 02. using (var tc = new TransactionScope()) 03. { 04. var database1 = EnterpriseLibraryContainer.Current.GetInstance<Database>(); 05. Console.WriteLine("最初のdatabaseオブジェクト取得 HashCode: {0}", database1.GetHashCode()); 06. for (int i = 0; i < 2; i++) 07. { 08. // 登録対象のデータ 09. var p = new Person { Name = "hanami", Age = 100 }; 10. // コマンドをSQLから作成 11. var command = database1.GetSqlStringCommand("INSERT INTO PERSON(NAME, AGE) VALUES(@p1, @p2)"); 12. // パラメータを追加 13. database1.AddInParameter(command, "p1", DbType.String, p.Name); 14. database1.AddInParameter(command, "p2", DbType.Int32, p.Age); 15. // トランザクションを指定してコマンドを実行 16. var count = database1.ExecuteNonQuery(command); 17. 18. // DB側でふられたIDを取得 19. var newId = database1.ExecuteScalar(CommandType.Text, "SELECT @@IDENTITY"); 20. // 登録件数と、登録時にふられたIDを表示 21. Console.WriteLine("database1: inserted: {0}, newId: {1}", count, newId); 22. } 23. var database2 = EnterpriseLibraryContainer.Current.GetInstance<Database>(); 24. Console.WriteLine("2つ目のdatabaseオブジェクト取得 HashCode: {0}", database2.GetHashCode()); 25. for (int i = 0; i < 2; i++) 26. { 27. // 登録対象のデータ 28. var p = new Person { Name = "sakurai", Age = 100 }; 29. // コマンドをSQLから作成 30. var command = database2.GetSqlStringCommand("INSERT INTO PERSON(NAME, AGE) VALUES(@p1, @p2)"); 31. // パラメータを追加 32. database2.AddInParameter(command, "p1", DbType.String, p.Name); 33. database2.AddInParameter(command, "p2", DbType.Int32, p.Age); 34. // トランザクションを指定してコマンドを実行 35. var count = database1.ExecuteNonQuery(command); 36. 37. // DB側でふられたIDを取得 38. var newId = database2.ExecuteScalar(CommandType.Text, "SELECT @@IDENTITY"); 39. // 登録件数と、登録時にふられたIDを表示 40. Console.WriteLine("database2: inserted: {0}, newId: {1}", count, newId); 41. } 42. tc.Complete(); 43. }
複数のDatabaseオブジェクトを取得してTransactionScopeの中で複数のSQL文を発行しています。ここで複数個のコネクションが開かれていたら先ほどと同じように例外になるはずです。上記のコードの実行結果を以下に示します。
最初のdatabaseオブジェクト取得 HashCode: 58204539 database1: inserted: 1, newId: 11 database1: inserted: 1, newId: 12 2つ目のdatabaseオブジェクト取得 HashCode: 54078809 database2: inserted: 1, newId: 13 database2: inserted: 1, newId: 14
ヘルパーメソッドのすゝめ
このようにData Access Application Blockを使うと、SELECT系のコードが簡潔に、そして更新系はTransactionScope内でのコネクションのキャッシュにより昇格が起こりにくくなる機能の恩恵を受けることができます。しかし、UPDATE文やINSERT文を発行するコードはお世辞にも直感的とは言い難いです。そのため、下記のようなヘルパーメソッドを作ることで完結に更新系のSQL文の発行も行えるようになります。
01. /// <summary> 02. /// Databaseクラスへの拡張メソッド 03. /// </summary> 04. static class DatabaseExtensions 05. { 06. // IParameterMapperに指定したパラメータマッピングルールでSQL文を実行する 07. public static int ExecuteUpdate( 08. this Database self, 09. string sql, 10. IParameterMapper mapper, 11. params object[] parameters) 12. { 13. using (var command = self.GetSqlStringCommand(sql)) 14. { 15. mapper.AssignParameters(command, parameters); 16. return self.ExecuteNonQuery(command); 17. } 18. } 19. 20. // SequenceParameterMapperのパラメータマッピングルールでSQL文を実行する 21. public static int ExecuteUpdate( 22. this Database self, 23. string sql, 24. params object[] parameters) 25. { 26. return self.ExecuteUpdate(sql, SequenceParameterMapper.Default, parameters); 27. } 28. }
上記のような拡張メソッドを定義すると、INSERT文などの更新系SQLを実行するコードは下記のようになります。
01. using (var tc = new TransactionScope()) 02. { 03. // Enterprise LibraryのコンテナからDatabaseクラスのインスタンスを取得 04. var database = EnterpriseLibraryContainer.Current.GetInstance<Database>(); 05. // 登録対象のデータ 06. var p = new Person { Name = "hanami", Age = 100 }; 07. // 拡張メソッドを使って登録 08. var count = database.ExecuteUpdate( 09. "INSERT INTO PERSON(NAME, AGE) VALUES(@p1, @p2)", 10. p.Name, p.Age); 11. // DB側で割り振られたIDを取得 12. var newId = (decimal)database.ExecuteScalar( 13. CommandType.Text, "SELECT @@IDENTITY"); 14. 15. // 結果を表示 16. Console.WriteLine("inserted: {0}, newId {1}", count, newId); 17. tc.Complete(); 18. }
実行結果は以下のようになります。
inserted: 1, newId 9
Fluent APIでの設定
因みに、Data Access Application BlockをFluent APIで設定すると下記のようになります。
01. var builder = new ConfigurationSourceBuilder(); 02. builder.ConfigureData() 03. // 使用する接続文字列を取得 04. .ForDatabaseNamed("SqlCe") 05. .AsDefault(); 06. // Enterprise LibraryのコンテナからDatabaseクラスのインスタンスを取得 07. var database = EnterpriseLibraryContainer.Current.GetInstance<Database>(); 08. // SQL文を発行してデータを格納 09. var people = database.ExecuteSqlStringAccessor<Person>( 10. "SELECT Id, Name, Age FROM PERSON ORDER BY ID DESC"); 11. // 結果を表示 12. foreach (var p in people) 13. { 14. Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", p.Id, p.Name, p.Age); 15. }
ForDatabaseNamedで接続文字列の名前を指定して、AsDefaultでデフォルトで使用するものに設定します。実行結果については割愛します。
