Moonmile Solutions Blog

C#からMySQLを扱う（更新編）

射撃しながら前進（あるいは迷走）する方への援護射撃。第２弾です。

MySQLでも、SQL Serverでも、Oracleでも、データをグリッドで表示する場合は、DataSetを使うのが断然楽です。
顧客が、グリッドのチープな画面を許容してくださるならば、グリッドでOKでしょう。

// コネクション作成
MySqlConnection cn = new MySqlConnection(
 "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
MySqlDataAdapter da = new MySqlDataAdapter(
 "SELECT * FROM sample", cn);
DataTable dt = new DataTable();
// 検索
da.Fill(dt);
// 表示
dataGridView1.DataSource = dt;

こんな風に、DataTable を使って書けます。

ちなみに、DataSet で書く場合は、

// コネクション作成
MySqlConnection cn = new MySqlConnection(
 "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
MySqlDataAdapter da = new MySqlDataAdapter(
 "SELECT * FROM sample", cn);
DataSet ds = new DataSet();
// 検索
da.Fill(ds);
// 表示
dataGridView1.DataSource = ds;

何が違うかというと（これも歴史的な話になるのですが）、もともと、DataSetは、複数のテーブルを扱える設計になっています。なので、DataSetは複数のテーブルを扱うのですが、普通は上の例のように、ひとつのテーブル（検索結果）しか扱いません。
なので、

dataGridView1.DataSource = ds;

とした場合は、最初のテーブルを使う、という仕様になっているんですね。
これが、ADO.NET のバージョンで DataTable ってのが出てきました。所詮、ひとつのテーブルしか扱わないのですから、DataSetの部分は無駄、という考えです。
なので、DataSetとDataTableは、同じように扱って大丈夫です。

ちなみに、DataSetから最初のテーブルを取り出す場合は、

dataGridView1.DataSource = ds.Tables[0];

と書きます。

余談ですが、DataSetに複数のテーブルを使う場合は、

MySqlDataAdapter da = new MySqlDataAdapter(
 "SELECT * FROM person;" +
    "SELECT * FROM company", cn);
DataSet ds = new DataSet();
// 検索
da.Fill(ds);

のように、データアダプタに対して、SQL文を二つ書きます。これは、SQL Server のみ使える機能です。
# つまりは、このために DataSet は複数のテーブルを扱えるようになっているわけです。

■データを追加 INSERT

データを追加するINSERT文は、通常はMySqlCommandクラスを使います。
この場合、素直にMySqlCommandクラスを使ったのが次の例です。

MySqlConnection cn = new MySqlConnection(
        "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
// コマンドを作成
MySqlCommand cmd =
    new MySqlCommand("insert into sample values (10,'konica',null,null)", cn);
// オープン
cmd.Connection.Open();
// 実行
cmd.ExecuteNonQuery();
// クローズ
cmd.Connection.Close();

SELECTの場合と違うのは、コネクションのオープンとクローズを前後に入れないと駄目なところですね。
実は、このオープン＆クローズはデータアダプタが担っているのです。
データアダプタを使って INSERT 文を実行する場合は、こんな風になります。

MySqlConnection cn = new MySqlConnection(
        "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
// コマンドを作成
MySqlDataAdapter da =
    new MySqlDataAdapter("insert into sample values (11,'konica',null,null)", cn);
// データテーブル
DataTable dt = new DataTable();
// 実行
da.Fill(dt);
// 結果は無視

SELECT文とは違って、Fillメソッドで呼び出した後の結果は無視します。

さて、このままSQL文を書いてもいいのですが、各データを書くのが非常に面倒です。
で、よくやるパターンは、stringクラスのFormatメソッドが使われます。

MySqlCommand cmd =
    new MySqlCommand(
    string.Format("insert into sample values ({0},'{1}',{2},{3})",
        id, name, url, date));

変数を使って、整形をするパターンですが、これには重大な欠点があります。

・文字列の場合は「’」を使って囲まないといけない。忘れるとエラーになる。
・nullが指定できない。
・SQLインジェクションが発生する。

SQLインジェクションの問題もそうですが、文字列を意識しないといけなかったり、nullが指定できないのは致命的です。なにより、日付型（DateTime型）の指定が非常に困難です。

なので、MySqlCommandクラスでパラメータを指定するのがベターです。

MySqlConnection cn = new MySqlConnection(
        "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
// コマンドを作成
MySqlCommand cmd =
    new MySqlCommand("insert into sample values (@id, @name, @url, @date )", cn);
// パラメータ設定
cmd.Parameters.Add(
    new MySqlParameter("id", 13));
cmd.Parameters.Add(
    new MySqlParameter("name", "konica"));
cmd.Parameters.Add(
    new MySqlParameter("url", null));
cmd.Parameters.Add(
    new MySqlParameter("date", DateTime.Now));

// オープン
cmd.Connection.Open();
// 実行
cmd.ExecuteNonQuery();
// クローズ
cmd.Connection.Close();

MySqlCommandクラスでSQL文を指定する時に、「@name」のようにパラメータを指定して、MySqlParameterオブジェクトで値を指定します。

このようにパラメータを使うと、先の３点が一遍に解決できますし、さらに、日付型のようなちょっと指定の難しい型も簡単に設定できます。

# 本来は、ストアドプロシージャを使うのが筋なのですが、SQL文の埋め込みとストアドプロシージャは「配置」が異なるので、一概に交換できるとは言えません。なので、プロジェクトごとに、SQL文の埋め込みにするか、ストアドプロシージャにするかを考える必要があります。

■データを追加で自動採番のIDを使う場合

さて、INSERT文は書けるようになりましたが、自動でIDを振っているときはどうするのか、が問題です。
これは普通に次のように書きます。

MySqlConnection cn = new MySqlConnection(
        "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
// コマンドを作成
MySqlCommand cmd =
    new MySqlCommand("insert into sample ( name, url, date ) values ( @name, @url, @date )", cn);
// パラメータ設定
cmd.Parameters.Add(
    new MySqlParameter("id", 13));
cmd.Parameters.Add(
    new MySqlParameter("name", "konica"));
cmd.Parameters.Add(
    new MySqlParameter("url", null));
cmd.Parameters.Add(
    new MySqlParameter("date", DateTime.Now));

// オープン
cmd.Connection.Open();
// 実行
cmd.ExecuteNonQuery();
// クローズ
cmd.Connection.Close();

INSERT文で列名を指定するパターンですね。

ただし、ここで問題があります。このINSERTをした後でIDを取得したいときってありますよね。この場合はどうするのでしょうか？
これは、最終に更新したIDを取得するクエリを叩きます。

MySQLの場合は、「SELECT LAST_INSERT_ID()」を使うので、

MySqlConnection cn = new MySqlConnection(
        "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
// コマンドを作成
MySqlCommand cmd =
    new MySqlCommand("insert into sample (name, url, updatedate) values (@name, @url, @date)", cn);
// パラメータ設定
cmd.Parameters.Add(
    new MySqlParameter("name", "konica"));
cmd.Parameters.Add(
    new MySqlParameter("url", null));
cmd.Parameters.Add(
    new MySqlParameter("date", DateTime.Now));
MySqlCommand cmd2 =
    new MySqlCommand("SELECT LAST_INSERT_ID()", cn);

// オープン
cmd.Connection.Open();
// 実行
cmd.ExecuteNonQuery();
// 更新IDを取得
var id = cmd2.ExecuteScalar();
// クローズ
cmd.Connection.Close();

MessageBox.Show(“更新ID:” + id);

のように動かせば、最後に更新したIDを取得できます。
ExecuteScalarメソッドは、long型を返すのですが、キャストが面倒なのでvar型で受け取ってOKです。

■INSERT時の例外はどうするのか？

INSERT時に型チェックやIDの採番などでエラーが発生する場合があります。
これは、普通にC#の例外処理（try-catch）を入れてやれば取得できます。

MySqlConnection cn = new MySqlConnection(
        "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
// コマンドを作成
MySqlCommand cmd =
    new MySqlCommand("insert into sample (name, url, updatedate) values (@name, @url, @date)", cn);
// パラメータ設定
cmd.Parameters.Add(
    new MySqlParameter("name", "konica"));
cmd.Parameters.Add(
    new MySqlParameter("url", null));
cmd.Parameters.Add(
    new MySqlParameter("date", DateTime.Now));
MySqlCommand cmd2 =
    new MySqlCommand("SELECT LAST_INSERT_ID()", cn);
try
{
    // オープン
    cmd.Connection.Open();
    // 実行
    cmd.ExecuteNonQuery();
    // 更新IDを取得
    var id = cmd2.ExecuteScalar();
    // クローズ
    cmd.Connection.Close();
}
catch (SqlException ex)
{
    // 例外処理
    MessageBox.Show("例外発生:" + ex.Message);
}

■データを削除 DELETE

削除も同じように書けます。

int id = 15; // 削除するID

MySqlConnection cn = new MySqlConnection(
        "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
// コマンドを作成
MySqlCommand cmd =
    new MySqlCommand("delete from sample where id = @id", cn);
// パラメータ設定
cmd.Parameters.Add(
    new MySqlParameter("id", id));
// オープン
cmd.Connection.Open();
// 実行
cmd.ExecuteNonQuery();
// クローズ
cmd.Connection.Close();

ここまで来ると簡単ですね。

■データを更新 UPDATE

更新も同じように書きます。

int id = 20; // 更新するID

MySqlConnection cn = new MySqlConnection(
        "Data Source=localhost;Database=konicadb;User ID=konica;password=konica");
// コマンドを作成
MySqlCommand cmd =
    new MySqlCommand("update sample set updatedate = @date where id = @id", cn);
// パラメータ設定
cmd.Parameters.Add(
    new MySqlParameter("id", id));
cmd.Parameters.Add(
    new MySqlParameter("date", DateTime.Now));
// オープン
cmd.Connection.Open();
// 実行
cmd.ExecuteNonQuery();
// クローズ
cmd.Connection.Close();

こんな風にパラメータを使うと、順不同に書けるのコードも見やすくなります。
■DataSetをいつ更新するのか？

所詮 DataSet はキャッシュなので、データの追加/削除/更新した場合は、もう一度 DataSet を読み直すのが吉です。

DataSet/DataTableの内容を直接更新することも可能ですが、かえってややこしいので、業務的にはやめたほうがいいです。

# LINQ to SQL や ADO.NET Data Entity の場合は、オブジェクト自身そのものを通すので、更新しなくても良いでしょう。ただし、SQL Server と Oracle しか使えないので。

手順としては、

・追加したら検索
・削除したら検索
・更新したら検索

にします。

具体的には、

MySqlConnection cn = new MySqlConnection("...");
// コマンドを作成
MySqlCommand cmd =
 new MySqlCommand("insert into TPerson ( @id, @name, @age )", cn);
cmd.Parameters.Add(new MySqlParameter("id", 1));
cmd.Parameters.Add(new MySqlParameter("name", "konica"));
cmd.Parameters.Add(new MySqlParameter("age", 100));
// 実行
cmd.ExecuteNonQuery();
// DataTabel/DataSetを再読み込み
MySqlConnection cn = new MySqlConnection("...");
MySqlDataAdapter da = new MySqlDataAdapter(
 "SELECT * FROM TPerson", cn);
da.Fill(m_dt);

のように、ローカルに保存しているDataSet/DataTable（m_dt）に、設定します。

■DataSet/DataTableをどのような形式で持つのか？

実は、例では Tsample 簡単なテーブルをデータグリッドに表示するだけなのですが、実務ではもっと複雑な形式でグリッドに表示することになります。
この場合は、どのパターンでデータを保存するかを「考える」必要があります。

ひとまず、アンチパターンを書き並べておくと、

・複数テーブルを利用している場合、それぞれのテーブルをDataTableに保管しようとする。
・グリッドに表示するときに、常に複雑なフィルタが必要になる。

ような設計になっている場合は、考え直してください。
このような設計は、非常にデータをシンプルに検索する方法に変更します。

指針としては、

.データグリッドやリストに表示する単位で、DataSet/DataTable に保存する。

でOKです。

例えば、

TPerson
+ id
+ name
+ age
+ companyid

TCompany
+ id
+ name

のようなテーブルがあって、グリッドに

+ TPerson.name
+ TCompany.name

の２つを表示している場合には、

MySqlDataAdapter da = new MySqlDataAdapter(
 "SELECT p.name as 'PName', c.name as 'CName' " +
    " FROM TPerson p, TCompany c " +
    " WHERE p.companyid = c.id ";
DataTable dt = new DataTable();
// 実行
da.Fill(dt);

のように、DataTable に保存しておきます。

これを表示する画面ごとに取得してしまいます。
昔ならば、メモリが問題になってしまうのですが、今のコンピュータならば、多少のメモリのロスは大丈夫でしょう。
逆に、大量のデータをグリッドに表示するような場合は、DataSetに保存せずに、一回ずつ検索したほうが良いし、表示形式そのものを考え直す必要があります。

射撃しつつ前進する方への援護射撃です。

群馬に行った時、データセットの件で「う～ん」と思い、なんとなく落とし穴に陥りそうな匂いはしていたのですが、時間が取れずにそのままに。

という訳で遅ればせながら、.NET(C#)でMySQLを扱うときのコツなんかを、詳しめに書いておきます。

■接続方法はADO.NET Driver for MySQL (Connector/NET)を使う

まず、.NETから扱えるように下記をダウンロードしてインストールします。

MySQL :: Download Connector/Net

http://www-jp.mysql.com/downloads/connector/net/

■名前空間は MySql.Data.MySqlClient

SQL Server の場合は System.Data.SqlClient を使いますが、MySQL の場合は MySql.Data.MySqlClient をインポートします。

C# だったら先頭の行に

using MySql.Data.MySqlClient;

でOK

■主なクラス

ADO.NET経由でSQL Serverを扱う場合は、

コネクション SqlConnect

アダプタ SqlDataAdapter

となっています。

これが MySQL の場合は

コネクション MySqlConnect

アダプタ MySqlDataAdapter

となればOK。

巷のADO.NETのサンプルもこれを書き換えれば大抵動きます。

肝心のDataSet、DataTable クラスに関しては、SQL Serverでも MySQL でも同じものを使います。最初のコネクションとアダプタの名前が違うだけです。使い方は統一されているので、クラス名だけ変えれば普通は通ります。

■サンプル

いわゆる、こんな画面を作ってデータ接続をテストすると場合

サンプルコードはこちら

// クリアボタン
    private void button1_Click(object sender, EventArgs e)
    {
        // 挿入時のクリア
        dataGridView1.Columns.Clear();
        // バインド時
        dataGridView1.DataSource = null;
    }

    // DataSetボタン
    private void button2_Click(object sender, EventArgs e)
    {
        // コネクション作成
        MySqlConnection cn = new MySqlConnection(
            “Data Source=localhost;Database=konicadb;User ID=konica;password=konica”);
        MySqlDataAdapter da = new MySqlDataAdapter(
            “SELECT * FROM sample”, cn);
        DataTable dt = new DataTable();
        // 検索
        da.Fill(dt);
        // 表示
        dataGridView1.DataSource = dt;
        // データ保持
        m_dt = dt;

    }
    ///
    /// データ保持用
    ///
    DataTable m_dt;

    // DataSet自前ボタン
    private void button3_Click(object sender, EventArgs e)
    {
        // コネクション作成
        MySqlConnection cn = new MySqlConnection(
            “Data Source=localhost;Database=konicadb;User ID=konica;password=konica”);
        MySqlDataAdapter da = new MySqlDataAdapter(
            “SELECT * FROM sample”, cn);
        DataTable dt = new DataTable();
        // 検索
        da.Fill(dt);
        // 自前で表示
        dataGridView1.Columns.Add(“ID”, “ID”);
        dataGridView1.Columns.Add(“name”, “名前”);
        foreach (DataRow item in dt.Rows)
        {
            int n = dataGridView1.Rows.Add();
            dataGridView1.Rows[n].Cells[0].Value = item["id"];
            dataGridView1.Rows[n].Cells[1].Value = item["name"];
        }
    }

    // 保存済みのDataSetボタン
    private void button4_Click(object sender, EventArgs e)
    {
        // 表示
        dataGridView1.DataSource = m_dt;
    }

    // LINQは動かせない
    private void button5_Click(object sender, EventArgs e)
    {
        // これはSQL Serverしかできない
        string cnstr = “Data Source=localhost;Database=konicadb;User ID=konica;password=konica”;
        DataContext dc = new DataContext(cnstr);
        var tb = dc.GetTable();
        dataGridView1.DataSource = tb;
    }

    [TableAttribute(Name = "sample")]
    public class TSample
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public DateTime UpdateDate { get; set; }
    }

データグリッドで表示するだけならば、DataSourceプロパティを使ったほうが簡単です。列名なんかが固定であれば、デザイン時に表示する文字列を入れて、バインドする列名を入れておけばOK。

DataSet（ここではDataTable）の使いどころは、保存済みのDataSetボタンのところのように、一度キープしたものをデータベースに接続せずに使えることですね。いわゆるキャッシュの役割です。

当然、DataSetに一時的に更新状態をキープして、一気にデータベースにコミット、という流れもできるんですが、型付きDataSetを作らないと効率的でないので、最初は避けたほうがよいかなと思っています。MySqlCommand を使って、ちまちま INSERT文やUPDATE文を書いたほうが、後々の見通しが良くなります（経験上ですが）。

■DataSetとは何ぞや？

という訳で、DataSetとは何ぞやを、もうちょっと。

最初にデータベース接続に関しては歴史的に、

– ODBC接続

– RDO,DAO,ADO

– ADO.NET, DataSet

– ADO.NET, 型付きDataSet

– LINQ to SQL

– ADO.NET Data Entity

な流れがあります。

ODBC接続は、SQL文を流してちまちまと受け取る方法ですね。Oracleで埋め込み型のPL/SQLを使っているとカーソルを使ったり、フェッチを使ったりとややこしいことこの上ないのですが、C言語からモロ扱えるしろものです。今でも結構やります。

これじゃあ、大変という訳でRDO,DAO,ADOなんてのが出来ました。

主にVBやExcel VBAから使っていたもので、接続する方法をCOM/ActiveXなんかで扱えるようになっています。VB6ぐらいにはお世話になったものです。VB6自体は本格的なオブジェクト指向言語ではない（一部、クラスが使えますが）ので、C++で作成したActiveXを使うという形で、「オブジェクト」を扱うん出うのです。

これを VC++で使っていたりしました。

この最終形のADO（ActiveX Data Objects）の.NET版が、ADO.NETです。

最初は、ADOの移植版という形。

その中でDataSetというのが、データキャッシュの役割を持ちます。

おそらく当時、ASP.NETを作った関係で、WEBサイトのキャッシュをする必要があってのDataSetかなぁ、と。

最初、DataSetは型がない（列名を持たない）ので、アクセスするときのパフォーマンスが落ちました。また、C#のような厳密にメンバを指定したい場合なんかは、文字列で列名を書くのはちょっと…な感じでした（Row[“name”]みたいなの）。

これを、Tsample.name のようにプロパティ名にしたいなぁ、と思ったのが型付きDataSetの始まりです。

この型付の話は、当時、データベースとオブジェクト指向を繋げるという意味で、「O/Rマッピング」と言われています。今では、この手の自動生成ツール（Ruby on Railsとか）が主流になりましたが、その最初の頃ってな感じです。

O/Rマッピングが流行って、データベースからクラスのコードを自動生成してくるのに慣れてくると、一方で SQL 文をプログラマが書かなくなったんですね。自前のデータアクセス方法を提案し始めたというところです。

それが、LINQであったり、という訳で、LINQ to SQL なんかがあります。

LINQ自体は、データベースに限らないのですが、まあ、関数言語の良いとこ取りな感じで。

一方で、ADO.NET Data Entity というのがあって、これがDataSetの正式な子供ですね。Visual Studio でデータベースに接続して、データクラスを自動生成してってやり方です。

このような流れの中で、MySQL は

– ADO.NET, 型付きDataSet

– LINQ to SQL

– ADO.NET Data Entity

のところが使えません。厳密には型付きDataSetは使えるのですが、ちまちまと自前で作らないといけないので、メンテ上非常に大変（それを上回る保守コストならば別ですが）。

なので、MySQL+.NETという組み合わせでは、MySQLのConnectorを使って DataSet/DataTable を使うのが定番かと思います。

■DataSetの使いどころ

MSさんの宣伝記事ならば、

DataSetで、複数のデータ更新ができるのでデータベースに直結せずに、全てのCRUD処理をDataSetを媒介したほうが効率が良い。

と言いたいところなのですが、現場レベルとしては、ちょっと困るところが出てきます。

1)複数のテーブルが連携しているときの処理が分かりずらい。

2)大量のデータを更新中に、DataSet内で止まってしまう。

のようなお困りが発生します。

1)のほうは、マスターテーブルのような単純なテーブルに関しては DataSet/DataTable を使うと非常に早く更新ができるので楽です。

ですが、他のテーブルのリレーションがあった場合、同時更新などで別のキーを参照する場合の時は、コミットする前の場合、DataSet内のテーブルを探らないと駄目なので、この処理が非常に面倒です。なので、この場合は、トランザクションを付けて、直接データベースにアクセスしたほうが楽にコーディングができます。

2)のほうは、致命的なところがあって、データ量が非常に多くなる（1万件など）場合は、DataSet自体のメモリが大きくなると同時に、更新時にいちいちデータのチェックが走るので、結構重たい処理になってしまいます。この更新処理をDataSet内部のコードを弄って、分割したりするよりは、素直に更新時は直接データベースにアクセスにしに行ったほうがすっきりしたコードが書けます。

という訳で、実際はこんな風に使います。

ええ、なんか見たことがある矢印の向きですよね。

こんな風に、MVCスタイルにするとコードが見やすくなります。

つまり、コントローラ部分を分離させた方が、コードの見通しが良くなる、という予想が立ちます（実際、そうなります）。