Recuperación de valores de identidad o autonuméricos

Artículo
05/23/2023

Se aplica a: .NET Framework .NET .NET Standard

Una clave principal de una base de datos relacional es una columna o combinación de columnas que siempre contienen valores únicos. Conocer el valor de la clave principal permite localizar la fila que la contiene. 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. Estos valores los genera el servidor cuando se agregan filas a una tabla. 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.

DataColumn también se puede utilizar para generar de manera automática valores incrementales estableciendo la propiedad AutoIncrement en 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. 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.

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. El proveedor de datos SqlClient de Microsoft para SQL Server puede recuperar estos valores y actualizar las columnas correspondientes en el DataRow que se está actualizando.

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. 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. 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(). Utilizar un GUID como clave principal puede afectar de manera negativa al rendimiento. SQL Server proporciona compatibilidad para la función NEWSEQUENTIALID(), que genera un GUID secuencial que no está garantizado que sea único globalmente, pero que se puede indexar de forma más eficaz.

Recuperación de los valores de la columna de identidad de SQL Server

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. La siguiente tabla describe las tres funciones de Transact-SQL en SQL Server que se pueden utilizar para recuperar valores de columna de identidad.

Función	Descripción
SCOPE_IDENTITY	Devuelve el último valor de identidad en el ámbito de ejecución actual. SCOPE_IDENTITY se recomienda en la mayoría de los casos.
@@IDENTITY	Contiene el último valor de identidad generado en cualquier tabla de la sesión actual. @@IDENTITY puede verse afectado por los desencadenadores y no devolver el valor de identidad esperado.
IDENT_CURRENT	Devuelve el último valor de identidad generado para una tabla concreta de cualquier sesión y en cualquier ámbito.

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

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. La propiedad CommandType de InsertCommand debe establecerse en StoredProcedure. La salida de identidad se recupera creando un SqlParameter que tiene un ParameterDirection de Output. Cuando se procesa InsertCommand, el valor de identidad de incremento automático se devuelve y se coloca en la columna CategoryID de la fila actual si se establece la propiedad UpdatedRowSource del comando de inserción en UpdateRowSource.OutputParameters o 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.

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]);
            }
        }
    }
}

Combinación de nuevos valores de identidad

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. Esto es especialmente útil cuando hay que serializar las filas modificadas en un componente independiente que realiza la actualización. Después de la actualización, la copia puede contener nuevos valores de identidad que se deben volver a combinar en el DataTable original. Probablemente los nuevos valores de identidad son diferentes a los valores originales de DataTable. Para conseguir la combinación, se deben mantener los valores originales de las columnas AutoIncrement en la copia para poder localizar y actualizar filas existentes en el DataTable original, en lugar de anexar filas nuevas con los nuevos valores de identidad. 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.

Hay dos maneras de mantener los valores originales de DataColumn en DataRow durante una actualización de DataAdapter:

El primer método para mantener los valores originales consiste en establecer la propiedad AcceptChangesDuringUpdate de DataAdapter en false. Esta configuración afecta a cada DataRow de DataTable que se está actualizando. Para más información y ver un código de ejemplo, vea AcceptChangesDuringUpdate.
El segundo método consiste en escribir código en el controlador de eventos RowUpdated de DataAdapter para establecer Status en SkipCurrentRow. DataRow se actualiza pero se mantiene el valor original de cada DataColumn. Este método permite mantener los valores originales en algunas filas y no en otras. 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.

Cuando se usa cualquiera de estos métodos para conservar los valores originales en un DataRow durante una actualización de DataAdapter, el adaptador de datos SqlClient de Microsoft para SQL Server realiza una serie de acciones para establecer los valores actuales de DataRow en los nuevos valores devueltos por los parámetros de salida o por la primera fila devuelta de un conjunto de resultados, al tiempo que se conserva el valor original en cada 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. 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.

El modo en que se aplican los resultados del comando a cada DataRow que se actualiza lo determina la propiedad UpdatedRowSource de cada DbCommand. Esta propiedad se establece en un valor desde la enumeración UpdateRowSource.

La siguiente tabla describe cómo afectan los valores de enumeración UpdateRowSource a la propiedad RowState de las filas actualizadas.

Nombre del miembro	Descripción
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. Si no hay valores que aplicar, `RowState` será 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`. Si no se devuelve ninguna fila, entonces no se llama a `AcceptChanges` y `RowState` permanece en `Added`.
None	Se pasan por alto todos los parámetros o filas devueltos. No hay llamada a `AcceptChanges` y `RowState` permanece en `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`. Si no hay parámetros de salida, `RowState` será `Unchanged`.

Ejemplo

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. InsertCommand ejecuta dos instrucciones Transact-SQL; la primera es la instrucción INSERT y la segunda es la instrucción SELECT.

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. DataTable se rellena y el código agrega una nueva fila a DataTable. A continuación, las filas modificadas se extraen en un nuevo DataTable, que se pasa a DataAdapter, el cual actualiza el servidor.

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]);
            }
        }
    }
}

El controlador de eventos OnRowUpdated comprueba StatementType de SqlRowUpdatedEventArgs para determinar si la fila es una inserción. Si lo es, entonces la propiedad se establece Status en SkipCurrentRow. La fila está actualizada, pero los valores originales de la fila se mantienen. 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.

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;
    }
}