Diferencias entre conexiones de modo Conectado y Desconectado

ADO.NET nos provee de dos formas de conectarse a la base de datos. Son los modos Conectado y Desconectado.
Cada uno utiliza un árbol diferente de objetos.

El Modo conectado es el habitual. Consiste en conectarse a la base de datos y realizar despues multitud de operaciones contra la base de datos, e ir actualizando. Finalmente, al acabar la sesión,cerramos la conexión. Pueden pasar horas, si no nos tira antes la base de datos.

El Modo desconectado, es mas rápido, porque se trata de cargar la primera vez que se hace la consulta toda la información en formato XML, que manejaremos en memoria. Y al final, cuando interesé se hará el volcado a la base de datos. Durante todo ese tiempo, no estará conectado a la base de datos. Esto es útil por ejemplo para manejar listas de provincias que no tienen modificaciones. O también una lista de empleados que se actualice una vez al día o muy poco. No sería nada recomendable utilizarlo en sitios donde se requiera actualización constante de la información.

En este último modo, la conexión a la base de datos se cierra inmediatamente, cuando ha terminado de descargar la información. Esto sucede en el momento en que se rellena el DataSet. Cuando tengamos el conjunto de resultados, ya no habrá conexión.

Para el modo conectado, se utilizan los objetos de ADO.NET, SQlConnection, SQLCommand y SQLDataReader.
Para el modo desconectado, hay que ir bajando desde SQLDataAdapter, SQLDataSet, SQLDataTables, y unas cuantas más.

En ambos modos, para conectar con bases de datos SQLServer, requiere importar System.Data.SQLClient.

Crear consultas SQL Server que generen XML

Pues es tan simple como añadir las sentencias FOR XML AUTO al final de la sentencia sql.

SELECT * FROM EMP FOR XML AUTO

Genera el siguiente resultado. Una fila con esta información.

<emp EMP_NO="7499" APELLIDO="ARROYO" OFICIO="VENDEDOR" DIR="7698" FECHA_ALT="1981-02-22T00:00:00" SALARIO="208000" COMISION="31200" DEPT_NO="33" />
<emp EMP_NO="7521" APELLIDO="SALA" OFICIO="VENDEDOR" DIR="7698" FECHA_ALT="1981-02-22T00:00:00" SALARIO="162500" COMISION="24375" DEPT_NO="33" />
<emp EMP_NO="7654" APELLIDO="MARTIN" OFICIO="VENDEDOR" DIR="7698" FECHA_ALT="1981-09-28T00:00:00" SALARIO="182000" COMISION="27300" DEPT_NO="33" />
<emp EMP_NO="7698" APELLIDO="NEGRO" OFICIO="DIRECTOR" DIR="7839" FECHA_ALT="1981-05-01T00:00:00" SALARIO="370500" COMISION="55575" DEPT_NO="33" />
<emp EMP_NO="7782" APELLIDO="CEREZO" OFICIO="DIRECTOR" DIR="7839" FECHA_ALT="1981-06-09T00:00:00" SALARIO="318500" COMISION="47775" DEPT_NO="10" />
<emp EMP_NO="7844" APELLIDO="TOVAR" OFICIO="VENDEDOR" DIR="7698" FECHA_ALT="1981-09-08T00:00:00" SALARIO="195000" COMISION="29250" DEPT_NO="33" />
<emp EMP_NO="7900" APELLIDO="JIMENO" OFICIO="EMPLEADO" DIR="7698" FECHA_ALT="1981-12-03T00:00:00" SALARIO="123500" COMISION="18525" DEPT_NO="33" />
<emp EMP_NO="7934" APELLIDO="MUÑOZ" OFICIO="EMPLEADO" DIR="7782" FECHA_ALT="1982-06-23T00:00:00" SALARIO="169000" COMISION="25350" DEPT_NO="10" />

Crear una tabla de registro de actividades en SQL Server

Utilizando los descencadenadores o triggers, podemos hacer que cualquier modificación que hagamos sobre nuestra tabla de empleados EMP, se vea reflejada en una tabla que llamaremos TABLA_CONTROL. Y se añadirán todos los empleados dados de alta, modificados o eliminados en dicha tabla, incluyendo la hora en la que se realizó el cambio, el apellido del empleado y el usuario que hizo el cambio. Además en una cuarta columna indicaremos si ha sido un cambio por ALTA, MODIFICACIÓN o BAJA de/de los empleado/s.

Creamos el trigger para las bajas de la siguente manera:

