Recuperar valores de identidade ou numeração automática

Aplica-se a: .NET Framework .NET .NET Standard

Uma chave primária em um banco de dados relacional é uma coluna ou combinação de colunas que sempre contêm valores exclusivos. Conhecer o valor da chave primária permite localizar a linha que a contém. Os mecanismos de banco de dados relacional, como SQL Server, Oracle e Microsoft Access/Jet, oferecem suporte à criação de colunas de incremento automático que podem ser designadas como chaves primárias. Esses valores são gerados pelo servidor à medida que as linhas são adicionadas a uma tabela. No SQL Server, define a propriedade Identity de uma coluna; no Oracle, cria uma sequência; e no Microsoft Access, cria uma coluna de AutoNumeração.

A DataColumn também pode ser usado para gerar valores de incremento automático definindo a AutoIncrement propriedade como true. No entanto, você pode acabar com valores duplicados em instâncias separadas de um DataTable, se vários aplicativos cliente estiverem gerando valores de incremento automático de forma independente. Fazer com que o servidor gere automaticamente valores incrementais elimina potenciais conflitos, permitindo que cada usuário recupere o valor gerado para cada linha inserida.

Durante uma chamada para o Update método de um DataAdapter, o banco de dados pode enviar dados de volta para seu aplicativo ADO.NET como parâmetros de saída ou como o primeiro registro retornado do conjunto de resultados de uma instrução SELECT executada no mesmo lote que a instrução INSERT. O Microsoft SqlClient Data Provider para SQL Server pode recuperar estes valores e atualizar as colunas correspondentes no DataRow que está a ser atualizado.

Observação

Uma alternativa ao uso de um valor de incremento automático é usar o NewGuid método de um Guid objeto para gerar um GUID, ou identificador global exclusivo, no computador cliente que pode ser copiado para o servidor à medida que cada nova linha é inserida. O NewGuid método gera um valor binário de 16 bytes que é criado usando um algoritmo que fornece uma alta probabilidade de que nenhum valor será duplicado. Em um banco de dados do SQL Server, um GUID é armazenado em uma uniqueidentifier coluna que o SQL Server pode gerar automaticamente usando a função Transact-SQL NEWID() . Usar um GUID como chave primária pode afetar negativamente o desempenho. O SQL Server fornece suporte para a NEWSEQUENTIALID() função, que gera um GUID sequencial que não tem garantia de ser globalmente exclusivo, mas que pode ser indexado de forma mais eficiente.

Recuperar valores das colunas de identidade do SQL Server

Ao trabalhar com o Microsoft SQL Server, você pode criar um procedimento armazenado com um parâmetro de saída para retornar o valor de identidade para uma linha inserida. A tabela a seguir descreve as três funções Transact-SQL no SQL Server que podem ser usadas para recuperar valores de coluna de identidade.

Função	Description
SCOPE_IDENTITY	Retorna o último valor de identidade dentro do escopo de execução atual. SCOPE_IDENTITY é recomendado para a maioria dos cenários.
@@IDENTITY	Contém o último valor de identidade gerado em qualquer tabela na sessão atual. @@IDENTITY pode ser afetada por gatilhos e pode não retornar o valor de identidade esperado.
IDENT_CURRENT	Retorna o último valor de identidade gerado para uma tabela específica em qualquer sessão e escopo.

O procedimento armazenado seguinte demonstra como inserir uma linha na tabela de Categorias e usar um parâmetro de saída para devolver o novo valor de identidade gerado pela função 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()

O procedimento armazenado pode então ser especificado como a origem do InsertCommand de um SqlDataAdapter objeto. A CommandType propriedade do InsertCommand deve ser definida como StoredProcedure. A saída de identidade é obtida criando um SqlParameter que contém um ParameterDirection de Output. Quando o InsertCommand é processado, o valor de identidade auto-incrementado é devolvido e colocado na coluna CategoryID da linha atual se definir a UpdatedRowSource propriedade do comando insert como UpdateRowSource.OutputParameters ou para UpdateRowSource.Both.

Se o comando insert executar um lote que inclui uma instrução INSERT e uma instrução SELECT que retorna o novo valor de identidade, você poderá recuperar o novo valor definindo a UpdatedRowSource propriedade do comando insert como 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]);
            }
        }
    }
}

Fusão de novos valores de identidade

