ID 値および Autonumber 値の取得Retrieving Identity or Autonumber Values

リレーショナル データベースの主キーとは、常に一意の値を含んだ列または列の組み合わせをいいます。A primary key in a relational database is a column or combination of columns that always contain unique values. 主キー値がわかっていれば、それが格納されている行を特定できます。Knowing the primary key value allows you to locate the row that contains it. SQL Server、Oracle、Microsoft Access/Jet などのリレーショナル データベース エンジンは、主キーとして指定可能な自動インクリメント列の作成をサポートしています。Relational database engines, such as SQL Server, Oracle, and Microsoft Access/Jet support the creation of automatically incrementing columns that can be designated as primary keys. これらの値はテーブルに行を追加するとサーバーによって自動的に生成されます。These values are generated by the server as rows are added to a table. SQL Server では列の Identity プロパティを設定し、Oracle では Sequence を作成します。また、Microsoft Access では、AutoNumber 列を作成します。In SQL Server, you set the identity property of a column, in Oracle you create a Sequence, and in Microsoft Access you create an AutoNumber column.

DataColumn を使用し、AutoIncrement プロパティを true に設定することで、インクリメント値を自動的に生成することもできます。A DataColumn can also be used to generate automatically incrementing values by setting the AutoIncrement property to true. ただし、複数のクライアント アプリケーションがそれぞれ独立して自動インクリメント値を生成した場合、最終的に DataTable の別々のインスタンスに重複する値が存在することも考えられます。However, you might end up with duplicate values in separate instances of a DataTable, if multiple client applications are independently generating automatically incrementing values. サーバーで自動的にインクリメント値を生成すると、各ユーザーが、挿入された行ごとに生成された値を取得できるようになるため、競合を未然に防ぐことができます。Having the server generate automatically incrementing values eliminates potential conflicts by allowing each user to retrieve the generated value for each inserted row.

ADO.NET アプリケーションでは、UpdateDataAdapter メソッドを呼び出している間、データベースから出力パラメーターとして、または (INSERT ステートメントと同じバッチで実行した) SELECT ステートメントの結果セットの最初に返されたレコードとして、データを取得できます。During a call to the Update method of a DataAdapter, the database can send data back to your ADO.NET application as output parameters or as the first returned record of the result set of a SELECT statement executed in the same batch as the INSERT statement. これらの値を取得することで、更新対象となる DataRow 内の対応する列を更新できます。ADO.NET can retrieve these values and update the corresponding columns in the DataRow being updated.

Microsoft Access Jet データベース エンジンなど、一部のデータベース エンジンは、出力パラメーターをサポートしておらず、複数のステートメントを 1 回のバッチで処理することもできません。Some database engines, such as the Microsoft Access Jet database engine, do not support output parameters and cannot process multiple statements in a single batch. Jet データベース エンジンを使用する場合は、RowUpdatedDataAdapter イベントのイベント ハンドラーで別途 SELECT コマンドを実行することによって、挿入行に対して生成された新しい AutoNumber 値を取得できます。When working with the Jet database engine, you can retrieve the new AutoNumber value generated for an inserted row by executing a separate SELECT command in an event handler for the RowUpdated event of the DataAdapter.

注意

自動インクリメント値を使用する代わりに、クライアント コンピューター側で NewGuid オブジェクトの Guid メソッドを使用して GUID (グローバルな一意識別子) を生成し、新しい行が挿入されるたびにそれをサーバーにコピーする方法もあります。An alternative to using an auto incrementing value is to use the NewGuid method of a Guid object to generate a GUID, or globally unique identifier, on the client computer that can be copied to the server as each new row is inserted. NewGuid メソッドでは、値の重複を高い確率で防ぐアルゴリズムを使って 16 バイトのバイナリ値が生成されます。The NewGuid method generates a 16-byte binary value that is created using an algorithm that provides a high probability that no value will be duplicated. SQL Server データベースでは、Transact-SQL の uniqueidentifier 関数を使って自動的に生成される GUID が、NEWID() 列に格納されます。In a SQL Server database, a GUID is stored in a uniqueidentifier column which SQL Server can automatically generate using the Transact-SQL NEWID() function. GUID を主キーとして使用すると、パフォーマンスが低下する場合があります。Using a GUID as a primary key can adversely affect performance. SQL Server は、グローバルにNEWSEQUENTIALID()一意であることが保証されていないが、より効率的にインデックスを作成できる、シーケンシャルな GUID を生成する関数のサポートを提供します。SQL Server provides support for the NEWSEQUENTIALID() function, which generates a sequential GUID that is not guaranteed to be globally unique but that can be indexed more efficiently.

SQL Server の ID 列値の取得Retrieving SQL Server Identity Column Values

Microsoft SQL Server を使用している場合は、出力パラメーターを持ったストアド プロシージャを作成して、挿入された行の ID 値を取得できます。When working with Microsoft SQL Server, you can create a stored procedure with an output parameter to return the identity value for an inserted row. 次の表は、SQL Server で ID 列値の取得に使用できる 3 つの Transact-SQL 関数を示しています。The following table describes the three Transact-SQL functions in SQL Server that can be used to retrieve identity column values.

関数Function 説明Description
SCOPE_IDENTITYSCOPE_IDENTITY 現在の実行スコープ内の最後の ID 値を返します。Returns the last identity value within the current execution scope. 通常は、SCOPE_IDENTITY を使用することをお勧めします。SCOPE_IDENTITY is recommended for most scenarios.
@@IDENTITY@@IDENTITY 現在のセッション内の任意のテーブルで生成された最後の ID 値を保持します。Contains the last identity value generated in any table in the current session. @@IDENTITYはトリガーの影響を受ける可能性があり、予想される id 値を返すことはできません。@@IDENTITY can be affected by triggers and may not return the identity value that you expect.
IDENT_CURRENTIDENT_CURRENT 任意のセッションとスコープ内の特定のテーブルに対して生成された最後の ID 値を返します。Returns the last identity value generated for a specific table in any session and any scope.

