Retornado o valor do campo Identity

VB 2005 - Retornando o valor do campo Identity

Uma chave primária (Primary Key) em um banco de dados relacional é uma coluna ou combinação de colunas que sempre contém valores únicos. O conhecimento do valor da chave primária permite que você localize uma linha (registro) no banco de dados de forma rápida e segura.

Os bancos de dados relacionais como o SQL Server , Microsoft Access, Oracle, etc. suportam a criação de colunas com incremento automático (colunas do tipo auto incremental); esses valores são gerados automaticamente pelo servidor de banco de dados quando uma nova linha (registro) for incluída na tabela.

Para ativar este recurso no SQL Server você define a propriedade identity para a coluna; no Microsoft Access você selecione a coluna como do tipo AutoNumber(Auto-Numeração).

Você também pode usar a classe DataColumn para gerar valores de forma automática para uma coluna definindo a propriedade AutoIncrement como igual a true. (Pode também usar as propriedades AutoIncrementSeed e AutoIncrementStep para definir o valor inicial da coluna e o valor do incremento que será usado). Lembre-se que a utilização deste recurso pode levar a valores duplicados em instâncias separadas do objeto DataTable se diversas aplicações clientes estiverem gerando de forma automática os valores para um coluna de forma incremental. Se você deixar a tarefa por conta do servidor vai eliminar esses conflitos visto é permitido ao usuário obter somente o valor gerado para cada linha incluída.

Como podemos obter o retorno dos valores para um campo identity ?

Durante a chamada do método Update de um DataAdapter, o banco de dados pode enviar dados de volta a aplicação ADO.NET como parâmetros de saída ou como o primeiro registro retornado como resultado de um comando SELECT executado no mesmo lote como uma instrução INSERT.

Nota: O Microsoft Access não suporta parâmetros de saída (output parameters) e não pode processar múltiplas instruções em uma única operação de lote.
Para estes tipos de banco de dados podemos obter o novo valor para o campo auto-numeração gerado para uma linha incluída executando uma instrução
SELECT em um tratamento de evento para o evento RowUpdated de um DataAdapter.

Retornando valores para colunas Identity no SQL Server

Para retornar o valor de um campo identity de uma nova linha incluída no SQL Server usamos uma stored procedure com um parâmetro de saída (OUTPUT Parameter). Para realizar estas tarefas podemos usar três funções Transact-SQL no SQL Server:

Função	Descrição
SCOPE_IDENTITY	Retorna o último valor identity no escopo da execução atual. (É o recomendado)
@@IDENTITY	Contém o último valor identity gerado em qualquer tabela na sessão atual. Pode ser afetado por Triggers e pode não conter o valor identity que você espera.
IDENTI_CURRENT	Retorna o último valor identity gerado para uma tabela específica em qualquer sessão e qualquer escopo.

Nota: se você não sabe o que são Triggers leia o artigo : SQL - Triggers

Vejamos a seguir uma Stored Procedure que mostra como inserir uma linha na tabela Categories e usa um parâmetro OutPut para retornar o valor do campo identity gerado pela função Transact-SQL SCOPE_IDENTITY;

Para criar a stored Procedure usada como exemplo abaixo, você pode usar o SQL Server Management Studio Express. Após fazer o download e instalar. Abra o SSMS e selecione o banco de dados Northwind.mdf e expanda os objetos do banco de dados selecionando Stored Procedure. Clique com o botão direito do mouse e escolha : New Stored Procedure:

Para saber mais sobre o SSMS acompanhe os artigos:

A seguir digite a instrução para criar a Stored Procedure conforme abaixo:

Clicando no botão Execute vemos o resultado sendo exibido no painel do SSMS.

Podemos também definir uma stored procedure para ser usada por um objeto Command. Abaixo temos a stored procedure acima em outro formato:

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

Esta SP pode ser definida como a fonte de comando InsertCommand de um SqlDataAdapter. O valor para de saída para identity é retornado pela criação do parâmetro ParameterDirection.

Quando o InsertCommand é processado o valor gerado para o campo identity é retornado e colocado na coluna CategoryID da linha atual se você definiu a propriedade UpdateRowSource do comando insert para UpdateRowSource.OutputParameters ou para UpdateRowSource.Both.

Vejamos a seguir um exemplo de como podemos retornar o valor de um campo identity no SQL Server 2005 Express Edition usando o banco de dados Northwind.mdf e a tabela Categories.

Crie um novo projeto no Visual Basic 2005 Express Edition do tipo Windows Application e inclua no formulário padrão um botão de comando e um ListBox conforme abaixo:

A seguir expanda os objetos do banco de dados Northwind.mdf na janela Database Explorer e em Stored Procedure clique com o botão direito do mouse e selecione a opção Add New Stored Procedure. A seguir digite a SP conforme abaixo e salve no banco de dados:

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

O resultado será exibido conforme a figura abaixo: (Note que eu estou usando SCOPE_IDENTITY para retornar o valor)

Após criar a SP no banco de dados inclua o seguinte código no formulário:

A função RetornaValorIdentity() recebe a string de conexão e obtém o valor do campo identity para a tabela Categories do banco de dados Northwind.mdf;

Private Sub RetornaValorIdentity(ByVal connectionString As String)

Using connection As SqlConnection = New SqlConnection(connectionString)

' Cria um SqlDataAdapter baseado na consulta SELECT

Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID, CategoryName FROM dbo.Categories", connection)

' Cria o objeto SqlCommand para executar a stored procedure- InserirCategoria que ja foi criada

adapter.InsertCommand = New SqlCommand("dbo.InserirCategoria", connection)

adapter.InsertCommand.CommandType = CommandType.StoredProcedure

' Inclui o parâmetro para o campo CategoryName.

' Define o valor de ParameterDirection para um parâmetro input não é requerido

adapter.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")

' Inclui o SqlParameter para retornar o novo valor identity

' Define o ParameterDirection como Output.(Saida)

Dim parameter As SqlParameter = adapter.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID")

parameter.Direction = ParameterDirection.Output

' Cria um DataTable e preenche

Dim categories As DataTable = New DataTable

adapter.Fill(categories)

' Inclui uma nova linha

Dim newRow As DataRow = categories.NewRow()

newRow("CategoryName") = "Nova Categoria"

categories.Rows.Add(newRow)

' Atualiza o banco de dados.

adapter.Update(categories)

ListBox1.Items.Add("Lista todas as linhas :")

Dim row As DataRow

For Each row In categories.Rows

ListBox1.Items.Add(row(0) & vbTab & row(1))

End Using

End Sub

No evento Click do botão de comando temos a chamada da função:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim stringConexao As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\dados\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"

RetornaValorIdentity(stringConexao)

End Sub

Executando o projeto teremos o resultado abaixo: