• Author / Uploaded
• Camilo Alberto Prieto Rodrigez

Citation preview

Ingeniería De Bases De Datos

Camilo Alberto Prieto

Docente: ING. GERMÁN NIÑO Docente Ingeniería De Bases De Datos

Universidad Manuela Beltrán Ingeniería de Sistemas Bogotá 2014

TABLA DE CONTENIDO Introducción a los Procedimientos Almacenados ........................................................................................... 3 Procedimientos almacenados extendidos .......................................................................................................... 3 Registro y uso de procedimientos .net con clr en sql server ................................................................. 3 Ejemplos ........................................................................................................................................................................... 5 Ejemplo #1 ............................................................................................................................................................. 6 Ejemplo #2 ............................................................................................................................................................. 8 Ejemplo #3 .......................................................................................................................................................... 10 Conclusiones ................................................................................................................................................................ 12 Referencias ................................................................................................................................................................... 12

INTRODUCCIÓN A LOS PROCEDIMIENTOS ALMACENADOS

Un procedimiento almacenado es un pequeño programa almacenado en la base de datos que puede ser ejecutado en cualquier momento. Los procedimientos almacenados, utilizan un lenguaje propietario ya que el estándar SQL ANSI 92 no especifica nada acerca de ellos.

PROCEDIMIENTOS ALMACENADOS EXTENDIDOS Los Procedimientos almacenados extendidos permiten crear Rutinas Externas propias en un lenguaje de Programación como c#, vb, c++…etc. , estos procedimientos se muestran ante los usuarios como procedimientos almacenados Normales y se ejecutan del mismo modo, estos procedimientos extendidos son (.DLL) que contienen módulos (Ensamblados) de Código Objeto llamado código MSIL (Nicrosoft Intermediate Language).Este es un código Multiplataforma, Independiente del procesador y Sistema operativo, que requiere ser compilado al Vuelo cuando se quiere ejecutar. Para esta tarea, SQL Server incluye en su núcleo el Compilador JIT (Just in-time), que es la base del CLR(Common Language Runtime). Los procedimientos extendidos son implementados como librerías dinámicas (DLLs) por lo general su nombre empieza con el prefijo “xp_” por ejemplo xp_cmdshell. [3]

