Recupero di identità o di valori numerati automaticamenteRetrieving Identity or Autonumber Values

Per chiave primaria di un database relazionale si intende una colonna o una combinazione di colonne che contengono sempre valori univoci.A primary key in a relational database is a column or combination of columns that always contain unique values. Se si conosce il valore della chiave primaria, è possibile individuare la riga che lo contiene.Knowing the primary key value allows you to locate the row that contains it. 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.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. Tali valori vengono generati dal server quando si aggiungono righe a una tabella.These values are generated by the server as rows are added to a table. 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.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.

È inoltre possibile usare un oggetto DataColumn per generare valori a incremento automatico impostando la proprietà AutoIncrement su true.A DataColumn can also be used to generate automatically incrementing values by setting the AutoIncrement property to 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.However, you might end up with duplicate values in separate instances of a DataTable, if multiple client applications are independently generating automatically incrementing values. 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.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 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.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 può recuperare questi valori e aggiornare le colonne corrispondenti nell'oggetto DataRow da aggiornare.ADO.NET can retrieve these values and update the corresponding columns in the DataRow being updated.

Alcuni motori di database, ad esempio il motore di database Jet di Microsoft Access, non supportano parametri di output e non sono in grado di elaborare più istruzioni in un unico batch.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. Quando si usa il motore di database Jet, è possibile recuperare il nuovo valore del campo Contatore generato per una riga inserita eseguendo un comando SELECT distinto in un gestore eventi per l'evento RowUpdated di 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

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.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. 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.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. 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().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. L'utilizzo di un GUID come chiave primaria può influire negativamente sulle prestazioni.Using a GUID as a primary key can adversely affect performance. SQL Server fornisce il supporto per NEWSEQUENTIALID() la funzione che genera un GUID sequenziale che non è necessariamente univoco a livello globale, ma che può essere indicizzato in modo più efficiente.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.

Recupero dei valori delle colonne Identity di SQL ServerRetrieving SQL Server Identity Column Values

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.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. Nella tabella seguente sono descritte le tre funzioni Transact-SQL disponibili in SQL Server che è possibile usare per recuperare i valori delle colonne Identity.The following table describes the three Transact-SQL functions in SQL Server that can be used to retrieve identity column values.

FunzioneFunction DescrizioneDescription
SCOPE_IDENTITYSCOPE_IDENTITY Restituisce l'ultimo valore della colonna Identity compreso nell'ambito di esecuzione corrente.Returns the last identity value within the current execution scope. SCOPE_IDENTITY è consigliato per la maggior parte degli scenari.SCOPE_IDENTITY is recommended for most scenarios.
@@IDENTITY@@IDENTITY Contiene l'ultimo valore di colonna Identity generato in qualsiasi tabella nella sessione corrente.Contains the last identity value generated in any table in the current session. il@IDENTITY parametro @ può essere influenzato da trigger e non può restituire il valore Identity previsto.@@IDENTITY can be affected by triggers and may not return the identity value that you expect.
IDENT_CURRENTIDENT_CURRENT Restituisce l'ultimo valore di colonna Identity generato per una tabella specifica in qualsiasi sessione e in qualsiasi ambito.Returns the last identity value generated for a specific table in any session and any scope.

Nell'stored procedure seguente viene illustrato come inserire una riga nella tabella Categories e come utilizzare un parametro di output per restituire il nuovo valore Identity generato dalla funzione 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()

È quindi possibile specificare la stored procedure come origine di InsertCommand in un oggetto SqlDataAdapter.The stored procedure can then be specified as the source of the InsertCommand of a SqlDataAdapter object. La proprietà CommandType di InsertCommand deve essere impostata su StoredProcedure.The CommandType property of the InsertCommand must be set to StoredProcedure. Per recuperare l'output relativo all'identità, viene creato un oggetto SqlParameter il cui ParameterDirection è Output.The identity output is retrieved by creating a SqlParameter that has a ParameterDirection of Output. UpdateRowSource.Both UpdatedRowSource UpdateRowSource.OutputParameters Quando viene elaborato, il valore di identità con incremento automatico viene restituito e inserito nella colonna CategoryID della riga corrente se si imposta la proprietà del comando di inserimento su o su. InsertCommandWhen the InsertCommand is processed, the auto-incremented identity value is returned and placed in the CategoryID column of the current row if you set the UpdatedRowSource property of the insert command to UpdateRowSource.OutputParameters or to UpdateRowSource.Both.

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.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

