ADO.NET入門記-009 (DataAdapterの使用(Insert,Update,Delete) (AcceptChanges, DbDataAdapter, Fill, Update, InsertCommand, UpdateCommand, DeleteCommand))

引き続き、データアダプタに関しての記事です。
今回は、変更系の処理を行なってみます。

変更系となると、Insert, Update, Deleteの３つが存在するのですが、
これらもSelectの場合と同じく、コマンドオブジェクトを作成し、データアダプタにセットします。

その際、注意しなければならないのは次の点です。

1. あるパラメータの値がどのデータテーブルの値とマッピングされているか？
2. あるパラメータの値がデータテーブルの該当する列のどのバージョンの値を使用するか？

どちらも、DbParameterクラスに定義されているプロパティで設定します。
１に関しては、

DbParameter.SourceColumn

で指定します。
２に関しては、

DbParameter.SourceVersion

で指定します。指定する値は、DataRowVersion列挙体のどれかです。
通常、CurrentもしくはOriginalを指定します。

纏めると以下のようになります。

• Insertの場合は、SourceColumnプロパティの指定が必須。
• Update,Deleteの場合は、SourceColumn,SourceVersionの指定が必須。

以下サンプルです。今回使用するテーブル及びテーブルデータに関しては
前回の記事で使用しているものと同じです。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Transactions;

using NUnit.Framework;

using Gsf.Lib.UnitTests;

namespace Gsf.Samples.AdoNet {

    [TestFixture()]
    public class AdoNetSample010 : TestBase{

        [Test()]
        public void Insertを行なってみる(){

            using(TransactionScope tx = new TransactionScope()){

                DbProviderFactory factory = DbProviderFactories.GetFactory(_settings.ProviderName);
                using(DbConnection conn = factory.CreateConnection()){
                    conn.ConnectionString = _settings.ConnectionString;
                    conn.Open();

                    DbCommand selectCommand = conn.CreateCommand();
                    DbCommand insertCommand = conn.CreateCommand();

                    selectCommand.CommandText = "select id, name, age from AdoNetSample009Table";
                    insertCommand.CommandText = "insert into AdoNetSample009Table (id, name, age) values (@Id, @Name, @Age)";

                    //
                    // Insertコマンドに指定するパラメータを設定する。
                    // SourceColumnプロパティを必ず設定する事！
                    // これが無いと、追加時にDataAdapterがパラメータとデータテーブルのカラムを紐付けられないため
                    // エラーとなってしまう。
                    //
                    DbParameter param = insertCommand.CreateParameter();
                    param.ParameterName = "@Id";
                    param.DbType        = DbType.Int32;
                    param.SourceColumn  = "id";
                    insertCommand.Parameters.Add(param);

                    param = insertCommand.CreateParameter();
                    param.ParameterName = "@Name";
                    param.SourceColumn  = "name";
                    insertCommand.Parameters.Add(param);

                    param = insertCommand.CreateParameter();
                    param.ParameterName = "@Age";
                    param.DbType        = DbType.Int32;
                    param.SourceColumn  = "age";
                    insertCommand.Parameters.Add(param);

                    using(DbDataAdapter adapter = factory.CreateDataAdapter()){
                        adapter.SelectCommand = selectCommand;
                        adapter.InsertCommand = insertCommand;

                        //
                        // 追加前のデータを取り込む.
                        //
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        foreach(DataRow row in table.Rows){
                            Console.WriteLine(string.Format("[追加前] id:{0}, name:{1}, age:{2}", row["id"], row["name"], row["age"]));
                        }

                        //
                        // データテーブルに行を追加し、Updateメソッドをコールする。
                        //
                        DataRow newRow = table.NewRow();
                        newRow["id"]   = GetNextId(conn);
                        newRow["name"] = "gsf_zero1";
                        newRow["age"]  = 32;
                        table.Rows.Add(newRow);

                        Assert.AreEqual(1, adapter.Update(table));

                        //
                        // トランザクションをコミット.
                        //
                        tx.Complete();

                        //
                        // もう一度データを取り直す
                        //
                        Console.WriteLine("");
                        table.Clear();
                        adapter.Fill(table);

                        foreach(DataRow row in table.Rows) {
                            Console.WriteLine(string.Format("[追加後] id:{0}, name:{1}, age:{2}", row["id"], row["name"], row["age"]));
                        }

                    }

                }

            }

        }

