【Tips: ADO.NET】 IDENTITY 列を持ったテーブルに行を挿入した時の IDENTITY 列の値の取得

SQL Server の IDENTITY 列は、テーブルに新しい行が挿入されるときに自動的にその値がインクリメントされます。主キーとしてたいへん便利です。

行の挿入後に IDENTITY 列の値を取得したいことがあります。ADO.NET における、そのやり方を見て行きましょう。

※ 動作環境
  • ここで使用する Visual Studio は Visual Studio 2010 です。
  • 想定している .NET Framework のバージョンは .NET Framework 4.0 です。

■ 1. SQL Server 側のテーブルの準備

先ず、IDENTITY 列を持ったテーブルを SQL Server 側に準備しましょう。

  1. Visual Studio の「サーバーエクスプローラ」 (Visual Studio Express Edition の場合は「データベース・エクスプローラ」)
    を開きます。
  2. 「データ接続」を右クリックし、ポップアップ メニューから「接続の追加」または「新しい SQL Server データベースの作成」をします。
  3. その接続の「テーブル」 – 「新しいテーブルの追加」でデータベースにテーブルを追加します。

  1. ここでは、試しに以下のような "Item" というテーブルを追加することにします。
Item
列名 主キー データ型 Null を許容 IDENTITY の設定
Id int × はい
Name × nvarchar(100) × いいえ
  1. Id 列を主キーとし、IDENTITY の設定を「はい」にしておきます。

  1. または、SQL Server Management Studio などから以下の SQL を実行することでも上記 Item テーブルを作成することができます。
