viernes, 13 de enero de 2012

Transact SQL - Obtener ID de registro insertado (SQL Server)

Introducción
Generalmente necesitamos guardar un registro e inmediatamente insertar otros relacionados en otras tablas. Un ejemplo claro sería el de la facturación, para el cual necesitamos por lo menos dos tablas: una para guardar la información de la factura y las partes implicadas, y otra para el detalle de la misma. Tal vez muchos se han encontrado con el problema de obtener el ID del registro recien insertado, para solucionar esto tenemos a nuestra disposición una función llamada SCOPE_IDENTITY.
(Para SQLServer CE investigar sobre @@IDENTITY).


Definición
SCOPE_IDENTITY() es una función capaz de devolver el valor de la columna de identidad (llave, index, etc) recien insertada.


Implementación
Para llevar a cabo la implementación de esta función tomaremos como ejemplo un caso en el cuál un registro se guarda en dos tablas: una tabla de Clientes y otra de Monitoreo de Actividad.


Estructura de Tablas

Clientes
  • Cli_Id (int) (identidad) (NO NULL)
  • Cli_Nombre (nvarchar(60)) (NO NULL)
  • Cli_Direccion (nvarchar(50))(NULL)
  • Cli_Telefono (nvarchar(30))(NULL)
  • Cli_Email (nvarchar(30))(NULL)


Monitor
  • Mon_Id (int) (identidad) (NO NULL)
  • Mon_IdRegistro (int) (NO NULL)
  • Mon_NombreTabla (nvarchar(50))(NO NULL)


Code-behind
C#
            //Variable de Conexión
            SqlConnection con = new SqlConnection("Data Source=USER\\SQLEXPRESS;Initial Catalog=basePrueba;Integrated Security=True");
            con.Open();

            //Variable que contendrá el valor de la columna de identidad devuelto por la función SCOPE_IDENTITY()
            int idRegistro = 0;

            //Armo el Query para Clientes
            //A la hora de declarar el query agrego la función SCOPE_IDENTITY()
            SqlCommand cmd = new SqlCommand("INSERT INTO Clientes " +
                                            "(Cli_Nombre, Cli_Direccion, Cli_Telefono, Cli_Email) " +
                                            "VALUES" +
                                            "(@nombre, @direccion, @telefono, @email) " +
                                            "SELECT SCOPE_IDENTITY()", con);

            cmd.Parameters.Add("@nombre", SqlDbType.NVarChar).Value = txtNombre.Text;
            cmd.Parameters.Add("@direccion", SqlDbType.NVarChar).Value = txtDireccion.Text;
            cmd.Parameters.Add("@telefono", SqlDbType.NVarChar).Value = txtTelefono.Text;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = txtEmail.Text;

            //Ejecuto la consulta y obtengo el valor devuelto por la misma
            idRegistro = Convert.ToInt32(cmd.ExecuteScalar());

            //Armo Query para el Monitor de Actividades
            //En la consulta paso un parámetro con el valor obtenido de la consulta anterior
            cmd.CommandText = "INSERT INTO Monitor " +
                              "(Mon_IdRegistro, Mon_NombreTabla) " +
                              "VALUES" +
                              "(@idRegistro, @nombreTabla)";

            cmd.Parameters.Add("@idRegistro", SqlDbType.Int).Value = idRegistro;
            cmd.Parameters.Add("@nombreTabla", SqlDbType.NVarChar).Value = "Clientes";
            cmd.ExecuteNoQuery();

            con.Close();


