Recuperar valores autonuméricos y de identidadRetrieving Identity or Autonumber Values

Una clave principal de una base de datos relacional es una columna o combinación de columnas que siempre contienen valores únicos.A primary key in a relational database is a column or combination of columns that always contain unique values. Conocer el valor de la clave principal permite localizar la fila que la contiene.Knowing the primary key value allows you to locate the row that contains it. Los motores de bases de datos relacionales, como SQL Server, Oracle y Microsoft Access/Jet admiten la creación de columnas de incremento automático que pueden designarse como claves principales.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. Estos valores los genera el servidor cuando se agregan filas a una tabla.These values are generated by the server as rows are added to a table. En SQL Server se establece la propiedad de identidad de una columna, en Oracle se crea una secuencia y en Microsoft Access se crea una columna Autonumérica.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 también se puede utilizar para generar de manera automática valores incrementales estableciendo la propiedad AutoIncrement en true.A DataColumn can also be used to generate automatically incrementing values by setting the AutoIncrement property to true. No obstante, podría haber valores duplicados en instancias distintas de DataTable si varias aplicaciones cliente están generando por separado valores incrementales de manera automática.However, you might end up with duplicate values in separate instances of a DataTable, if multiple client applications are independently generating automatically incrementing values. Si se tiene un servidor que genera de manera automática valores incrementales se eliminan posibles conflictos, pues se permite a cada usuario recuperar el valor generado para cada fila insertada.Having the server generate automatically incrementing values eliminates potential conflicts by allowing each user to retrieve the generated value for each inserted row.

Durante una llamada al método Update de DataAdapter, la base de datos puede volver a enviar datos a la aplicación ADO.NET como parámetros de salida o como el primer registro devuelto del conjunto de resultados de una instrucción SELECT ejecutada en el mismo lote que la instrucción INSERT.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. ADO.NET puede recuperar estos valores y actualizar las columnas correspondientes en el DataRow que se está actualizando.ADO.NET can retrieve these values and update the corresponding columns in the DataRow being updated.

Algunos motores de base de datos, como los de Microsoft Access Jet, no admiten parámetros de salida y no pueden procesar varias instrucciones en un único lote.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. Cuando trabaje con el motor de base de datos de Jet, puede recuperar el nuevo valor Autonumérico generado para una fila insertada ejecutando un comando SELECT distinto en un controlador de eventos para el evento RowUpdated de DataAdapter.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.

Nota

Una opción alternativa al uso de un valor de incremento automático es utilizar el método NewGuid de un objeto Guid para generar un GUID (identificador único global) en el equipo cliente que se pueda copiar al servidor cuando se inserte una nueva fila.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. El método NewGuid genera un valor binario de 16 bits que se crea mediante un algoritmo que permite que haya una alta probabilidad de que no se duplique ningún valor.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. En una base de datos de SQL Server, el GUID se almacena en una columna uniqueidentifier que SQL Server puede generar automáticamente mediante la función Transact-SQL 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. Utilizar un GUID como clave principal puede afectar de manera negativa al rendimiento.Using a GUID as a primary key can adversely affect performance. SQL Server proporciona compatibilidad con la NEWSEQUENTIALID() función, que genera un GUID secuencial que no se garantiza que sea globalmente único, pero que se puede indizar de forma más eficaz.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.

Recuperar valores de columnas de identidad de SQL ServerRetrieving SQL Server Identity Column Values

Cuando trabaje con Microsoft SQL Server, puede crear procedimientos almacenados con un parámetro de salida para devolver el valor de identidad de una fila insertada.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. La siguiente tabla describe las tres funciones de Transact-SQL en SQL Server que se pueden utilizar para recuperar valores de columna de identidad.The following table describes the three Transact-SQL functions in SQL Server that can be used to retrieve identity column values.