        [Test()]
        public void Updateを行なってみる(){

            using(TransactionScope tx = new TransactionScope()){

                DbProviderFactory factory = DbProviderFactories.GetFactory(_settings.ProviderName);
                using(DbConnection conn = factory.CreateConnection()){

                    conn.ConnectionString = _settings.ConnectionString;
                    conn.Open();

                    DbCommand selectCommand = conn.CreateCommand();
                    DbCommand updateCommand = conn.CreateCommand();

                    selectCommand.CommandText = "select id, name, age from AdoNetSample009Table";
                    updateCommand.CommandText = "update AdoNetSample009Table set name = @Name, age = @Age where id = @IdOriginal";

                    //
                    // UpdateCommandのパラメータの設定を行なう。
                    // 以下のプロパティの値は必ず設定しないといけない。でないと更新時にアダプターが
                    // どのカラムの値にどの値を設定すればいいのかわからなくなるため。
                    //
                    //     ・SourceColumnプロパティ ：データテーブル側の対応するカラムの値
                    //     ・SourceVersionプロパティ：更新・削除の場合は、元の値を検索条件として指定する必要がある。
                    //                                現在最新の値は、Currentで、元の値はOriginalで設定する。
                    //
                    DbParameter param = updateCommand.CreateParameter();
                    param.ParameterName = "@Name";
                    param.SourceColumn  = "name";
                    param.SourceVersion = DataRowVersion.Current;
                    updateCommand.Parameters.Add(param);

                    param = updateCommand.CreateParameter();
                    param.ParameterName = "@Age";
                    param.DbType        = DbType.Int32;
                    param.SourceColumn  = "age";
                    param.SourceVersion = DataRowVersion.Current;
                    updateCommand.Parameters.Add(param);

                    param = updateCommand.CreateParameter();
                    param.ParameterName = "@IdOriginal";
                    param.DbType        = DbType.Int32;
                    param.SourceColumn  = "id";
                    param.SourceVersion = DataRowVersion.Original;
                    updateCommand.Parameters.Add(param);

                    using(DbDataAdapter adapter = factory.CreateDataAdapter()){
                        adapter.SelectCommand = selectCommand;
                        adapter.UpdateCommand = updateCommand;

                        //
                        // 更新前のデータを取り込む.
                        // 
                        // なお、後で主キーを元に検索を行う為、ここでデータベーステーブルのスキーマを
                        // 取得しておく。(FillSchemeメソッド)
                        //
                        DataTable table = new DataTable();
                        adapter.FillSchema(table, SchemaType.Source);
                        adapter.Fill(table);

                        foreach(DataRow row in table.Rows) {
                            Console.WriteLine(string.Format("[更新前] id:{0}, name:{1}, age:{2}", row["id"], row["name"], row["age"]));
                        }

                        //
                        // IDが３のデータを更新する。
                        //
                        DataRow updateRow = table.Rows.Find(3);
                        updateRow["name"] = "updated_name";
                        updateRow["age"]  = 99;

                        Assert.AreEqual(1, adapter.Update(table));

                        // 実際にデータが更新されてしまうと面倒なので、データベース側はロールバックさせる。
                        //tx.Complete();

                        //
                        // データテーブルに対して、変更確定処理を行なう。
                        //
                        table.AcceptChanges();

                        //
                        // 更新後のデータを表示.
                        //
                        Console.WriteLine("");

                        foreach(DataRow row in table.Rows) {
                            Console.WriteLine(string.Format("[更新後] id:{0}, name:{1}, age:{2}", row["id"], row["name"], row["age"]));
                        }

                    }

                }

            }
            
        }


        [Test()]
        public void Deleteを行なってみる(){

            using(TransactionScope tx = new TransactionScope()){

                DbProviderFactory factory = DbProviderFactories.GetFactory(_settings.ProviderName);
                using(DbConnection conn = factory.CreateConnection()){

                    conn.ConnectionString = _settings.ConnectionString;
                    conn.Open();

                    DbCommand selectCommand = conn.CreateCommand();
                    DbCommand deleteCommand = conn.CreateCommand();

                    selectCommand.CommandText = "select id, name, age from AdoNetSample009Table";
                    deleteCommand.CommandText = "delete from AdoNetSample009Table where name = @NameOriginal and age = @AgeOriginal";

                    //
                    // DeleteCommandのパラメータの設定を行なう。
                    // 基本的には、UpdateCommandの場合と同じ。
                    // 以下のプロパティの値は必ず設定しないといけない。でないと更新時にアダプターが
                    // どのカラムの値にどの値を設定すればいいのかわからなくなるため。
                    //
                    //     ・SourceColumnプロパティ ：データテーブル側の対応するカラムの値
                    //     ・SourceVersionプロパティ：更新・削除の場合は、元の値を検索条件として指定する必要がある。
                    //                                現在最新の値は、Currentで、元の値はOriginalで設定する。
                    //
                    DbParameter param = deleteCommand.CreateParameter();
                    param.ParameterName = "@NameOriginal";
                    param.SourceColumn  = "name";
                    param.SourceVersion = DataRowVersion.Original;
                    deleteCommand.Parameters.Add(param);

                    param = deleteCommand.CreateParameter();
                    param.ParameterName = "@AgeOriginal";
                    param.SourceColumn  = "age";
                    param.SourceVersion = DataRowVersion.Original;
                    deleteCommand.Parameters.Add(param);

                    using(DbDataAdapter adapter = factory.CreateDataAdapter()){
                        adapter.SelectCommand = selectCommand;
                        adapter.DeleteCommand = deleteCommand;

                        //
                        // 削除前のデータを取り込む.
                        //
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        foreach(DataRow row in table.Rows) {
                            Console.WriteLine(string.Format("[削除前] id:{0}, name:{1}, age:{2}", row["id"], row["name"], row["age"]));
                        }

                        //
                        // Nameがgsf_zero5でAgeが31のデータを削除する。
                        // (本来は、エスケープ処理を行なうべきであるが今回は割愛する。)
                        //
                        foreach(DataRow row in table.Select(string.Format("name = '{0}' and age = {1}", "gsf_zero5", 31))) {
                            row.Delete();
                        }

                        Assert.AreEqual(1, adapter.Update(table));

                        // 実際にデータが消えてしまうと面倒なので、データベース側はロールバックする。
                        //tx.Complete();

                        //
                        // 元のデータテーブルに対して、変更確定処理を行なう。
                        //
                        table.AcceptChanges();

                        //
                        // 削除後のデータを表示.
                        //
                        Console.WriteLine("");

                        foreach(DataRow row in table.Rows) {
                            Console.WriteLine(string.Format("[削除後] id:{0}, name:{1}, age:{2}", row["id"], row["name"], row["age"]));
                        }

                    }

                }

            }

        }

