C# - [ADO.NET] – Parte 6 - Campos Auto numéricos (Identity)

 

Introducción

---

Se basara la explicación de este articulo de uno anterior en donde se aprendió de una forma simple y por medio de un ejemplo completo a trabajar los datos con ado.net

[ADO.NET] – Parte 5 - Ejemplos Simples – Operaciones CRUD

Por lo cual tomando ese ejemplo de base se pasara a explicar como trabajar con campos del tipo Autonuméricos o Identity.

Como es sabido en estos tipos de campos es la base de datos la que genera el Id del registro que se inserta, por lo tanto las modificaciones solo afectaran a las operaciones INSERT de la aplicación de ejemplo.

En la base de datos se ha realizado el siguiente cambio en la tabla:

 

 

1- Obtener Identity en la misma consulta

---

En este ejemplo se hará uso de una consulta combinada en donde en la misma ejecución se insertara el registro y a la vez se recuperara el identificador autonumérico generado.

[C#]

private void btnGuardar_Click(object sender, EventArgs e)
{
    //
    // Validaciones
    //
    int Id = 0;
    if (!string.IsNullOrEmpty(txtId.Text))
    {
        if (!int.TryParse(txtId.Text, out Id))
        {
            MessageBox.Show("El Id debe ser un valor numerico");
            return;
        }
    }

    if (Exists(Id))
    {
        bool result = Update(Id, txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value);

        if (result)
            MessageBox.Show("El registro se ha actualizado correctamente.");
    }
    else
    {
        int id = Insert(txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value);

        if (id > 0)
        {
            MessageBox.Show(string.Format("El registro se ha insertado correctamente. Id : {0}", id));
            txtId.Text = Convert.ToString(id);
        }
    }
}

private int Insert(string nombre, string direccion, DateTime fechaNacimiento)
{
    if (!ValidateForm())
        return 0;

    string sql = @"INSERT INTO Contactos (NombreCompleto
                          ,Direccion
                          ,FechaNacimiento)
                      VALUES (@Nombre, 
                            @Direccion, 
                            @FechaNacimiento)
                    SELECT SCOPE_IDENTITY()";


    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {

        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddWithValue("Nombre", nombre);
        command.Parameters.AddWithValue("Direccion", direccion);
        command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento);

        conn.Open();

        return Convert.ToInt32(command.ExecuteScalar());

    }
}

[VB.NET]

Private Sub btnGuardar_Click(sender As Object, e As EventArgs)
	'
	' Validaciones
	'
       Dim _Id As Integer = 0
	If Not String.IsNullOrEmpty(txtId.Text) Then
           If Not Integer.TryParse(txtId.Text, _Id) Then
               MessageBox.Show("El Id debe ser un valor numerico")
               Return
           End If
	End If

       If Exists(_Id) Then
           Dim result As Boolean = Update(_Id, txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value)

           If result Then
               MessageBox.Show("El registro se ha actualizado correctamente.")
           End If
       Else
           Dim idObtenido As Integer = Insert(txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value)

           If idObtenido > 0 Then
               MessageBox.Show(String.Format("El registro se ha insertado correctamente. Id : {0}", idObtenido))
               txtId.Text = Convert.ToString(idObtenido)
           End If
       End If
End Sub

Private Function Insert(nombre As String, direccion As String, fechaNacimiento As DateTime) As Integer
	If Not ValidateForm() Then
		Return 0
	End If

       Dim sql As String = "INSERT INTO Contactos (NombreCompleto" & _
                               ",Direccion" & _
                               ",FechaNacimiento)" & _
                               "VALUES (@Nombre, " & _
                               "@Direccion, " & _
                               "@FechaNacimiento)" & _
                           "SELECT SCOPE_IDENTITY()"


	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())

		Dim command As New SqlCommand(sql, conn)
		command.Parameters.AddWithValue("Nombre", nombre)
		command.Parameters.AddWithValue("Direccion", direccion)
		command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento)

		conn.Open()


		Return Convert.ToInt32(command.ExecuteScalar())
	End Using
End Function	

Como se observa en el código el principal cambio se lo lleva al consulta SQL, en donde se combinan dos consultas en una.

Por medio de método ExecuteScalar() del SqlCommand se realizaran las dos operaciones, ejecutar la consulta de INSERT y al mismo tiempo devolver el valor único del SELECT.

Por ultimo se lo pasa el id obtenido al evento en al presentación para que muestre el id generado en pantalla.

 

2 – Obtener Id en consultas separadas

---

Un problema con la técnica anterior es que si por alguna razón el INSERT no realiza la operación de forma correcta, puede haber problemas con el id que se obtiene.

Esto puede ser extraño pero hay formas de asegurar que solo se obtenga con el id si es que la operación anterior se ha realizado de forma correcta.

Mas que nada esta técnica es útil cuando se quiere asegurar o validar que el INSERT afecto a los registros de forma adecuada.

[C#]

private int InsertComandosSeparadas(string nombre, string direccion, DateTime fechaNacimiento)
{
    if (!ValidateForm())
        return 0;

    string sql = @"INSERT INTO Contactos (NombreCompleto
                          ,Direccion
                          ,FechaNacimiento)
                      VALUES (@Nombre, 
                            @Direccion, 
                            @FechaNacimiento)";

    
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {

        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddWithValue("Nombre", nombre);
        command.Parameters.AddWithValue("Direccion", direccion);
        command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento);

        conn.Open();


        int rowsAffected = command.ExecuteNonQuery();

        if (rowsAffected > 0)
        {
            string sqlIdentity = "SELECT @@IDENTITY";
            SqlCommand cmdIdentity = new SqlCommand(sqlIdentity, conn);

            return Convert.ToInt32(cmdIdentity.ExecuteScalar());
        }
        else
            return -1;


    }
}