CREATE TABLE [dbo].[Item](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

データベース側の準備ができましたので、これから ADO.NET の各種手法において、新規行の IDENTITY 列の値の取得方法を見ていきましょう。

■ 2. SqlCommand の場合

SQL Server で使用する Transact-SQL では、"SCOPE_IDENTITY" という関数を使って、INSERT 後に、自動採番された値を取得することができます。

※ 参考: MSDN – SCOPE_IDENTITY (Transact-SQL)

それでは、実際のプログラムで試してみましょう。

  1. Visual Studio を起動し、メニューの「ファイル」 – 「新規作成」 – 「プロジェクト」から「新しいプロジェクト」ダイアログ
    ボックスを開きます。
  2. 「Visual C#」 – 「コンソール アプリケーション」で新しいプロジェクトを作成します。
  3. ここでは、以下のようなサンプルで試してみることにします。
    (connectionString の値は、適切なものに変更してください。下の例では、ローカルの SQL Server Express に作成した "Test" という名前のデータベースに接続しています。)
using System.Data.SqlClient;
namespace TypedDataSetUpdateWithIdentityColumn
{
    class Program
    {
        static void Main()
        {
            const string connectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";
            using (var connection = new SqlConnection(connectionString)) {
                connection.Open();
                // 新規 Item の INSERT
                using (var command = new SqlCommand("INSERT INTO Item (Name) VALUES (@Name);SELECT CAST(SCOPE_IDENTITY() AS int)", connection)) {
                    command.Parameters.Add(new SqlParameter("@Name""Hoge"));
                    var newId = (int)command.ExecuteScalar();
                }
            }
        }
    }
}
  1. 実行結果をデバッグ実行で確認してみましょう。

  1. command を ExecuteScalar することで、新たな行が挿入され、その行の Id 列の値が返ってきています。その直後の "newId" の値が、新たな Id 値である "1" になっているのが確認できます。

■ 3. 型付き DataSet の場合

型付き DataSet の場合はどうでしょう。試してみましょう。

  1. Visual Studio で、新しい「コンソール アプリケーション」を作成し、「ソリューション
    エクスプローラー」でプロジェクト名を右クリックします。
  2. ポップアップ メニューから、「追加」 – 「新しい項目」を選択します。

  1. 「新しい項目の追加」ダイアログ ボックスが開きます。「Visual C# アイテム」 – 「データセット」を選択します。
  2. ここでは「名前」を "ItemDataSet.xsd" と、し「追加」ボタンを押します。

  1. 暫く待つと、"ItemDataSet.xsd" というファイルが開きます。
  2. 「サーバーエクスプローラ」 (Visual Studio Express Edition の場合は「データベース・エクスプローラ」) から、「データ接続」 内の接続 – 「テーブル」 – Item を "ItemDataSet.xsd" 内にドラッグ アンド ドロップします。

  1. "ItemDataSet.xsd" 内に以下のようなアイテムが追加されます。

  1. このアイテムを右クリックして出てきたポップアップ メニューから「構成」を選択します。

  1. 「TableAdapter 構成ウィザード」が開きます。

  1. 「詳細オプション」ボタンを押します。
  2. 「詳細設定」ダイアログ ボックスが開きます。以下のように設定して「OK」ボタンを押してみましょう。「データ
    テーブルの更新」のチェックを外しておきます。

  1. いくつかのソースコードが自動生成されます。
  2. 自動生成された "ItemDataSet.Designer.cs" というファイルの中を覗いて、どのような INSERT
    文が生成されたか見てみることにしましょう。
  3. "INSERT"
    でファイルの中を検索してみると、以下のような記述が見つかります。
this._adapter.InsertCommand.CommandText = "INSERT INTO [dbo].[Item] ([Name]) VALUES (@Name)";
  1. INSERT 文が生成されているのが分かります。INSERT 後に、自動採番された値を取得するような記述は見当たりません。
  2. 以下のようなサンプル コードで試してみます。
using TypedDataSetUpdateWithIdentityColumn.ItemDataSetTableAdapters;
namespace TypedDataSetUpdateWithIdentityColumn
{
    class Program
    {
        static void Main()
        {
            using (var itemTableAdapter = new ItemTableAdapter()) {
                // Item テーブルの SELECT
                var itemTable = new ItemDataSet.ItemDataTable();
                itemTableAdapter.Fill(itemTable);
                // 新規 Item の INSERT
                var newItemRow = itemTable.NewItemRow();
                newItemRow.Name = "Hoge";
                itemTable.Rows.Add(newItemRow);
                itemTableAdapter.Update(itemTable);
            }
        }
    }
}
  1. デバッグ実行して、結果を確認してみましょう。

  1. 予想通り、新たな Id の値は取得できていません。
  2. ちなみに、このときの実際のデータベースのテーブルの値は以下のようになっています。

  1. では、設定を変えて試してみましょう。
  2.  先ほどと同じように、"ItemDataSet.xsd" ファイルを開き、中のアイテムを右クリック、ポップアップ
    メニューから「構成」を選び、「TableAdapter 構成ウィザード」上の「詳細オプション」ボタンを押して「詳細設定」ダイアログ ボックスを開きます。
  3. 今度は、以下のように「データ テーブルの更新」にチェックを入れて「OK」ボタンを押します。

  1. 先ほどと同様に、"ItemDataSet.Designer.cs" 中を覗いて、どのような INSERT 文が生成されたか見てみることにしましょう。
this._adapter.InsertCommand.CommandText = "INSERT INTO [dbo].[Item] ([Name]) VALUES (@Name);\r\nSELECT Id, Name FROM Item WHERE (Id = SCOPE_IDENTITY())";
  1. 今度は、INSERT の後に "SCOPE_IDENTITY()" と一致する Id を持った行を SELECT
    しているのが分かります。
  2. デバッグ実行して結果を確認してみます。

  1. 今度は、自動生成された Id の値が取得できています。

■ 4. LINQ to SQL の場合

LINQ to SQL の場合はどうでしょう。試してみましょう。

  1. Visual Studio で、新しい「コンソール アプリケーション」を作成し、「ソリューション エクスプローラー」でプロジェクト名を右クリックします。
  2. ポップアップ メニューから、「追加」 – 「新しい項目」を選択します。
  3. 「新しい項目の追加」ダイアログ ボックスが開きます。「Visual C# アイテム」 – 「LINQ to SQL クラス」を選択します。
  4. ここでは「名前」を "ItemDataClasses.dbml" と、し「追加」ボタンを押します。

  1. 暫く待つと、"ItemDataClasses.dbml" というファイルが開きます。

  1. 「サーバーエクスプローラ」 (Visual Studio Express Edition の場合は「データベース・エクスプローラ」) から、「データ接続」 内の接続 – 「テーブル」 – Item を "ItemDataClasses.dbml" 内にドラッグ アンド ドロップします。
  2. "ItemDataClasses.dbml" 内に以下のようなアイテムが追加されます。

  1. 自動生成された "ItemDataClasses.designer.cs" を開いて、中の "Item" クラスの "Id" プロパティを見てみましょう。
[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Item")]
public partial class Item : INotifyPropertyChanging, INotifyPropertyChanged
{
// ……途中省略……
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int Id
{
get
{
return this._Id;
}
set
{
if ((this._Id != value))
{
this.OnIdChanging(value);
this.SendPropertyChanging();
this._Id = value;
this.SendPropertyChanged("Id");
this.OnIdChanged();
}
}
}
  1. この中の、
AutoSync=AutoSync.OnInsert

という指定によって、行の挿入時に自動で Id 列の値を取得する SELECT 文が発行されます。

  1. 以下のサンプル コードで試してみましょう。
using System;
namespace TypedDataSetUpdateWithIdentityColumn
{
    class Program
    {
        static void Main()
        {
            using (var itemData = new ItemDataClassesDataContext()) {
                // 生成される SQL を標準出力ストリームに出力
                itemData.Log = Console.Out;
                // 新規 Item の INSERT
                var newItem = new Item { Name = "Hoge" };
                itemData.Item.InsertOnSubmit(newItem);
                itemData.SubmitChanges();
            }
        }
  1. 実行結果は、以下のようになります。

  1. 挿入直後の、"newItem" の Id に新たな値が入っているのが確認できます。
  2. コンソールには、以下のように出力されます。

  1. Linq to SQL によって、生成された SQL は以下のようなものです。
INSERT INTO [dbo].[Item]([Name])
VALUES (@p0)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Hoge]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
  1. 新たな Id 列の値を取得する SELECT 文が生成されているのが分かります。

■ 5. Entity Framework の場合

最後に、Entity Framework の場合を見てみましょう。

  1. Visual Studio で、新しい「コンソール アプリケーション」を作成し、「ソリューション エクスプローラー」でプロジェクト名を右クリックします。
  2. ポップアップ メニューから、「追加」 – 「新しい項目」を選択します。
  3. 「新しい項目の追加」ダイアログ ボックスが開きます。「Visual C# アイテム」 – 「ADO.NET Entity Data Model」を選択します。
  4. ここでは「名前」を "ItemModel.edmx" と、し「追加」ボタンを押します。

  1. 「Entity Data Model ウィザード」が開きます。
  2. ここでは、「データベースから生成」を選択し、「次へ」ボタンを押します。

  1. 次の画面では、既存のデータ接続を選択し、「エンティティ接続設定に名前を付けて App.Config に保存」では
    "ItemEntities" と入力して「次へ」ボタンを押します。

  1. 次の「データベース オブジェクトの選択」の画面では、「テーブル」の中の "Item" をチェックします。
  2. 「生成されたオブジェクトの名前を複数化または単数化する」にもチェックを入れ、「モデル名前空間」をここでは "ItemModel"
    として、「完了」ボタンを押します。

  1. 暫く待つと、"ItemModel.edmx" というファイルが開きます。中に以下のようなアイテムが追加されているのが分かります。

  1. 今度は、以下のサンプル コードで試してみましょう。
namespace TypedDataSetUpdateWithIdentityColumn
{
    class Program
    {
        static void Main()
        {
            using (var itemData = new ItemEntities()) {
                // 新規 Item の INSERT
                var newItem = new Item { Name = "Hoge" };
                itemData.AddToItems(newItem);
                itemData.SaveChanges();
            }
        }
    }
}
  1. 実行結果は、以下のようになります。

  1. 挿入直後の、"newItem" の Id に新たな値が入っているのが確認できます。

.NET

Posted by Fujiwo