FunciónFunction DescripciónDescription
SCOPE_IDENTITYSCOPE_IDENTITY Devuelve el último valor de identidad en el ámbito de ejecución actual.Returns the last identity value within the current execution scope. SCOPE_IDENTITY se recomienda en la mayoría de los casos.SCOPE_IDENTITY is recommended for most scenarios.
@@IDENTITY@@IDENTITY Contiene el último valor de identidad generado en cualquier tabla de la sesión actual.Contains the last identity value generated in any table in the current session. @ @IDENTITY puede verse afectado por los desencadenadores y es posible que no devuelva el valor de identidad que espera.@@IDENTITY can be affected by triggers and may not return the identity value that you expect.
IDENT_CURRENTIDENT_CURRENT Devuelve el último valor de identidad generado para una tabla concreta de cualquier sesión y en cualquier ámbito.Returns the last identity value generated for a specific table in any session and any scope.

El siguiente procedimiento almacenado muestra cómo insertar una fila en la tabla Categories y cómo utilizar un parámetro de salida para devolver el nuevo valor de identidad generado por la función TRANSACT-SQL SCOPE_IDENTITY ().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()

El procedimiento almacenado se puede especificar como el origen de InsertCommand de un objeto SqlDataAdapter.The stored procedure can then be specified as the source of the InsertCommand of a SqlDataAdapter object. La propiedad CommandType de InsertCommand debe establecerse en StoredProcedure.The CommandType property of the InsertCommand must be set to StoredProcedure. La salida de identidad se recupera creando un SqlParameter que tiene un ParameterDirection de Output.The identity output is retrieved by creating a SqlParameter that has a ParameterDirection of Output. Cuando InsertCommand se procesa, se devuelve el valor de identidad de incremento automático y se coloca en la columna CategoryID de la fila actual si se establece la UpdatedRowSource propiedad del comando INSERT en UpdateRowSource.OutputParameters o en UpdateRowSource.Both .When 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.

Si el comando de inserción ejecuta un lote que incluye tanto una instrucción INSERT como una instrucción SELECT que devuelven el nuevo valor de identidad, entonces puede recuperar el nuevo valor estableciendo la propiedad UpdatedRowSource del comando de inserción en 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

Combinar nuevos valores de identidadMerging New Identity Values

Un caso frecuente es llamar al método GetChanges de DataTable para crear una copia que contiene únicamente filas modificadas y utilizar la nueva copia al llamar al método Update de DataAdapter.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. Esto es especialmente útil cuando hay que serializar las filas modificadas en un componente independiente que realiza la actualización.This is especially useful when you need to marshal the changed rows to a separate component that performs the update. Después de la actualización, la copia puede contener nuevos valores de identidad que se deben volver a combinar en el DataTable original.Following the update, the copy can contain new identity values that must then be merged back into the original DataTable. Probablemente los nuevos valores de identidad son diferentes a los valores originales de DataTable.The new identity values are likely to be different from the original values in the DataTable. Para realizar la combinación, deben conservarse los valores originales de las columnas de incremento automático de la copia, con el fin de poder localizar y actualizar las filas existentes en el original DataTable , en lugar de anexar nuevas filas que contengan los nuevos valores de identidad.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. No obstante, de manera predeterminada estos valores se pierden después de una llamada al método Update de DataAdapter, debido a que se llama implícitamente a AcceptChanges en cada DataRow actualizada.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.

Hay dos maneras de mantener los valores originales de DataColumn en DataRow durante una actualización de DataAdapter:There are two ways to preserve the original values of a DataColumn in a DataRow during a DataAdapter update:

  • El primer método para mantener los valores originales consiste en establecer la propiedad AcceptChangesDuringUpdate de DataAdapter en false.The first method of preserving the original values is to set the AcceptChangesDuringUpdate property of the DataAdapter to false. Esto afecta a cada DataRow de DataTable que se está actualizando.This affects every DataRow in the DataTable being updated. Para más información y ver un código de ejemplo, vea AcceptChangesDuringUpdate.For more information and a code example, see AcceptChangesDuringUpdate.

  • El segundo método consiste en escribir código en el controlador de eventos RowUpdated de DataAdapter para establecer Status en SkipCurrentRow.The second method is to write code in the RowUpdated event handler of the DataAdapter to set the Status to SkipCurrentRow. DataRow se actualiza pero se mantiene el valor original de cada DataColumn.The DataRow is updated but the original value of each DataColumn is preserved. Este método permite mantener los valores originales en algunas filas y no en otras.This method enables you to preserve the original values for some rows and not for others. Por ejemplo, el código puede mantener los valores originales de filas agregadas y no los de filas editadas o eliminadas comprobando primero StatementType y, a continuación, estableciendo Status en SkipCurrentRow únicamente para filas con un StatementType de Insert.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.

