Recuperare i valori Identity o di numerazione automatica

Articolo
05/23/2023

Si applica a: .NET Framework .NET .NET Standard

Per chiave primaria di un database relazionale si intende una colonna o una combinazione di colonne che contengono sempre valori univoci. Se si conosce il valore della chiave primaria, è possibile individuare la riga che lo contiene. Nei motori dei database relazionali, ad esempio SQL Server, Oracle e Microsoft Access/Jet, è supportata la creazione di colonne a incremento automatico che è possibile impostare come chiavi primarie. Tali valori vengono generati dal server quando si aggiungono righe a una tabella. In SQL Server viene impostata la proprietà Identity di una colonna, in Oracle viene creata una sequenza, mentre in Microsoft Access viene creata una colonna Contatore.

È inoltre possibile usare un oggetto DataColumn per generare valori a incremento automatico impostando la proprietà AutoIncrement su true. Se, tuttavia, più applicazioni client generano valori a incremento automatico in modo indipendente l'una dall'altra, è possibile che in istanze distinte di un oggetto DataTable siano presenti valori duplicati. Se invece i valori a incremento automatico vengono generati dal server, è possibile eliminare possibili conflitti in quanto si consente a ogni utente di recuperare il valore generato per ciascuna riga inserita.

Durante una chiamata al metodo Update di un DataAdapter, il database può restituire dati all'applicazione ADO.NET sotto forma di parametri di output o di primo record restituito del set di risultati di un'istruzione SELECT eseguita nello stesso batch dell'istruzione INSERT. Il provider di dati Microsoft SqlClient per SQL Server può recuperare questi valori e aggiornare le colonne corrispondenti nell'oggetto DataRow da aggiornare.

Nota

In alternativa a un valore a incremento automatico, è possibile usare il metodo NewGuid di un oggetto Guid per generare nel computer client un GUID che può essere copiato nel server a ogni nuova riga inserita. Il metodo NewGuid genera un valore binario a 16 byte per la cui creazione viene usato un algoritmo che consente con buona probabilità di evitare valori duplicati. In un database SQL Server un GUID viene archiviato in una colonna uniqueidentifier che SQL Server è in grado di generare automaticamente usando la funzione Transact-SQL NEWID(). L'utilizzo di un GUID come chiave primaria può influire negativamente sulle prestazioni. SQL Server include il supporto per la funzione NEWSEQUENTIALID() che genera un GUID sequenziale di cui non viene garantita l'univocità a livello globale ma che può essere indicizzato in modo più efficiente.

Recuperare i valori della colonna Identity di SQL Server

Quando si usa Microsoft SQL Server, è possibile creare una stored procedure con un parametro di output in modo che venga restituito il valore della colonna Identity per una riga inserita. Nella tabella seguente sono descritte le tre funzioni Transact-SQL disponibili in SQL Server che è possibile usare per recuperare i valori delle colonne Identity.

Funzione	Descrizione
SCOPE_IDENTITY	Restituisce l'ultimo valore della colonna Identity compreso nell'ambito di esecuzione corrente. SCOPE_IDENTITY è consigliato per la maggior parte degli scenari.
@@IDENTITY	Contiene l'ultimo valore di colonna Identity generato in qualsiasi tabella nella sessione corrente. L'utilizzo di trigger può influire sul funzionamento di @@IDENTITY, pertanto è possibile che il valore previsto non venga restituito.
IDENT_CURRENT	Restituisce l'ultimo valore di colonna Identity generato per una tabella specifica in qualsiasi sessione e in qualsiasi ambito.

Nella stored procedure seguente viene illustrato come inserire una riga nella tabella Categories e usare un parametro di output per restituire il nuovo valore Identity generato dalla funzione SCOPE_IDENTITY() di Transact-SQL.

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

È quindi possibile specificare la stored procedure come origine di InsertCommand in un oggetto SqlDataAdapter. La proprietà CommandType di InsertCommand deve essere impostata su StoredProcedure. Per recuperare l'output relativo all'identità, viene creato un oggetto SqlParameter il cui ParameterDirection è Output. Quando viene eseguito InsertCommand, il valore Identity a incremento automatico viene restituito e inserito nella colonna CategoryID della riga corrente se si imposta la proprietà UpdatedRowSource del comando di inserimento su UpdateRowSource.OutputParameters o su UpdateRowSource.Both.

Se il comando di inserimento esegue un batch in cui sono incluse sia un'istruzione INSERT che un'istruzione SELECT che restituisce il nuovo valore Identity, è possibile recuperare il nuovo valore impostando la proprietà UpdatedRowSource del comando di inserimento su 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]);
            }
        }
    }
}

Unire nuovi valori Identity

In uno scenario comune viene chiamato il metodo GetChanges di un oggetto DataTable per creare una copia che contiene solo le righe modificate e viene usata la nuova copia durante la chiamata al metodo Update di un oggetto DataAdapter. Tale scenario risulta particolarmente utile quando è necessario effettuare il marshalling delle righe modificate in un componente diverso che esegue l'aggiornamento. In seguito all'aggiornamento, la copia può contenere nuovi valori Identity che devono essere reinseriti nell'oggetto DataTable originale. È probabile che i nuovi valori Identity siano diversi dai valori originali di DataTable. Per eseguire il merge, è necessario mantenere i valori originali delle colonne AutoIncrement nella copia per poter individuare e aggiornare le righe esistenti nell'oggetto DataTable originale, anziché accodare altre righe che contengono i nuovi valori Identity. Per impostazione predefinita, i valori originali vengono tuttavia persi dopo una chiamata al metodo Update di un oggetto DataAdapter, perché viene chiamato implicitamente AcceptChanges per ogni DataRow aggiornato.

