Contents
Por qué crear procedimientos sql compilados?. 3
Debug de funciones compiladas. 4
Restauración de base de datos. 6
Introducción
Las funciones definidas por el usuario son rutinas que pueden aceptar parámetros, realizar cálculos u otras acciones y devolver un resultado. Puede escribir funciones definidas por el usuario en cualquier lenguaje de programación de Microsoft .NET Framework, como Microsoft Visual Basic .NET o Microsoft Visual C#.
Fuente msdn microsoft : https://msdn.microsoft.com/es-es/library/ms254508(v=vs.110).aspx
Por qué crear procedimientos sql compilados?
Microsoft SQL Server proporciona compatibilidad con tipos definidos por el usuario (UDT) implementados con Common Language Runtime (CLR) de Microsoft .NET Framework.CLR se integra en SQL Server y este mecanismo permite ampliar el sistema de tipos de la base de datos. Los tipos definidos por el usuario proporcionan al usuario extensibilidad del sistema de tipos de datos de SQL Server, así como la capacidad para definir tipos estructurados complejos.
Desde la perspectiva de una arquitectura de aplicación, pueden proporcionar dos ventajas clave:
- Sólido encapsulado (en el cliente y el servidor) entre el estado interno y los comportamientos externos.
- Fuerte integración con otras características de servidor relacionadas.Una vez definidos sus propios tipos definidos por el usuario, puede utilizarlos en todos los contextos en los que pueda emplear un tipo de sistema de SQL Server, como definiciones de columnas, variables, parámetros, resultados de funciones, cursores, desencadenadores y replicación.
Fuente msdn microsoft : https://msdn.microsoft.com/es-es/library/ms254944(v=vs.110).aspx
Debug de funciones compiladas
Tener en cuenta al momento de realizar debug de funciones compiladas que debemos ajustar las propiedades de debugging de visual studio. Para realizar dichos ajustes hacer click en “Debug” y luego “Options and Settings..”
Luego veremos en la sección “Debugging” -> “General” que tendremos chequeado por default “Enable Just My Code ( Managed only)” esta opción no debe estar chequeada.
En caso de tener chequeada esa opción lo que pasara es que al querer debuggear una función compilada solo mostrara el resultado de la misma sin poder hacer el step into, el motivo de este comportamiento es porque no es código nativo de c#.
Una vez realizado este ajuste es importante hacer right click sobre el test script que deseamos probar y click en “Set as Default Debug Script”. Luego para empezar el debug es importante hacer click en “Debug Script”.
Restauración de base de datos
Al restaurar una base de datos que contenga procedimientos compilados .net deberá tenerse en cuenta que es necesario eliminar los assemblies generados correspondientes a dichos procedimientos y volver a generarlos. A continuación se toma una base de datos de ejemplo y se muestran los pasos a seguir para realizar la restauración.
- Restaurar la base de datos
- Una vez restaurada la base debemos borrar los assemblies y volver a generarlos, para visualizar cuales son, podemos ir a programability -> Assemblies
- El primer paso es setear la base de datos con TRUSTWORTHY ON, esta instrucción sirve para indicar al motor de base de datos que puede confiar en los ensamblados y el contenido de la base de datos
ALTER DATABASE DB_NAME SET TRUSTWORTHY ON
- El Segundo paso es borrar los assemblies y sus dependencias. Es importante que se ejecute en este orden ya que si no borramos primero los stored procedures y funciones compilados, no podremos borrar los ensamblados.
DROP PROCEDURE Calculation_SP1
DROP PROCEDURE Calculation_SP2
DROP FUNCTION UDF1
DROP assembly [Example.SP.SQL.Calculation]
DROP assembly [System.Data.Entity]
DROP assembly [System.Runtime.Serialization]
DROP ASSEMBLY [SMDiagnostics]
DROP assembly [System.ComponentModel.DataAnnotations]
- Hacemos click derecho sobre assemblies, refresh y no deberíamos ver ningún assemblie relacionado con las funciones compiladas
- Ahora debemos volver a generar los assemblies borrados en el paso anterior:
CREATE ASSEMBLY [SMDiagnostics]
authorization [dbo] /* puede darse que sea [linksis] */
from ‘C:\Windows\Microsoft.NET\Framework64\v3.0\Windows Communication Foundation\SMDiagnostics.dll’
with permission_set = unsafe
create assembly [System.Runtime.Serialization]
authorization [dbo] /* puede darse que sea [linksis] */
from ‘C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\system.runtime.serialization.dll’
with permission_set = unsafe
create assembly [System.Data.Entity]
authorization [dbo] /* puede darse que sea [linksis] */
from ‘C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.data.entity.dll’
with permission_set = unsafe
create assembly [System.ComponentModel.DataAnnotations]
authorization [dbo] /* puede darse que sea [linksis] */
from ‘C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.componentmodel.dataannotations.dll’
with permission_set = unsafe
- Ahora debemos volver a generar los assemblies correspondientes a nuestro proyecto Ejemplo.Model.dll y Ejemplo.SQL.CalculationEngine.dll.
Nota : Estas dll corresponden a nuestro proyecto
DECLARE @AssemblyPath nvarchar(255)
create assembly [Ejemplo.Model]
authorization [dbo]
from @AssemblyPath + ‘ Ejemplo.Model.dll’
with permission_set = unsafe
create assembly [Ejemplo.CalculationEngine]
authorization [dbo]
from @AssemblyPath + ‘Ejemplo.CalculationEngine.dll’
with permission_set = unsafe
- Una vez creados los assemblies, procedemos a crear los stored procedures y funciones compiladas.
CREATE PROCEDURE [dbo].[Calculation_SP1]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Calculation].[StoredProcedures].[SP1]
GO
EXEC sys.sp_addextendedproperty @name=N’AutoDeployed’, @value=N’yes’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’PROCEDURE’,@level1name=N’SP_CPER_CalculationEngine_Execute’
EXEC sys.sp_addextendedproperty @name=N’SqlAssemblyFile’, @value=N’StoredProcedures.cs’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’PROCEDURE’,@level1name=N’SP_CPER_CalculationEngine_Execute’
EXEC sys.sp_addextendedproperty @name=N’SqlAssemblyFileLine’, @value=16 , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’PROCEDURE’,@level1name=N’Calculation_SP1′
GO
CREATE FUNCTION [dbo].[UDF_1](@Param [int])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Calculation].[UserDefinedFunctions].[UDF_1]
GO
EXEC sys.sp_addextendedproperty @name=N’AutoDeployed’, @value=N’yes’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’FUNCTION’,@level1name=N’UDF_CPER_GetValueByMatrixID’
EXEC sys.sp_addextendedproperty @name=N’SqlAssemblyFile’, @value=N’UserDefinedFunctions.cs’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’FUNCTION’,@level1name=N’UDF_CPER_GetValueByMatrixID’
EXEC sys.sp_addextendedproperty @name=N’SqlAssemblyFileLine’, @value=10 , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’FUNCTION’,@level1name=N’UDF_1′
- Por ultimo para verificar que las rutinas compiladas están funcionando correctamente podemos ejecutar un stored procedure compilado desde sql management por ejemplo.
EXEC dbo.Ejemplo.GetCustomers
