21 may 2011

Identity recien insertado

Supongamos que tenemos la siguiente tabla

IF OBJECT_ID ('tb1', 'U') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1
(
id int IDENTITY(1,1) PRIMARY KEY,
descripcion varchar(30)
)

Desde una aplicación C# necesitamos realizar la tarea de insertar en la misma y recuperar la llave primaria recién insertada, que como vemos es un identity, o sea, autoincrementa. Valga el presente ejemplo para decir que en lo personal no me gusta nada la utilización de columnas identity y menos como llaves ya que dan una serie de problemas, principalmente a la hora de migrar datos etc, además se tiene la sensación de que se pierde parte del control con las mismas; dicho lo anterior continuamos. Esto se puede afrontar por medio de la implementación de un store procedure como el siguiente:

IF OBJECT_ID ( '[dbo].[inserta_tb1]', 'P' ) IS NOT NULL
DROP PROCEDURE [dbo].[inserta_tb1]
GO
CREATE PROCEDURE [dbo].[inserta_tb1]
@id int Output,
@descripcion varchar(30)
AS
BEGIN TRY
INSERT INTO tb1 (descripcion) values (@descripcion)
set @id = scope_identity();
END TRY
BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

--Si hay una trasaccion activa hace rollback
IF @@TRANCOUNT > 0
ROLLBACK
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

SET @ErrorMessage = 'Se presentó un error en el procedimiento
almacenado [inserta_tb1]: ' + @ErrorMessage

--Enviando el error
RAISERROR ( @ErrorMessage,
@ErrorSeverity,
@ErrorState
);
END CATCH
GO

En SQL server 2005 existen tres opciones cuando se trata de acceder al valor de columnas identity IDENT_CURRENT, @@IDENTITY y SCOPE_IDENTITY.

IDENT_CURRENT: recibe como parámetro un string con el nombre de la tabla (pe. IDENT_CURRENT (‘tb1’) ;) y devuelve para la tabla dada el ultimo identity generado en cualquier sesión y en cualquier scope (ámbito).

@@IDENTITY: devuelve el último identity generado para cualquier tabla dentro de la sesión actual pero sin importar el scope.

SCOPE_IDENTITY: es una función que devuelve el último identity generado para cualquier tabla dentro de la sesión actual y dentro del scope actual.

Ahora bien para entender bien lo del scope, supongamos que tenemos nuestra tabla tb1 y que esta a su vez tuviese un trigger el cual a la hora de inserta en tb1, insertara un registro en otra tabla que también contiene una columna identity. Si al final de insertar en tb1 invocáramos @@IDENTITY nos devolvería el identity generado en el trigger para la segunda tabla, ya que es el último generado, pero si utilizamos SCOPE_IDENTITY() nos devolvería el identity de tb1, esto porque el scope(ámbito) del trigger es distinto al de la instrucción de inserción en tb1 propiamente dicha.

No es recomendable IDENT_CURRENT, porque en un ambiente de alta concurrencia se corre el riesgo de que el identity que me devuelva no sea el generado por mi sesión si no por la de otro usuario que casualmente esta realizando la misma tarea al mismo tiempo.

Finalmente desde C# podemos extraer el valor de identity por medio del siguiente código:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace Pruebas

{

class Program

{

static void Main(string[] args)

{

string conexion = @"data source=Servidor\Instancia; initial catalog=BDInicial; user id=Usuario; password= ";

SqlConnection conn = new SqlConnection(conexion);

try

{

conn.Open();

SqlCommand comm = new SqlCommand("inserta_tb1",conn);

comm.CommandType= CommandType.StoredProcedure;

SqlParameter id = new SqlParameter ();

id.ParameterName="@id";

id.DbType = DbType.Int16;

id.Direction = ParameterDirection.Output;

comm.Parameters.Add(id);

SqlParameter des = new SqlParameter();

des.ParameterName="@descripcion";

des.DbType= DbType.String;

des.Direction = ParameterDirection.Input;

des.Value = "Descripcion de prueba";

comm.Parameters.Add(des);

comm.ExecuteNonQuery();

Console.WriteLine("El identity de la insercion es {0}", comm.Parameters["@id"].Value.ToString());

}

catch (Exception ex)

{

Console.WriteLine("Se ha producido un Error: {0}", ex.Message);

}

finally

{

if (conn.State != System.Data.ConnectionState.Closed)

{

conn.Close();

}

}

Console.Write("Presione cualquier tecla para continuar...");

Console.ReadKey();

}

}

}

Insisto que en lo personal no me gustan ni recomiendo (como si tuviera la autoridad de recomendar algo jejeje) el uso de columnas tipo identity, es mejor según mi experiencia, tener un mecanismo propio si es que se necesita que los sistemas generen consecutivos.

Roy {aka. Foy}

Autor & Editor

Desarrallador y líder técnico, con experiencia en tecnologías Microsoft desde los tiempos del VB6 y el asp clásico hasta el .Net Core, pasando por COM+, javascript, angularjs, Ionic, xaml, cordova, MVC, Web Api, Sql Server, Oracle... . Ávido lector, apasionado programador.

2 comments:

David Bernad "Berni" dijo...

Buenas,

Es mu útil esta información ya que yo me encuentro en esta situación con mi proyecto, pero necesito hacer esto con LinQ. Si por lo que sea lo supieras te agradecería mucho que me lo comentaras.

Un saludo.
Gracias.

Roy dijo...

Tal vez este link http://foyland.blogspot.com/2012/07/linq-to-sql-insert.html te Sirva