[VB.NET]

Private Function InsertComandosSeparadas(nombre As String, direccion As String, fechaNacimiento As DateTime) As Integer
	If Not ValidateForm() Then
		Return 0
	End If

       Dim sql As String = "INSERT INTO Contactos (NombreCompleto" & _
                                   ",Direccion" & _
                                   ",FechaNacimiento)" & _
                               "VALUES (@Nombre, " & _
                                   "@Direccion, " & _
                                   "@FechaNacimiento)"


	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())

		Dim command As New SqlCommand(sql, conn)
		command.Parameters.AddWithValue("Nombre", nombre)
		command.Parameters.AddWithValue("Direccion", direccion)
		command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento)

		conn.Open()


		Dim rowsAffected As Integer = command.ExecuteNonQuery()

		If rowsAffected > 0 Then
			Dim sqlIdentity As String = "SELECT @@IDENTITY"
			Dim cmdIdentity As New SqlCommand(sqlIdentity, conn)

			Return Convert.ToInt32(cmdIdentity.ExecuteScalar())
		Else
			Return -1


		End If
	End Using
End Function

 

En le código puede observarse que la primer parte se conserva idéntica a la origina, en donde solo una instrucción de INSERT es declarada, y ejecutada por medio del ExecuteNonQuery(), este retornara los registros afectados y según el valor de retorno se procederá a recuperar el id autonumérico generado.

Hay un cambio que hay que hacer notar y es que en el SELECT se ha cambiado la técnica usada para recuperar el id, en este caso se hace uso del @@IDENTITY

Seguramente la pregunta que se harán es porque de este cambio, bien resulta que la diferencia entre estas instrucciones esta en el ámbito en que se ejecutan, SCOPE_IDENTITY trabaja en el ámbito actual, o sea el ámbito de la instrucción, si en este ejemplo usáramos esta instrucción obtendríamos como resultado un NULL, pues este debe ser ejecutado en la misma operación.

En cambio @@IDENTY no es afectado por aun ámbito especifico, puede ser consultado luego de haber realizado la operación de INSERT en una operación distinta. Algo que haya que marcar es que @@IDENTITY puede tener problemas ya que al no trabajar en un ámbito determinado puede ser afectada por otras operaciones, si es que se realizan acciones con varias tablas.

@@IDENTITY

SCOPE_IDENTITY

 

3 – Obtener Id desde Stored Procedure

---

En este caso se analizara como obtener el id generado por la tabla, en donde la operacion de actualización esta dentro de un Stored Procedure, para esto se requerirán algunos cambios.

 

[C#]

private int InsertStoredprocedure(string nombre, string direccion, DateTime fechaNacimiento)
{
    if (!ValidateForm())
        return 0;

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        SqlCommand command = new SqlCommand("sp_ContactoInsert", conn);
        command.CommandType = CommandType.StoredProcedure;

        SqlParameter paramId = new SqlParameter("Id", SqlDbType.Int);
        paramId.Direction = ParameterDirection.Output;
        command.Parameters.Add(paramId);

        command.Parameters.AddWithValue("Nombre", nombre);
        command.Parameters.AddWithValue("Direccion", direccion);
        command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento);

        int rowsAffected = command.ExecuteNonQuery();

        if (rowsAffected > 0)
        {
            return Convert.ToInt32(command.Parameters["Id"].Value);
        }
        else
            return -1;


    }
}

[VB.NET]

Private Function InsertStoredProcedure(nombre As String, direccion As String, fechaNacimiento As DateTime) As Integer
	If Not ValidateForm() Then
		Return 0
	End If

	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())
		conn.Open()

		Dim command As New SqlCommand("sp_ContactoInsert", conn)
		command.CommandType = CommandType.StoredProcedure

		Dim paramId As New SqlParameter("Id", SqlDbType.Int)
		paramId.Direction = ParameterDirection.Output
		command.Parameters.Add(paramId)

		command.Parameters.AddWithValue("Nombre", nombre)
		command.Parameters.AddWithValue("Direccion", direccion)
		command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento)

		Dim rowsAffected As Integer = command.ExecuteNonQuery()

		If rowsAffected > 0 Then
			Return Convert.ToInt32(command.Parameters("Id").Value)
		Else
			Return -1
           End If

       End Using

End Function

[Stored Procedure]

ALTER PROCEDURE dbo.sp_ContactoInsert
	
	@Id int OUTPUT,
	@Nombre varchar(50),
	@Direccion varchar(50),
	@FechaNacimiento DateTime
	
AS
	INSERT INTO Contactos (NombreCompleto
                                  ,Direccion
                                  ,FechaNacimiento)
                              VALUES (@Nombre, 
                                    @Direccion, 
                                    @FechaNacimiento);

                                                                        
    SELECT @Id = SCOPE_IDENTITY()     
                             
	RETURN

 

En el código se han quitados todas las consulta reemplazándolo por el nombre del stored procedure.

En este caso se hace uso de un parámetro que actuara como output para el valor generado dentro del Stored procedure, el cual será recuperado luego de la ejecución de la instrucción.

[C#]

[VB.NET]