Cuando se utiliza alguno de estos métodos para mantener los valores originales de DataRow durante una actualización de DataAdapter, ADO.NET realiza una serie de acciones para establecer los valores actuales de DataRow a los nuevos valores devueltos por parámetros de salida o por la primera fila devuelta de un conjunto de resultados, al tiempo que se mantiene el valor original de cada 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. Primero, se llama al método AcceptChanges de DataRow para mantener los valores actuales como valores originales y, a continuación, se asignan los nuevos valores.First, the AcceptChanges method of the DataRow is called to preserve the current values as original values, and then the new values are assigned. Después de estas acciones, las DataRows que tienen la propiedad RowState establecida en Added tendrán su propiedad RowState establecida en Modified, lo que puede ser inesperado.Following these actions, DataRows that had their RowState property set to Added will have their RowState property set to Modified, which may be unexpected.

El modo en que se aplican los resultados del comando a cada DataRow que se actualiza lo determina la propiedad UpdatedRowSource de cada DbCommand.How the command results are applied to each DataRow being updated is determined by the UpdatedRowSource property of each DbCommand. Esta propiedad se establece en un valor desde la enumeración UpdateRowSource.This property is set to a value from the UpdateRowSource enumeration.

La siguiente tabla describe cómo afectan los valores de enumeración UpdateRowSource a la propiedad RowState de las filas actualizadas.The following table describes how the UpdateRowSource enumeration values affect the RowState property of updated rows.

Nombre del miembroMember name DescripciónDescription
Both Se llama a AcceptChanges y tanto los parámetros de salida como los valores de la primera fila de cualquier conjunto de resultados devuelto se colocan en la DataRow que se está actualizando.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. Si no hay valores que aplicar, RowState será Unchanged.If there are no values to apply, the RowState will be Unchanged.
FirstReturnedRecord Si se devuelve una fila, se llama a AcceptChanges y la fila se asigna a la fila modificada en DataTable, estableciendo RowState en Modified.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. Si no se devuelve ninguna fila, entonces no se llama a AcceptChanges y RowState permanece en Added.If no row is returned, then AcceptChanges is not called and the RowState remains Added.
None Se pasan por alto todos los parámetros o filas devueltos.Any returned parameters or rows are ignored. No hay llamada a AcceptChanges y RowState permanece en Added.There is no call to AcceptChanges and the RowState remains Added.
OutputParameters Se llama a AcceptChanges y todos los parámetros de salida se asignan a la fila modificada en DataTable, estableciendo RowState en Modified.AcceptChanges is called and any output parameters are mapped to the changed row in the DataTable, setting the RowState to Modified. Si no hay parámetros de salida, RowState será Unchanged.If there are no output parameters, the RowState will be Unchanged.

EjemploExample

Este ejemplo muestra la extracción de filas modificadas desde DataTable y el uso de SqlDataAdapter para actualizar el origen de datos y recuperar un nuevo valor de columna de identidad.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 ejecuta dos instrucciones Transact-SQL; la primera es la instrucción INSERT y la segunda es la instrucción 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();

La propiedad UpdatedRowSource del comando de inserción se establece en UpdateRowSource.FirstReturnedRow y la propiedad MissingSchemaAction de DataAdapter se establece en 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 se rellena y el código agrega una nueva fila a DataTable.The DataTable is filled and the code adds a new row to the DataTable. A continuación, las filas modificadas se extraen en un nuevo DataTable, que se pasa a DataAdapter, el cual actualiza el servidor.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

El controlador de eventos OnRowUpdated comprueba StatementType de SqlRowUpdatedEventArgs para determinar si la fila es una inserción.The OnRowUpdated event handler checks the StatementType of the SqlRowUpdatedEventArgs to determine if the row is an insert. Si lo es, entonces la propiedad se establece Status en SkipCurrentRow.If it is, then the Status property is set to SkipCurrentRow. La fila está actualizada, pero los valores originales de la fila se mantienen.The row is updated, but the original values in the row are preserved. En el cuerpo principal del procedimiento, se llama al método Merge para fusión mediante combinación el nuevo valor de identidad en el DataTable original y, finalmente, se llama a 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

