Create Table elegante

Talves sea la fuerza de la costumbre, pero siempre que me ponían a crear un tabla en sql server, primero creaba la estructura de la tabla, las columnas, a parte el primary key y luego las foreign keys.

Recientemente me he dado cuenta que se puede crear todo de una vez y queda elegante. veamos un ejemplo

CREATE TABLE dbo.OrdenDetalle
(
    conOrden INT NOT NULL 
        CONSTRAINT OrdenEncabezado_OrdenDetalle_FK FOREIGN KEY
        REFERENCES dbo.OrdenEncabezado(conOrden),
    conOrdenDetalle INT NOT NULL IDENTITY (1,1), 
    desLinea VARCHAR(254) NOT NULL,
    numPrecio MONEY NOT NULL,
    usrIngreso VARCHAR(20) NOT NULL 
        CONSTRAINT DF_OrdenDetalle_usrIngreso DEFAULT (user_name()),
    fecIngreso DATETIME NOT NULL 
        CONSTRAINT DF_OrdenDetalle_fecingreso DEFAULT (getdate()),
    CONSTRAINT PK_OrdenDetalle
        PRIMARY KEY CLUSTERED (conOrden, conOrdenDetalle)
        WITH (IGNORE_DUP_KEY = OFF)
);

Describamos el script.

Primeramente tenemos la declaración de la creación de la tabla OrdenDetalle y entre paréntesis la descripción completa de la misma. Declaramos el primer campo, conOrden, que es una llave foránea proveniente de la tabla OrdenEncabezado. Todo esto lo añadimos en la especificación de la columna por medio de un constraint para darle nombre a la FK. Cada especificación completa de columna se separa de la siguiente con una coma.

Seguidamente añadimos conOrdenDetalle que es un número consecutivo Identity, que comienza en uno y se incrementa automáticamente de uno en uno. Añadimos dos columnas más: desLinea (varchar) y  numPrecio (money), la descripción de la línea y el precio de la misma respectivamente.

Seguidamente dos columna de seguimiento como son el usuario que ingresó la línea (usrIngreso) y la fecha de ingreso (fecIngreso), ambas con valores por defecto usando constraints con nombres descriptivos.

Finalmente creamos una constraint más, correspondiente a la llave primaria de la tabla, usando las columnas conOrden y conOrdenDetalle dejando explícitamente que esta llave no se puede duplicar (innecesario en realidad ya que la segunda parte de la llave es un identity).

A mi, en lo personal, me parece una forma más compacta y elegante, que manejarlo por separado.

Leer más

Linq to SQL Insert

Tratando de aprender cosas nuevas, recientemente he intentado hacer algunos experimentos con LINQ. Aquí un ejemplo de como insertar un registro simple.

Creamos un proyecto de tipo consola. A este le añadimos un "New Item" de tipo "LINQ to SQL Classes", en este momento se nos abre un diseñador de clases. Para evitarnos la fatiga, lo que vamos a hacer es irnos al "Server Explorer"  abrir una base de datos y arrastrar una tabla sobre el diseñador. Para este ejemplo yo arrastré una tabla que tiene un campo Id que corresponde a su llave primaria y que ademas es un identity, y un campo descripción.

Una vez hecho esto lo siguiente es intentar incluir un registro por medio de linq, lo cual es muy simple.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
  
    class Program
    {
        static void Main(string[] args)
        {
            string connstr = @"Data Source=MiServer;Initial Catalog=miBD;Integrated Security=True";
            DataClasses1DataContext dc = new DataClasses1DataContext (connstr);

            TablaPrueba tb = new TablaPrueba ();
            tb.Descripcion = "Nuevo Registro";
            dc.TablaPruebas.InsertOnSubmit (tb);
            dc.SubmitChanges();
            Console.WriteLine ("El id del registro insertado es {0}",tb.Id);
            Console.ReadLine ();
        }
    }
}

Como podemos ver lo que hacemos es utilizar una instancia de la clase DataClasses1DataContextal (DataClases1 es el nombre que le dimos a las clases de Linq To Sql) a la que le pasamos un string de conexión correspondiente a la base de datos sobre la cual vamos a trabajar. Usamos la clase que se creó al arrastrar la tabla para llenar sus propiedades (en este caso TablaPrueba) y, finalmente, usamos los métodos InsertOnSubmit y SubmitChanges para guardar los datos. Al terminar la operación podemos extraer el id creado automáticamente por la base de datos el cual queda en el mismo objeto correspondiente al registro.

Ya con este ejemplo podemos darnos cuenta lo simple que es trabajar con Linq To Sql

Leer más

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.

Leer más