次のストアドプロシージャは、 Categoriesテーブルに行を挿入し、output パラメーターを使用して、transact-sql SCOPE_IDENTITY () 関数によって生成された新しい id 値を返す方法を示しています。The following stored procedure demonstrates how to insert a row into the Categories table and use an output parameter to return the new identity value generated by the Transact-SQL SCOPE_IDENTITY() function.

CREATE PROCEDURE dbo.InsertCategory
  @CategoryName nvarchar(15),
  @Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()

このストアド プロシージャは InsertCommand オブジェクトの SqlDataAdapter のソースとして指定できます。The stored procedure can then be specified as the source of the InsertCommand of a SqlDataAdapter object. CommandTypeInsertCommand プロパティには、有効な StoredProcedure を設定する必要があります。The CommandType property of the InsertCommand must be set to StoredProcedure. ID の出力を取得するには、SqlParameterParameterDirection に設定した Output を作成します。The identity output is retrieved by creating a SqlParameter that has a ParameterDirection of Output. UpdateRowSource.Both UpdatedRowSource UpdateRowSource.OutputParametersが処理されると、挿入コマンドのプロパティをまたはに設定すると、自動インクリメントされた id 値が返され、現在の行の CategoryID 列に配置されます。 InsertCommandWhen the InsertCommand is processed, the auto-incremented identity value is returned and placed in the CategoryID column of the current row if you set the UpdatedRowSource property of the insert command to UpdateRowSource.OutputParameters or to UpdateRowSource.Both.

挿入コマンドで、INSERT ステートメントと (新しい ID 値を返す) SELECT ステートメントの両方を含むバッチを実行した場合、挿入コマンドの UpdatedRowSource プロパティを UpdateRowSource.FirstReturnedRecord に設定することによって新しい値を取得できます。If your insert command executes a batch that includes both an INSERT statement and a SELECT statement that returns the new identity value, then you can retrieve the new value by setting the UpdatedRowSource property of the insert command to UpdateRowSource.FirstReturnedRecord.

private static void RetrieveIdentity(string connectionString)
{
    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        // Create a SqlDataAdapter based on a SELECT query.
        SqlDataAdapter adapter =
            new SqlDataAdapter(
            "SELECT CategoryID, CategoryName FROM dbo.Categories",
            connection);

        //Create the SqlCommand to execute the stored procedure.
        adapter.InsertCommand = new SqlCommand("dbo.InsertCategory", 
            connection);
        adapter.InsertCommand.CommandType = CommandType.StoredProcedure;

        // Add the parameter for the CategoryName. Specifying the
        // ParameterDirection for an input parameter is not required.
        adapter.InsertCommand.Parameters.Add(
           new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15,
           "CategoryName"));

        // Add the SqlParameter to retrieve the new identity value.
        // Specify the ParameterDirection as Output.
        SqlParameter parameter = 
            adapter.InsertCommand.Parameters.Add(
            "@Identity", SqlDbType.Int, 0, "CategoryID");
        parameter.Direction = ParameterDirection.Output;

        // Create a DataTable and fill it.
        DataTable categories = new DataTable();
        adapter.Fill(categories);

        // Add a new row. 
        DataRow newRow = categories.NewRow();
        newRow["CategoryName"] = "New Category";
        categories.Rows.Add(newRow);

        adapter.Update(categories);

        Console.WriteLine("List All Rows:");
        foreach (DataRow row in categories.Rows)
        {
            {
                Console.WriteLine("{0}: {1}", row[0], row[1]);
            }
        }
    }
}
Private Sub RetrieveIdentity(ByVal connectionString As String)
    Using connection As SqlConnection = New SqlConnection( _
       connectionString)

        ' Create a SqlDataAdapter based on a SELECT query.
        Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
           "SELECT CategoryID, CategoryName FROM dbo.Categories", _
           connection)

        ' Create the SqlCommand to execute the stored procedure. 
        adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _
           connection)
        adapter.InsertCommand.CommandType = CommandType.StoredProcedure

        ' Add the parameter for the CategoryName. Specifying the
        ' ParameterDirection for an input parameter is not required.
        adapter.InsertCommand.Parameters.Add( _
          "@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")

        ' Add the SqlParameter to retrieve the new identity value.
        ' Specify the ParameterDirection as Output.
        Dim parameter As SqlParameter = _
           adapter.InsertCommand.Parameters.Add( _
          "@Identity", SqlDbType.Int, 0, "CategoryID")
        parameter.Direction = ParameterDirection.Output

        ' Create a DataTable and fill it.
        Dim categories As DataTable = New DataTable
        adapter.Fill(categories)

        ' Add a new row.
        Dim newRow As DataRow = categories.NewRow()
        newRow("CategoryName") = "New Category"
        categories.Rows.Add(newRow)

        ' Update the database.
        adapter.Update(categories)

        Console.WriteLine("List All Rows:")
        Dim row As DataRow
        For Each row In categories.Rows
            Console.WriteLine("{0}: {1}", row(0), row(1))
        Next
    End Using
End Sub

新しい ID 値の結合Merging New Identity Values