VB.NET
        'Variable de Conexión
        Dim con As New SqlClient.SqlConnection("Data Source=USER\\SQLEXPRESS;Initial Catalog=basePrueba;Integrated Security=True")
        con.Open()

        'Variable que contendrá el valor de la columna de identidad devuelto por la función SCOPE_IDENTITY()
        Dim idRegistro As Integer = 0

        'Armo el Query para Clientes
        'A la hora de declarar el query agrego la función SCOPE_IDENTITY()
        Dim cmd As New SqlClient.SqlCommand("INSERT INTO Clientes " _
                                        & "(Cli_Nombre, Cli_Direccion, Cli_Telefono, Cli_Email) " _
                                        & "VALUES" _
                                        & "(@nombre, @direccion, @telefono, @email) " _
                                        & "SELECT SCOPE_IDENTITY()", con)

        cmd.Parameters.Add("@nombre", SqlDbType.NVarChar).Value = txtNombre.Text
        cmd.Parameters.Add("@direccion", SqlDbType.NVarChar).Value = txtDireccion.Text
        cmd.Parameters.Add("@telefono", SqlDbType.NVarChar).Value = txtTelefono.Text
        cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = txtEmail.Text

        'Ejecuto la consulta y obtengo el valor devuelto por la misma
        idRegistro = Convert.ToInt32(cmd.ExecuteScalar())

        'Armo Query para el Monitor de Actividades
        'En la consulta paso un parámetro con el valor obtenido de la consulta anterior
        cmd.CommandText = "INSERT INTO Monitor " _
                          & "(Mon_IdRegistro, Mon_NombreTabla) " _
                          & "VALUES" _
                          & "(@idRegistro, @nombreTabla)"

        cmd.Parameters.Add("@idRegistro", SqlDbType.Int).Value = idRegistro
        cmd.Parameters.Add("@nombreTabla", SqlDbType.NVarChar).Value = "Clientes"
        cmd.ExecuteNoQuery();
        
        con.Close()


Seudocódigo
1. Declaro variable de conexión y le asigno un Connection String
2. Abro la Conexión
3. Declaro la variable 'idRegistro' que contendrá el valor de la columna de identidad del registro recién insertado
4. Declaro variable Command y le asigno un CommandText
--CommandText: Insertar en Clientes: en los campos (campos) los valores (valores) y seleccionar el valor del campo de identidad
5. Agrego los parámetros necesarios
6. Ejecuto la consulta y obtengo el valor solicitado
7. Asigno CommandText
--CommandtText: Insertar en Monitor: en los campos (campos) los valores (valores)
8. Agrego parámetros necesarios
--Parametro 'idRegistro': lo igualo al valor obtenido en la consulta anterior.
9. Ejecuto el query
10. Cierro la conexión