CREATE TRIGGER TRG_BAJA ON EMP FOR DELETE AS
INSERT INTO TABLA_CONTROL
SELECT DELETED.APELLIDO,GETDATE(),USER_NAME(),'BAJA' FROM deleted

El de las altas:

CREATE TRIGGER TRG_ALTA ON EMP FOR INSERT AS
INSERT INTO TABLA_CONTROL
SELECT INSERTED.APELLIDO,GETDATE(),USER_NAME(),'ALTA' FROM INSERTED

Y el de las modificaciones, donde incluiremos el dato antiguo antes del cambio, por eso usamos FROM DELETED.
Si quisieramos guardar el dato cambiado, sería FROM INSERTED y INSERTE.APELLIDO en el SELECT.
O podemos poner en el campo APELLIDO el apellido original y en la columna OPERACIÓN, en lugar de poner ALTA o BAJA o MODIFICACIÓN, pues ponemos el nuevo apellido.

CREATE TRIGGER TRG_UPDATE ON EMP FOR UPDATE AS
INSERT INTO TABLA_CONTROL
SELECT DELETED.APELLIDO,GETDATE(),USER_NAME(),INSERTED.APELLIDO FROM DELETED,INSERTED

El procedimiento común es que haremos un INSERT INTO en la TABLA_CONTROL, con el contenido de la SELECT que tira de la tabla temporal que proceda en cada caso. INSERTED o DELETED.

Explicación de Triggers en SQL Server

Un Trigger de base de datos, es un evento que se dispara cuando se realizan modificaciones en una tabla.
Internamente se crean tablas temporales mientras se realiza la actualización de la tabla. Son dos tablas, llamadas DELETED e INSERTED. No existe UPDATED. Si es una actualización los datos nuevos se ingresan en INSERTED y los viejos en DELETED.

Este código permite que si se borra un departamento, se borren automaticamente todos los empleados de dicho departamento.

CREATE TRIGGER BorrarEmpDept
ON Dept
FOR DELETE AS

DELETE FROM EMP
FROM EMP,DELETED
WHERE EMP.DEPT_NO=DELETED.DEPT_NO

Esta línea rara en medio FROM EMP,DELETED es una imposición de Microsoft que requiere que se incluya un FROM Adicional con todas las tablas involucradas en la consulta.

Con este si se cambia el código de un departamento, automaticamente actualizará esa clave de departamento en todos los empleados de dicho departamento.

CREATE TRIGGER ModifEmpDept
ON Dept
FOR UPDATE AS

UPDATE EMP
SET EMP.DEPT_NO=INSERTED.DEPT_NO
FROM EMP,DELETED,INSERTED
WHERE EMP.DEPT_NO=DELETED.DEPT_NO

Se puede ver, que hay que incluir el FROM adicional. En este último caso, en INSERTED, tenemos una fila con un Departamento con el codigo nuevo 33. Lo cogemos y se lo asignamos a los códigos de departamento de los empleados que tuvieran el código viejo 30, el cuál obtenemos de la tabla DELETED.

 

En este último ejemplo cambiamos el código del departamento DEPT.DEPT_NO del 30 al 33. El proceso interno es el siguiente.

  • 1º Inserta en la tabla DELETED el registro del Departamento modificado, que incluye su clave DELETED.DEPT_NO = 30.
  • 2º El departamento modificado con DEPT_NO= 33, se crea como un nuevo registro en INSERTED.DPTO_NO=33.
  • 3º Ejecuta el código del trigger, que actualiza los DEPT_NO de la tabla EMP que estuvieran en 30, y los pone en 33.
  • 4ºRealiza el cambio en la tabla DEPT, que es donde se inicio la actualización.
  • 5ºBorra las tablas temporales DELETED e INSERTED.

Crear valores por defecto en columnas SQL Server

De forma similar a como creamos reglas (Rules) en este artículo, podemos crear valores por defecto de forma genérica y asignarlos a las distintas columnas que nos interese.

Un ejemplo sencillo, sería crear un valor default (por defecto), que indique que un salario por defecto para un nuevo empleado sea el minimo interprofesional, de 641 euros. Entonces, primero se crearía el Default, y despues lo asignaríamos a la columna Salario.

 

CREATE DEFAULT Nombre AS Expresión
CREATE DEFAULT SalarioMinimo AS int

Para vincular este DEFAULT se usa:

EXEC sp_bindefault SalarioMinimo,'Empleados.Salario'

Para desvincularlo es similar pero intercambia los parámetros.

EXEC sp_unbindefault 'Empleados.Salario',SalarioMinimo

Se pueden ver todas las restricciones con sp_helpcontraint