Unione di nuovi valori IdentityMerging New Identity Values

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.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. Tale scenario risulta particolarmente utile quando è necessario effettuare il marshalling delle righe modificate in un componente diverso che esegue l'aggiornamento.This is especially useful when you need to marshal the changed rows to a separate component that performs the update. In seguito all'aggiornamento, la copia può contenere nuovi valori Identity che devono essere reinseriti nell'oggetto DataTable originale.Following the update, the copy can contain new identity values that must then be merged back into the original DataTable. È probabile che i nuovi valori Identity siano diversi dai valori originali di DataTable.The new identity values are likely to be different from the original values in the DataTable. Per eseguire l'Unione, è necessario mantenere i valori originali delle colonne AutoIncrement nella copia per poter individuare e aggiornare le righe esistenti nell'originale DataTable, anziché aggiungere nuove righe contenenti i nuovi valori Identity. .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. 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.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.

Sono disponibili sono due tecniche per mantenere i valori originali di un oggetto DataColumn in un DataRow durante un aggiornamento di DataAdapter:There are two ways to preserve the original values of a DataColumn in a DataRow during a DataAdapter update:

  • La prima tecnica per mantenere i valori originali consiste nell'impostare la proprietà AcceptChangesDuringUpdate di DataAdapter su false.The first method of preserving the original values is to set the AcceptChangesDuringUpdate property of the DataAdapter to false. Tale operazione influisce su ogni DataRow dell'oggetto DataTable da aggiornare.This affects every DataRow in the DataTable being updated. Per altre informazioni ed esempi di codice, vedere AcceptChangesDuringUpdate.For more information and a code example, see AcceptChangesDuringUpdate.

  • La seconda tecnica consiste invece nello scrivere codice nel gestore eventi RowUpdated di DataAdapter per impostare Status su SkipCurrentRow.The second method is to write code in the RowUpdated event handler of the DataAdapter to set the Status to SkipCurrentRow. DataRow viene aggiornato ma il valore originale di ogni DataColumn viene mantenuto.The DataRow is updated but the original value of each DataColumn is preserved. Questa tecnica consente di mantenere i valori originali per alcune righe ma non per altre.This method enables you to preserve the original values for some rows and not for others. 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.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.

Quando si usa una di queste soluzioni per mantenere i valori originali di un oggetto DataRow durante un aggiornamento di DataAdapter, ADO.NET esegue una serie di azioni per impostare i valori correnti di DataRow sui valori nuovi restituiti dai parametri di output o dalla prima riga restituita di un set di risultati, pur mantenendo il valore originale in ogni 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. Viene innanzitutto chiamato il metodo AcceptChanges di DataRow per mantenere i valori correnti come valori originali, quindi vengono assegnati i nuovi valori.First, the AcceptChanges method of the DataRow is called to preserve the current values as original values, and then the new values are assigned. 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.Following these actions, DataRows that had their RowState property set to Added will have their RowState property set to Modified, which may be unexpected.

La modalità di applicazione dei risultati del comando a ogni DataRow da aggiornare dipende dalla proprietà UpdatedRowSource di ogni DbCommand.How the command results are applied to each DataRow being updated is determined by the UpdatedRowSource property of each DbCommand. Questa proprietà è impostata su un valore restituito dall'enumerazione UpdateRowSource.This property is set to a value from the UpdateRowSource enumeration.

Nella tabella seguente viene descritto il modo in cui i diversi valori dell'enumerazione UpdateRowSource influiscono sulla proprietà RowState delle righe aggiornate.The following table describes how the UpdateRowSource enumeration values affect the RowState property of updated rows.

Nome del membroMember name DescrizioneDescription
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.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. Se non sono disponibili valori da applicare, il valore di RowState sarà Unchanged.If there are no values to apply, the RowState will be 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.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. Se non viene restituita nessuna riga, AcceptChanges non viene chiamato e RowState rimane impostato su Added.If no row is returned, then AcceptChanges is not called and the RowState remains Added.
None Eventuali parametri o righe restituite vengono ignorati.Any returned parameters or rows are ignored. Non viene eseguita nessuna chiamata a AcceptChanges e RowState rimane impostato su Added.There is no call to AcceptChanges and the RowState remains Added.
OutputParameters Viene chiamato AcceptChanges e viene eseguito il mapping degli eventuali parametri di output alla riga modificata in DataTable, impostando RowState su Modified.AcceptChanges is called and any output parameters are mapped to the changed row in the DataTable, setting the RowState to Modified. Se non sono disponibili parametri di output, il valore di RowState sarà Unchanged.If there are no output parameters, the RowState will be Unchanged.

