C# + MS Access にて、DapperとTableAdapterで INSERT時のオートナンバー型の値の取得方法を比べてみる

C# Dapper MS Access

他のデータベースにもあるような、INSERT時に自動的に連番を入れてくれる型として、MS Accessではオートナンバー型が用意されています。

オートナンバー型の自動採番された値をINSERT時に取得する方法について、型付Datase(TableAdapter)とDapperではどのように違うのかを調べてみました。

　

TableAdapterの場合

TableAdapterの場合、hogeTableAdapter.Update() メソッドとTableAdapterの RowUpdated イベントを使います。 詳細は以下が参考になります。

　
ただ、Dapperに比べて作業量が多いこともあり、作業のメモを残しておきます。

　

TableAdapterのパーシャルクラスを生成

Datasetのデザインビューを開き、TableAdapterの上で右クリック、コードの表示を選びます。

　

RowUpdatedイベント用のメソッドを作成し、 @@IDENTITY の値を取得するロジックを追加
private void Adapter_RowUpdated(object sender, System.Data.OleDb.OleDbRowUpdatedEventArgs e)
{
    if (e.StatementType == System.Data.StatementType.Insert
        && e.Status == System.Data.UpdateStatus.Continue)
    {
        var cmd = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", null);
        cmd.Connection = e.Command.Connection;
        cmd.Transaction = e.Command.Transaction;

        var result = cmd.ExecuteScalar();

        if (result != null
            && result.GetType() != typeof(System.DBNull))
        {
            e.Row["ID"] = (int)result;
            e.Row.AcceptChanges();
        }
    }
}

　

RowUpdatedのイベントハンドラを追加するメソッドを用意

C#のTableAdapterでは以下の制限があります。

　
そこで、RowUpdatedのイベントハンドラを追加するメソッドを用意し、実行時に忘れないようにそのメソッドを呼び出ます。

public void AddRowUpdatedEvent()
{
    this.Adapter.RowUpdated += new System.Data.OleDb.OleDbRowUpdatedEventHandler(Adapter_RowUpdated);
}

　
なお、手元の環境のせいかもしれませんが、this.Adapter のかわりにWebでよく見かける this._adapterを使うと、実行時にNullReferenceExceptionが発生しました。

　

実際の使用方法

イベントハンドラを追加するメソッドを呼んでから、Update()メソッドを呼びます。

//  TableAdapterのRowUpdatedイベントハンドラの追加
ta.AddRowUpdatedEvent();

var ds = new SampleDataset();
var row = ds.Item.NewItemRow();
row.ItemName = "秋映";
ds.Item.AddItemRow(row);

ta.Update(ds);

　

TableAdapterの接続情報について

TableAdapterは自前の接続情報を持っているため、通常は動的に切り替えることができません。

ただ、今回はTableAdapterの接続文字列を動的に切り替えることが必要になったので、一番簡単そうなPropertiesの設定のコード表示からパーシャルクラスを作成する方法を選びました。
参考： Ito Blog » Blog Archive » TableAdapter の接続先をコードから設定

なお、上記以外の方法はあまりお手軽でない気がしたので、今回は使いませんでした。

　

Dapperの場合

TableAdapterではいろいろと面倒でしたが、Dapperでは簡単に取得することができました。

参考にしたコードはSQL Server Compact Edition向けのコードでしたが、MS Access向けでも問題なく動作しました。
Dapper Getting Started - lancscoder / Connection.cs

var sql = "INSERT INTO Item(ItemName) VALUES (@ItemName)";
cn.Execute(sql, new { ItemName = "秋映" });

var id = (int)cn.Query("SELECT @@IDENTITY as ID").First().ID;

　
なお、上記の「@@IDENTITY クライシスを管理する」にも記載がある通り、MS Accessでは単一バッチで複数のSQLステートメントを実行できないため、stackoverflowの回答にあるようなことをやろうとしてもエラーになります。
c# - How do I perform an insert and return inserted identity with Dapper? - Stack Overflow

　

OLE DB接続について

話は変わりますが、過去にOLE DB接続が廃止されるということを目にしましたが、よく調べてみるとそれはSQL Serverだけのようでした。

今のところは公式でも、

This deprecation applies to the Microsoft SQL Server OLE DB provider only. Other OLE DB providers as well as the OLE DB standard will continue to be supported until explicitly announced.

と記載されているため、とりあえずは使っていけそうです。

　

ソースコード

GitHubに上げておきました。前回のものに追加してあります。
CSharp-Sample/DapperApp at master - thinkAmi/CSharp-Sample

　

参考