Sono disponibili sono due tecniche per mantenere i valori originali di un oggetto DataColumn in un DataRow durante un aggiornamento di DataAdapter:

La prima tecnica per mantenere i valori originali consiste nell'impostare la proprietà AcceptChangesDuringUpdate di DataAdapter su false. Questa configurazione ha effetto su ogni DataRow nel DataTable da aggiornare. Per altre informazioni ed esempi di codice, vedere AcceptChangesDuringUpdate.
La seconda tecnica consiste invece nello scrivere codice nel gestore eventi RowUpdated di DataAdapter per impostare Status su SkipCurrentRow. DataRow viene aggiornato ma il valore originale di ogni DataColumn viene mantenuto. Questa tecnica consente di mantenere i valori originali per alcune righe ma non per altre. Ad esempio, il codice può mantenere i valori originali per le righe aggiunte e non per quelle modificate o eliminate controllando dapprima StatementType e quindi impostando Status su SkipCurrentRow solo per le righe in cui il valore di StatementType è Insert.

Quando uno di questi metodi viene usato per mantenere i valori originali in un DataRow durante un aggiornamento di DataAdapter, l'adattatore dati Microsoft SqlClient per SQL Server esegue una serie di azioni per impostare i valori correnti del DataRow sui nuovi valori restituiti dai parametri di output o dalla prima riga restituita di un set di risultati, mantenendo comunque il valore originale in ogni DataColumn. Viene innanzitutto chiamato il metodo AcceptChanges di DataRow per mantenere i valori correnti come valori originali, quindi vengono assegnati i nuovi valori. In seguito a queste azioni, è possibile che inaspettatamente la proprietà DataRows degli oggetti RowState la cui proprietà Added è stata impostata su RowState verrà impostata su Modified.

La modalità di applicazione dei risultati del comando a ogni DataRow da aggiornare dipende dalla proprietà UpdatedRowSource di ogni DbCommand. Questa proprietà è impostata su un valore restituito dall'enumerazione UpdateRowSource.

Nella tabella seguente viene descritto il modo in cui i diversi valori dell'enumerazione UpdateRowSource influiscono sulla proprietà RowState delle righe aggiornate.

Nome del membro	Descrizione
Both	Viene chiamato `AcceptChanges` e i valori dei parametri di output e/o i valori nella prima riga di qualsiasi set di risultati restituito vengono inseriti nell'oggetto `DataRow` da aggiornare. Se non sono disponibili valori da applicare, il valore di `RowState` sarà Unchanged.
FirstReturnedRecord	Se è stata restituita una riga, viene chiamato `AcceptChanges` e viene eseguito il mapping della riga alla riga modificata in `DataTable`, impostando `RowState` su `Modified`. Se non viene restituita nessuna riga, `AcceptChanges` non viene chiamato e `RowState` rimane impostato su `Added`.
None	Eventuali parametri o righe restituite vengono ignorati. Non viene eseguita nessuna chiamata a `AcceptChanges` e `RowState` rimane impostato su `Added`.
OutputParameters	Viene chiamato `AcceptChanges` e viene eseguito il mapping degli eventuali parametri di output alla riga modificata in `DataTable`, impostando `RowState` su `Modified`. Se non sono disponibili parametri di output, il valore di `RowState` sarà `Unchanged`.

Esempio

In questo esempio vengono illustrati l'estrazione di righe modificate da un oggetto DataTable e l'uso di SqlDataAdapter per aggiornare l'origine dati e recuperare un nuovo valore per la colonna Identity. InsertCommand esegue due istruzioni Transact-SQL: la prima è l'istruzione INSERT, mentre la seconda è un'istruzione SELECT che usa la funzione SCOPE_IDENTITY per recuperare il valore Identity.

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

La proprietà UpdatedRowSource del comando di inserimento viene impostata su UpdateRowSource.FirstReturnedRow, mentre la proprietà MissingSchemaAction di DataAdapter viene impostata su MissingSchemaAction.AddWithKey. DataTable viene riempito e il codice aggiunge una nuova riga a DataTable. Le righe modificate vengono quindi estratte in un nuovo oggetto DataTable, che viene passato a DataAdapter e usato per l'aggiornamento del 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]);
            }
        }
    }
}

Il gestore eventi OnRowUpdated verifica la proprietà StatementType di SqlRowUpdatedEventArgs per determinare se la riga è un inserimento. In caso affermativo, la proprietà Status viene impostata su SkipCurrentRow. La riga viene aggiornata, tuttavia i valori originali della riga vengono mantenuti. Nel corpo principale della procedura viene chiamato il metodo Merge per unire il nuovo valore Identity nell'oggetto DataTable originale e infine viene chiamato 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;
    }
}