GetChangesDataTable は、変更行だけを含んだコピーを作成するメソッドです。作成した新しいコピーは UpdateDataAdapter メソッドの呼び出し時に使用できます。A common scenario is to call the GetChanges method of a DataTable to create a copy that contains only changed rows, and to use the new copy when calling the Update method of a DataAdapter. この方法を用いると、変更された行を別のコンポーネントにマーシャリングして更新を実行できます。This is especially useful when you need to marshal the changed rows to a separate component that performs the update. 更新後、このコピーには、元の DataTable に再び結合する必要のある新しい ID 値が格納されている可能性もあります。Following the update, the copy can contain new identity values that must then be merged back into the original DataTable. 新しい ID 値は、おそらく DataTable 内の元の値とは異なります。The new identity values are likely to be different from the original values in the DataTable. マージを実行するには、コピー内のAutoIncrement列の元の値を保持し、新しい id 値を含む新しい行を追加するのではDataTableなく、元ので既存の行を検索して更新できるようにする必要があります.To accomplish the merge, the original values of the AutoIncrement columns in the copy must be preserved, in order to be able to locate and update existing rows in the original DataTable, rather than appending new rows containing the new identity values. しかし、既定では、UpdateDataAdapter メソッドを呼び出した後、元の値は失われてしまいます。更新された AcceptChanges ごとに DataRow が暗黙的に呼び出されるためです。However, by default those original values are lost after a call to the Update method of a DataAdapter, because AcceptChanges is implicitly called for each updated DataRow.

DataColumn の更新処理中、DataRowDataAdapter に格納されていた元の値を保持するには、次の 2 とおりの方法があります。There are two ways to preserve the original values of a DataColumn in a DataRow during a DataAdapter update:

  • 元の値を保持する 1 つ目の方法は、AcceptChangesDuringUpdateDataAdapter プロパティを false に設定することです。The first method of preserving the original values is to set the AcceptChangesDuringUpdate property of the DataAdapter to false. この設定は、更新対象となる DataRow のすべての DataTable に影響します。This affects every DataRow in the DataTable being updated. コード例および詳細については、「AcceptChangesDuringUpdate」を参照してください。For more information and a code example, see AcceptChangesDuringUpdate.

  • 2 つ目は、RowUpdatedDataAdapter イベント ハンドラーに、StatusSkipCurrentRow に設定するコードを記述する方法です。The second method is to write code in the RowUpdated event handler of the DataAdapter to set the Status to SkipCurrentRow. DataRow が更新されても、各 DataColumn の元の値は保持されます。The DataRow is updated but the original value of each DataColumn is preserved. この方法を使用した場合、一部の行についてのみ元の値を保持するといったことも可能です。This method enables you to preserve the original values for some rows and not for others. たとえば、最初に StatementType を調べ、StatusSkipCurrentRow である行に限定して StatementTypeInsert に設定することで、追加された行については元の値を保持し、編集または削除された行については元の値を保持しないようなコードを作成できます。For example, your code can preserve the original values for added rows and not for edited or deleted rows by first checking the StatementType and then setting Status to SkipCurrentRow only for rows with a StatementType of Insert.

DataRow の更新処理中、上記のいずれかの方法で DataAdapter の元の値を保持した場合、ADO.NET は、各 DataRow の元の値を保持する一方で、DataColumn の現在の値を新しい値 (つまり、出力パラメーターまたは結果セットの先頭行として返された値) に設定するための一連の処理を実行します。When either of these methods is used to preserve original values in a DataRow during a DataAdapter update, ADO.NET performs a series of actions to set the current values of the DataRow to new values returned by output parameters or by the first returned row of a result set, while still preserving the original value in each DataColumn. 最初に AcceptChangesDataRow メソッドが呼び出され、現在の値が元の値として保持された後、新しい値が割り当てられます。First, the AcceptChanges method of the DataRow is called to preserve the current values as original values, and then the new values are assigned. これらの処理に続けて、DataRows プロパティが RowState に設定されていた Added については、RowState プロパティが Modified に設定されます。これはユーザーが想定していない可能性があります。Following these actions, DataRows that had their RowState property set to Added will have their RowState property set to Modified, which may be unexpected.

更新対象の各 DataRow に対し、コマンドの結果がどのように適用されるかは、各 UpdatedRowSourceDbCommand プロパティによって決まります。How the command results are applied to each DataRow being updated is determined by the UpdatedRowSource property of each DbCommand. このプロパティは、UpdateRowSource 列挙型のいずれかの値に設定されます。This property is set to a value from the UpdateRowSource enumeration.

次の表では、UpdateRowSource 列挙型の各値が、更新対象の行の RowState プロパティにどのように影響するかを説明します。The following table describes how the UpdateRowSource enumeration values affect the RowState property of updated rows.

メンバー名Member name 説明Description
Both AcceptChanges が呼び出され、出力パラメーターの値と、返された結果セットの先頭行が、どちらも更新対象の DataRow に格納されます。AcceptChanges is called and both output parameter values and/or the values in the first row of any returned result set are placed in the DataRow being updated. 適用する値が存在しない場合、RowStateUnchanged になります。If there are no values to apply, the RowState will be Unchanged.
FirstReturnedRecord 行が返された場合、AcceptChanges が呼び出され、その行が DataTable の変更行にマップされて、RowStateModified に設定されます。If a row was returned, AcceptChanges is called and the row is mapped to the changed row in the DataTable, setting the RowState to Modified. 行が返されなかった場合、AcceptChanges は呼び出されず、RowStateAdded のままになります。If no row is returned, then AcceptChanges is not called and the RowState remains Added.
None 返されたパラメーターまたは行はすべて無視されます。Any returned parameters or rows are ignored. AcceptChanges は呼び出されず、RowStateAdded のままになります。There is no call to AcceptChanges and the RowState remains Added.
OutputParameters AcceptChanges が呼び出され、出力パラメーターがある場合は DataTable の変更行にマップされて、RowStateModified に設定されます。AcceptChanges is called and any output parameters are mapped to the changed row in the DataTable, setting the RowState to Modified. 出力パラメーターがない場合は、RowStateUnchanged になります。If there are no output parameters, the RowState will be Unchanged.

