endokのブログ

IT・プログラミングネタ

.NETの各種DBアクセス方法を試す(DataSet,EntityFramework,Dapperなど)

はじめに

.NET使って何か作ろうと思ったのだが、仕事では独自Frameworkばかり使っていて標準的な方法を触ったことがないので、有名所のDBアクセス方法を試してみたいと思う。

サンプルコードの全体は下記。
GitHub - endok/sample-dotnetdb: .NETのDBアクセスサンプルコード。

対象

試す対象は、

環境

データ準備

下記状態を前提とする。

下記テーブル、データを作成しておく。

CREATE TABLE member
(
	id int PRIMARY KEY,
	name nvarchar(20) NOT NULL,
	age int NOT NULL
)

INSERT INTO member (id, name, age) VALUES (1, 'Alice', 20)
INSERT INTO member (id, name, age) VALUES (2, 'Bob', 25)
INSERT INTO member (id, name, age) VALUES (3, 'Carol', 30)

このような状態になる。
f:id:endok:20171230161433p:plain

ADO.NET SqlClient

たぶん一番シンプルにADO.NETを使うパターン。
System.Data.SqlClientあたりをusingするだけで使える。
SQLServerに接続するためのクライアントで、ODBCだったりOracleの場合は別のクラスを利用する。

今回は、DataReaderでの参照、UPDATE文の発行を試した。
DataReaderは、前方参照専用、読み取り専用だがパフォーマンスに優れるとのこと。

サンプルコード

        private static void SqlCommandSample(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();

                // SqlCommandによる参照
                string selectquery = "SELECT id, name, age FROM member WHERE age >= @age ";

                SqlCommand selectcommand = new SqlCommand(selectquery, connection);
                selectcommand.Parameters.AddWithValue("@age", 25);

                SqlDataReader reader = selectcommand.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("id={0},name={1},age={2}", reader[0], reader[1], reader[2]);
                }
                reader.Close();

                // SqlCommandによる更新
                string updatequery = "UPDATE member SET age = 40 WHERE id = @id";

                SqlCommand updatecommand = new SqlCommand(updatequery, connection);
                updatecommand.Parameters.AddWithValue("@id", 3);

                updatecommand.ExecuteNonQuery();
            }
        }

ADO.NET DataSet

こちらもADO.NET標準の仕組み。
データソースに依存せず、メモリ上でリレーショナルデータを扱うための仕組み。DBなど既存のデータソースとのやり取りにはDataAdapterを介する。
テーブルなど一つのデータのまとまり単位でDataAdapterを定義して、CRUD操作するSQLを設定。その後はDataSetを操作した結果をDBに同期するという使い方となる模様。

今回はDataSetでの参照、データ更新を試した。

データソースに依存しないで操作できること、細かいことを考えずにデータ取得、同期できるという良さがある。しかしDataAdapterの定義がやや面倒そうという印象。

サンプルコード

        private static void DataSetSample(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();

                // DataSetによる参照、更新
                string selectQuery = "SELECT id, name, age FROM member";
                string updateQuery = "UPDATE member SET age = @age WHERE id = @id";

                SqlDataAdapter adapter = new SqlDataAdapter();

                // 主キー情報をDBから取得
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                // クエリの設定
                adapter.SelectCommand = new SqlCommand(selectQuery, connection);
                adapter.UpdateCommand = new SqlCommand(updateQuery, connection);

                // Updateパラメータの設定
                SqlParameter ageParam = new SqlParameter();
                ageParam.ParameterName = "@age";
                ageParam.SourceColumn = "age";
                adapter.UpdateCommand.Parameters.Add(ageParam);
                SqlParameter idParam = new SqlParameter();
                idParam.ParameterName = "@id";
                idParam.SourceColumn = "id";
                adapter.UpdateCommand.Parameters.Add(idParam);

                DataSet dataset = new DataSet();
                adapter.Fill(dataset, "memberTable");
                DataTable datatable = dataset.Tables["memberTable"];

                // データの参照
                foreach (DataRow row in datatable.Rows)
                {
                    Console.WriteLine("id={0},name={1},age={2}", row["id"], row["name"], row["age"]);
                }

                // データの更新
                DataRow carolRow = datatable.Rows.Find(3);
                carolRow["age"] = 50;

                adapter.Update(dataset, "memberTable");
            }

        }

EntityFramework

ADO.NETに含まれるデータアクセスフレームワーク。いわゆるORMで、データソースとオブジェクトをマッピングして取り扱う。
DBマイグレーションの仕組みも備えている。

VisualStudio2012以上でないとEntityFrameworkToolsが利用できない。

導入方法

NuGetでインストールする。
プロジェクトを右クリックして[NuGetパッケージの管理...]の参照で"EntityFramework"を検索してインストールする。
※Dapperも同じプロジェクトに導入する場合、v6.1.3をインストールする。

EntityFrameworkのワークフロー

Entity Framework (EF) Documentation の概要

EntityFramewokでは概念モデルを作成するにあたり、「コードファースト」「モデルファースト」「データベースファースト」という方式がある。

コードファースト・・・ソースコード上にModelクラスをまず作成。その情報を元にDBスキーマを作成するという方式。スキーマの変更は、マイグレーション機能で管理する。
モデルファースト・・・EFデザイナを利用してVisualStudio上でモデルを追加。その情報をDBスキーマに反映するという方式。
データベースファースト・・・DBスキーマSQLでまず作成。その情報をVisualStudioのモデル(ソースコード、EFデザイナ)に反映するという方式。

