サンプルを、SQLiteを使って試してみた。
テスト用に、sample_tableという名前のテーブルを
CREATE TABLE sample_table
(
id INTEGER NOT NULL PRIMARY KEY
, value TEXT NOT NULL
, update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))
);
上記のように作成。ここに、
id | value | update_date |
---|---|---|
1 | ABCD | 2012-04-05 18:05:56 |
2 | EFGH | 2012-04-05 18:05:56 |
3 | IJKL | 2012-04-05 18:05:56 |
このテーブルをDataTableに取り込み、
id | value | update_date |
---|---|---|
1 | ABCD | DATE('now','localtime') |
2 | EFGH | DATE('now','localtime') |
3 | MNOP | DATE('now','localtime') |
4 | NULL | DATE('now','localtime') |
※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(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)
(失敗する)のあとではid | value | update_date |
---|---|---|
1 | ABCD | 2012-04-05 18:05:56 |
2 | EFGH | 2012-04-05 18:05:56 |
3 | IJKL | 2012-04-05 18:05:56 |
DataTableの内容は
id | value | update_date | RowState |
---|---|---|---|
1 | ABCD | 2012-04-05 18:05:56 | Unchanged |
2 | EFGH | 2012-04-05 18:05:56 | Unchanged |
3 | MNOP | 2012-04-05 18:05:56 | Unchanged |
4 | QRST | NULL | Added |
次に、id=4のvalueに文字列をセットした後の2回目の
da.Update(table)
のあとのデータベースのテーブルはid | value | update_date |
---|---|---|
1 | ABCD | 2012-04-05 18:05:56 |
2 | EFGH | 2012-04-05 18:05:56 |
3 | IJKL | 2012-04-05 18:05:56 |
4 | QRST | 2012-04-05 18:07:27 |
このとき、id=4のDataRowが保存されるので、このDataRowのRowStateがUnchangedに変わり、すべての行がUnchangedとなる。
このとき、DataTableの中身は
id | value | update_date | RowState |
---|---|---|---|
1 | ABCD | 2012-04-05 18:05:56 | Unchanged |
2 | EFGH | 2012-04-05 18:05:56 | Unchanged |
3 | MNOP | 2012-04-05 18:05:56 | Unchanged |
4 | QRST | 2012-04-05 18:07:27 | Unchanged |
このサンプルプログラムでは、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)
(失敗する)のあとではid | value | update_date |
---|---|---|
1 | ABCD | 2012-04-05 18:28:23 |
2 | EFGH | 2012-04-05 18:28:23 |
3 | IJKL | 2012-04-05 18:28:23 |
DataTableの内容は
id | value | update_date | RowState |
---|---|---|---|
1 | ABCD | 2012-04-05 18:05:56 | Unchanged |
2 | EFGH | 2012-04-05 18:05:56 | Unchanged |
3 | MNOP | 2012-04-05 18:05:56 | Modified |
4 | QRST | NULL | Added |
次に、id=4のvalueに文字列をセットした後の2回目の
da.Update(table)
のあとのデータベースのテーブルはid | value | update_date |
---|---|---|
1 | ABCD | 2012-04-05 18:28:23 |
2 | EFGH | 2012-04-05 18:28:23 |
3 | MNOP | 2012-04-05 18:28:27 |
4 | QRST | 2012-04-05 18:28:27 |
このとき、DataTableの中身は
id | value | update_date | RowState |
---|---|---|---|
1 | ABCD | 2012-04-05 18:28:23 | Unchanged |
2 | EFGH | 2012-04-05 18:28:23 | Unchanged |
3 | MNOP | 2012-04-05 18:28:27 | Unchanged |
4 | QRST | 2012-04-05 18:28:27 | Unchanged |
修正版のソースを載せておきます。
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(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;
}
}
}
}
}
}
0 コメント :
コメントを投稿