Example

次の例では、DataTable から変更行を抽出し、SqlDataAdapter でデータ ソースを更新して、新しい ID 列値を取得します。This example demonstrates extracting changed rows from a DataTable and using a SqlDataAdapter to update the data source and retrieve a new identity column value. InsertCommand は、2 つの Transact-SQL ステートメントを実行します。1 つは INSERT ステートメントで、もう 1 つは、SCOPE_IDENTITY 関数を使って ID 値を取得する SELECT ステートメントです。The InsertCommand executes two Transact-SQL statements; the first one is the INSERT statement, and the second one is a SELECT statement that uses the SCOPE_IDENTITY function to retrieve the identity value.

INSERT INTO dbo.Shippers (CompanyName)
VALUES (@CompanyName);
SELECT ShipperID, CompanyName FROM dbo.Shippers
WHERE ShipperID = SCOPE_IDENTITY();

挿入コマンドの UpdatedRowSource プロパティは UpdateRowSource.FirstReturnedRow に、MissingSchemaActionDataAdapter プロパティは MissingSchemaAction.AddWithKey に設定します。The UpdatedRowSource property of the insert command is set to UpdateRowSource.FirstReturnedRow and the MissingSchemaAction property of the DataAdapter is set to MissingSchemaAction.AddWithKey. DataTable にデータを格納した後、新しい行を DataTable に追加します。The DataTable is filled and the code adds a new row to the DataTable. 次に、変更行を抽出して新しい DataTable に格納します。このデータ テーブルが DataAdapter に渡され、サーバー側が更新されます。The changed rows are then extracted into a new DataTable, which is passed to the DataAdapter, which then updates the server.

private static void MergeIdentityColumns(string connectionString)
{
    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        // Create the DataAdapter
        SqlDataAdapter adapter =
            new SqlDataAdapter(
            "SELECT ShipperID, CompanyName FROM dbo.Shippers",
            connection);

        //Add the InsertCommand to retrieve new identity value.
        adapter.InsertCommand = new SqlCommand(
            "INSERT INTO dbo.Shippers (CompanyName) " +
            "VALUES (@CompanyName); " +
            "SELECT ShipperID, CompanyName FROM dbo.Shippers " +
            "WHERE ShipperID = SCOPE_IDENTITY();", connection);

        // Add the parameter for the inserted value.
        adapter.InsertCommand.Parameters.Add(
           new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40,
           "CompanyName"));
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

        // MissingSchemaAction adds any missing schema to 
        // the DataTable, including identity columns
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

        // Fill the DataTable.
        DataTable shipper = new DataTable();
        adapter.Fill(shipper);

        // Add a new shipper. 
        DataRow newRow = shipper.NewRow();
        newRow["CompanyName"] = "New Shipper";
        shipper.Rows.Add(newRow);

        // Add changed rows to a new DataTable. This
        // DataTable will be used by the DataAdapter.
        DataTable dataChanges = shipper.GetChanges();

        // Add the event handler. 
        adapter.RowUpdated +=
            new SqlRowUpdatedEventHandler(OnRowUpdated);

        adapter.Update(dataChanges);
        connection.Close();

        // Merge the updates.
        shipper.Merge(dataChanges);

        // Commit the changes.
        shipper.AcceptChanges();

        Console.WriteLine("Rows after merge.");
        foreach (DataRow row in shipper.Rows)
        {
            {
                Console.WriteLine("{0}: {1}", row[0], row[1]);
            }
        }
    }
}
Private Sub MergeIdentityColumns(ByVal connectionString As String)

    Using connection As SqlConnection = New SqlConnection( _
       connectionString)

        ' Create the DataAdapter
        Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
          "SELECT ShipperID, CompanyName FROM dbo.Shippers", connection)

        ' Add the InsertCommand to retrieve new identity value.
        adapter.InsertCommand = New SqlCommand( _
            "INSERT INTO dbo.Shippers (CompanyName) " & _
            "VALUES (@CompanyName); " & _
            "SELECT ShipperID, CompanyName FROM dbo.Shippers " & _
            "WHERE ShipperID = SCOPE_IDENTITY();", _
            connection)

        ' Add the parameter for the inserted value.
        adapter.InsertCommand.Parameters.Add( _
           New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, _
           "CompanyName"))
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both

        ' MissingSchemaAction adds any missing schema to 
        ' the DataTable, including identity columns
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        ' Fill the DataTable.
        Dim shipper As New DataTable
        adapter.Fill(shipper)

        ' Add a new shipper. 
        Dim newRow As DataRow = shipper.NewRow()
        newRow("CompanyName") = "New Shipper"
        shipper.Rows.Add(newRow)

        ' Add changed rows to a new DataTable. This
        ' DataTable will be used by the DataAdapter.
        Dim dataChanges As DataTable = shipper.GetChanges()

        ' Add the event handler. 
        AddHandler adapter.RowUpdated, New _
           SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)

        ' Update the datasource with the modified records.
        adapter.Update(dataChanges)

        ' Merge the two DataTables.
        shipper.Merge(dataChanges)

        ' Commit the changes.
        shipper.AcceptChanges()

        Console.WriteLine("Rows after merge.")
        Dim row As DataRow
        For Each row In shipper.Rows
            Console.WriteLine("{0}: {1}", row(0), row(1))
        Next
    End Using
End Sub