REGISTRO Y USO DE PROCEDIMIENTOS .NET CON CLR EN SQL SERVER Usando Visual Studio y cualquiera de los lenguajes de la plataforma .NET (Visual C#, Visual C++, Visual J# y Visual Basic) se pueden crear librerías (ficheros.DLL) que contengan diferentes elementos entre ellos los Procedimientos Almacenados. [2]

Para implementar los procedimientos almacenados se debe Agregar y Registrar la DLL, para esto existen 2 Formas:

TRANSACT-SQL Para Crear el Ensamblado en SQL Server se utilizan las siguientes Sentencias: CREATE ASSEMBLY [Nombre_SqlServerProject] AUTHORIZATION [dbo] FROM 'Ruta\fichero.DLL' WITH PERMISSION_SET = SAFE

INTERFAZ GRAFICA SQL SERVER MANAGEMENT Otra forma para crear el Ensamblado no la brinda la interfaz Gráfica del Administrador de SQL Server Presionamos Clic en: BBDD  Programación  Ensamblados  Botón Derecho  Nuevo Ensamblado... Presionamos Clic en el botón Examinar y seleccionamos el en la ubicación donde este.

REGISTRO DE PROCEDIENTOS ALMACENADOS El siguiente paso es Registrar los Procedimientos Almacenados, para esto utilizamos TRANSACT-SQL con las siguientes sentencias:

CREATE PROCEDURE [dbo].[Nombre] @Parametro1 [TipoDato], @Parametro2 [TipoDato], @Parametron [TipoDato] WITH EXECUTE AS CALLER AS EXTERNAL NAME [Nombre Ensamblado].[Nombre Clase].[Nombre Procedimiento/Función del Ensamblado ]

Se debe realizar un CREATE por cada Procedimiento o Función del Ensamblado.

EJECUCION DE PROCEDIENTOS ALMACENADOS Para la ejecución del Procedimiento utilizamos la Siguiente Sentencia: Exec [Nombre Procemiento] Parametro1, Parametro1, ParametroN

Al ejecutar el procedimiento Extendido nos informara que no tenemos habilitada la ejecución de código de usuario en .NET Framework:

La ejecución de código de usuario en .NET Framework está deshabilitada. Habilite la opción de configuración "clr enabled".

HABILITAR LA INTEGRACIÓN CLR Para habilitar la Integración CLR utilizamos la Siguiente Sentencia: Exec sp_configure 'clr enabled', 1

Nos arrojara el siguiente Mensaje: Se ha cambiado la opción de configuración 'clr enabled' de 0 a 1. Ejecute la instrucción RECONFIGURE para instalar. En la Consulta ejecutamos: RECONFIGURE Este paso sólo se debe hacer una vez en cada servidor de SQL Server Ahora ya podemos usar los Procedimientos / Funciones que tengamos registrados.

EJEMPLOS Para el desarrollo de los ejemplos vamos a utilizar C# y la siguiente Base de Datos create database Ejemplos go use Ejemplos go Create table Empleados ( Identificacion Integer Not null, Nombre Varchar(30) Not Null, Apellido Varchar(30) Not null, Primary Key (Identificacion) ) go Create Table Sueldos_Empleados ( Identificacion Integer Not Null, Sueldo_Base Integer Not Null Foreign key (Identificacion) References Empleados(Identificacion) ) go delete from Empleados Insert Into Empleados values(1024493356,'Camilo','Prieto') Insert Into Empleados values(1024493222,'Alberto','Rodriguez') go delete from Sueldos_Empleados Insert Into Sueldos_Empleados values(1024493356,1000000) Insert Into Sueldos_Empleados values(1024493222,2000000)

EJEMPLO #1 En este ejemplo se realiza una consulta a los Objetos de la Base de Datos , donde se pasa como parámetro el Tipo de objetos que se quiere mostrar al usuario CODIGO ENSAMBLADO using using using using using using using using using

System; System.Collections.Generic; System.Linq; System.Text; System.Threading.Tasks; System.Data; System.Data.SqlClient; System.Data.SqlTypes; Microsoft.SqlServer.Server;

public class Calcular { public static void Calcular_Registros(string Tipo) { SqlConnection conexion = new SqlConnection("context connection=true"); SqlCommand comando = conexion.CreateCommand(); comando.CommandText = "SELECT name FROM SYSOBJECTS WHERE TYPE = '" + Tipo + "'"; conexion.Open(); SqlDataReader Lector = comando.ExecuteReader(); SqlDataRecord Resultado = new SqlDataRecord(new SqlMetaData("Tabla", SqlDbType.Text)); SqlContext.Pipe.SendResultsStart(Resultado);

while (Lector.Read()) { Resultado.SetString(0, Lector.GetString(0)); SqlContext.Pipe.SendResultsRow(Resultado); } SqlContext.Pipe.SendResultsEnd(); conexion.Close(); Lector.Close();

}

}

CREAR ENSAMBLADO Create assembly CalcularRegistros FROM 'C:\Users\usuario\Copy\Sistemas Operativos\Calcular_Registros\Calcular_Registros\bin\Debug\Calcular_Registros.dll' WITH PERMISSION_SET=SAFE GO

REGISTRAR PROCEDMIENTO Create Procedure Registros @Tipo nvarchar (3) WITH EXECUTE AS CALLER AS EXTERNAL name CalcularRegistros.Calcular.Calcular_Registros GO

EJECUCIÓN PROCEDIMIENTO exec Registros 'U'

EJEMPLO #2 En este Ejemplo se trabajara con la Base de datos Ejemplos, donde se toma los datos de la Tabla Sueldo_Empleados y se realizara una operación matemática para calcular el Sueldo a pagar por cada Empleado según el Sueldo Base, el porcentaje de subsidio y el porcentaje de Descuento donde estos 2 ultimo los recibe como parametros. CODIGO ENSAMBLADO using using using using using using using using using

System; System.Collections.Generic; System.Linq; System.Text; System.Threading.Tasks; System.Data; System.Data.SqlTypes; System.Data.SqlClient; Microsoft.SqlServer.Server;

public class CalcularSueldo { [Microsoft.SqlServer.Server.SqlProcedure()] public static void Calcular_Sueldo(int Subsidio,int Porcentaje_Descuento) { SqlConnection conexion = new SqlConnection("context connection=true"); SqlCommand comando = conexion.CreateCommand(); comando.CommandText = "Select * from Sueldos_Empleados"; conexion.Open(); SqlDataReader Total_Sueldo = comando.ExecuteReader(); SqlDataRecord Resultado = new SqlDataRecord(new SqlMetaData("Identificacion", SqlDbType.Int), new SqlMetaData("Sueldo_Base", SqlDbType.Int), new SqlMetaData("Sueldo_Total", SqlDbType.Int)); SqlContext.Pipe.SendResultsStart(Resultado); while (Total_Sueldo.Read()) { Resultado.SetInt32(0, Total_Sueldo.GetInt32(0)); Resultado.SetInt32(1, Total_Sueldo.GetInt32(1)); Resultado.SetInt32(2, ((Total_Sueldo.GetInt32(1) * Subsidio) / 100) + Total_Sueldo.GetInt32(1)); } SqlContext.Pipe.SendResultsEnd();

conexion.Close(); Total_Sueldo.Close();

}

}

CREAR ENSAMBLADO CREATE ASSEMBLY CalcularSueldo FROM 'C:\Users\usuario\Copy\Sistemas Operativos\CalcularSueldo\CalcularSueldo\bin\Debug\CalcularSueldo.dll' WITH PERMISSION_SET = SAFE GO

REGISTRAR PROCEDMIENTO Create Procedure Calcular @Subsidio integer, @Porcentaje_Descuento integer with execute as caller as external name CalcularSueldo.CalcularSueldo.Calcular_Sueldo GO

EJECUCIÓN PROCEDIMIENTO exec calcular 100,2

EJEMPLO #3 En este Ejemplo se trabajara con la Base de datos Ejemplos, donde se Insertara Registros a la Tabla Empleados desde el Procedimiento Almacenado, recibiendo como parámetros los datos del Nuevo Registro CODIGO ENSAMBLADO

using using using using using using using using using using

System; System.Collections.Generic; System.Linq; System.Text; System.Threading.Tasks; Microsoft.SqlServer.Server; System.Data.SqlClient; System.Data.SqlTypes; System.Data; System.IO;

public class Insertar_Datos {

[Microsoft.SqlServer.Server.SqlProcedure] public static void Inserta(int Cedula, string Nombre, string Apellido) { SqlConnection conexion = new SqlConnection("context connection=true"); SqlCommand comando = conexion.CreateCommand(); comando.CommandText = "Insert Into Empleados Values (@Identificacion,@Nombre,@Apellido)";

comando.Parameters.Add("@Identificacion", SqlDbType.Int).Value = Cedula ; comando.Parameters.Add("@Nombre", SqlDbType.NVarChar, 30).Value = Nombre; comando.Parameters.Add("@Apellido", SqlDbType.NVarChar, 30).Value = Apellido; try { conexion.Open(); comando.ExecuteNonQuery(); } catch (SqlException ex) { SqlContext.Pipe.Send("Error" + ex); } finally {

conexion.Close(); } }

}

CREAR ENSAMBLADO CREATE ASSEMBLY Insertar FROM 'C:\Users\usuario\Copy\Sistemas Operativos\Insertar_Datos\Insertar_Datos\bin\Debug\Insertar_Datos.dll' WITH PERMISSION_SET=SAFE GO

REGISTRAR PROCEDMIENTO CREATE PROCEDURE Insertar_Datos @Cedula int, @Nombre nvarchar(50), @Apellido nvarchar(50) with execute as caller as external name Insertar.Insertar_Datos.Inserta GO

EJECUCIÓN PROCEDIMIENTO Exec Insertar_Datos 1024493360,'Camilo','Prueba'

CONCLUSIONES

Transact-SQL está diseñado específicamente para el acceso directo a los datos y la manipulación de la base de datos. Aunque Transact-SQL destaca en el acceso y administración de datos, no es un lenguaje de programación completo. Por ejemplo, Transact-SQL no admite matrices, colecciones, bucles for-each, desplazamiento bit a bit o clases. Aunque algunas de estas construcciones se pueden simular en Transact-SQL, el código administrado tiene compatibilidad integrada para estas construcciones. Dependiendo de la situación, estas características pueden proporcionar una razón de peso para implementar cierta funcionalidad de base de datos en el código administrado.

REFERENCIAS

http://msdn.microsoft.com/es-es/library/ms345106.aspx [1] http://msdn.microsoft.com/es-es/library/ms131094.aspx [2] ftp://ftp.heanet.ie/mirrors/sourceforge/t/te/technicaldocs/PDF/CLR.pdf [3] http://msdn.microsoft.com/es-es/library/ms131092.aspx [4]