Crear procedimientos almacenados y funciones definidas por el usuario con código administrado (C#)
por Scott Mitchell
Microsoft SQL Server 2005 se integra con Common Language Runtime de .NET para permitir a los desarrolladores crear objetos de base de datos mediante código administrado. En este tutorial se muestra cómo crear procedimientos almacenados administrados y funciones administradas definidas por el usuario con el código de Visual Basic o C#. También vemos cómo estas ediciones de Visual Studio permiten depurar estos objetos de base de datos administrados.
Introducción
Las bases de datos como SQL Server 2005 de Microsoft usan el Lenguaje de consulta estructurado de Transact (T-SQL) para insertar, modificar y recuperar datos. La mayoría de los sistemas de base de datos incluyen construcciones para agrupar una serie de instrucciones SQL que se pueden ejecutar como una sola unidad reutilizable. Los procedimientos almacenados son un ejemplo. Otra es Funciones definidas por el usuario (UDF), una construcción que examinaremos con más detalle en el paso 9.
En su núcleo, SQL está diseñado para trabajar con conjuntos de datos. Las extractos SELECT, UPDATE, y DELETE se aplican inherentemente a todos los registros de la tabla correspondiente y solo están limitados por sus cláusulas WHERE. Sin embargo, hay muchas características de lenguaje diseñadas para trabajar con un registro cada vez y para manipular datos escalares. CURSOR permite que un conjunto de registros se recorra uno a uno. Las funciones de manipulación de cadenas como LEFT, CHARINDEX, y PATINDEX funcionan con datos escalares. SQL también incluye instrucciones de flujo de control como IF y WHILE.
Antes de Microsoft SQL Server 2005, los procedimientos almacenados y las UDF solo se podían definir como una colección de instrucciones T-SQL. Sin embargo, SQL Server 2005 se ha diseñado para proporcionar integración con Common Language Runtime (CLR), que es el tiempo de ejecución que usan todos los ensamblados de .NET. Por lo tanto, los procedimientos almacenados y las UDF en una base de datos de SQL Server 2005 se pueden crear mediante código administrado. Es decir, puede crear un procedimiento almacenado o UDF como método en una clase de C#. Esto permite que estos procedimientos almacenados y UDF usen la funcionalidad en .NET Framework y desde sus propias clases personalizadas.
En este tutorial examinaremos cómo crear procedimientos almacenados administrados y funciones definidas por el usuario y cómo integrarlos en nuestra base de datos Northwind. ¡Comencemos!
Nota:
Los objetos de base de datos administrados ofrecen algunas ventajas sobre sus homólogos de SQL. La riqueza y la familiaridad del lenguaje y la capacidad de reutilizar el código y la lógica existentes son las principales ventajas. Pero es probable que los objetos de base de datos administrados sean menos eficaces al trabajar con conjuntos de datos que no implican mucha lógica de procedimientos. Para obtener una explicación más detallada sobre las ventajas de usar código administrado frente a T-SQL, consulte las Ventajas de usar código administrado para crear objetos de base de datos.
Paso 1: Mover la base de datos Northwind fuera de App_Data
Todos nuestros tutoriales hasta ahora han usado un archivo de base de datos de Microsoft SQL Server 2005 Express Edition en la carpeta App_Data de la aplicación web. Colocar la base de datos en distribución App_Data simplificada y ejecutar estos tutoriales, ya que todos los archivos se encontraban en un directorio y no requerían pasos de configuración adicionales para probar el tutorial.
En este tutorial, sin embargo, vamos a mover la base de datos Northwind fuera de App_Data y registrarla explícitamente con la instancia de base de datos de SQL Server 2005 Express Edition. Aunque podemos realizar los pasos de este tutorial con la base de datos de la carpeta App_Data, varios de los pasos se hacen mucho más sencillos registrando explícitamente la base de datos con la instancia de base de datos de SQL Server 2005 Express Edition.
La descarga de este tutorial tiene los dos archivos de base de datos ( NORTHWND.MDF y NORTHWND_log.LDF ) colocados en una carpeta denominada DataFiles. Si sigue junto con su propia implementación de los tutoriales, cierre Visual Studio y mueva los archivos NORTHWND.MDFy NORTHWND_log.LDF de la carpeta del sitio web a una carpeta App_Data fuera del sitio web. Una vez que los archivos de base de datos se han movido a otra carpeta, es necesario registrar la base de datos Northwind con la instancia de base de datos de SQL Server 2005 Express Edition. Esto se puede hacer desde SQL Server Management Studio. Si tiene instalada una edición que no sea Express de SQL Server 2005 en el equipo, es probable que ya tenga instalado Management Studio. Si solo tiene SQL Server 2005 Express Edition en el equipo, dedique un momento a descargar e instalar Microsoft SQL Server Management Studio.
Inicie SQL Server Management Studio. Como se muestra en la figura 1, Management Studio comienza preguntando a qué servidor se va a conectar. Escriba localhost\SQLExpress como nombre del servidor, elija Autenticación de Windows en la lista desplegable Autenticación y haga clic en Conectar.
Figura 1: Conexión a la instancia de base de datos adecuada
Una vez que se haya conectado, la ventana Explorador de objetos mostrará información sobre la instancia de base de datos de SQL Server 2005 Express Edition, incluidas sus bases de datos, información de seguridad, opciones de administración, etc.
Es necesario adjuntar la base de datos Northwind en la carpeta DataFiles (o dondequiera que se haya movido) a la instancia de base de datos de SQL Server 2005 Express Edition. Haga clic con el botón derecho en la carpeta Bases de datos y elija la opción Asociar en el menú contextual. Se abrirá el cuadro de diálogo Adjuntar bases de datos. Haga clic en el botón Agregar, explore en profundidad el archivo NORTHWND.MDF, adecuado y haga clic en Aceptar. En este momento, la pantalla debe ser similar a la figura 2.
Figura 2: Conectarse a la instancia de base de datos adecuada (Haga clic para ver la imagen de tamaño completo)
Nota:
Al conectarse a la instancia de SQL Server 2005 Express Edition a través de Management Studio, el cuadro de diálogo Adjuntar bases de datos no permite explorar en profundidad los directorios de perfil de usuario, como Mis documentos. Por lo tanto, asegúrese de colocar los archivos NORTHWND.MDF y NORTHWND_log.LDF en un directorio de perfil que no sea de usuario.
Haga clic en el botón Aceptar para adjuntar la base de datos. El cuadro de diálogo Adjuntar bases de datos se cerrará y el Explorador de objetos debería mostrar ahora la base de datos adjunta. Es probable que la base de datos Northwind tenga un nombre como 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Cambie el nombre de la base de datos a Northwind haciendo clic con el botón derecho en la base de datos y seleccionando Cambiar nombre.
Figura 3: Cambiar el nombre de la base de datos a Northwind
Paso 2: Crear una nueva solución y un proyecto de SQL Server en Visual Studio
Para crear procedimientos almacenados administrados o UDF en SQL Server 2005, escribiremos el procedimiento almacenado y la lógica de UDF como código de C# en una clase. Una vez escrito el código, es necesario compilar esta clase en un ensamblado (un .dll archivo), registrar el ensamblado con la base de datos de SQL Server y a continuación, crear un procedimiento almacenado o un objeto UDF en la base de datos que apunte al método correspondiente en el ensamblado. Estos pasos se pueden realizar manualmente. Podemos crear el código en cualquier editor de texto, compilarlo desde la línea de comandos mediante el compilador de C# (csc.exe), registrarlo con la base de datos mediante el CREATE ASSEMBLY comando o desde Management Studio, y agregar el procedimiento almacenado o el objeto UDF a través de medios similares. Afortunadamente, las versiones Professional y Team Systems de Visual Studio incluyen un tipo de proyecto de SQL Server que automatiza estas tareas. En este tutorial se le guiará por el uso del tipo de proyecto de SQL Server para crear un procedimiento almacenado administrado y UDF.
Nota:
Si usa Visual Web Developer o la edición Standard de Visual Studio, tendrá que usar el enfoque manual en su lugar. El paso 13 proporciona instrucciones detalladas para realizar estos pasos manualmente. Le recomendamos que lea los pasos 2 a 12 antes de leer el paso 13, ya que estos pasos incluyen instrucciones de configuración importantes de SQL Server que deben aplicarse independientemente de la versión de Visual Studio que esté usando.
Para empezar, abra Visual Studio. En el menú Archivo, elija Nuevo proyecto para mostrar el cuadro de diálogo Nuevo proyecto (vea la figura 4). Explore en profundidad el tipo de proyecto Base de datos y a continuación, en las plantillas que aparecen a la derecha, elija crear un nuevo proyecto de SQL Server. He elegido asignar un nombre a este proyecto ManagedDatabaseConstructs y colocarlo en una solución denominada Tutorial75.
Figura 4: Crear un nuevo proyecto de SQL Server (Haga clic para ver la imagen de tamaño completo)
Haga clic en el botón Aceptar del cuadro de diálogo Nuevo proyecto para crear la solución y el proyecto de SQL Server.
Un proyecto de SQL Server está vinculado a una base de datos determinada. Por lo tanto, después de crear el nuevo proyecto de SQL Server, se nos pide inmediatamente que especifique esta información. En la figura 5 se muestra el cuadro de diálogo Nueva referencia de base de datos que se ha rellenado para que apunte a la base de datos Northwind registrada en la instancia de base de datos de SQL Server 2005 Express Edition en el paso 1.
Figura 5: Asociar el proyecto de SQL Server con la base de datos Northwind
Para depurar los procedimientos almacenados administrados y las UDF, crearemos dentro de este proyecto, es necesario habilitar la compatibilidad con la depuración de SQL/CLR para la conexión. Siempre que asocien un proyecto de SQL Server con una nueva base de datos (como hicimos en la figura 5), Visual Studio nos pregunta si queremos habilitar la depuración de SQL/CLR en la conexión (vea la figura 6). Haga clic en Sí.
Figura 6: Habilitación de la depuración de SQL/CLR
En este momento, el nuevo proyecto de SQL Server se ha agregado a la solución. Contiene una carpeta denominada Test Scripts con un archivo denominado Test.sql, que se usa para depurar los objetos de base de datos administrados creados en el proyecto. Veremos la depuración en el paso 12.
Ahora podemos agregar nuevos procedimientos almacenados administrados y UDF a este proyecto, pero antes de dejar que incluyamos primero nuestra aplicación web existente en la solución. En el menú Archivo, seleccione la opción Agregar y elija Sitio web existente. Vaya a la carpeta del sitio web adecuada y haga clic en Aceptar. Como se muestra en la figura 7, se actualizará la solución para incluir dos proyectos: el sitio web y el proyecto de SQL Server ManagedDatabaseConstructs.
Figura 7: El Explorador de soluciones ahora incluye dos proyectos
El valor NORTHWNDConnectionString actualmente Web.config hace referencia al archivo NORTHWND.MDF de la carpeta App_Data. Puesto que hemos quitado esta base de datos de App_Data y la registramos explícitamente en la instancia de base de datos de SQL Server 2005 Express Edition, es necesario actualizar el valor NORTHWNDConnectionString correspondiente. Abra el archivo Web.config en el sitio web y cambie el valor NORTHWNDConnectionString para que la cadena de conexión lea: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Después de este cambio, su sección <connectionStrings> en Web.config debería ser similar a la siguiente:
<connectionStrings>
<add name="NORTHWNDConnectionString" connectionString=
"Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
Integrated Security=True;Pooling=false"
providerName="System.Data.SqlClient" />
</connectionStrings>
Nota:
Como se explicó en el tutorial anterior, al depurar un objeto de SQL Server desde una aplicación cliente, como un sitio web de ASP.NET, es necesario deshabilitar la agrupación de conexiones. La cadena de conexión mostrada anteriormente deshabilita la agrupación de conexiones ( Pooling=false ). Si no planea depurar los procedimientos almacenados administrados y las UDF desde el sitio web de ASP.NET, habilite la agrupación de conexiones.
Paso 3: Crear un procedimiento almacenado administrado
Para agregar un procedimiento almacenado administrado a la base de datos Northwind, primero es necesario crear el procedimiento almacenado como un método en el proyecto de SQL Server. En el Explorador de soluciones, haga clic con el botón derecho en el nombre del proyecto ManagedDatabaseConstructs y elija agregar un nuevo elemento. Se mostrará el cuadro de diálogo Agregar nuevo elemento, que enumera los tipos de objetos de base de datos administrados que se pueden agregar al proyecto. Como se muestra en la figura 8, esto incluye procedimientos almacenados y funciones definidas por el usuario, entre otros.
Comencemos agregando un procedimiento almacenado que simplemente devuelve todos los productos que se han descontinuado. Asigne al nuevo archivo de procedimiento almacenado el nombre GetDiscontinuedProducts.cs.
Figura 8: Agregar un nuevo procedimiento almacenado denominado GetDiscontinuedProducts.cs (Haga clic para ver la imagen de tamaño completo)
Esto creará un nuevo archivo de clase de C# con el siguiente contenido:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetDiscontinuedProducts()
{
// Put your code here
}
};
Tenga en cuenta que el procedimiento almacenado se implementa como un método static dentro de un archivo de clase partial denominado StoredProcedures. Además, el métodoGetDiscontinuedProducts está decorado con SqlProcedure attribute, que marca el método como un procedimiento almacenado.
El código siguiente crea un objeto SqlCommand y establece su CommandText en una consulta SELECT que devuelve todas las columnas de la tabla Products para los productos cuyo campo Discontinued es igual a 1. A continuación, ejecuta el comando y devuelve los resultados a la aplicación cliente. Agregue este código al método GetDiscontinuedProducts.
// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE Discontinued = 1";
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
Todos los objetos de base de datos administrados tienen acceso a un SqlContextobjeto que representa el contexto del autor de la llamada. SqlContext proporciona acceso a un SqlPipeobjeto a través de su Pipe propiedad. Este objeto SqlPipe se usa para transportar información entre la base de datos de SQL Server y la aplicación que realiza la llamada. Como su nombre implica, el ExecuteAndSend método ejecuta un objeto pasado SqlCommand y devuelve los resultados a la aplicación cliente.
Nota:
Los objetos de base de datos administrados son más adecuados para procedimientos almacenados y UDF que usan lógica de procedimientos en lugar de lógica basada en conjuntos. La lógica de procedimientos implica trabajar con conjuntos de datos por fila o trabajar con datos escalares. Sin embargo, el método GetDiscontinuedProducts que acabamos de crear no implica ninguna lógica de procedimientos. Por lo tanto, idealmente se implementaría como un procedimiento almacenado de T-SQL. Se implementa como un procedimiento almacenado administrado para mostrar los pasos necesarios para crear e implementar procedimientos almacenados administrados.
Paso 4: Implementar el procedimiento almacenado administrado
Con este código completo, estamos listos para implementarlo en la base de datos Northwind. La implementación de un proyecto de SQL Server compila el código en un ensamblado, registra el ensamblado con la base de datos y crea los objetos correspondientes en la base de datos, vinculándolos a los métodos adecuados del ensamblado. El conjunto exacto de tareas realizadas por la opción Implementar se detalla más precisamente en el paso 13. Haga clic con el botón derecho en el nombre del proyecto ManagedDatabaseConstructs en el Explorador de soluciones y elija la opción Implementar. Sin embargo, se produce un error en la implementación con el siguiente error: sintaxis incorrecta cerca de "EXTERNAL". Es probable que tenga que establecer el nivel de compatibilidad de la base de datos actual en un valor superior para habilitar esta característica. Consulte la ayuda del procedimiento almacenado sp_dbcmptlevel.
Este mensaje de error se produce al intentar registrar el ensamblado con la base de datos Northwind. Para registrar un ensamblado con una base de datos de SQL Server 2005, el nivel de compatibilidad de la base de datos debe establecerse en 90. De manera predeterminada, las nuevas bases de datos de SQL Server 2005 tienen un nivel de compatibilidad de 90. Sin embargo, las bases de datos creadas con Microsoft SQL Server 2000 tienen un nivel de compatibilidad predeterminado de 80. Dado que la base de datos Northwind era inicialmente una base de datos de Microsoft SQL Server 2000, su nivel de compatibilidad se establece actualmente en 80 y, por lo tanto, debe aumentarse a 90 para registrar objetos de base de datos administrados.
Para actualizar el nivel de compatibilidad de la base de datos, abra una ventana Nueva consulta en Management Studio y escriba:
exec sp_dbcmptlevel 'Northwind', 90
Haga clic en el icono Ejecutar de la barra de herramientas para ejecutar la consulta anterior.
Figura 9: Actualizar el nivel de compatibilidad de la base de datos Northwind (Haga clic para ver la imagen de tamaño completo)
Después de actualizar el nivel de compatibilidad, vuelva a implementar el proyecto de SQL Server. Esta vez la implementación debe completarse sin errores.
Vuelva a SQL Server Management Studio, haga clic con el botón derecho en la base de datos Northwind en el Explorador de objetos y elija Actualizar. A continuación, explore en profundidad la carpeta Programmability y a continuación, expanda la carpeta Ensamblados. Como se muestra en la figura 10, la base de datos Northwind ahora incluye el ensamblado generado por el proyecto ManagedDatabaseConstructs.
Figura 10: El ensamblado ManagedDatabaseConstructs ahora está registrado con la base de datos Northwind
Expanda también la carpeta procedimientos almacenados. Allí verá un procedimiento almacenado denominado GetDiscontinuedProducts. Este procedimiento almacenado se ha creado mediante el proceso de implementación y apunta al método GetDiscontinuedProducts en el ensamblado ManagedDatabaseConstructs. Cuando se ejecuta el procedimiento almacenado GetDiscontinuedProducts, a su vez ejecuta el método GetDiscontinuedProducts. Dado que se trata de un procedimiento almacenado administrado, no se puede editar a través de Management Studio (por lo tanto, el icono de bloqueo junto al nombre del procedimiento almacenado).
Figura 11: El procedimiento almacenado GetDiscontinuedProducts aparece en la carpeta Procedimientos almacenados
Todavía hay un obstáculo más que tenemos que superar antes de poder llamar al procedimiento almacenado administrado: la base de datos está configurada para evitar la ejecución del código administrado. Para comprobarlo, abra una nueva ventana de consulta y ejecute el procedimiento almacenado GetDiscontinuedProducts. Recibirá el siguiente mensaje de error: la ejecución del código de usuario en .NET Framework está deshabilitada. Habilite la opción de configuración “habilitado para clr”.
Para examinar la información de configuración de la base de datos Northwind, escriba y ejecute el comando exec sp_configure en la ventana de consulta. Esto muestra que la configuración habilitada para clr está establecida actualmente en 0.
Figura 12: La configuración habilitada para clr está establecida actualmente en 0 (Haga clic para ver la imagen de tamaño completo)
Tenga en cuenta que cada valor de configuración de la figura 12 tiene cuatro valores enumerados con él: los valores mínimo y máximo y los valores de configuración y ejecución. Para actualizar el valor de configuración de la configuración habilitada para clr, ejecute el siguiente comando:
exec sp_configure 'clr enabled', 1
Si vuelve a ejecutar exec sp_configure verá que la instrucción anterior actualizó el valor de configuración de clr habilitada en 1, pero que el valor de ejecución todavía está establecido en 0. Para que este cambio de configuración tenga efecto, es necesario ejecutar el RECONFIGURE comando, que establecerá el valor de ejecución en el valor de configuración actual. Simplemente escriba RECONFIGURE en la ventana de consulta y haga clic en el icono Ejecutar de la barra de herramientas. Si se ejecuta exec sp_configure ahora debería ver un valor de 1 para la configuración de clr habilitada para valores de configuración y ejecución.
Una vez completada la configuración habilitada para clr, estamos listos para ejecutar el procedimiento almacenado administrado GetDiscontinuedProducts. En la ventana de consulta, escriba y ejecute el comando execGetDiscontinuedProducts. Invocar el procedimiento almacenado hace que se ejecute el código administrado correspondiente en el método GetDiscontinuedProducts. Este código emite una consulta SELECT para devolver todos los productos que se descontinuan y devuelve estos datos a la aplicación que realiza la llamada, que es SQL Server Management Studio en esta instancia. Management Studio recibe estos resultados y los muestra en la ventana Resultados.
Figura 13: El procedimiento almacenado GetDiscontinuedProducts devuelve todos los productos descontinuados (Haga clic para ver la imagen de tamaño completo)
Paso 5: Crear procedimientos almacenados administrados que acepten parámetros de entrada
Muchas de las consultas y procedimientos almacenados que hemos creado en estos tutoriales han usado parámetros. Por ejemplo, en el tutorial Creating New Stored Procedures for the Typed DataSet s TableAdapters creamos un procedimiento almacenado denominado GetProductsByCategoryID que aceptó un parámetro de entrada denominado @CategoryID. A continuación, el procedimiento almacenado devolvió todos los productos cuyo campo CategoryID coincida con el valor del parámetro proporcionado @CategoryID.
Para crear un procedimiento almacenado administrado que acepte parámetros de entrada, simplemente especifique esos parámetros en la definición del método. Para ilustrar esto, vamos a agregar otro procedimiento almacenado administrado al proyecto ManagedDatabaseConstructs denominado GetProductsWithPriceLessThan. Este procedimiento almacenado administrado aceptará un parámetro de entrada que especifique un precio y devolverá todos los productos cuyo campo UnitPrice sea menor que el valor del parámetro.
Para agregar un nuevo procedimiento almacenado al proyecto, haga clic con el botón derecho en el nombre del proyecto ManagedDatabaseConstructs y elija agregar un nuevo procedimiento almacenado. Asigne al archivo el nombre GetProductsWithPriceLessThan.cs. Como vimos en el paso 3, esto creará un nuevo archivo de clase de C# con un método denominado GetProductsWithPriceLessThan colocado dentro de partial clase StoredProcedures.
Actualice la definición del método GetProductsWithPriceLessThan para que acepte un parámetro de entrada SqlMoney denominado price y escriba el código para ejecutar y devolver los resultados de la consulta:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE UnitPrice < @MaxPrice";
myCommand.Parameters.AddWithValue("@MaxPrice", price);
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
}
La definición y el código del método GetProductsWithPriceLessThan se asemejan mucho a la definición y el código del método GetDiscontinuedProducts creado en el paso 3. Las únicas diferencias son que el método GetProductsWithPriceLessThan acepta como parámetro de entrada (price), la consulta s SqlCommand incluye un parámetro (@MaxPrice) y se agrega un parámetro a la colección SqlCommand s Parameters y se le asigna el valor de la variable price.
Después de agregar este código, vuelva a implementar el proyecto de SQL Server. A continuación, vuelva a SQL Server Management Studio y actualice la carpeta procedimientos almacenados. Debería ver una nueva entrada, GetProductsWithPriceLessThan. En una ventana de consulta, escriba y ejecute el comando exec GetProductsWithPriceLessThan 25, que enumerará todos los productos inferiores a $25, como se muestra en la figura 14.
Figura 14: Se muestran los productos inferiores a 25 USD (Haga clic para ver la imagen de tamaño completo)
Paso 6: Llamar al procedimiento almacenado administrado desde la capa de acceso a datos
En este momento hemos agregado los procedimientos almacenados administrados GetDiscontinuedProducts y GetProductsWithPriceLessThan al proyecto ManagedDatabaseConstructs y hemos registrado con la base de datos de SQL Server Northwind. También se han invocado estos procedimientos almacenados administrados desde SQL Server Management Studio (vea la figura 13 y 14). Para que nuestra aplicación de ASP.NET use estos procedimientos almacenados administrados, sin embargo, es necesario agregarlos a las capas de acceso a datos y lógica de negocios en la arquitectura. En este paso agregaremos dos nuevos métodos al ProductsTableAdapter en el NorthwindWithSprocs DataSet con tipo, que se creó inicialmente en el Crear nuevos procedimientos almacenados para el tutorial TableAdapters de TableAdapters de Typed DataSet. En el paso 7 agregaremos los métodos correspondientes al BLL.
Abra el conjunto de datos con tipo NorthwindWithSprocs en Visual Studio y empiece agregando un nuevo métodoProductsTableAdapter denominado GetDiscontinuedProducts. Para agregar un nuevo método a TableAdapter, haga clic con el botón derecho en el nombre de TableAdapter en el Diseñador y elija la opción Agregar consulta en el menú contextual.
Nota:
Dado que hemos movido la base de datos Northwind de la carpeta App_Data a la instancia de base de datos de SQL Server 2005 Express Edition, es imperativo que se actualice la cadena de conexión correspondiente en Web.config para reflejar este cambio. En el paso 2 hemos analizado la actualización del valor NORTHWNDConnectionString en Web.config. Si ha olvidado realizar esta actualización, verá el mensaje de error No se pudo agregar la consulta. No se puede encontrar la conexión NORTHWNDConnectionString para el objeto Web.config en un cuadro de diálogo al intentar agregar un nuevo método a TableAdapter. Para resolver este error, haga clic en Aceptar y a continuación, vaya a Web.config y actualice el valor NORTHWNDConnectionString como se describe en el paso 2. A continuación, intente volver a agregar el método a TableAdapter. Esta vez debería funcionar sin error.
Al agregar un nuevo método, se inicia el Asistente para configuración de consultas TableAdapter, que hemos usado muchas veces en los tutoriales anteriores. El primer paso nos pide que especifiquemos cómo TableAdapter debe tener acceso a la base de datos: a través de una instrucción SQL ad hoc o a través de un procedimiento almacenado nuevo o existente. Puesto que ya hemos creado y registrado el procedimiento almacenado administrado GetDiscontinuedProducts con la base de datos, elija la opción Usar procedimiento almacenado existente y presione Siguiente.
Figura 15: Elija la opción Usar procedimiento almacenado existente (Haga clic para ver la imagen de tamaño completo)
La siguiente pantalla nos pide el procedimiento almacenado que invocará el método. Elija el procedimiento almacenado administrado GetDiscontinuedProducts en la lista desplegable y presione Siguiente.
Figura 16: Seleccionar el procedimiento almacenado administradoGetDiscontinuedProducts (Haga clic para ver la imagen de tamaño completo)
A continuación, se le pide que especifique si el procedimiento almacenado devuelve filas, un valor único o nada. Dado que GetDiscontinuedProducts devuelve el conjunto de filas de producto discontinuas, elija la primera opción (datos tabulares) y haga clic en Siguiente.
Figura 17: Seleccionar la opción datos tabulares (Haga clic para ver la imagen de tamaño completo)
La pantalla del asistente final nos permite especificar los patrones de acceso a datos usados y los nombres de los métodos resultantes. Deje activadas ambas casillas y asigne el nombre a los métodos FillByDiscontinued y GetDiscontinuedProducts. Haga clic en Finalizar para completar el asistente.
Figura 18: Cambie el nombre de los métodos a FillByDiscontinued y GetDiscontinuedProducts (haga clic aquí para ver la imagen a tamaño completo)
Repita estos pasos para crear métodos denominados FillByPriceLessThan y GetProductsWithPriceLessThan en ProductsTableAdapter para el procedimiento almacenado administrado GetProductsWithPriceLessThan.
En la figura 19 se muestra una captura de pantalla del Diseñador de conjuntos de datos después de agregar los métodos ProductsTableAdapter para los procedimientos almacenados administrados GetDiscontinuedProducts y GetProductsWithPriceLessThan.
Figura 19: ProductsTableAdapter Incluye los nuevos métodos agregados en este paso (Haga clic para ver la imagen de tamaño completo)
Paso 7: Agregar métodos correspondientes a la capa de lógica de negocios
Ahora que hemos actualizado la capa de acceso a datos para incluir métodos para llamar a los procedimientos almacenados administrados agregados en los pasos 4 y 5, es necesario agregar los métodos correspondientes al nivel de lógica de negocios. Agregue los dos métodos siguientes a la clase ProductsBLLWithSprocs:
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable
GetProductsWithPriceLessThan(decimal priceLessThan)
{
return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}
Ambos métodos simplemente llaman al método DAL correspondiente y devuelven la instancia ProductsDataTable. El marcado DataObjectMethodAttribute anterior a cada método hace que estos métodos se incluyan en la lista desplegable de la pestaña SELECT del Asistente para configurar orígenes de datos de ObjectDataSource.
Paso 8: invocar los procedimientos almacenados administrados desde la capa de presentación
Con las capas de lógica de negocios y acceso a datos aumentadas para incluir compatibilidad para llamar a los procedimientos almacenados administrados GetDiscontinuedProducts y GetProductsWithPriceLessThan, ahora podemos mostrar estos resultados de procedimientos almacenados a través de una página de ASP.NET.
Abra la página ManagedFunctionsAndSprocs.aspx en la carpeta AdvancedDAL y, desde el cuadro de herramientas, arrastre un Control GridView al Diseñador. Establezca la propiedad ID de GridView en DiscontinuedProducts y, desde su etiqueta inteligente, vincule a un nuevo ObjectDataSource denominado DiscontinuedProductsDataSource. Configure ObjectDataSource para extraer sus datos de la clase ProductsBLLWithSprocs del método GetDiscontinuedProducts.
Figura 20: Configurar ObjectDataSource para usar la clase ProductsBLLWithSprocs (Haga clic para ver la imagen de tamaño completo)
Figura 21: Elegir el método GetDiscontinuedProducts de la lista desplegable en la pestaña SELECT (haga clic para ver la imagen de tamaño completo)
Puesto que esta cuadrícula se usará para mostrar solo la información del producto, establezca las listas desplegables en las pestañas UPDATE, INSERT y DELETE en (Ninguno) y a continuación, haga clic en Finalizar.
Al completar el asistente, Visual Studio agregará automáticamente un BoundField o CheckBoxField para cada campo de datos de ProductsDataTable. Dedique un momento a quitar todos estos campos excepto para ProductName y Discontinued, en cuyo punto el marcado declarativo GridView y ObjectDataSource deben tener un aspecto similar al siguiente:
<asp:GridView ID="DiscontinuedProducts" runat="server"
AutoGenerateColumns="False" DataKeyNames="ProductID"
DataSourceID="DiscontinuedProductsDataSource">
<Columns>
<asp:BoundField DataField="ProductName" HeaderText="ProductName"
SortExpression="ProductName" />
<asp:CheckBoxField DataField="Discontinued"
HeaderText="Discontinued"
SortExpression="Discontinued" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>
Dedique un momento a ver esta página a través de un explorador. Cuando se visita la página, ObjectDataSource llama al método GetDiscontinuedProducts de la clase ProductsBLLWithSprocs. Como vimos en el paso 7, este método llama al método GetDiscontinuedProducts de la clase ProductsDataTable DAL, que invoca el procedimiento almacenado GetDiscontinuedProducts. Este procedimiento almacenado es un procedimiento almacenado administrado y ejecuta el código que creamos en el paso 3, devolviendo los productos discontinuos.
Los resultados devueltos por el procedimiento almacenado administrado se empaquetan en un ProductsDataTable por la DAL y, a continuación, se devuelven a la BLL, que luego los devuelve a la capa de presentación donde están enlazados a GridView y se muestran. Como se esperaba, la cuadrícula enumera los productos que se han dejado de usar.
Figura 22: Los productos descontinuados aparecen en la lista (haga clic para ver la imagende tamaño completo)
Para seguir practicando, agregue un TextBox y otro GridView a la página. Haga que gridView muestre los productos inferiores a la cantidad especificada en textBox llamando al método GetProductsWithPriceLessThan de la clase ProductsBLLWithSprocs.
Paso 9: Crear y llamar a UDF de T-SQL
Las funciones definidas por el usuario o las UDF son objetos de base de datos que imitan estrechamente la semántica de las funciones en lenguajes de programación. Al igual que una función en C#, las UDF pueden incluir un número variable de parámetros de entrada y devolver un valor de un tipo determinado. Una UDF puede devolver datos escalares( una cadena, un entero, etc.) o datos tabulares. Echemos un vistazo rápido a ambos tipos de UDF, empezando por una UDF que devuelve un tipo de datos escalar.
La siguiente UDF calcula el valor estimado del inventario de un producto determinado. Para ello, toma tres parámetros de entrada : los valores UnitPrice, UnitsInStock, y Discontinued de un producto determinado y devuelve un valor de tipo money. Calcula el valor estimado del inventario multiplicandoUnitPrice por el UnitsInStock. En el caso de los elementos discontinuos, este valor se reduce a la mitad.
CREATE FUNCTION udf_ComputeInventoryValue
(
@UnitPrice money,
@UnitsInStock smallint,
@Discontinued bit
)
RETURNS money
AS
BEGIN
DECLARE @Value decimal
SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
IF @Discontinued = 1
SET @Value = @Value * 0.5
RETURN @Value
END
Una vez que se ha agregado esta UDF a la base de datos, se puede encontrar a través de Management Studio expandiendo la carpeta Programación, después Funciones y a continuación, Funciones escalares y valores escalares. Se puede usar en una consulta SELECT como esta:
SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
(UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC
He agregado la udf_ComputeInventoryValue UDF a la base de datos Northwind; En la figura 23 se muestra la salida de la consulta anterior SELECT cuando se ve a través de Management Studio. Tenga en cuenta también que la UDF aparece en la carpeta Funciones escalares-valor en el Explorador de objetos.
Figura 23: Cada valor de inventario de productos aparece en la lista (Haga clic para ver la imagen de tamaño completo)
Las UDF también pueden devolver datos tabulares. Por ejemplo, podemos crear una UDF que devuelva productos que pertenecen a una categoría determinada:
CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(
@CategoryID int
)
RETURNS TABLE
AS
RETURN
(
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
)
La udf_GetProductsByCategoryID UDF acepta un parámetro de entrada @CategoryID y devuelve los resultados de la consulta especificada SELECT. Una vez creada, se puede hacer referencia a esta UDF en la cláusula FROM (o JOIN) de una cláusulaSELECT. En el ejemplo siguiente se devolverían los valores ProductID, ProductName, y CategoryID para cada una de las bebidas.
SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)
He agregado la udf_GetProductsByCategoryID UDF a la base de datos Northwind; En la figura 24 se muestra la salida de la consulta anterior SELECT cuando se ve a través de Management Studio. Las UDF que devuelven datos tabulares se pueden encontrar en la carpeta Funciones Table-value del Explorador de objetos.
Figura 24: los ProductID, ProductName y CategoryID se enumeran para cada bebida (Haga clic para ver imagen de tamaño completo)
Nota:
Para obtener más información sobre cómo crear y usar UDF, consulte Introducción a funciones definidas por el usuario. Consulte también Ventajas e inconvenientes de las funciones definidas por el usuario.
Paso 10: Crear una UDF administrada
Los UDF udf_ComputeInventoryValue y udf_GetProductsByCategoryID creados en los ejemplos anteriores son objetos de base de datos T-SQL. SQL Server 2005 también admite UDF administradas, que se pueden agregar al proyecto de ManagedDatabaseConstructs igual que los procedimientos almacenados administrados de los pasos 3 y 5. Para este paso, vamos a implementar la udf_ComputeInventoryValue UDF en código administrado.
Para agregar una UDF administrada al proyecto ManagedDatabaseConstructs, haga clic con el botón derecho en el nombre del proyecto en el Explorador de soluciones y elija Agregar un nuevo elemento. Seleccione la plantilla definida por el usuario en el cuadro de diálogo Agregar nuevo elemento y asigne al nuevo archivo UDF el nombre udf_ComputeInventoryValue_Managed.cs.
Figura 25: Agregar una nueva UDF administrada al proyecto de ManagedDatabaseConstructs (Haga clic para ver la imagen de tamaño completo)
La plantilla Función definida por el usuario crea una clase partial denominada UserDefinedFunctions con un método cuyo nombre es el mismo que el nombre del archivo de clase (udf_ComputeInventoryValue_Managed, en esta instancia). Este método está decorado mediante el SqlFunction atributo, que marca el método como una UDF administrada.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString udf_ComputeInventoryValue_Managed()
{
// Put your code here
return new SqlString("Hello");
}
};
El método udf_ComputeInventoryValue devuelve actualmente un SqlString objeto y no acepta ningún parámetro de entrada. Es necesario actualizar la definición del método para que acepte tres parámetros de entrada ( UnitPrice, UnitsInStock, y Discontinued ) y devuelva un objeto SqlMoney. La lógica para calcular el valor de inventario es idéntica a la de T-SQL UDF udf_ComputeInventoryValue.
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
(SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
SqlMoney inventoryValue = 0;
if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
{
inventoryValue = UnitPrice * UnitsInStock;
if (Discontinued == true)
inventoryValue = inventoryValue * new SqlMoney(0.5);
}
return inventoryValue;
}
Tenga en cuenta que los parámetros de entrada del método UDF son de sus tipos SQL correspondientes: SqlMoney para el campo de UnitPrice, SqlInt16 para UnitsInStock y SqlBoolean para Discontinued. Estos tipos de datos reflejan los tipos definidos en la tabla de Products : la columna UnitPrice es de tipo money, la columna UnitsInStock de tipo smallinty la columna Discontinued de tipo bit.
El código comienza creando una instancia SqlMoney denominada inventoryValue que tiene asignado un valor de 0. La tabla Products permite valores de base de datos NULL en las columnas UnitsInPrice y UnitsInStock. Por lo tanto, primero debemos comprobar si estos valores contienen NULL, lo que hacemos a través de la propiedad IsNulldel objetoSqlMoney. Si tanto UnitPrice como UnitsInStock contienen valores que no NULL, calculamos el inventoryValue para que sea el producto de los dos. A continuación, si Discontinued es true, se reduce el valor.
Nota:
El objeto SqlMoney solo permite multiplicar dos instancias SqlMoney juntas. No permite que una instancia SqlMoney se multiplique por un número de punto flotante literal. Por lo tanto, para reducir inventoryValue la multiplicamos por una nueva instancia de SqlMoney que tenga el valor 0,5.
Paso 11: Implementación de la UDF administrada
Ahora que se ha creado la UDF administrada, estamos listos para implementarla en la base de datos Northwind. Como vimos en el paso 4, los objetos administrados en un proyecto de SQL Server se implementan haciendo clic con el botón derecho en el nombre del proyecto en el Explorador de soluciones y seleccionando la opción Implementar en el menú contextual.
Una vez implementado el proyecto, vuelva a SQL Server Management Studio y actualice la carpeta Functions con valores escalares. Ahora debería ver dos entradas:
dbo.udf_ComputeInventoryValue: la UDF de T-SQL creada en el paso 9 ydbo.udf ComputeInventoryValue_Managed: la UDF administrada creada en el paso 10 que se acaba de implementar.
Para probar esta UDF administrada, ejecute la siguiente consulta desde Management Studio:
SELECT ProductID, ProductName,
dbo.udf_ComputeInventoryValue_Managed(
UnitPrice,
UnitsInStock,
Discontinued
) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC
Este comando usa el UDF administrado udf ComputeInventoryValue_Managed en lugar de la UDF de T-SQL udf_ComputeInventoryValue, pero la salida es la misma. Consulte la figura 23 para ver una captura de pantalla de la salida de UDF.
Paso 12: Depuración de los objetos de base de datos administrados
En el tutorial Depuración de procedimientos almacenados se describen las tres opciones para depurar SQL Server a través de Visual Studio: Depuración directa de bases de datos, Depuración de aplicaciones y Depuración desde un proyecto de SQL Server. Los objetos de base de datos administrados no se pueden depurar a través de la depuración de Direct Database, pero se pueden depurar desde una aplicación cliente y directamente desde el proyecto de SQL Server. Sin embargo, para que la depuración funcione, la base de datos de SQL Server 2005 debe permitir la depuración de SQL/CLR. Recuerde que cuando creamos por primera vez el proyecto de ManagedDatabaseConstructs Visual Studio nos preguntó si queríamos habilitar la depuración de SQL/CLR (consulte la figura 6 del paso 2). Esta configuración se puede modificar haciendo clic con el botón derecho en la base de datos desde la ventana Explorador de servidores.
Figura 26: Asegurarse de que la base de datos permite la depuración de SQL/CLR
Imagine que queríamos depurar el procedimiento almacenado administrado GetProductsWithPriceLessThan. Comenzaríamos estableciendo un punto de interrupción dentro del código del método GetProductsWithPriceLessThan.
Figura 27: Establecer un punto de interrupción en el métodoGetProductsWithPriceLessThan ( Haga clic para ver la imagen de tamaño completo)
Echemos un vistazo primero a la depuración de los objetos de base de datos administrados desde el proyecto de SQL Server. Dado que nuestra solución incluye dos proyectos , el proyecto de SQL Server ManagedDatabaseConstructs junto con nuestro sitio web, para depurar desde el proyecto de SQL Server, es necesario indicar a Visual Studio que inicie el proyecto de SQL Server ManagedDatabaseConstructs al iniciar la depuración. Haga clic con el botón derecho en el proyecto ManagedDatabaseConstructs en el Explorador de soluciones y elija la opción Establecer como proyecto de inicio en el menú contextual.
Cuando el proyecto ManagedDatabaseConstructs se inicia desde el depurador, ejecuta las instrucciones SQL en el archivo Test.sql, que se encuentra en la carpeta Test Scripts. Por ejemplo, para probar el procedimiento almacenado administrado GetProductsWithPriceLessThan, reemplace el contenido del archivo Test.sql existente por la siguiente instrucción, que invoca el procedimiento almacenado administrado GetProductsWithPriceLessThan que pasa el valor @CategoryID de 14.95:
exec GetProductsWithPriceLessThan 14.95
Una vez que haya escrito el script anterior en Test.sql, inicie la depuración; para ello, vaya al menú Depurar y elija Iniciar depuración o presione F5 o el icono de reproducción verde en la barra de herramientas. Esto compilará los proyectos dentro de la solución, implementará los objetos de base de datos administrados en la base de datos Northwind y a continuación, ejecutará el script Test.sql. En este punto, se alcanzará el punto de interrupción y podemos recorrer el métodoGetProductsWithPriceLessThan, examinar los valores de los parámetros de entrada, etc.
Figura 28: El punto de interrupción en el método GetProductsWithPriceLessThan se alcanzó (Haga clic para ver la imagen de tamaño completo)
Para que un objeto de base de datos SQL se depure a través de una aplicación cliente, es imperativo que la base de datos esté configurada para admitir la depuración de aplicaciones. Haga clic con el botón derecho en la base de datos en el Explorador de servidores y asegúrese de que la opción Depuración de aplicaciones esté comprobada. Además, es necesario configurar la aplicación ASP.NET para que se integre con SQL Debugger y para deshabilitar la agrupación de conexiones. Estos pasos se trataron en detalle en el paso 2 del tutorial Depuración de procedimientos almacenados.
Una vez que haya configurado la aplicación y la base de datos de ASP.NET, establezca el sitio web de ASP.NET como proyecto de inicio e inicie la depuración. Si visita una página que llama a uno de los objetos administrados que tiene un punto de interrupción, la aplicación se detendrá y el control se volverá al depurador, donde puede recorrer el código como se muestra en la figura 28.
Paso 13: Compilar e implementar objetos de base de datos administrados manualmente
Los proyectos de SQL Server facilitan la creación, compilación e implementación de objetos de base de datos administrados. Desafortunadamente, los proyectos de SQL Server solo están disponibles en las ediciones Professional y Team Systems de Visual Studio. Si usa Visual Web Developer o Standard Edition de Visual Studio y quiere usar objetos de base de datos administrados, deberá crearlos e implementarlos manualmente. Esto implica cuatro pasos:
- Cree un archivo que contenga el código fuente para el objeto de base de datos administrada,
- Compile el objeto en un ensamblado,
- Registrar el ensamblado con la base de datos de SQL Server 2005 y
- Cree un objeto de base de datos en SQL Server que apunte al método adecuado en el ensamblado.
Para ilustrar estas tareas, vamos a crear un nuevo procedimiento almacenado administrado que devuelva esos productos cuyo valor UnitPrice sea mayor que un valor especificado. Cree un nuevo archivo en el equipo denominado GetProductsWithPriceGreaterThan.cs y escriba el código siguiente en el archivo (puede usar Visual Studio, Bloc de notas o cualquier editor de texto para ello):
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceGreaterThan(SqlMoney price)
{
// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE UnitPrice > @MinPrice";
myCommand.Parameters.AddWithValue("@MinPrice", price);
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
}
};
Este código es casi idéntico al del método GetProductsWithPriceLessThan creado en el paso 5. Las únicas diferencias son los nombres de método, la cláusula WHERE y el nombre del parámetro usado en la consulta. De nuevo en el método GetProductsWithPriceLessThan, la cláusula WHERE lee: WHERE UnitPrice < @MaxPrice. Aquí, en GetProductsWithPriceGreaterThan, usamos: WHERE UnitPrice > @MinPrice .
Ahora es necesario compilar esta clase en un ensamblado. En la línea de comandos, vaya al directorio donde guardó el archivo GetProductsWithPriceGreaterThan.cs y use el compilador de C# (csc.exe) para compilar el archivo de clase en un ensamblado:
csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs
Si la carpeta que contiene csc.exe no en el sistema s PATH, tendrá que hacer referencia completamente a su ruta de acceso, %WINDOWS%\Microsoft.NET\Framework\version\, de la siguiente manera:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs
Figura 29: Compilar GetProductsWithPriceGreaterThan.cs en un ensamblado (Haga clic para ver la imagen de tamaño completo)
La marca /t especifica que el archivo de clase de C# debe compilarse en un archivo DLL (en lugar de en un archivo ejecutable). La marca /out especifica el nombre del ensamblado resultante.
Nota:
En lugar de compilar el archivo GetProductsWithPriceGreaterThan.cs de clase desde la línea de comandos, también puede usar Visual C# Express Edition o crear un proyecto de biblioteca de clases independiente en Visual Studio Standard Edition. S ren Jacob Lauritsen ha proporcionado un proyecto de Visual C# Express Edition con código para el procedimiento almacenado GetProductsWithPriceGreaterThan y los dos procedimientos almacenados administrados y UDF creados en los pasos 3, 5 y 10. El proyecto S ren también incluye los comandos T-SQL necesarios para agregar los objetos de base de datos correspondientes.
Con el código compilado en un ensamblado, estamos listos para registrar el ensamblado en la base de datos de SQL Server 2005. Esto se puede realizar a través de T-SQL, mediante el comando CREATE ASSEMBLY, o a través de SQL Server Management Studio. Vamos a centrarnos en el uso de Management Studio.
En Management Studio, expanda la carpeta Programmability de la base de datos Northwind. Una de sus subcarpetas es Ensamblados. Para agregar manualmente un nuevo ensamblado a la base de datos, haga clic con el botón derecho en la carpeta Ensamblados y elija Nuevo ensamblado en el menú contextual. Esto muestra el cuadro de diálogo Nuevo ensamblado (vea la figura 30). Haga clic en el botón Examinar, seleccione el ensamblado ManuallyCreatedDBObjects.dll que acabamos de compilar y a continuación, haga clic en Aceptar para agregar el ensamblado a la base de datos. No debería ver el ensambladoManuallyCreatedDBObjects.dll en el Explorador de objetos.
Figura 30: Agregar el ensamblado de ManuallyCreatedDBObjects.dll a la base de datos (Haga clic para ver la imagen de tamaño completo)
Figura 31: El ManuallyCreatedDBObjects.dll se muestra en el Explorador de objetos
Aunque hemos agregado el ensamblado a la base de datos Northwind, todavía tenemos que asociar un procedimiento almacenado con el método GetProductsWithPriceGreaterThan en el ensamblado. Para ello, abra una nueva ventana de consulta y ejecute el siguiente script:
CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan]
(
@price money
)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan]
GO
Esto crea un nuevo procedimiento almacenado en la base de datos Northwind denominada GetProductsWithPriceGreaterThan y lo asocia al método administrado GetProductsWithPriceGreaterThan (que se encuentra en la clase StoredProcedures, que se encuentra en el ensamblado ManuallyCreatedDBObjects).
Después de ejecutar el script anterior, actualice la carpeta Procedimientos almacenados en el Explorador de objetos. Debería ver una nueva entrada de procedimiento almacenado, GetProductsWithPriceGreaterThan que tiene un icono de bloqueo junto a él. Para probar este procedimiento almacenado, escriba y ejecute el siguiente script en la ventana de consulta:
exec GetProductsWithPriceGreaterThan 24.95
Como se muestra en la figura 32, el comando anterior muestra información para esos productos con un UnitPrice valor superior a 24,95 USD.
Figura 32: el ManuallyCreatedDBObjects.dll aparece en el Explorador de objetos (Haga clic para ver la imagen de tamaño completo)
Resumen
Microsoft SQL Server 2005 proporciona integración con Common Language Runtime (CLR), que permite crear objetos de base de datos mediante código administrado. Anteriormente, estos objetos de base de datos solo se podían crear con T-SQL, pero ahora podemos crear estos objetos mediante lenguajes de programación .NET como C#. En este tutorial hemos creado dos procedimientos almacenados administrados y una función administrada definida por el usuario.
El tipo de proyecto de SQL Server de Visual Studio facilita la creación, compilación e implementación de objetos de base de datos administrados. Además, ofrece compatibilidad con depuración enriquecida. Sin embargo, los tipos de proyecto de SQL Server solo están disponibles en las ediciones Professional y Team Systems de Visual Studio. Para aquellos que usan Visual Web Developer o standard Edition de Visual Studio, los pasos de creación, compilación e implementación se deben realizar manualmente, como vimos en el paso 13.
¡Feliz programación!
Lecturas adicionales
Para obtener más información sobre los temas tratados en este tutorial, consulte los siguientes recursos:
- Ventajas y desventajas de las funciones definidas por el usuario
- Creación de objetos SQL Server 2005 en código administrado
- Cómo: Crear y ejecutar un procedimiento almacenado de SQL Server CLR
- Cómo: Crear y ejecutar una función definida por el usuario de CLR SQL Server
- Cómo: Editar el script
Test.sqlpara ejecutar objetos SQL - Introducción a las funciones definidas por el usuario
- Código administrado y SQL Server 2005 (vídeo)
- Referencia de Transact-SQL
- Tutorial: Creación de un procedimiento almacenado en código administrado
Acerca del autor
Scott Mitchell, autor de siete libros de ASP/ASP.NET y fundador de 4GuysFromRolla.com, ha estado trabajando con tecnologías web de Microsoft desde 1998. Scott trabaja como consultor independiente, entrenador y escritor. Su último libro es Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Puede ponerse en contacto con él a través de mitchell@4GuysFromRolla.com. o a través de su blog, que se puede encontrar en http://ScottOnWriting.NET.
Agradecimientos especiales a
Muchos revisores han evaluado esta serie de tutoriales. El revisor principal de este tutorial fue S ren Jacob Lauritsen. Además de revisar este artículo, S ren también ha creado el proyecto visual C# Express Edition incluido en esta descarga de este artículo para compilar manualmente los objetos de base de datos administrados. ¿Le interesa revisar mis próximos artículos de MSDN? Si es así, escríbame a mitchell@4GuysFromRolla.com.
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de