C#でExecuteScalarメソッドを使うときの注意点。

• facebook

オフィス狛 技術部のHammarです。

C#のSQL実行メソッドに「ExecuteScalar」というのがあります。
これはクエリを実行し、そのクエリが返す結果セットの最初の行にある最初の列を返してくれます。
このメソッドを使って、ちょっとハマってしまったことについて書こうと思います。

例えば、テーブルの主キーとなるidが自動インクリメントになっているテーブルに、INSERTすると同時に登録したidを取得したい時などがあると思います。
今回試した環境はSQLServerだったので、INSERT実行時に
を使えばそれが可能なのですが、INSERT文が別テーブルからの「INSERT SELECT」の場合、SELECTの結果が条件によっては取得できない場合があります。
そのときの結果の挙動で自分が想定した結果ではなかったので、ちょっと戸惑いました。

まず、上記の「INSERT SELECT」と「SELECT SCOPE_IDENTITY」を使って「ExecuteScalar」を実行するサンプルコードとして

SqlConnection Connection = new SqlConnection();
SqlCommand cmd = new SqlCommand("INSERT INTO users(...) SELECT ...; SELECT SCOPE_IDENTITY();", Connection);
decimal id = (decimal)cmd.ExecuteScalar();

上記は、別テーブルから取得した情報を登録し、登録した時のidを取得するというような処理になります。

で、基本SELECTでデータが取得できる前提であれば問題ないんですが、このSELECTでデータが取得できない場合、
「指定されたキャストは有効ではありません。」
というエラーが発生してしまいます。

恐らくこのエラーは、「SELECTでデータ取得できない」→「INSERTするデータなし」→「SELECT SCOPE_IDENTITYでid取得できない」
ということなので、返り値としてはNullが返ってくると思います。
なので、結果decimalにキャストできなくてエラー発生していると思ったので、以下のように、Nullが入ってくることを許容すればいいと思い、返り値にNullable型を使って

SqlConnection Connection = new SqlConnection();
SqlCommand cmd = new SqlCommand("INSERT INTO users(...) SELECT ...; SELECT SCOPE_IDENTITY();", Connection);
decimal? id = (decimal?)cmd.ExecuteScalar();
if (id.HasValue)
{
    string insertId = id.ToString();
}

という感じにしました。
返り値の型decimalに?を付けてNull許容（Nullable）とすれば、仮にNullが返っても問題ないのかなと。
その後で、Nullの場合の判定を加えてやれば処理的にうまくいくかなと思いましたが、やはり同じく「指定されたキャストは有効ではありません。」のエラーが発生しました。

よくよく考えてみると、そもそもここでdecimal型にキャストして、基本的に値が返ってくる前提のような作りがよくないのと、SELECT時データが取得できない場合は、返り値が「Null」ではなくて、「DBNull」という別物だということを知りました。

ということで、返り値の型をobject型に変更し、下記のように変更しました。

SqlConnection Connection = new SqlConnection();
SqlCommand cmd = new SqlCommand("INSERT INTO users(...) SELECT ...; SELECT SCOPE_IDENTITY();", Connection);
object id = cmd.ExecuteScalar();
if (!DBNull.Value.Equals(id))
{
    string insertId = id.ToString();
}

これでデータが取得できた時だけ、登録時のidを取得できるようになりました。

もしかしたらこういったケースはあまりないかもしれませんが、「ExecuteScalar」メソッドを使って「INSERT SELECT」するときには、ご参考にしてみてください。

C#