18 comentarios:

  1. Respuestas
    1. Buenos días Isaí.
      Como verás el valor de la columna de identidad de guarda en la variable "idRegistro".
      Seguidamente utilizamos el valor de esta variable para insertar el registro en la otra tabla, como lo muestra el ejemplo.
      Primero se inserta un registro en la tabla de Clientes y se obtiene el ID del mismo, luego se inserta un registro en la tabla de Monitor de Actividades.

      Saludos.

      Eliminar
  2. buenas tardes, ante todo muchas gracias por este blog, mira he hecho todo igual:
    tabla pedido (campo numpedido PK,identity)
    tabla detallepedido(campo numpedido FK)

    string s = System.Configuration.ConfigurationManager.ConnectionStrings["tiedadiscos"].ConnectionString;
    SqlConnection cn = new SqlConnection(s);
    cn.Open();

    int idRegistro = 0;
    SqlCommand cmd = new SqlCommand("INSERT INTO pedido " +
    "(fecha, tipodepago, total) " +
    "VALUES" +
    "(@fecha, @tipodepago, @total) " +
    "select IDENT_CURRENT('pedidos')", cn);

    cmd.Parameters.Add("@fecha", SqlDbType.DateTime).Value = txtfecha.Text;
    cmd.Parameters.Add("@tipodepago", SqlDbType.VarChar).Value = ddltipopago.SelectedItem;
    cmd.Parameters.Add("@total", SqlDbType.Float).Value = txttotal.Text;

    idRegistro = Convert.ToInt32(cmd.ExecuteScalar());//aqui es donde me salta el error


    cmd.CommandText = "INSERT INTO detallepedido " +
    "(numpedido,iddiscos,cantidad,precioventa) " +
    "VALUES (@idRegistro, @iddiscos,@cantidad,@precioventa)";

    cmd.Parameters.Add("@idRegistro", SqlDbType.Int).Value = idRegistro;
    cmd.Parameters.Add("@iddiscos", SqlDbType.Int).Value = Dlcompras.SelectedValue;
    cmd.Parameters.Add("@cantidad", SqlDbType.Int).Value = txtcantidad.Text;
    cmd.Parameters.Add("@precioventa", SqlDbType.Float).Value = txttotal.Text;

    cn.Close();

    Pero al ejecutar e introducir los datos me sale este error;
    el código de usuario no controlo InvalidCastException
    Error al convertir el valor del parametro del ListItem a String

    Un saludo muy cordial

    ResponderEliminar
  3. Hey soy yo de nuevo he descubierto una forma de hacerlo. Lo pongo aquí y después si tu quieres lo agregas a tu blog.

    public int GetAutoId()//obtener el valor del último id en la tabla pedido que después servirá para la forenkey de la tabla detallepedido
    {
    CargaDataSet();
    DataColumn idColumn = ds.Tables["pedido"].Columns["numpedido"];
    DataTable dt = idColumn.Table;
    String cn = idColumn.ColumnName;

    DataRow[] maxRow = dt.Select(cn + " = MAX(" + cn + ")");
    int value = 0;

    try
    {
    value = Convert.ToInt32(maxRow[0][cn]);
    }
    catch
    {
    return 0;
    }
    return value;
    }

    y el zona de ingresar datos ponemos esto


    protected void BTvalidar_Click(object sender, EventArgs e)//ingresar datos en la tabla pedido y detallepedido
    {
    try
    {
    CargaDataSet();
    DataRow nuevafila,nuevafila1;
    //agregar datos a la tabla pedido
    nuevafila1 = ds.Tables["pedido"].NewRow();
    ds.Tables["pedido"].Rows.Add(nuevafila1);
    nuevafila1["fecha"] = txtfecha.Text;
    nuevafila1["tipodepago"] = ddltipopago.SelectedItem;
    nuevafila1["total"] = txttotal.Text;

    dapedido.Update(ds, "pedido");

    //agregar datos a la tabla detallepedido
    int valor = GetAutoId();
    nuevafila = ds.Tables["detallepedido"].NewRow();
    ds.Tables["detallepedido"].Rows.Add(nuevafila);
    nuevafila["numpedido"] = valor;
    nuevafila["iddiscos"] = Dlcompras.SelectedValue;
    nuevafila["cantidad"] = txtcantidad.Text;
    nuevafila["precioventa"] = txtprecio.Text;

    dadetalle.Update(ds, "detallepedido");

    txtprecio.Text = "";
    txtcantidad.Text = "";
    txttotal.Text = "";
    }
    catch (Exception e1)
    {
    lblResult.Text = e1.Message;
    }
    }

    ResponderEliminar
    Respuestas
    1. Hola Andrés.
      Me alegro que hayas solucionado tu problema, recién veo el mensaje.
      Gracias por compartir tu solución en el blog, a algún usuario le servirá.

      Ahora que comentas me han entrado ganas de postear algunas cosas jaja, no he tenido tiempo para darle un poco de atención al blog.

      Saludos!

      Eliminar
  4. Muchas gracias por el aporte, me ha funcionado de excelente. Era justo lo que buscaba.



    Saludos,

    ResponderEliminar
  5. Excelente aporte, muchas gracias, muy claro y sencillo.
    Gracias por compartirlo.
    Saludos!!

    ResponderEliminar
    Respuestas
    1. Me alegro que te haya servido!. Gracias por comentar.

      Eliminar
  6. hola, no me marca error, pero la segunda consulta no me hace nada, lo estoy haciendo en C# y sql server, solo me inserta la primera consulta,

    ¿Qué puedo hacer?

    ResponderEliminar
    Respuestas
    1. La segunda consulta debería devolverte el ID del registro recién insertado, el cual deberás guardar en una variable.

      ¿La variable queda en 0?

      Eliminar
  7. gracias me fue muy util sobretodo por lo sencillo

    ResponderEliminar
  8. Genial Santiago! gracias por compartirlo!

    ResponderEliminar
  9. segui el ejemplo pero no me inserta nada en la segunda taba :/

    ResponderEliminar
    Respuestas
    1. te falta ExecuteNonQuery() en tu segundo query :)

      Eliminar
  10. Muy claro tu ejemplo, muchas gracias.

    ResponderEliminar