Recupero di valori del campo Identity o Autonumber

Per garantire che a ogni riga della tabella sia associato un valore univoco, è possibile impostare una colonna della DataTable come chiave primaria ad incremento automatico. È tuttavia possibile che siano presenti più client per un'applicazione e che ognuno di tali client utilizzi un'istanza separata della DataTable. In questo caso è possibile che si ottengano dati duplicati nelle istanze distinte della DataTable. Poiché tutti i client utilizzano un'unica origine dati, è possibile risolvere questo conflitto consentendo all'origine dati di definire il valore a incremento automatico. Per ottenere questo risultato, utilizzare i campi Identity in Microsoft SQL Server o i campi Autonumber in Microsoft Access.

L'utilizzo dell'origine dati per la compilazione di una colonna Identity o Autonumber per una nuova riga aggiunta al DataSet crea una situazione unica, poiché DataSet non ha alcuna connessione diretta all'origine dati. Il DataSet non viene quindi influenzato da alcun valore generato automaticamente dall'origine dati. Nel caso di un'origine dati in grado di creare stored procedure con parametri di output, quale Microsoft SQL Server, è possibile tuttavia specificare i valori generati automaticamente, quale un nuovo valore relativo all'identità, come parametro di output e utilizzare DataAdapter per eseguire il mapping di tale valore alla colonna nel DataSet.

È possibile che l'origine dati in uso non supporti le stored procedure con parametri di output. In questo caso potrebbe essere necessario utilizzare l'evento RowUpdated per ottenere un valore generato automaticamente e posizionarlo nella riga inserita o aggiornata del DataSet. In questa sezione è incluso un esempio che mostra come Microsoft Access 2000 o versione successiva e il provider OLE DB Jet 4.0 consentano di aggiungere codice all'evento RowUpdated, per stabilire se è stato effettuato un inserimento e per recuperare il valore incrementato automaticamente e memorizzarlo nella riga correntemente aggiornata.

Nell'esempio di stored procedure e codice seguente viene mostrato come eseguire il mapping del valore ad incremento automatico relativo all'identità da una tabella di Microsoft SQL Server alla colonna corrispondente in una riga aggiunta a una tabella in un DataSet. La stored procedure viene utilizzata per inserire una nuova riga nella tabella Categories del database Northwind e per restituire il valore relativo all'identità restituito da SCOPE_IDENTITY() come parametro di output.

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

È quindi possibile specificare la stored procedure InsertCategory come origine di DataAdapter.InsertCommand. Viene creato un parametro per ricevere il parametro di output relativo all'identità. Tale parametro ha come Direction ParameterDirection.Output e come SourceColumn la colonna CategoryID della tabella locale Categories del DataSet. Quando viene eseguito InsertCommand per l'aggiunta di una riga, il valore di identità a incremento automatico viene restituito come parametro di output e viene posto nella colonna CategoryID della riga corrente.

Nell'esempio di codice che segue viene mostrato come restituire il valore a incremento automatico come parametro di output e specificarlo come valore di origine per la colonna CategoryID del DataSet.

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)

catDA.InsertCommand = New SqlCommand("InsertCategory", nwindConn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure

catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName")

Dim myParm As SqlParameter = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output

nwindConn.Open()

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")

Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)

catDA.Update(catDS, "Categories")

nwindConn.Close()
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlDataAdapter catDA = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);

catDA.InsertCommand = new SqlCommand("InsertCategory", nwindConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;

catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName");

SqlParameter myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;

nwindConn.Open();

DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");

DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);

catDA.Update(catDS, "Categories");

nwindConn.Close();

Microsoft Access non supporta le stored procedure o l'elaborazione dei comandi batch. Non è quindi possibile eseguire il mapping di un parametro di output alla colonna di origine della tabella dell'esempio precedente. In Microsoft Access 2000 o versioni successive tuttavia è supportata la proprietà @@IDENTITY, che consente di ottenere il valore di un campo Autonumber dopo un INSERT. Utilizzando l'evento RowUpdated, è possibile determinare se si è verificato un INSERT, recuperare il valore @@IDENTITY più recente e inserirlo nella colonna relativa all'identità della tabella locale nel DataSet.

Nell'esempio di codice che segue viene mostrato come inserire un nuovo valore nella tabella Categories del database Northwind di Microsoft Access 2000. L'evento RowUpdated viene utilizzato nell'esempio per riempire i valori Autonumber generati dal modulo Jet e dal database di Access durante l'inserimento di un record nella tabella Categories. Si noti che questa operazione è possibile solo con il provider OLE DB di Jet 4.0 e Microsoft Access 2000 o versione successiva.

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic

Public class Sample

  Shared nwindConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                              "Data Source=c:\Program Files\Microsoft Office\Office\Samples\northwind.mdb;")

  Public Shared Sub Main() 

    ' Use the DataAdapter to fill and update the DataSet.
    Dim catDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID", nwindConn)

    catDA.InsertCommand = New OleDbCommand("INSERT INTO Categories (CategoryName) Values(?)", nwindConn)
    catDA.InsertCommand.CommandType = CommandType.Text

    catDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName")

    nwindConn.Open()
 
    ' Fill the DataSet.
    Dim catDS As DataSet = New DataSet
    catDA.Fill(catDS, "Categories")

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

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

    ' Update the DataSet.
    catDA.Update(catDS, "Categories")

    nwindConn.Close()
  End Sub

  Private Shared Sub OnRowUpdated(sender As Object, args As OleDbRowUpdatedEventArgs)
    ' Include a variable and a command to retrieve the identity value from the Access database.
    Dim newID As Integer = 0
    Dim idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", nwindConn)

    If args.StatementType = StatementType.Insert
      ' Retrieve the identity value and store it in the CategoryID column.
      newID = CInt(idCMD.ExecuteScalar())
      args.Row("CategoryID") = newID
    End If
  End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.OleDb;

public class Sample
{
  static OleDbConnection nwindConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
                                                  @"Data Source=c:\Program Files\Microsoft Office\Office\Samples\northwind.mdb;");

  public static void Main() 
  {
    // Use the DataAdapter to fill and update the DataSet.
    OleDbDataAdapter catDA = new OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID", nwindConn);

    catDA.InsertCommand = new OleDbCommand("INSERT INTO Categories (CategoryName) Values(?)", nwindConn);
    catDA.InsertCommand.CommandType = CommandType.Text;

    catDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName");

    nwindConn.Open();
 
    // Fill the DataSet.
    DataSet catDS = new DataSet();
    catDA.Fill(catDS, "Categories");

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

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

    // Update the DataSet.
    catDA.Update(catDS, "Categories");

    nwindConn.Close();
  }

  protected static void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs args)
  {
    // Include a variable and a command to retrieve the identity value from the Access database.
    int newID = 0;
    OleDbCommand idCMD = new OleDbCommand("SELECT @@IDENTITY", nwindConn);

    if (args.StatementType == StatementType.Insert)
    {
      // Retrieve the identity value and store it in the CategoryID column.
      newID = (int)idCMD.ExecuteScalar();
      args.Row["CategoryID"] = newID;
    }
  }
}

Vedere anche

Scenari ADO.NET di esempio | Accesso ai dati tramite ADO.NET | Utilizzo di provider di dati .NET Framework per accedere ai dati