ADO.NET入門記-006 (Insert,Update,Deleteの発行(DbCommand, DbParameter, TransactionScope))

前回は、Selectの発行をやってみましたので今回はInsert,Update,Deleteという所謂データの変更を行なう
SQLを発行してみます。

とはいっても、基本はSelectの時とほぼ同じです。
DbConnectionからDbCommandを作成し、CommandオブジェクトのCommandTextプロパティにSQLを設定し
後は発行するだけです。

Insert,Update,Deleteの際に使用するメソッドは以下のものです。

DbComannd.ExecuteNonQuery()

実行すると、影響があった行数が戻り値として返却されます。

後、今回はコマンドオブジェクトに対してパラメータを使用してコマンドを発行しています。
パラメータの設定方法は以下のようになります。

using(DbCommand command = conn.CreateCommand()){
    //
    // 発行するSQLにプレースホルダを設定
    // この場合だと、"@Name"という名前のプレースホルダが存在します。
    //
    command.CommandText = "insert into xxx (name) values (@Name)";

    //
    // パラメータの作成
    // 通常は、Commandオブジェクトからパラメータオブジェクトを作成します。
    //
    DbParameter param   = command.CreateParameter();
    // このパラメータオブジェクトが示すパラメータ名を設定
    param.ParameterName = "@Name";
    // このパラメータオブジェクトの値を設定
    param.Value         = "gsf_zero1";

    //
    // コマンドオブジェクトに上記のパラメータオブジェクトを追加.
    //
    // 複数のパラメータオブジェクトを一気に追加する場合は
    //     command.Parameters.AddRange(new DbParameter[]{param1, param2, param3});
    // 等とすると便利です。
    //
    command.Parameters.Add(param);

    //
    // 実行
    //
    command.ExecuteNonQuery();
}

また、データの追加・変更・削除時にはトランザクションがつきものですが
ADO.NET 2.0では、System.Transactions.TransactionScopeを利用する事により
簡単にトランザクション制御ができます。
TransactionScopeについては、予めMS-DTCサービスが起動していないと
使用できませんのでご注意を。MS-DTCサービスについては、http://d.hatena.ne.jp/gsf_zero1/20070612/p1
を参照してください。
以下のように使用します。

//
// 処理途中で例外などが発生し、Completeメソッドが呼ばれないまま
// usingブロックを抜けると自動的にロールバックされます。
//
using(TransactionScope tx = new TransactionScope()){
    //
    // 処理.
    //

    //
    // 処理が完了した事を通知します。つまりコミットと同じです。
    //
    tx.Complete();
}

今回、以下のテーブルが存在するとします。

create table AdoNetSample007Table(
     id   int identity primary key
    ,name varchar(50)  not null
);

以下サンプルです。Insertのテストでは100件の追加を、Updateでは特定の行を更新、Deleteでは
特定の行を削除しています。

using System;
using System.Data;
using System.Data.Common;
using System.Transactions;

using NUnit.Framework;

using Gsf.Lib.UnitTests;

namespace Gsf.Samples.AdoNet {

    [TestFixture()]
    public class AdoNetSample007 : TestBase{

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

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

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

                //
                // テーブル内の全行を削除
                //
                TruncateTable(conn);

                //
                // Insert処理
                //
                using(DbCommand command = conn.CreateCommand()){
                    //
                    // Insert用のコマンドオブジェクトを作成し、パラメータを設定.
                    //
                    command.CommandText = "insert into AdoNetSample007Table (name, inserted) values (@Name, @Inserted)";
                    
                    DbParameter nameParam   = command.CreateParameter();
                    nameParam.ParameterName = "@Name";

                    DbParameter insertedParam   = command.CreateParameter();
                    insertedParam.DbType        = DbType.DateTime;
                    insertedParam.ParameterName = "@Inserted";

                    command.Parameters.AddRange(new DbParameter{nameParam, insertedParam});

                    //
                    // Transactionを開始
                    //
                    int count = 0;
                    using(TransactionScope tx = new TransactionScope()){
                        //
                        // 100件Insertを行なう。
                        //
                        for(int i = 0; i < 100; i++){
                            //
                            // Parameterの値を設定.
                            //
                            command.Parameters["@Name"].Value     = string.Format("gsf_zero{0}", i);
                            command.Parameters["@Inserted"].Value = DateTime.Now;

                            count += command.ExecuteNonQuery();
                        }

                        tx.Complete();
                    }

                    Assert.AreEqual(100, count);
                }
            }
        }

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

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

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

                //
                // 削除対象の行を追加する
                //
                using(TransactionScope tx = new TransactionScope()){

                    InsertOneRow(conn, "delete_target_row");
                    tx.Complete();
                }
                
                //
                // 上記で追加した行に対してdeleteを行なう。
                //
                using(DbCommand command = conn.CreateCommand()){

                    command.CommandText = "delete from AdoNetSample007Table where name = @Name";

                    DbParameter nameParam   = command.CreateParameter();
                    nameParam.ParameterName = "@Name";
                    nameParam.Value         = "delete_target_row";

                    command.Parameters.Add(nameParam);

                    int count = 0;
                    using(TransactionScope tx = new TransactionScope()){
                        count += command.ExecuteNonQuery();

                        tx.Complete();
                    }
                    
                    Assert.AreEqual(1, count);
                }
            }
        }

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

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

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

                //
                // 更新対象の行を追加する
                //
                using(TransactionScope tx = new TransactionScope()) {

                    InsertOneRow(conn, "update_target_row");
                    tx.Complete();
                }

                //
                // 上記で追加した行に対してupdateを行なう。
                //
                using(DbCommand command = conn.CreateCommand()) {

                    command.CommandText = "update AdoNetSample007Table set name = @NewName, updated = @Updated where name = @Name";

                    DbParameter nameParam      = command.CreateParameter();
                    nameParam.ParameterName    = "@Name";
                    nameParam.Value            = "update_target_row";

                    DbParameter newNameParam   = command.CreateParameter();
                    newNameParam.ParameterName = "@NewName";
                    newNameParam.Value         = "updated";

                    DbParameter updatedParam   = command.CreateParameter();
                    updatedParam.DbType        = DbType.DateTime;
                    updatedParam.ParameterName = "@Updated";
                    updatedParam.Value         = DateTime.Now;

                    command.Parameters.AddRange(new DbParameter{nameParam, newNameParam, updatedParam});

                    int count = 0;
                    using(TransactionScope tx = new TransactionScope()) {
                        count += command.ExecuteNonQuery();

                        tx.Complete();
                    }

                    Assert.AreEqual(1, count);
                }
            }
        }

        private void TruncateTable(DbConnection conn){

            using(DbCommand command = conn.CreateCommand()){
                command.CommandText = "truncate table AdoNetSample007Table";
                command.ExecuteNonQuery();
            }
        }

        private void InsertOneRow(DbConnection conn, string targetName){

            using(DbCommand command = conn.CreateCommand()){
                command.CommandText = string.Format("insert into AdoNetSample007Table (name) values ('{0}')", targetName);
                command.ExecuteNonQuery();
            }
        }
    }
}