C#からMySQLを扱う(更新編)
射撃しながら前進(あるいは迷走)する方への援護射撃。第2弾です。
MySQLでも、SQL Serverでも、Oracleでも、データをグリッドで表示する場合は、DataSetを使うのが断然楽です。
顧客が、グリッドのチープな画面を許容してくださるならば、グリッドでOKでしょう。
1 2 3 4 5 6 7 8 9 10 | // コネクション作成 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 で書く場合は、
1 2 3 4 5 6 7 8 9 10 | // コネクション作成 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は複数のテーブルを扱うのですが、普通は上の例のように、ひとつのテーブル(検索結果)しか扱いません。
なので、
1 | dataGridView1.DataSource = ds; |
とした場合は、最初のテーブルを使う、という仕様になっているんですね。
これが、ADO.NET のバージョンで DataTable ってのが出てきました。所詮、ひとつのテーブルしか扱わないのですから、DataSetの部分は無駄、という考えです。
なので、DataSetとDataTableは、同じように扱って大丈夫です。
ちなみに、DataSetから最初のテーブルを取り出す場合は、
1 | dataGridView1.DataSource = ds.Tables[0]; |
と書きます。
余談ですが、DataSetに複数のテーブルを使う場合は、
1 2 3 4 5 6 | 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クラスを使ったのが次の例です。
1 2 3 4 5 6 7 8 9 10 11 | 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 文を実行する場合は、こんな風になります。
1 2 3 4 5 6 7 8 9 10 | 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メソッドが使われます。
1 2 3 4 | MySqlCommand cmd = new MySqlCommand( string .Format( "insert into sample values ({0},'{1}',{2},{3})" , id, name, url, date)); |
変数を使って、整形をするパターンですが、これには重大な欠点があります。
・文字列の場合は「’」を使って囲まないといけない。忘れるとエラーになる。
・nullが指定できない。
・SQLインジェクションが発生する。
SQLインジェクションの問題もそうですが、文字列を意識しないといけなかったり、nullが指定できないのは致命的です。なにより、日付型(DateTime型)の指定が非常に困難です。
なので、MySqlCommandクラスでパラメータを指定するのがベターです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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オブジェクトで値を指定します。
このようにパラメータを使うと、先の3点が一遍に解決できますし、さらに、日付型のようなちょっと指定の難しい型も簡単に設定できます。
# 本来は、ストアドプロシージャを使うのが筋なのですが、SQL文の埋め込みとストアドプロシージャは「配置」が異なるので、一概に交換できるとは言えません。なので、プロジェクトごとに、SQL文の埋め込みにするか、ストアドプロシージャにするかを考える必要があります。
■データを追加で自動採番のIDを使う場合
さて、INSERT文は書けるようになりましたが、自動でIDを振っているときはどうするのか、が問題です。
これは普通に次のように書きます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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()」を使うので、
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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)を入れてやれば取得できます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 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
削除も同じように書けます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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
更新も同じように書きます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 しか使えないので。
手順としては、
・追加したら検索
・削除したら検索
・更新したら検索
にします。
具体的には、
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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
の2つを表示している場合には、
1 2 3 4 5 6 7 | 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に保存せずに、一回ずつ検索したほうが良いし、表示形式そのものを考え直す必要があります。
おぉぉww感謝。
ちょうど迷走していたです。
これは感謝。
早速実際に書いて検証してみるです。
このあたりは、「瞑想」・・・じゃなかった「迷走」する人が多いので、ご注意を、ってな感じです。
宣伝記事に踊らされずに、
・地道に DataSet/DataTable、MySqlCommand でちまちま。
・これらを、自前のDAO(Data Access Object)クラスにまとめて、呼び出しルールを決める。
って、流れでやると、データ(モデル)まわりはうまく動きます(プロジェクト的にも)
DataSet/DataTable の利用パターンは、今晩にでもUp予定。
// オープン
cmd.Connection.Open();
これいらないだろ!
いるよw
—
MySQL :: MySQL 5.1 リファレンスマニュアル :: 24.2.3.1 MySqlCommand の使用
http://ftp.nchu.edu.tw/MySQL/doc/refman/5.1/ja/connector-net-examples-mysqlcommand.html
以下のサンプルコードでcloseがcloneになってます
■データを追加で自動採番のIDを使う場合 ←1か所
■データを追加 INSERT ←2か所
Thx です。直しておきました。