OnRowUpdated イベント ハンドラーでは、StatementTypeSqlRowUpdatedEventArgs をチェックして、行が挿入であるかどうかを調べています。The OnRowUpdated event handler checks the StatementType of the SqlRowUpdatedEventArgs to determine if the row is an insert. 挿入である場合は、Status プロパティを SkipCurrentRow に設定します。If it is, then the Status property is set to SkipCurrentRow. 行は更新されますが、その行の元の値は保持されます。The row is updated, but the original values in the row are preserved. プロシージャの本体では、Merge メソッドを呼び出すことによって、新しい ID 値を元の DataTable に結合し、最後に AcceptChanges を呼び出します。In the main body of the procedure, the Merge method is called to merge the new identity value into the original DataTable, and finally AcceptChanges is called.

protected static void OnRowUpdated(
    object sender, SqlRowUpdatedEventArgs e)
{
    // If this is an insert, then skip this row.
    if (e.StatementType == StatementType.Insert)
    {
        e.Status = UpdateStatus.SkipCurrentRow;
    }
}
Private Sub OnRowUpdated( _
    ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
    ' If this is an insert, then skip this row.
    If e.StatementType = StatementType.Insert Then
        e.Status = UpdateStatus.SkipCurrentRow
    End If
End Sub

Microsoft Access の Autonumber 値の取得Retrieving Microsoft Access Autonumber Values

このセクションでは、Jet 4.0 データベースから Autonumber 値を取得する方法を例で示します。This section includes a sample that shows how to retrieve Autonumber values from a Jet 4.0 database. Jet データベース エンジンは、複数のステートメントをバッチとして実行することも、出力パラメーターを使用することもできません。そのため、前述した方法では、挿入行に割り当てられた新しい Autonumber 値を取得することはできません。The Jet database engine does not support the execution of multiple statements in a batch or the use of output parameters, so it is not possible to use either of these techniques to return the new Autonumber value assigned to an inserted row. ただし、別の SELECT @ RowUpdated @IDENTITYステートメントを実行するコードをイベントハンドラーに追加して、新しい`Autonumber`値を取得することもできます。However, you can add code to the RowUpdated event handler that executes a separate SELECT @@IDENTITY statement to retrieve the new Autonumber value.

Example

この例では、MissingSchemaAction.AddWithKey を使ってスキーマ情報を追加する代わりに、あらかじめ適切なスキーマで DataTable を構成した後、OleDbDataAdapter を呼び出して DataTable にデータを格納しています。Instead of adding schema information using MissingSchemaAction.AddWithKey, this example configures a DataTable with the correct schema prior to calling the OleDbDataAdapter to fill the DataTable. この場合、 CategoryID列は、0から始まる挿入された各行に割り当てられた値をデクリメントAutoIncrementするように構成されています。これは、をにtrueAutoIncrementSeedを 0 AutoIncrementStepに、を-1 に設定することによって行います。In this case, the CategoryID column is configured to decrement the value assigned each inserted row starting from zero, by setting AutoIncrement to true, AutoIncrementSeed to 0, and AutoIncrementStep to -1. その後、2 つの新しい行を追加し、GetChanges を使って変更行を新しい DataTable に追加します。Update メソッドには、このデータ テーブルが渡されます。The code then adds two new rows and uses GetChanges to add the changed rows to a new DataTable that is passed to the Update method.

private static OleDbConnection connection = null;

private static void MergeIdentityColumns(OleDbConnection connection)
{
    using (connection)
    {
        // Create a DataAdapter based on a SELECT query.
        OleDbDataAdapter adapter = new OleDbDataAdapter(
         "SELECT CategoryID, CategoryName FROM Categories",
         connection);

        // Create the INSERT command for the new category.
        adapter.InsertCommand = new OleDbCommand(
          "INSERT INTO Categories (CategoryName) Values(?)", connection);
        adapter.InsertCommand.CommandType = CommandType.Text;

        // Add the parameter for the CategoryName.
        adapter.InsertCommand.Parameters.Add(
          "@CategoryName", OleDbType.VarWChar, 15, "CategoryName");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

        // Create a DataTable
        DataTable categories = new DataTable();

        // Create the CategoryID column and set its auto 
        // incrementing properties to decrement from zero. 
        DataColumn column = new DataColumn();
        column.DataType = System.Type.GetType("System.Int32");
        column.ColumnName = "CategoryID";
        column.AutoIncrement = true;
        column.AutoIncrementSeed = 0;
        column.AutoIncrementStep = -1;
        categories.Columns.Add(column);

        // Create the CategoryName column.
        column = new DataColumn();
        column.DataType = System.Type.GetType("System.String");
        column.ColumnName = "CategoryName";
        categories.Columns.Add(column);

        // Set the primary key on CategoryID.
        DataColumn[] pKey = new DataColumn[1];
        pKey[0] = categories.Columns["CategoryID"];
        categories.PrimaryKey = pKey;

        // Fetch the data and fill the DataTable
        adapter.Fill(categories);

        // Add a new row.
        DataRow newRow = categories.NewRow();
        newRow["CategoryName"] = "New Category";
        categories.Rows.Add(newRow);

        // Add another new row.
        DataRow newRow2 = categories.NewRow();
        newRow2["CategoryName"] = "Another New Category";
        categories.Rows.Add(newRow2);

        // Add changed rows to a new DataTable that will be
        // used to post the inserts to the database.
        DataTable dataChanges = categories.GetChanges();

        // Include an event to fill in the Autonumber value.
        adapter.RowUpdated +=
            new OleDbRowUpdatedEventHandler(OnRowUpdated);

        // Update the database, inserting the new rows. 
        adapter.Update(dataChanges);

        Console.WriteLine("Rows before merge:");
        foreach (DataRow row in categories.Rows)
        {
            {
                Console.WriteLine("  {0}: {1}", row[0], row[1]);
            }
        }

        // Merge the two DataTables.
        categories.Merge(dataChanges);

        // Commit the changes.
        categories.AcceptChanges();

        Console.WriteLine("Rows after merge:");
        foreach (DataRow row in categories.Rows)
        {
            {
                Console.WriteLine("  {0}: {1}", row[0], row[1]);
            }
        }
    }
}
Shared connection As OleDbConnection = Nothing

Private Shared Sub MergeIdentityColumns(ByVal connection As OleDbConnection)
    Using connection

        ' Create a DataAdapter based on a SELECT query.
        Dim adapter As OleDbDataAdapter = New OleDbDataAdapter( _
          "SELECT CategoryID, CategoryName FROM Categories", _
          connection)

        ' Create the INSERT command for the new category.
        adapter.InsertCommand = New OleDbCommand( _
          "INSERT INTO Categories (CategoryName) Values(?)", connection)
        adapter.InsertCommand.CommandType = CommandType.Text

        ' Add the parameter for the CategoryName.
        adapter.InsertCommand.Parameters.Add( _
          "@CategoryName", OleDbType.VarWChar, 15, "CategoryName")
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both

        ' Create a DataTable.
        Dim categories As DataTable = New DataTable

        ' Create the CategoryID column and set its auto
        ' incrementing properties to decrement from zero.
        Dim column As New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "CategoryID"
        column.AutoIncrement = True
        column.AutoIncrementSeed = 0
        column.AutoIncrementStep = -1
        categories.Columns.Add(column)

        ' Create the CategoryName column.
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "CategoryName"
        categories.Columns.Add(column)

        ' Set the primary key on CategoryID.
        Dim pKey(0) As DataColumn
        pKey(0) = categories.Columns("CategoryID")
        categories.PrimaryKey = pKey

        ' Fetch the data and fill the DataTable.
        adapter.Fill(categories)

        ' Add a new row.
        Dim newRow As DataRow = categories.NewRow()
        newRow("CategoryName") = "New Category"
        categories.Rows.Add(newRow)

        ' Add another new row.
        Dim newRow2 As DataRow = categories.NewRow()
        newRow2("CategoryName") = "Another New Category"
        categories.Rows.Add(newRow2)

        ' Add changed rows to a new DataTable that will be
        ' used to post the inserts to the database.
        Dim dataChanges As DataTable = categories.GetChanges()

        ' Include an event to fill in the Autonumber value.
        AddHandler adapter.RowUpdated, _
          New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)

        ' Update the database, inserting the new rows.
        adapter.Update(dataChanges)

        Console.WriteLine("Rows before merge:")
        Dim row1 As DataRow
        For Each row1 In categories.Rows
            Console.WriteLine("  {0}: {1}", row1(0), row1(1))
        Next

        ' Merge the two DataTables.
        categories.Merge(dataChanges)

        ' Commit the changes.
        categories.AcceptChanges()

        Console.WriteLine("Rows after merge:")
        Dim row As DataRow
        For Each row In categories.Rows
            Console.WriteLine("  {0}: {1}", row(0), row(1))
        Next
    End Using