        private int GetNextId(DbConnection conn){

            int result = int.MinValue;

            using(DbCommand command = conn.CreateCommand()){
                command.CommandText = "select max(id) from AdoNetSample009Table";

                result = int.Parse(command.ExecuteScalar().ToString());
            }

            return (result + 1);
        }
    }
}

上記のサンプルを実行する以下のように表示されます。

[追加前] id:1, name:gsf_zero1, age:27
[追加前] id:2, name:gsf_zero2, age:28
[追加前] id:3, name:gsf_zero3, age:29
[追加前] id:4, name:gsf_zero4, age:30
[追加前] id:5, name:gsf_zero5, age:31
[追加前] id:6, name:gsf_zero1, age:32
[追加前] id:7, name:gsf_zero1, age:32
[追加前] id:8, name:gsf_zero1, age:32
[追加前] id:9, name:gsf_zero1, age:32

[追加後] id:1, name:gsf_zero1, age:27
[追加後] id:2, name:gsf_zero2, age:28
[追加後] id:3, name:gsf_zero3, age:29
[追加後] id:4, name:gsf_zero4, age:30
[追加後] id:5, name:gsf_zero5, age:31
[追加後] id:6, name:gsf_zero1, age:32
[追加後] id:7, name:gsf_zero1, age:32
[追加後] id:8, name:gsf_zero1, age:32
[追加後] id:9, name:gsf_zero1, age:32
[追加後] id:10, name:gsf_zero1, age:32


[更新前] id:1, name:gsf_zero1, age:27
[更新前] id:2, name:gsf_zero2, age:28
[更新前] id:3, name:gsf_zero3, age:29
[更新前] id:4, name:gsf_zero4, age:30
[更新前] id:5, name:gsf_zero5, age:31
[更新前] id:6, name:gsf_zero1, age:32
[更新前] id:7, name:gsf_zero1, age:32
[更新前] id:8, name:gsf_zero1, age:32
[更新前] id:9, name:gsf_zero1, age:32
[更新前] id:10, name:gsf_zero1, age:32

[更新後] id:1, name:gsf_zero1, age:27
[更新後] id:2, name:gsf_zero2, age:28
[更新後] id:3, name:updated_name, age:99
[更新後] id:4, name:gsf_zero4, age:30
[更新後] id:5, name:gsf_zero5, age:31
[更新後] id:6, name:gsf_zero1, age:32
[更新後] id:7, name:gsf_zero1, age:32
[更新後] id:8, name:gsf_zero1, age:32
[更新後] id:9, name:gsf_zero1, age:32
[更新後] id:10, name:gsf_zero1, age:32


[削除前] id:1, name:gsf_zero1, age:27
[削除前] id:2, name:gsf_zero2, age:28
[削除前] id:3, name:gsf_zero3, age:29
[削除前] id:4, name:gsf_zero4, age:30
[削除前] id:5, name:gsf_zero5, age:31
[削除前] id:6, name:gsf_zero1, age:32
[削除前] id:7, name:gsf_zero1, age:32
[削除前] id:8, name:gsf_zero1, age:32
[削除前] id:9, name:gsf_zero1, age:32
[削除前] id:10, name:gsf_zero1, age:32

[削除後] id:1, name:gsf_zero1, age:27
[削除後] id:2, name:gsf_zero2, age:28
[削除後] id:3, name:gsf_zero3, age:29
[削除後] id:4, name:gsf_zero4, age:30
[削除後] id:6, name:gsf_zero1, age:32
[削除後] id:7, name:gsf_zero1, age:32
[削除後] id:8, name:gsf_zero1, age:32
[削除後] id:9, name:gsf_zero1, age:32
[削除後] id:10, name:gsf_zero1, age:32

しかし、面倒ですね・・。
でも、実際はここまで手動で組み立てる必要はあまりなくて、もう少し楽なやり方がありますので、それを次回に
書こうとおもいます。