2012年4月5日木曜日

DataTableのデータをDBに保存する際、エラーが発生してRollbackしたときの問題

0 コメント
ADO.NETのDataTableとDataAdapterを使って、DataTableのデータをデータベースに書き込むとき、途中でエラーが発生してロールバックすると、DataTableのDataRowの一部(更新処理がうまくいったDataRow)のRowStateがUnchangedになってしまう場合がある。

サンプルを、SQLiteを使って試してみた。

テスト用に、sample_tableという名前のテーブルを
CREATE TABLE sample_table
(
    id          INTEGER NOT NULL PRIMARY KEY
  , value       TEXT NOT NULL
  , update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))
);
上記のように作成。
ここに、
idvalueupdate_date
1ABCD2012-04-05 18:05:56
2EFGH2012-04-05 18:05:56
3IJKL2012-04-05 18:05:56
というデータを予め入れておく。
このテーブルをDataTableに取り込み、
idvalueupdate_date
1ABCDDATE('now','localtime')
2EFGHDATE('now','localtime')
3MNOPDATE('now','localtime')
4NULLDATE('now','localtime')
id=3の行のvalueを'MNOP'に書き換え、id=4の行をvalue=NULLで追加する。
※value列はNOT NULLとしているので、id=4の行の追加はエラーとなる。
DataTableのデータをデータベースに書きこむとき、id=4の行の追加でエラーが発生するのでロールバックする。
このときのDataTableのRowStateをチェックするというサンプルを用意した。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SQLiteSample1
{
    class Program
    {
        static private SQLiteConnection m_conn;

        static void Main(string[] args)
        {
            //////////////////////////////////////////////// 準備
            // データベース接続
            string dbfile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), "dbfile.db");
            m_conn = new SQLiteConnection("Data Source=" + dbfile);
            m_conn.Open();

            // テーブルの有無を確認
            bool exists = false;
            SQLiteCommand existsCommand = new SQLiteCommand("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='sample_table';", m_conn);
            object existsResult = existsCommand.ExecuteScalar();
            try
            {
                if (int.Parse(existsResult.ToString()) > 0)
                {
                    exists = true;
                }
            }
            catch
            {
            }

            // テーブル生成
            if (exists == false)
            {
                SQLiteCommand createCommand = new SQLiteCommand
                (
                      "CREATE TABLE sample_table ("
                    +  " id INTEGER NOT NULL PRIMARY KEY"
                    + ", value TEXT NOT NULL"
                    + ", update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))"
                    + ");"
                    , m_conn
                );
                createCommand.ExecuteNonQuery();

                // データ生成
                SQLiteCommand insertCommand;
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (1, 'ABCD');", m_conn);
                insertCommand.ExecuteNonQuery();
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (2, 'EFGH');", m_conn);
                insertCommand.ExecuteNonQuery();
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (3, 'IJKL');", m_conn);
                insertCommand.ExecuteNonQuery();
            }

            // データベース切断
            m_conn.Close();


            //////////////////////////////////////////////// テスト
            // データベース接続
            m_conn.Open();

            // データテーブル生成
            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("id"   , typeof(int)   ));
            table.Columns.Add(new DataColumn("value", typeof(string)));
            table.PrimaryKey = new DataColumn[] { table.Columns["id"] };

            // DataAdapter生成
            SQLiteDataAdapter da = new SQLiteDataAdapter();
            // SelectCommand
            da.SelectCommand = new SQLiteCommand("SELECT id, value, update_date FROM sample_table", m_conn);
            // InsertCommand
            da.InsertCommand = new SQLiteCommand("INSERT INTO sample_table(id, value) VALUES (@id, @value);", m_conn);
            da.InsertCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Current, DBNull.Value));
            da.InsertCommand.Parameters.Add(new SQLiteParameter("value", DbType.String, 0, ParameterDirection.Input, false, 0, 0, "value", DataRowVersion.Current, DBNull.Value));
            // UpdateCommand
            da.UpdateCommand = new SQLiteCommand("UPDATE sample_table SET value=@value, update_date=(DATETIME('now','localtime')) WHERE id=@id;", m_conn);
            da.UpdateCommand.Parameters.Add(new SQLiteParameter("value", DbType.String, 0, ParameterDirection.Input, false, 0, 0, "value", DataRowVersion.Current , DBNull.Value));
            da.UpdateCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Original, DBNull.Value));
            // DeleteCommand
            da.DeleteCommand = new SQLiteCommand("DELETE FROM sample_table WHERE id=@id;", m_conn);
            da.DeleteCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Original, DBNull.Value));
            // RowUpdated
            da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(da_RowUpdated);

            // Fill
            da.Fill(table);
            
            // データ変更
            DataRow updateRow = table.Rows.Find(3);
            updateRow["value"] = "MNOP";
            // value列がNULLの行を追加...Updateでエラーになる
            DataRow newRow = table.NewRow();
            newRow["id"   ] = 4;
            newRow["value"] = DBNull.Value;
            table.Rows.Add(newRow);

            Console.WriteLine("保存前");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }

            // 保存
            SQLiteTransaction tran = m_conn.BeginTransaction();
            try
            {
                da.Update(table);
                tran.Commit();
                Console.WriteLine("保存成功");
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("保存失敗");
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("保存後");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }

            // id=4のvalueをセットして再度保存
            DataRow row4 = table.Rows.Find(4);
            row4["value"] = "QRST";
            tran = m_conn.BeginTransaction();
            try
            {
                da.Update(table);
                tran.Commit();
                Console.WriteLine("保存成功");
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("保存失敗");
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("保存後");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }
            Console.ReadKey();

            // データベース切断
            m_conn.Close();
        }

        static void da_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
        {
            if (e.Status == UpdateStatus.Continue)
            {
                if ((e.StatementType == StatementType.Insert) || (e.StatementType == StatementType.Update))
                {
                    // update_dateの取得
                    SQLiteCommand cmd = new SQLiteCommand("SELECT update_date FROM sample_table WHERE id=@id", m_conn);
                    SQLiteParameter param = new SQLiteParameter("id", DbType.Int32, 0, "id", DataRowVersion.Original);
                    param.Value = e.Row["id"];
                    cmd.Parameters.Add(param);
                    try
                    {
                        e.Row["update_date"] = cmd.ExecuteScalar();
                        e.Row.AcceptChanges();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }
    }
}
これを実行すると
保存前
1 : Unchanged
2 : Unchanged
3 : Modified
4 : Added
保存失敗
Abort due to constraint violation
sample_table.value may not be NULL
保存後
1 : Unchanged
2 : Unchanged
3 : Unchanged
4 : Added
保存成功
保存後
1 : Unchanged
2 : Unchanged
3 : Unchanged
4 : Unchanged
となり、この時のデータベースのテーブルの内容は、
最初のda.Update(table)(失敗する)のあとでは
idvalueupdate_date
1ABCD2012-04-05 18:05:56
2EFGH2012-04-05 18:05:56
3IJKL2012-04-05 18:05:56
と、ロールバックしているので当然初期の状態と何も変わらない。
DataTableの内容は
idvalueupdate_dateRowState
1ABCD2012-04-05 18:05:56Unchanged
2EFGH2012-04-05 18:05:56Unchanged
3MNOP2012-04-05 18:05:56Unchanged
4QRSTNULLAdded
と、DataTableのid=3のDataRowのRowStateはUnchangedになり、エラーの原因となるid=4のDataRowのRowStateはAddedのままとなっている。
次に、id=4のvalueに文字列をセットした後の2回目のda.Update(table)のあとのデータベースのテーブルは
idvalueupdate_date
1ABCD2012-04-05 18:05:56
2EFGH2012-04-05 18:05:56
3IJKL2012-04-05 18:05:56
4QRST2012-04-05 18:07:27
となっている。
このとき、id=4のDataRowが保存されるので、このDataRowのRowStateがUnchangedに変わり、すべての行がUnchangedとなる。
このとき、DataTableの中身は
idvalueupdate_dateRowState
1ABCD2012-04-05 18:05:56Unchanged
2EFGH2012-04-05 18:05:56Unchanged
3MNOP2012-04-05 18:05:56Unchanged
4QRST2012-04-05 18:07:27Unchanged
となっていて、実際のデータベース上のテーブルと食い違いが発生している。

このサンプルプログラムでは、2回続けて保存したが、例えばこのDataTableがDataGridViewで編集されるようなFormアプリケーションだった場合、エラー発生後に利用者がid=4のvalueに値をセットして、再度保存処理をやっても、id=3の行のvalueはデータベースには反映されない。
※しかも、Form上でid=3のvalueは修正した内容になっているため、利用者側にはid=3のvalueもデータベースに正しく書きこまれたかのように見えてしまう。

データベースへの保存の際、エラーが発生してロールバックするとき、DataTableの各行のRowStateも元の状態に戻せれば問題を解決できる。
方法として、DataTableの変更した行のみを取り出して、別のDataTableを生成し、そのDataTableを使って保存処理を行う。
保存が失敗した場合、元のDataTableの各行のRowStateは何も影響を受けていないため、保存処理前の状態になっている。
保存が成功した場合、コピーしたDataTableの内容と各行のRowStateを元のテーブルに反映させる。

具体的には、元のソースの
            // 保存
            SQLiteTransaction tran = m_conn.BeginTransaction();
            try
            {
                da.Update(table);
                tran.Commit();
                Console.WriteLine("保存成功");
            }
上記部分(2ヶ所)を、
            // 保存
            SQLiteTransaction tran = m_conn.BeginTransaction();
            try
            {
                DataTable tempTable = table.GetChanges();
                da.Update(tempTable);
                tran.Commit();
                Console.WriteLine("保存成功");
                table.Merge(tempTable);
                foreach (DataRow row in tempTable.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                    {
                        DataRow orgRow = table.Rows.Find(row["id"]);
                        if (orgRow != null)
                        {
                            orgRow.AcceptChanges();
                        }
                    }
                }
            }
のように修正する。

DataTable tempTable = table.GetChanges();で、元DataTableの変更された行のみをコピーしたDataTableを生成する。
データベースへの保存もこのコピーしたDataTableを使ってda.Update(tempTable);とする。
保存が成功した場合、table.Merge(tempTable);でコピーしたDataTableの内容を元のDataTableに取り込む。
ただし、まだ元のDataTableの各行のRowStateはModifiedやAddedのままなので、コピーしたDataTableの行に対応する元のDataTableのDataRowを見つけ、AcceptChanges()で、Unchangedにする。
※このサンプルならtable.AcceptChanged();でも良い。

変更したプログラムを実行すると
保存前
1 : Unchanged
2 : Unchanged
3 : Modified
4 : Added
保存失敗
Abort due to constraint violation
sample_table.value may not be NULL
保存後
1 : Unchanged
2 : Unchanged
3 : Modified
4 : Added
保存成功
保存後
1 : Unchanged
2 : Unchanged
3 : Unchanged
4 : Unchanged
となり、最初の保存で失敗したあとのロールバック後もDataTableの各行のRowStateは元のままになっている。
この時のデータベースのテーブルの内容は、
最初のda.Update(table)(失敗する)のあとでは
idvalueupdate_date
1ABCD2012-04-05 18:28:23
2EFGH2012-04-05 18:28:23
3IJKL2012-04-05 18:28:23
と、ロールバックしているので当然初期の状態と何も変わらない。
DataTableの内容は
idvalueupdate_dateRowState
1ABCD2012-04-05 18:05:56Unchanged
2EFGH2012-04-05 18:05:56Unchanged
3MNOP2012-04-05 18:05:56Modified
4QRSTNULLAdded
となっている。
次に、id=4のvalueに文字列をセットした後の2回目のda.Update(table)のあとのデータベースのテーブルは
idvalueupdate_date
1ABCD2012-04-05 18:28:23
2EFGH2012-04-05 18:28:23
3MNOP2012-04-05 18:28:27
4QRST2012-04-05 18:28:27
となっている。
このとき、DataTableの中身は
idvalueupdate_dateRowState
1ABCD2012-04-05 18:28:23Unchanged
2EFGH2012-04-05 18:28:23Unchanged
3MNOP2012-04-05 18:28:27Unchanged
4QRST2012-04-05 18:28:27Unchanged
となっていて、実際のデータベース上のテーブルと一致している。


修正版のソースを載せておきます。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SQLiteSample1
{
    class Program
    {
        static private SQLiteConnection m_conn;

        static void Main(string[] args)
        {
            //////////////////////////////////////////////// 準備
            // データベース接続
            string dbfile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), "dbfile.db");
            m_conn = new SQLiteConnection("Data Source=" + dbfile);
            m_conn.Open();

            // テーブルの有無を確認
            bool exists = false;
            SQLiteCommand existsCommand = new SQLiteCommand("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='sample_table';", m_conn);
            object existsResult = existsCommand.ExecuteScalar();
            try
            {
                if (int.Parse(existsResult.ToString()) > 0)
                {
                    exists = true;
                }
            }
            catch
            {
            }

            // テーブル生成
            if (exists == false)
            {
                SQLiteCommand createCommand = new SQLiteCommand
                (
                      "CREATE TABLE sample_table ("
                    +  " id INTEGER NOT NULL PRIMARY KEY"
                    + ", value TEXT NOT NULL"
                    + ", update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))"
                    + ");"
                    , m_conn
                );
                createCommand.ExecuteNonQuery();

                // データ生成
                SQLiteCommand insertCommand;
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (1, 'ABCD');", m_conn);
                insertCommand.ExecuteNonQuery();
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (2, 'EFGH');", m_conn);
                insertCommand.ExecuteNonQuery();
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (3, 'IJKL');", m_conn);
                insertCommand.ExecuteNonQuery();
            }

            // データベース切断
            m_conn.Close();


            //////////////////////////////////////////////// テスト
            // データベース接続
            m_conn.Open();

            // データテーブル生成
            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("id"   , typeof(int)   ));
            table.Columns.Add(new DataColumn("value", typeof(string)));
            table.PrimaryKey = new DataColumn[] { table.Columns["id"] };

            // DataAdapter生成
            SQLiteDataAdapter da = new SQLiteDataAdapter();
            // SelectCommand
            da.SelectCommand = new SQLiteCommand("SELECT id, value, update_date FROM sample_table", m_conn);
            // InsertCommand
            da.InsertCommand = new SQLiteCommand("INSERT INTO sample_table(id, value) VALUES (@id, @value);", m_conn);
            da.InsertCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Current, DBNull.Value));
            da.InsertCommand.Parameters.Add(new SQLiteParameter("value", DbType.String, 0, ParameterDirection.Input, false, 0, 0, "value", DataRowVersion.Current, DBNull.Value));
            // UpdateCommand
            da.UpdateCommand = new SQLiteCommand("UPDATE sample_table SET value=@value, update_date=(DATETIME('now','localtime')) WHERE id=@id;", m_conn);
            da.UpdateCommand.Parameters.Add(new SQLiteParameter("value", DbType.String, 0, ParameterDirection.Input, false, 0, 0, "value", DataRowVersion.Current , DBNull.Value));
            da.UpdateCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Original, DBNull.Value));
            // DeleteCommand
            da.DeleteCommand = new SQLiteCommand("DELETE FROM sample_table WHERE id=@id;", m_conn);
            da.DeleteCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Original, DBNull.Value));
            // RowUpdated
            da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(da_RowUpdated);

            // Fill
            da.Fill(table);
            
            // データ変更
            DataRow updateRow = table.Rows.Find(3);
            updateRow["value"] = "MNOP";
            // value列がNULLの行を追加...Updateでエラーになる
            DataRow newRow = table.NewRow();
            newRow["id"   ] = 4;
            newRow["value"] = DBNull.Value;
            table.Rows.Add(newRow);

            Console.WriteLine("保存前");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }

            // 保存
            SQLiteTransaction tran = m_conn.BeginTransaction();
            try
            {
                DataTable tempTable = table.GetChanges();
                da.Update(tempTable);
                tran.Commit();
                Console.WriteLine("保存成功");
                table.Merge(tempTable);
                foreach (DataRow row in tempTable.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                    {
                        DataRow orgRow = table.Rows.Find(row["id"]);
                        if (orgRow != null)
                        {
                            orgRow.AcceptChanges();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("保存失敗");
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("保存後");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }

            // id=4のvalueをセットして再度保存
            DataRow row4 = table.Rows.Find(4);
            row4["value"] = "QRST";
            tran = m_conn.BeginTransaction();
            try
            {
                DataTable tempTable = table.GetChanges();
                da.Update(tempTable);
                tran.Commit();
                Console.WriteLine("保存成功");
                table.Merge(tempTable);
                foreach (DataRow row in tempTable.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                    {
                        DataRow orgRow = table.Rows.Find(row["id"]);
                        if (orgRow != null)
                        {
                            orgRow.AcceptChanges();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("保存失敗");
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("保存後");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }
            Console.ReadKey();

            // データベース切断
            m_conn.Close();
        }

        static void da_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
        {
            if (e.Status == UpdateStatus.Continue)
            {
                if ((e.StatementType == StatementType.Insert) || (e.StatementType == StatementType.Update))
                {
                    // update_dateの取得
                    SQLiteCommand cmd = new SQLiteCommand("SELECT update_date FROM sample_table WHERE id=@id", m_conn);
                    SQLiteParameter param = new SQLiteParameter("id", DbType.Int32, 0, "id", DataRowVersion.Original);
                    param.Value = e.Row["id"];
                    cmd.Parameters.Add(param);
                    try
                    {
                        e.Row["update_date"] = cmd.ExecuteScalar();
                        e.Row.AcceptChanges();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }
    }
}

SQLiteでTIMESTAMP列のデフォルト値のタイムゾーンをJSTにする

0 コメント
SQLite3で次のようなテーブルを作った。
CREATE TABLE sample_table
(
    id          INTEGER   NOT NULL PRIMARY KEY
  , value       TEXT,
  , update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
すると、update_dateにはタイムゾーンがUTCで日時がセットされてしまう。

調べてみると、DATETIME('now','localtime')とすると、タイムゾーンがJSTで日時が取れるということがわかった。
そこで、早速上記SQLを
CREATE TABLE sample_table
(
    id          INTEGER   NOT NULL PRIMARY KEY
  , value       TEXT,
  , update_date TIMESTAMP DEFAULT DATETIME('now','localtime')
);
としてみた。 しかし、
SQLite error
near "(": syntax error
というエラーが発生。

色々試してみて、DATETIME('now','localtime')を括弧で囲めばいいことがわかった。
最終的には、次のようなSQLとなった。
CREATE TABLE sample_table
(
    id          INTEGER   NOT NULL PRIMARY KEY
  , value       TEXT,
  , update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))
);