End Sub

RowUpdated イベント ハンドラーでは、OleDbConnectionUpdate ステートメントと同じ、開いている OleDbDataAdapter を使用します。The RowUpdated event handler uses the same open OleDbConnection as the Update statement of the OleDbDataAdapter. このハンドラーでは、挿入された行について、StatementTypeOleDbRowUpdatedEventArgs をチェックします。It checks the StatementType of the OleDbRowUpdatedEventArgs for inserted rows. 挿入された行ごとOleDbCommandに、新しいが作成され@IDENTITY 、接続で SELECT @ ステートメントが実行Autonumberされ、新しい値が返されます。このDataRow値は、のCategoryID列に格納されます。For each inserted row a new OleDbCommand is created to execute the SELECT @@IDENTITY statement on the connection, returning the new Autonumber value, which is placed in the CategoryID column of the DataRow. 次に、Status の暗黙的な呼び出しを抑制するため、UpdateStatus.SkipCurrentRow プロパティを AcceptChanges に設定しています。The Status property is then set to UpdateStatus.SkipCurrentRow to suppress the hidden call to AcceptChanges. プロシージャの本体では、Merge メソッドを呼び出すことによって、2 つの DataTable を結合し、最後に AcceptChanges を呼び出します。In the main body of the procedure, the Merge method is called to merge the two DataTable objects, and finally AcceptChanges is called.

private static void OnRowUpdated(
  object sender, OleDbRowUpdatedEventArgs e)
{
    // Conditionally execute this code block on inserts only.
    if (e.StatementType == StatementType.Insert)
    {
        OleDbCommand cmdNewID = new OleDbCommand("SELECT @@IDENTITY",
            connection);
        // Retrieve the Autonumber and store it in the CategoryID column.
        e.Row["CategoryID"] = (int)cmdNewID.ExecuteScalar();
        e.Status = UpdateStatus.SkipCurrentRow;
    }
}
Private Shared Sub OnRowUpdated( _
    ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)
    ' Conditionally execute this code block on inserts only.
    If e.StatementType = StatementType.Insert Then
        ' Retrieve the Autonumber and store it in the CategoryID column.
        Dim cmdNewID As New OleDbCommand("SELECT @@IDENTITY", _
           connection)
        e.Row("CategoryID") = CInt(cmdNewID.ExecuteScalar)
        e.Status = UpdateStatus.SkipCurrentRow
    End If
End Sub

ID 値の取得Retrieving Identity Values

列内の値が一意である必要がある場合、列を ID として設定することがあります。We often set the column as identity when the values in the column must be unique. 新しいデータの ID 値が必要となる場合があります。And sometimes we need the identity value of new data. 次のサンプルは、ID 値を取得する方法を示しています。This sample demonstrates how to retrieve identity values:

  • データを挿入して ID 値を返すストアド プロシージャを作成します。Creates a stored procedure to insert data and return an identity value.

  • 新しいデータを挿入して結果を表示するコマンドを実行します。Executes a command to insert the new data and display the result.

  • SqlDataAdapter を使用し、新しいデータを挿入して結果を表示します。Uses SqlDataAdapter to insert new data and display the result.