Um cenário comum é chamar o GetChanges método do DataTable para criar uma cópia que contenha apenas linhas alteradas e usar a nova cópia ao chamar o Update método de um DataAdapter. Isso é especialmente útil quando você precisa organizar as linhas alteradas para um componente separado que executa a atualização. Após a atualização, a cópia pode conter novos valores de identidade que devem ser mesclados de volta ao original DataTable. É provável que os novos valores de identidade sejam diferentes dos valores originais no DataTable. Para realizar a fusão, os valores originais das colunas AutoIncrement na cópia devem ser preservados, para poder localizar e atualizar linhas existentes no original DataTable, em vez de adicionar novas linhas contendo os novos valores de identidade. No entanto, por padrão, esses valores originais são perdidos após uma chamada para o Update método de um DataAdapter, porque AcceptChanges é implicitamente chamado para cada DataRow atualizado.

Há duas maneiras de preservar os valores originais de a DataColumn em um DataRow durante uma DataAdapter atualização:

O primeiro método de preservar os valores originais é definir a AcceptChangesDuringUpdate propriedade do DataAdapter para false. Esta configuração afeta todos os DataRow em DataTable que estão a ser atualizados. Para obter mais informações e um exemplo de código, consulte AcceptChangesDuringUpdate.
O segundo método é escrever código no RowUpdated manipulador de eventos do DataAdapter para definir o Status como SkipCurrentRow. O DataRow é atualizado, mas o valor original de cada DataColumn é preservado. Esse método permite que você preserve os valores originais para algumas linhas e não para outras. Por exemplo, o seu código pode preservar os valores originais para linhas adicionadas e não para linhas editadas ou eliminadas, verificando primeiro o StatementType e, em seguida, definindo Status como SkipCurrentRow apenas para linhas com um StatementType de Insert.

Quando um destes métodos é usado para preservar os valores originais em um DataRow durante uma atualização de DataAdapter, o Microsoft SqlClient Data Adapter para SQL Server executa uma série de ações para atribuir os valores atuais do DataRow a novos valores retornados pelos parâmetros de saída ou pela primeira linha devolvida de um conjunto de resultados, enquanto mantém o valor original em cada DataColumn. Primeiro, o AcceptChanges método do DataRow é chamado para preservar os valores atuais como valores originais e, em seguida, os novos valores são atribuídos. Após essas ações, DataRows que tiveram a sua propriedade RowState definida para Added, terão a sua propriedade RowState definida para Modified, o que pode ser inesperado.

A forma como os resultados do comando são aplicados a cada DataRow que está a ser atualizado é determinada pela propriedade de cada UpdatedRowSource. Esta propriedade é definida como um valor da UpdateRowSource enumeração.

A tabela a seguir descreve como os UpdateRowSource valores de enumeração afetam a RowState propriedade de linhas atualizadas.

Nome do membro	Description
Both	`AcceptChanges` é chamado e os valores dos parâmetros de saída e/ou os valores na primeira linha de qualquer conjunto de resultados retornado são colocados na `DataRow` que está a ser atualizada. Caso não haja valores a aplicar, o `RowState` será Unchanged.
FirstReturnedRecord	Se uma linha for retornada, `AcceptChanges` é chamada e a linha é mapeada para a linha alterada no `DataTable`, definindo `RowState` para `Modified`. Se nenhuma linha for devolvida, então `AcceptChanges` não é chamado e o `RowState` permanece `Added`.
None	Todos os parâmetros ou linhas retornados são ignorados. Não há chamada para `AcceptChanges` e o `RowState` permanece `Added`.
OutputParameters	`AcceptChanges` é chamado e todos os parâmetros de saída são mapeados para a linha alterada no `DataTable`, definindo o `RowState` para `Modified`. Se não houver parâmetros de saída, o `RowState` será `Unchanged`.

Example

Este exemplo demonstra extrair linhas alteradas de a DataTable e usar a SqlDataAdapter para atualizar a fonte de dados e recuperar um novo valor de coluna de identidade. O InsertCommand executa duas instruções Transact-SQL: a primeira é a instrução INSERT e a segunda é uma instrução SELECT que usa a função SCOPE_IDENTITY para recuperar o valor de identidade.

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

A propriedade UpdatedRowSource do comando insert é definida como UpdateRowSource.FirstReturnedRow e a propriedade MissingSchemaAction do DataAdapter é definida como MissingSchemaAction.AddWithKey. O DataTable é preenchido e o código adiciona uma nova linha ao DataTable. As linhas alteradas são então extraídas para um novo DataTable, que é passado para o DataAdapter, que então atualiza o 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]);
            }
        }
    }
}

O OnRowUpdated manipulador de eventos verifica o StatementType do SqlRowUpdatedEventArgs para determinar se a linha é uma inserção. Se for, então a Status propriedade é definida como SkipCurrentRow. A linha é atualizada, mas os valores originais na linha são preservados. No corpo principal do procedimento, o Merge método é chamado para incorporar o novo valor de identidade ao original DataTable, e, finalmente, AcceptChanges é chamado.

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