Recuperar valores de autonumeración de Microsoft AccessRetrieving Microsoft Access Autonumber Values

Esta sección incluye un ejemplo que muestra cómo recuperar valores de Autonumber desde una base de datos de Jet 4.0.This section includes a sample that shows how to retrieve Autonumber values from a Jet 4.0 database. El motor de la base de datos de Jet no admite la ejecución de varias instrucciones en un lote o el uso de parámetros de salida, por lo que no es posible utilizar ninguna de estas técnicas para devolver el nuevo valor Autonumber asignado a una fila insertada.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. Sin embargo, puede agregar código al RowUpdated controlador de eventos que ejecuta una instrucción SELECT @ independiente @IDENTITY para recuperar el nuevo Autonumber valor.However, you can add code to the RowUpdated event handler that executes a separate SELECT @@IDENTITY statement to retrieve the new Autonumber value.

EjemploExample

En lugar de agregar información de esquema utilizando MissingSchemaAction.AddWithKey, este ejemplo configura DataTable con el esquema adecuado antes de llamar a OleDbDataAdapter para rellenar 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. En este caso, la columna CategoryID se configura para disminuir el valor asignado a cada fila insertada a partir de cero, estableciendo AutoIncrement en true , AutoIncrementSeed en 0 y AutoIncrementStep en-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. Entonces, el código agrega dos filas nuevas y utiliza GetChanges para agregar las filas modificadas a un nuevo DataTable que se pasa al método 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

El controlador de eventos RowUpdated utiliza el mismo OleDbConnection abierto que la instrucción Update de OleDbDataAdapter.The RowUpdated event handler uses the same open OleDbConnection as the Update statement of the OleDbDataAdapter. Comprueba el StatementType de OleDbRowUpdatedEventArgs de las filas insertadas.It checks the StatementType of the OleDbRowUpdatedEventArgs for inserted rows. Para cada fila insertada OleDbCommand , se crea un nuevo para ejecutar la @IDENTITY instrucción SELECT @ en la conexión, devolviendo el nuevo Autonumber valor, que se coloca en la columna CategoryID de DataRow .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. La propiedad Status se establece luego en UpdateStatus.SkipCurrentRow para suprimir la llamada oculta a AcceptChanges.The Status property is then set to UpdateStatus.SkipCurrentRow to suppress the hidden call to AcceptChanges. En el cuerpo principal del procedimiento, se llama al método Merge para fusionar mediante combinación los dos objetos DataTable y, finalmente, se llama a 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

Recuperar valores de identidadRetrieving Identity Values

A menudo se establece la columna como identidad cuando los valores de la columna deben ser únicos.We often set the column as identity when the values in the column must be unique. A veces se necesita el valor de identidad de los nuevos datos.And sometimes we need the identity value of new data. En este ejemplo se muestra cómo recuperar los valores de identidad:This sample demonstrates how to retrieve identity values:

  • Crea un procedimiento almacenado para insertar los datos y devolver un valor de identidad.Creates a stored procedure to insert data and return an identity value.

  • Ejecuta un comando para insertar los nuevos datos y mostrar el resultado.Executes a command to insert the new data and display the result.

  • Usa SqlDataAdapter para insertar nuevos datos y mostrar el resultado.Uses SqlDataAdapter to insert new data and display the result.

Antes de compilar y ejecutar el ejemplo, debe crear la base de datos de ejemplo mediante el script siguiente: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

A continuación se incluye la lista de código:The code listing follows:

Sugerencia

La lista de código hace referencia a un archivo de base de datos de Access denominado MySchool.mdb.The code listing refers to an Access database file called MySchool.mdb. Puede descargar el. mdb (como parte del proyecto de ejemplo completo de C# o Visual Basic) de code.msdn.Microsoft.com.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();
      }
   }
}

Consulte tambiénSee also