サンプルをコンパイルして実行する前に、次のスクリプトを使用してサンプル データベースを作成する必要があります。Before you compile and run the sample, you must create the sample database, using the following script:

USE [master]
GO

CREATE DATABASE [MySchool]
GO

USE [MySchool]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CourseExtInfo] @CourseId int
as
select c.CourseID,c.Title,c.Credits,d.Name as DepartmentName
from Course as c left outer join Department as d on c.DepartmentID=d.DepartmentID
where c.CourseID=@CourseId

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[DepartmentInfo] @DepartmentId int,@CourseCount int output
as
select @CourseCount=Count(c.CourseID)
from course as c
where c.DepartmentID=@DepartmentId

select d.DepartmentID,d.Name,d.Budget,d.StartDate,d.Administrator
from Department as d
where d.DepartmentID=@DepartmentId

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[GetDepartmentsOfSpecifiedYear]
@Year int,@BudgetSum money output
AS
BEGIN
        SELECT @BudgetSum=SUM([Budget])
  FROM [MySchool].[dbo].[Department]
  Where YEAR([StartDate])=@Year

SELECT [DepartmentID]
      ,[Name]
      ,[Budget]
      ,[StartDate]
      ,[Administrator]
  FROM [MySchool].[dbo].[Department]
  Where YEAR([StartDate])=@Year

END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GradeOfStudent]
-- Add the parameters for the stored procedure here
@CourseTitle nvarchar(100),@FirstName nvarchar(50),
@LastName nvarchar(50),@Grade decimal(3,2) output
AS
BEGIN
select @Grade=Max(Grade)
from [dbo].[StudentGrade] as s join [dbo].[Course] as c on
s.CourseID=c.CourseID join [dbo].[Person] as p on s.StudentID=p.PersonID
where c.Title=@CourseTitle and p.FirstName=@FirstName
and p.LastName= @LastName
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertPerson]
-- Add the parameters for the stored procedure here
@FirstName nvarchar(50),@LastName nvarchar(50),
@PersonID int output
AS
BEGIN
    insert [dbo].[Person](LastName,FirstName) Values(@LastName,@FirstName)

    set @PersonID=SCOPE_IDENTITY()