EsempioExample

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.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 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.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 proprietà UpdatedRowSource del comando di inserimento viene impostata su UpdateRowSource.FirstReturnedRow, mentre la proprietà MissingSchemaAction di DataAdapter viene impostata su 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 viene riempito e il codice aggiunge una nuova riga a DataTable.The DataTable is filled and the code adds a new row to the DataTable. Le righe modificate vengono quindi estratte in un nuovo oggetto DataTable, che viene passato a DataAdapter e usato per l'aggiornamento del server.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

Il gestore eventi OnRowUpdated verifica la proprietà StatementType di SqlRowUpdatedEventArgs per determinare se la riga è un inserimento.The OnRowUpdated event handler checks the StatementType of the SqlRowUpdatedEventArgs to determine if the row is an insert. In caso affermativo, la proprietà Status viene impostata su SkipCurrentRow.If it is, then the Status property is set to SkipCurrentRow. La riga viene aggiornata, tuttavia i valori originali della riga vengono mantenuti.The row is updated, but the original values in the row are preserved. 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.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

Recupero di valori del campo Contatore di Microsoft AccessRetrieving Microsoft Access Autonumber Values

Questa sezione include un esempio che illustra come recuperare valori Autonumber da un database Jet 4.0.This section includes a sample that shows how to retrieve Autonumber values from a Jet 4.0 database. Il motore del database Jet non supporta l'esecuzione di più istruzioni in un batch o l'uso di parametri di output, pertanto non è possibile usare nessuna di queste tecniche per restituire il nuovo valore di Autonumber assegnato a una riga inserita.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. Tuttavia, è possibile aggiungere codice al RowUpdated gestore eventi che esegue un'istruzione SELECT @@IDENTITY separata per recuperare il nuovo Autonumber valore.However, you can add code to the RowUpdated event handler that executes a separate SELECT @@IDENTITY statement to retrieve the new Autonumber value.

EsempioExample

Anziché aggiungere informazioni sullo schema tramite MissingSchemaAction.AddWithKey, in questo esempio viene configurato un oggetto DataTable con lo schema corretto prima di chiamare OleDbDataAdapter per riempire un oggetto 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. In questo caso, la colonna CategoryID viene configurata per decrementare il valore assegnato a ogni riga inserita a partire AutoIncrement da truezero, impostando su AutoIncrementStep , AutoIncrementSeed su 0 e su-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. Il codice aggiunge quindi due nuove righe e usa GetChanges per aggiungere le righe modificate a un nuovo oggetto DataTable che viene passato al metodo 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

Il gestore eventi RowUpdated usa lo stesso oggetto OleDbConnection aperto come istruzione Update di OleDbDataAdapter.The RowUpdated event handler uses the same open OleDbConnection as the Update statement of the OleDbDataAdapter. Verifica il valore di StatementType di OleDbRowUpdatedEventArgs per le righe inserite.It checks the StatementType of the OleDbRowUpdatedEventArgs for inserted rows. Per ogni riga inserita, viene OleDbCommand creato un nuovo oggetto per eseguire l'@IDENTITY istruzione SELECT @ sulla connessione, restituendo Autonumber il nuovo valore, che DataRowviene inserito nella colonna CategoryID della.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 proprietà Status viene quindi impostata su UpdateStatus.SkipCurrentRow per sopprimere la chiamata nascosta a AcceptChanges.The Status property is then set to UpdateStatus.SkipCurrentRow to suppress the hidden call to AcceptChanges. Nel corpo principale della procedura viene chiamato il metodo Merge per unire i due oggetti DataTable e infine viene chiamato 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

Recupero dei valori IdentityRetrieving Identity Values

Spesso la colonna viene impostata come Identity quando i valori nella colonna devono essere univoci.We often set the column as identity when the values in the column must be unique. E a volte è necessario il valore Identity di nuovi dati.And sometimes we need the identity value of new data. Questo esempio illustra come recuperare i valori Identity:This sample demonstrates how to retrieve identity values:

  • Crea una stored procedure per inserire dati e restituire un valore Identity.Creates a stored procedure to insert data and return an identity value.

  • Esegue un comando per inserire i nuovi dati e visualizzare il risultato.Executes a command to insert the new data and display the result.

  • Usa SqlDataAdapter per inserire i nuovi dati e visualizzare il risultato.Uses SqlDataAdapter to insert new data and display the result.

Prima di compilare ed eseguire l'esempio, è necessario creare il database di esempio, usando lo script seguente: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

Di seguito è riportato il listato di codice:The code listing follows:

Suggerimento

Il listato di codice fa riferimento a un file di database di Access denominato MySchool.mdb.The code listing refers to an Access database file called MySchool.mdb. È possibile scaricare la versione di School. mdb (come parte del C# progetto di esempio completo o Visual Basic) da 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();
      }
   }
}

Vedere ancheSee also