今回はDBスキーマ作成済のため、データベースファースト方式を試す。

モデルのリバースエンジニアリング

プロジェクトを右クリックして[追加]→[新しい項目]でデータカテゴリの"ADO.NET Entity Data Model"を追加する。
f:id:endok:20180101120234p:plain

"データベースからEF Desginger"を選ぶ。
f:id:endok:20180101021221p:plain

データ接続を選択し、App.Configに保存する。
f:id:endok:20180101120342p:plain

接続の設定は適当なものを選ぶまたは新規作成する。
対象のデータベースオブジェクト(今回はmemberテーブル)を選択する。
f:id:endok:20180101120452p:plain

下記のようにDBの情報をもとに抽出されたモデル情報がデザイナに表示される。
f:id:endok:20180101115134p:plain

クエリの方式

いくつか方法があるが、基本的にはLINQtoEntitiesでアクセスするのが主流の模様。複雑なクエリを発行する際には、EntitySQLというSQLに似た言語を利用することもできる(HybernateのHQLのようなものと思われる)。また、SQLを直接発行することもできる。
今回はLINQtoEntitiesでの参照、EntitySQLでの参照、EntityFrameworkでの更新を試した。

サンプルコード

        private static void EntityFrameworkSample()
        {
            // Entity SQLでの参照
            Console.WriteLine("-- Entity SQL");
            using (EntityConnection connection = new EntityConnection("name=SampleContext"))
            {
                connection.Open();

                string query1 = "SELECT m.id, m.name, m.age FROM SampleContext.member AS m WHERE m.age >= @age";

                using (EntityCommand cmd = new EntityCommand(query1, connection))
                {
                    cmd.Parameters.AddWithValue("age", 25);

                    using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        while (rdr.Read())
                        {
                            Console.WriteLine("id={0},name={1},age={2}", rdr["id"], rdr["name"], rdr["age"]);
                        }
                    }
                }

            }

            using (var db = new SampleContext())
            {
                // LINQ to Entitiesでの参照
                Console.WriteLine("-- LINQ to Entities");
                var query1 = from m in db.member
                             where m.age >= 25
                             select m;

                foreach (var member in query1)
                {
                    Console.WriteLine("id={0},name={1},age={2}", member.id, member.name, member.age);
                }

                // 別の書き方での参照
                Console.WriteLine("-- LINQ to Entities 2");
                var query2 = db.member.Where(m => m.age >= 25);

                foreach (var member in query2)
                {
                    Console.WriteLine("id={0},name={1},age={2}", member.id, member.name, member.age);
                }

                // Entity Framewokでの更新
                var carol = db.member.Find(3);
                carol.age = 60;
                db.SaveChanges();
            }
        }

Dapper

シンプルなORM(Micro-ORM)で、StackOverflowの中の人が作ったらしい。
Commandの作成やオブジェクトへのマッピングなど、定型的で面倒な部分を補ってくれる。重厚なORMはいらないけど生ADO.NETもちょっと・・・というケースに。

導入方法

NuGetで"Dapper"をインストールする。
EntityFramework6.2が入っている状態ではインストールできなかったので、EntityFrameworkは6.1.3にしておく。

サンプルコード

マッピング先のクラスはEntityFrameworkが作成した下記クラスを流用。

    public partial class member
    {
        public int id { get; set; }
        public string name { get; set; }
        public int age { get; set; }
    }

実際の処理は下記。

        private static void DapperSample(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();

                // Dapperによる参照
                string selectquery = "SELECT id, name, age FROM member WHERE age >= @age";
                var members = connection.Query<member>(selectquery, new { age = 25 });

                foreach (var member in members)
                {
                    Console.WriteLine("id={0},name={1},age={2}", member.id, member.name, member.age);
                }

                // Dapperによる更新
                string updatequery = "UPDATE member SET age = 30 WHERE id = @id";
                connection.Execute(updatequery, new { id = 3 });
            }
        }

その他

気になって調べた事柄をメモ。

LINQ to EntitiesとLINQ to SQLの違い

下記がわかりやすかった。
c# - What is the difference between "LINQ to Entities", "LINQ to SQL" and "LINQ to Dataset" - Stack Overflow

引用すると、下記の通り。

・すべてLINQ(Language Integrated Query)であるため、共通点は多い。これらの"方言"は、異なるデータ元に対するクエリの書き方を提供する。
・LINQtoSQLはMicrosoftの最初のORMへの試みだが、SQLServerにしか対応していない。SQLServerのテーブルと.NETオブジェクトのマッピングを行う。
・LINQtoEntitiesは同じMicrosoftによるORMだが、EntityFrameworkを背景に利用し、複数のDBMSに対応している。

まとめ

ADO.NET周りは(たぶん歴史的な経緯のためと思われるが)似たような用語、概念が多く、最新の技術セットが何なのか把握するのが難しく感じた。
今回の理解としてはMS的にはEntityFramework(LINQ to Entities)で、他の選択肢としてOSSを考えるという形なのだけど合っているのだろうか・・。
Dapperも簡単に使えて良さそうだが、MS系ではなんとなくMS流儀に乗っかりたくなるので、まずはEntityFrameworkを使ってみようかな。