END
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course]([CourseID] [nvarchar](10) NOT NULL,
[Year] [smallint] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Budget] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[Administrator] [int] NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[HireDate] [datetime] NULL,
[EnrollmentDate] [datetime] NULL,
[Picture] [varbinary](max) NULL,
 CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentGrade]([EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [nvarchar](10) NOT NULL,
[StudentID] [int] NOT NULL,
[Grade] [decimal](3, 2) NOT NULL,
 CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED
(
[EnrollmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[EnglishCourse]
as
select c.CourseID,c.Title,c.Credits,c.DepartmentID
from Course as c join Department as d on c.DepartmentID=d.DepartmentID
where d.Name=N'English'

GO
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)
SET IDENTITY_INSERT [dbo].[Department] ON

INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)
SET IDENTITY_INSERT [dbo].[Department] OFF
SET IDENTITY_INSERT [dbo].[Person] ON

INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (1, N'Hu', N'Nan', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (2, N'Norman', N'Laura', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (3, N'Olivotto', N'Nino', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (4, N'Anand', N'Arturo', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (5, N'Jai', N'Damien', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (6, N'Holt', N'Roger', CAST(0x000097F100000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (7, N'Martin', N'Randall', CAST(0x00008B1A00000000 AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[Person] OFF
SET IDENTITY_INSERT [dbo].[StudentGrade] ON

INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (1, N'C1045', 1, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (2, N'C1045', 2, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (3, N'C1045', 3, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (4, N'C1045', 4, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (5, N'C1045', 5, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (6, N'C1061', 1, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (7, N'C1061', 3, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (8, N'C1061', 4, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (9, N'C1061', 5, CAST(1.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (10, N'C2021', 1, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (11, N'C2021', 2, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (12, N'C2021', 4, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (13, N'C2021', 5, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (14, N'C2042', 1, CAST(2.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (15, N'C2042', 2, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (16, N'C2042', 3, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (17, N'C2042', 5, CAST(3.00 AS Decimal(3, 2)))
SET IDENTITY_INSERT [dbo].[StudentGrade] OFF
ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO
ALTER TABLE [dbo].[StudentGrade]  WITH CHECK ADD  CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Student]
GO

コード リストを次に示します。The code listing follows:

ヒント

このコード リストは、MySchool.mdb という名前の Access データベース ファイルを参照します。The code listing refers to an Access database file called MySchool.mdb. C# Code.msdn.microsoft.comから、(完全または Visual Basic サンプルプロジェクトの一部として) myschool .mdb をダウンロードできます。You can download MySchool.mdb (as part of the full C# or Visual Basic sample project) from code.msdn.microsoft.com.

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

class Program {
   static void Main(string[] args) {
      String SqlDbConnectionString = "Data Source=(local);Initial Catalog=MySchool;Integrated Security=True;Asynchronous Processing=true;";

      InsertPerson(SqlDbConnectionString, "Janice", "Galvin");
      Console.WriteLine();

      InsertPersonInAdapter(SqlDbConnectionString, "Peter", "Krebs");
      Console.WriteLine();

      String oledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Database\\MySchool.mdb";
      InsertPersonInJet4Database(oledbConnectionString, "Janice", "Galvin");
      Console.WriteLine();

      Console.WriteLine("Please press any key to exit.....");
      Console.ReadKey();
   }

   // Using stored procedure to insert a new row and retrieve the identity value
   static void InsertPerson(String connectionString, String firstName, String lastName) {
      String commandText = "dbo.InsertPerson";

      using (SqlConnection conn = new SqlConnection(connectionString)) {
         using (SqlCommand cmd = new SqlCommand(commandText, conn)) {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@FirstName", firstName));
            cmd.Parameters.Add(new SqlParameter("@LastName", lastName));
            SqlParameter personId = new SqlParameter("@PersonID", SqlDbType.Int);
            personId.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(personId);

            conn.Open();
            cmd.ExecuteNonQuery();

            Console.WriteLine("Person Id of new person:{0}", personId.Value);
         }
      }
   }

   // Using stored procedure in adapter to insert new rows and update the identity value.
   static void InsertPersonInAdapter(String connectionString, String firstName, String lastName) {
      String commandText = "dbo.InsertPerson";
      using (SqlConnection conn = new SqlConnection(connectionString)) {
         SqlDataAdapter mySchool = new SqlDataAdapter("Select PersonID,FirstName,LastName from [dbo].[Person]", conn);

         mySchool.InsertCommand = new SqlCommand(commandText, conn);
         mySchool.InsertCommand.CommandType = CommandType.StoredProcedure;

         mySchool.InsertCommand.Parameters.Add(
             new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, "FirstName"));
         mySchool.InsertCommand.Parameters.Add(
             new SqlParameter("@LastName", SqlDbType.NVarChar, 50, "LastName"));

         SqlParameter personId = mySchool.InsertCommand.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.Int, 0, "PersonID"));
         personId.Direction = ParameterDirection.Output;

         DataTable persons = new DataTable();
         mySchool.Fill(persons);

         DataRow newPerson = persons.NewRow();
         newPerson["FirstName"] = firstName;
         newPerson["LastName"] = lastName;
         persons.Rows.Add(newPerson);

         mySchool.Update(persons);
         Console.WriteLine("Show all persons:");
         ShowDataTable(persons, 14);
      }
   }

   /// For a Jet 4.0 database, we need use the single statement and event handler to insert new rows and retrieve the identity value.
   static void InsertPersonInJet4Database(String connectionString, String firstName, String lastName) {
      String commandText = "Insert into Person(FirstName,LastName) Values(?,?)";
      using (OleDbConnection conn = new OleDbConnection(connectionString)) {
         OleDbDataAdapter mySchool = new OleDbDataAdapter("Select PersonID,FirstName,LastName from Person", conn);

         // Create Insert Command
         mySchool.InsertCommand = new OleDbCommand(commandText, conn);
         mySchool.InsertCommand.CommandType = CommandType.Text;

         mySchool.InsertCommand.Parameters.Add(new OleDbParameter("@FirstName", OleDbType.VarChar, 50, "FirstName"));
         mySchool.InsertCommand.Parameters.Add(new OleDbParameter("@LastName", OleDbType.VarChar, 50, "LastName"));
         mySchool.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

         DataTable persons = CreatePersonsTable();

         mySchool.Fill(persons);

         DataRow newPerson = persons.NewRow();
         newPerson["FirstName"] = firstName;
         newPerson["LastName"] = lastName;
         persons.Rows.Add(newPerson);

         DataTable dataChanges = persons.GetChanges();

         mySchool.RowUpdated += OnRowUpdated;

         mySchool.Update(dataChanges);

         Console.WriteLine("Data before merging:");
         ShowDataTable(persons, 14);
         Console.WriteLine();

         persons.Merge(dataChanges);
         persons.AcceptChanges();

         Console.WriteLine("Data after merging");
         ShowDataTable(persons, 14);
      }
   }

   static void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs e) {
      if (e.StatementType == StatementType.Insert) {
         // Retrieve the identity value
         OleDbCommand cmdNewId = new OleDbCommand("Select @@IDENTITY", e.Command.Connection);
         e.Row["PersonID"] = (Int32)cmdNewId.ExecuteScalar();

         // After the status is changed, the original values in the row are preserved. And the
         // Merge method will be called to merge the new identity value into the original DataTable.
         e.Status = UpdateStatus.SkipCurrentRow;
      }
   }

   // Create the Persons table before filling.
   private static DataTable CreatePersonsTable() {
      DataTable persons = new DataTable();

      DataColumn personId = new DataColumn();
      personId.DataType = Type.GetType("System.Int32");
      personId.ColumnName = "PersonID";
      personId.AutoIncrement = true;
      personId.AutoIncrementSeed = 0;
      personId.AutoIncrementStep = -1;
      persons.Columns.Add(personId);

      DataColumn firstName = new DataColumn();
      firstName.DataType = Type.GetType("System.String");
      firstName.ColumnName = "FirstName";
      persons.Columns.Add(firstName);

      DataColumn lastName = new DataColumn();
      lastName.DataType = Type.GetType("System.String");
      lastName.ColumnName = "LastName";
      persons.Columns.Add(lastName);

      DataColumn[] pkey = { personId };
      persons.PrimaryKey = pkey;

      return persons;
   }

   private static void ShowDataTable(DataTable table, Int32 length) {
      foreach (DataColumn col in table.Columns) {
         Console.Write("{0,-" + length + "}", col.ColumnName);
      }
      Console.WriteLine();

      foreach (DataRow row in table.Rows) {
         foreach (DataColumn col in table.Columns) {
            if (col.DataType.Equals(typeof(DateTime)))
               Console.Write("{0,-" + length + ":d}", row[col]);
            else if (col.DataType.Equals(typeof(Decimal)))
               Console.Write("{0,-" + length + ":C}", row[col]);
            else
               Console.Write("{0,-" + length + "}", row[col]);
         }

         Console.WriteLine();
      }
   }
}

関連項目See also