Crear procedimientos almacenados para los TableAdapters del conjunto de datos con tipo (C#)

de Scott Mitchell

Descargar PDF

En tutoriales anteriores hemos creado instrucciones SQL en nuestro código y hemos pasado las instrucciones a la base de datos que se van a ejecutar. Un enfoque alternativo consiste en usar procedimientos almacenados, donde las instrucciones SQL están predefinidas en la base de datos. En este tutorial, aprenderá a hacer que el Asistente para TableAdapter genere nuevos procedimientos almacenados para nosotros.

Introducción

La capa de acceso a datos (DAL) de estos tutoriales utiliza Typed DataSets. Como se describe en el tutorial Creación de una capa de acceso a datos , Los conjuntos de datos tipados constan de DataTables y TableAdapters fuertemente tipados. Las DataTables representan las entidades lógicas del sistema, mientras que los TableAdapters interactúan con la base de datos subyacente para realizar el trabajo de acceso a los datos. Esto incluye rellenar dataTables con datos, ejecutar consultas que devuelven datos escalares e insertar, actualizar y eliminar registros de la base de datos.

Los comandos SQL ejecutados por TableAdapters pueden ser instrucciones SQL ad hoc, como SELECT columnList FROM TableName, o procedimientos almacenados. Los TableAdapters de nuestra arquitectura usan instrucciones SQL ad hoc. Sin embargo, muchos desarrolladores y administradores de bases de datos prefieren procedimientos almacenados en instrucciones SQL ad hoc por motivos de seguridad, mantenimiento y portabilidad. Otros prefieren apasionadamente las instrucciones SQL ad hoc por su flexibilidad. En mi propio trabajo favorezco los procedimientos almacenados sobre las instrucciones SQL ad hoc, pero elegí usar instrucciones SQL ad hoc para simplificar los tutoriales anteriores.

Al definir un TableAdapter o agregar nuevos métodos, el asistente de TableAdapter facilita la creación de nuevos procedimientos almacenados o el uso de procedimientos almacenados existentes como para usar instrucciones SQL ad hoc. En este tutorial examinaremos cómo hacer que el asistente de TableAdapter genere automáticamente procedimientos almacenados. En el siguiente tutorial veremos cómo configurar los métodos de TableAdapter para usar procedimientos almacenados existentes o creados manualmente.

Nota:

Consulte la entrada de blog de Rob Howard Don't Use Stored Procedures Yet? y la entrada de blog de Frans Bouma Stored Procedures are Bad, M Kay? para un animado debate sobre las ventajas y desventajas de los procedimientos almacenados y SQL ad hoc.

Conceptos básicos de procedimientos almacenados

Las funciones son una construcción común a todos los lenguajes de programación. Una función es una colección de instrucciones que se ejecutan cuando se llama a la función. Functions puede aceptar parámetros de entrada y, opcionalmente, devolver un valor. Los procedimientos almacenados son construcciones de base de datos que comparten muchas similitudes con funciones en lenguajes de programación. Un procedimiento almacenado se compone de un conjunto de instrucciones T-SQL que se ejecutan cuando se llama al procedimiento almacenado. Un procedimiento almacenado puede aceptar cero a muchos parámetros de entrada y puede devolver valores escalares, parámetros de salida o, normalmente, conjuntos de resultados de SELECT consultas.

Nota:

Los procedimientos almacenados suelen denominarse sprocs o SPs.

Los procedimientos almacenados se crean mediante la CREATE PROCEDURE instrucción T-SQL. Por ejemplo, el siguiente script T-SQL crea un procedimiento almacenado denominado GetProductsByCategoryID que toma un único parámetro denominado @CategoryID y devuelve los ProductIDcampos , ProductName, UnitPricey Discontinued de esas columnas de la Products tabla que tienen un valor coincidente CategoryID :

CREATE PROCEDURE GetProductsByCategoryID
(
    @CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID

Una vez creado este procedimiento almacenado, se puede llamar mediante la sintaxis siguiente:

EXEC GetProductsByCategory categoryID

Nota:

En el siguiente tutorial examinaremos la creación de procedimientos almacenados a través del IDE de Visual Studio. Sin embargo, en este tutorial vamos a permitir que el Asistente para TableAdapter genere automáticamente los procedimientos almacenados para nosotros.

Además de simplemente devolver datos, los procedimientos almacenados a menudo se usan para realizar varios comandos de base de datos dentro del ámbito de una sola transacción. Un procedimiento almacenado denominado DeleteCategory, por ejemplo, podría tomar un @CategoryID parámetro y realizar dos DELETE instrucciones: en primer lugar, una para eliminar los productos relacionados y otro para eliminar la categoría especificada. Varias instrucciones dentro de un procedimiento almacenado no se encapsulan automáticamente dentro de una transacción. Es necesario emitir comandos T-SQL adicionales para asegurarse de que el procedimiento almacenado y sus múltiples comandos se traten como una operación atómica. Veremos cómo encapsular los comandos de un procedimiento almacenado dentro del ámbito de una transacción en el tutorial posterior.

Cuando se usan procedimientos almacenados dentro de una arquitectura, los métodos de capa de acceso a datos invocan un procedimiento almacenado determinado en lugar de emitir una instrucción SQL ad hoc. Esto centraliza la ubicación de las instrucciones SQL ejecutadas (en la base de datos) en lugar de tenerla definida dentro de la arquitectura de la aplicación. Esta centralización posiblemente facilita la búsqueda, el análisis y la optimización de las consultas y proporciona una imagen mucho más clara sobre dónde y cómo se usa la base de datos.

Para obtener más información sobre los aspectos básicos del procedimiento almacenado, consulte los recursos de la sección Lectura adicional al final de este tutorial.

Paso 1: Crear las páginas web de escenarios avanzados de capa de acceso a datos

Antes de comenzar nuestra discusión sobre la creación de un DAL mediante procedimientos almacenados, primero dediquemos un momento a crear las páginas de ASP.NET en nuestro proyecto de sitio web que necesitaremos para esto y los siguientes tutoriales. Para empezar, agregue una nueva carpeta denominada AdvancedDAL. Después, agregue las siguientes páginas ASP.NET a esa carpeta, asegurándose de asociar cada página a la página maestra Site.master:

• Default.aspx
• NewSprocs.aspx
• ExistingSprocs.aspx
• JOINs.aspx
• AddingColumns.aspx
• ComputedColumns.aspx
• EncryptingConfigSections.aspx
• ManagedFunctionsAndSprocs.aspx

Figura 1: Adición de las páginas de ASP.NET para los tutoriales de escenarios de capa de acceso a datos avanzados

Igual que en las otras carpetas, Default.aspx en la carpeta AdvancedDAL enumerará los tutoriales en su sección. Recuerde que el control de usuario SectionLevelTutorialListing.ascx proporciona esta funcionalidad. Por lo tanto, agregue este control de usuario a Default.aspx arrastrándolo desde el Explorador de soluciones a la vista Diseño de la página.

Figura 2: Agregar el control de usuario a SectionLevelTutorialListing.ascx (Default.aspx la imagen de tamaño completo)

Por último, agregue estas páginas como entradas al Web.sitemap archivo. En concreto, agregue el marcado siguiente después de Trabajar con datos por lotes <siteMapNode>:

<siteMapNode url="~/AdvancedDAL/Default.aspx" 
    title="Advanced DAL Scenarios" 
    description="Explore a number of advanced Data Access Layer scenarios.">
    
    <siteMapNode url="~/AdvancedDAL/NewSprocs.aspx" 
        title="Creating New Stored Procedures for TableAdapters" 
        description="Learn how to have the TableAdapter wizard automatically 
            create and use stored procedures." />
    <siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx" 
        title="Using Existing Stored Procedures for TableAdapters" 
        description="See how to plug existing stored procedures into a 
            TableAdapter." />
    <siteMapNode url="~/AdvancedDAL/JOINs.aspx" 
        title="Returning Data Using JOINs" 
        description="Learn how to augment your DataTables to work with data 
            returned from multiple tables via a JOIN query." />
    <siteMapNode url="~/AdvancedDAL/AddingColumns.aspx" 
        title="Adding DataColumns to a DataTable" 
        description="Master adding new columns to an existing DataTable." />
    <siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx" 
        title="Working with Computed Columns" 
        description="Explore how to work with computed columns when using 
            Typed DataSets." />
    <siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx" 
        title="Protected Connection Strings in Web.config" 
        description="Protect your connection string information in 
            Web.config using encryption." />
    <siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx" 
        title="Creating Managed SQL Functions and Stored Procedures" 
        description="See how to create SQL functions and stored procedures 
            using managed code." />
</siteMapNode>

Después de actualizar Web.sitemap, dedique un momento a ver el sitio web de los tutoriales desde un explorador. El menú de la izquierda ahora incluye elementos para los tutoriales de escenarios avanzados de DAL.

Figura 3: El mapa del sitio ahora incluye entradas para los tutoriales de escenarios avanzados de DAL

Paso 2: Configurar un TableAdapter para crear nuevos procedimientos almacenados

Para demostrar la creación de una capa de acceso a datos que usa procedimientos almacenados en lugar de instrucciones SQL ad hoc, vamos a crear un nuevo DataSet con tipo en la ~/App_Code/DAL carpeta denominada NorthwindWithSprocs.xsd. Dado que hemos recorrido este proceso en detalle en los tutoriales anteriores, continuaremos rápidamente a través de los pasos que se indican aquí. Si te quedas atascado o necesitas instrucciones paso a paso adicionales para crear y configurar un Typed DataSet, consulta el tutorial Creación de una capa de acceso a datos.

Agregue un nuevo Conjunto de datos al proyecto haciendo clic con el botón derecho en la DAL carpeta, seleccionando Agregar nuevo elemento y seleccionando la plantilla DataSet como se muestra en la figura 4.

Figura 4: Agregar un nuevo conjunto de datos con tipo al proyecto denominado NorthwindWithSprocs.xsd (haga clic para ver la imagen de tamaño completo)

Esto creará un nuevo Conjunto de datos tipado, abrirá el Diseñador, generará un nuevo TableAdapter y lanzará el Asistente de configuración del TableAdapter. El Primer paso del Asistente para configuración de TableAdapter nos pide que seleccionemos la base de datos con la que trabajar. La cadena de conexión a la base de datos Northwind debe aparecer en la lista desplegable. Seleccione esto y haga clic en Siguiente.

En esta siguiente pantalla podemos elegir cómo TableAdapter debe tener acceso a la base de datos. En los tutoriales anteriores, hemos seleccionado la primera opción Usar instrucciones SQL. Para este tutorial, seleccione la segunda opción, Crear nuevos procedimientos almacenados y haga clic en Siguiente.

Figura 5: Indicar a TableAdapter que cree nuevos procedimientos almacenados (haga clic para ver la imagen de tamaño completo)

Al igual que con el uso de instrucciones SQL ad hoc, en el paso siguiente se le pide que proporcione la SELECT instrucción para la consulta principal de TableAdapter. Pero en lugar de usar la SELECT instrucción especificada aquí para realizar directamente una consulta ad hoc, el asistente de TableAdapter creará un procedimiento almacenado que contenga esta SELECT consulta.

Use la siguiente SELECT consulta para este TableAdapter:

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products

Figura 6: Escriba la consulta (SELECT imagen de tamaño completo)

Nota:

La consulta anterior difiere ligeramente de la consulta principal de ProductsTableAdapter en el Northwind conjunto de datos con tipo. Recuerde que en ProductsTableAdapter typed Northwind DataSet incluye dos subconsultas correlacionadas para devolver el nombre de categoría y el nombre de la compañía para cada categoría y proveedor de productos. En el próximo tutorial Actualización de TableAdapter para usar JOINs veremos cómo agregar estos datos relacionados a este TableAdapter.

Dedique un momento a hacer clic en el botón Opciones avanzadas. Desde aquí podemos especificar si el asistente de TableAdapter también debe generar instrucciones de inserción, actualización y eliminación, si se debe usar la concurrencia optimista y si la tabla de datos se debe actualizar después de las inserciones y actualizaciones. La opción Generar instrucciones insertar, actualizar y eliminar está activada por defecto. Déjelo activado. En este tutorial, deje desactivadas las opciones de uso de simultaneidad optimista.

Cuando el asistente TableAdapter crea automáticamente los procedimientos almacenados, parece que se omite la opción Actualizar la tabla de datos. Independientemente de si esta casilla está activada, los procedimientos almacenados de inserción y actualización resultantes recuperan el registro recién insertado o actualizado, como veremos en el paso 3.

Figura 7: Dejar marcada la opción Generar instrucciones Insert, Update y Delete

Nota:

Si se activa la opción Usar simultaneidad optimista, el asistente agregará condiciones adicionales a la WHERE cláusula que impiden que los datos se actualicen si se han producido cambios en otros campos. Consulte el tutorial Implementación de simultaneidad optimista para obtener más información sobre el uso de la característica de control de simultaneidad optimista integrada de TableAdapter.

Después de escribir la SELECT consulta y confirmar que la opción Generar instrucciones Insertar, Actualizar y Eliminar está activada, haga clic en Siguiente. Esta siguiente pantalla, que se muestra en la figura 8, solicita los nombres de los procedimientos almacenados que creará el asistente para seleccionar, insertar, actualizar y eliminar datos. Cambie estos nombres de procedimientos almacenados a Products_Select, Products_Insert, Products_Updatey Products_Delete.

Figura 8: Cambiar el nombre de los procedimientos almacenados (haga clic para ver la imagen de tamaño completo)

Para ver el T-SQL que utilizará el asistente de TableAdapter para crear los cuatro procedimientos almacenados, haga clic en el botón Vista previa de la secuencia de comandos SQL. En el cuadro de diálogo Vista previa del script SQL, puede guardar el script en un archivo o copiarlo en el Portapapeles.

Figura 9: Vista previa del script SQL usado para generar los procedimientos almacenados

Después de asignar un nombre a los procedimientos almacenados, haga clic en Siguiente para asignar un nombre a los métodos correspondientes de TableAdapter. Al igual que al usar instrucciones SQL ad hoc, podemos crear métodos que rellenen una dataTable existente o devuelvan una nueva. También podemos especificar si TableAdapter debe incluir el patrón DB-Direct para insertar, actualizar y eliminar registros. Deje activadas las tres casillas, pero cambie el nombre del método Return a DataTable ( GetProducts como se muestra en la figura 10).

Figura 10: Asigne un nombre a los métodos Fill y GetProducts (haga clic para ver la imagen de tamaño completo)

Haga clic en Siguiente para ver un resumen de los pasos que realizará el asistente. Para completar el asistente, haga clic en el botón Finalizar. Una vez completado el asistente, se le devolverá al Diseñador del conjunto de datos, que ahora debe incluir el ProductsDataTable.

Figura 11: El Diseñador de conjuntos de datos muestra el recién agregado ProductsDataTable (haga clic para ver la imagen de tamaño completo)

Paso 3: Examinar los procedimientos almacenados recién creados

El Asistente para TableAdapter usado en el paso 2 creó automáticamente los procedimientos almacenados para seleccionar, insertar, actualizar y eliminar datos. Estos procedimientos almacenados se pueden ver o modificar a través de Visual Studio; para ello, vaya al Explorador de servidores y explore en profundidad la carpeta Procedimientos almacenados de la base de datos. Como se muestra en la figura 12, la base de datos Northwind contiene cuatro nuevos procedimientos almacenados: Products_Delete, Products_Insert, Products_Selecty Products_Update.

Figura 12: Los cuatro procedimientos almacenados creados en el paso 2 se pueden encontrar en la carpeta De procedimientos almacenados de la base de datos

Nota:

Si no ve el Explorador de servidores, vaya al menú Ver y elija la opción Explorador de servidores. Si no ve los procedimientos almacenados relacionados con el producto agregados en el paso 2, haga clic con el botón derecho en la carpeta Procedimientos almacenados y elija Actualizar.

Para ver o modificar un procedimiento almacenado, haga doble clic en su nombre en el Explorador de servidores o, como alternativa, haga clic con el botón derecho en el procedimiento almacenado y elija Abrir. En la figura 13 se muestra el Products_Delete procedimiento almacenado, cuando se abre.

Figura 13: Los procedimientos almacenados se pueden abrir y modificar desde Visual Studio (haga clic para ver la imagen de tamaño completo)

El contenido de los procedimientos almacenados Products_Delete y Products_Select es bastante sencillo. Los procedimientos almacenados Products_Insert y Products_Update, por otro lado, requieren una inspección más detallada, ya que ambos realizan una sentencia SELECT después de sus sentencias INSERT y UPDATE. Por ejemplo, el siguiente código SQL compone el Products_Insert procedimiento almacenado:

ALTER PROCEDURE dbo.Products_Insert
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit
)
AS
    SET NOCOUNT OFF;
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], 
    [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, 
    @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
    UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = SCOPE_IDENTITY())

El procedimiento almacenado acepta como parámetros de entrada las Products columnas devueltas por la consulta SELECT especificada en el asistente de TableAdapter, y estos valores se utilizan en una instrucción INSERT. Después de la INSERT instrucción , se usa una SELECT consulta para devolver los Products valores de columna (incluido el ProductID) del registro recién agregado. Esta funcionalidad de actualización es útil al agregar un nuevo registro mediante el patrón batch Update, ya que actualiza automáticamente las propiedades de las instancias recién agregadas ProductRow con los valores de incremento automático asignados ProductID por la base de datos.

En el código siguiente se muestra esta característica. Contiene un ProductsTableAdapter y un ProductsDataTable creados para el NorthwindWithSprocs DataSet tipado. Se agrega un nuevo producto a la base de datos mediante la creación de una ProductsRow instancia, el suministro de sus valores y la llamada al método TableAdapter Update, pasando el ProductsDataTable. Internamente, el método TableAdapter Update enumera las ProductsRow instancias de dataTable pasadas (en este ejemplo solo hay una, la que acabamos de agregar) y realiza el comando de inserción, actualización o eliminación adecuados. En este caso, se ejecuta el Products_Insert procedimiento almacenado, que agrega un nuevo registro a la Products tabla y devuelve los detalles del registro recién agregado. A continuación, se actualiza el ProductsRow valor de la ProductID instancia. Una vez completado el Update método, podemos acceder al valor del ProductID registro recién agregado a través de la ProductsRow propiedad s ProductID .

// Create the ProductsTableAdapter and ProductsDataTable
NorthwindWithSprocsTableAdapters.ProductsTableAdapter productsAPI = 
    new NorthwindWithSprocsTableAdapters.ProductsTableAdapter();
NorthwindWithSprocs.ProductsDataTable products = 
    new NorthwindWithSprocs.ProductsDataTable();
// Create a new ProductsRow instance and set its properties
NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
product.ProductName = "New Product";
product.CategoryID = 1;  // Beverages
product.Discontinued = false;
// Add the ProductsRow instance to the DataTable
products.AddProductsRow(product);
// Update the DataTable using the Batch Update pattern
productsAPI.Update(products);
// At this point, we can determine the value of the newly-added record's ProductID
int newlyAddedProductIDValue = product.ProductID;

El Products_Update procedimiento almacenado incluye de forma similar una SELECT instrucción después de su UPDATE instrucción .

ALTER PROCEDURE dbo.Products_Update
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit,
    @Original_ProductID int,
    @ProductID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Products] 
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, 
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, 
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, 
    [Discontinued] = @Discontinued 
WHERE (([ProductID] = @Original_ProductID));
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = @ProductID)

Tenga en cuenta que este procedimiento almacenado incluye dos parámetros de entrada para ProductID: @Original_ProductID y @ProductID. Esta funcionalidad permite escenarios en los que se puede cambiar la clave principal. Por ejemplo, en una base de datos de empleados, cada registro de empleado podría usar el número de seguro social del empleado como clave principal. Para cambiar el número de seguro social de un empleado existente, se debe proporcionar el nuevo número de seguro social y el original. Para la Products tabla, esta funcionalidad no es necesaria porque la ProductID columna es una IDENTITY columna y nunca se debe cambiar. De hecho, la UPDATE instrucción del Products_Update procedimiento almacenado no incluye la columna ProductID en su lista de columnas. Por lo tanto, mientras @Original_ProductID se usa en la cláusula UPDATE de la instrucción WHERE, es superfluo para la tabla Products y podría reemplazarse por un parámetro @ProductID. Al modificar los parámetros de un procedimiento almacenado, es importante que también se actualicen los métodos TableAdapter que usan ese procedimiento almacenado.

Paso 4: Modificar parámetros de un procedimiento almacenado y actualizar TableAdapter

Dado que el @Original_ProductID parámetro es superfluo, vamos a quitarlo del Products_Update procedimiento almacenado por completo. Abra el Products_Update procedimiento almacenado, elimine el @Original_ProductID parámetro y, en la WHERE cláusula de la UPDATE instrucción , cambie el nombre del parámetro usado de @Original_ProductID a @ProductID. Después de realizar estos cambios, el T-SQL dentro del procedimiento almacenado debe ser similar al siguiente:

ALTER PROCEDURE dbo.Products_Update
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit,
    @ProductID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, 
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, 
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, 
    [Discontinued] = @Discontinued 
WHERE (([ProductID] = @ProductID));
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = @ProductID)

Para guardar estos cambios en la base de datos, haga clic en el icono Guardar de la barra de herramientas o presione Ctrl+S. En este momento, el Products_Update procedimiento almacenado no espera un @Original_ProductID parámetro de entrada, pero TableAdapter está configurado para pasar este parámetro. Para ver los parámetros que TableAdapter enviará al Products_Update procedimiento almacenado, seleccione TableAdapter en el Diseñador de DataSet, vaya a la ventana de propiedades y haga clic en los elipsis de la colección UpdateCommandParameters. Esto abre el cuadro de diálogo Editor de recopilación de parámetros que se muestra en la figura 14.

Figura 14: El Editor de recopilación de parámetros enumera los parámetros usados pasados al Products_Update procedimiento almacenado

Puede quitar este parámetro de aquí simplemente seleccionando el @Original_ProductID parámetro de la lista de miembros y haciendo clic en el botón Quitar.

Como alternativa, puede actualizar los parámetros usados para todos los métodos haciendo clic con el botón derecho en TableAdapter en el Diseñador y seleccionando Configurar. Se abrirá el Asistente para configuración de TableAdapter, en el que se enumeran los procedimientos almacenados que se usan para seleccionar, insertar, actualizar y eliminar, junto con los parámetros que esperan recibir los procedimientos almacenados. Si hace clic en la lista desplegable Actualizar, puede ver los parámetros de entrada esperados de los Products_Update procedimientos almacenados, que ahora no incluyen @Original_ProductID (consulte la figura 15). Simplemente haga clic en Finalizar para actualizar automáticamente la colección de parámetros usada por TableAdapter.

Figura 15: También puede usar el Asistente para configuración de TableAdapter para actualizar sus colecciones de parámetros de métodos (haga clic para ver la imagen de tamaño completo).

Paso 5: Agregar métodos TableAdapter adicionales

Como se muestra en el paso 2, al crear un nuevo TableAdapter es fácil tener los procedimientos almacenados correspondientes generados automáticamente. Lo mismo sucede al agregar métodos adicionales a TableAdapter. Para ilustrar esto, vamos a agregar un GetProductByProductID(productID) método al creado en el ProductsTableAdapter paso 2. Este método tomará como entrada un ProductID valor y devolverá detalles sobre el producto especificado.

Para empezar, haga clic con el botón derecho en TableAdapter y elija Agregar consulta en el menú contextual.

Figura 16: Agregar una nueva consulta a TableAdapter

Esto iniciará el Asistente para configuración de consultas de TableAdapter, que primero solicita cómo tableAdapter debe tener acceso a la base de datos. Para crear un nuevo procedimiento almacenado, elija la opción Crear un nuevo procedimiento almacenado y haga clic en Siguiente.

Figura 17: Elegir la opción Crear un nuevo procedimiento almacenado (Haga clic para ver la imagen de tamaño completo)

La siguiente pantalla nos pide que identifiquemos el tipo de consulta que se va a ejecutar, ya sea que devuelva un conjunto de filas, un único valor escalar, o realice una instrucción UPDATE, INSERT o DELETE. Puesto que el GetProductByProductID(productID) método devolverá una fila, deje la opción SELECT que devuelve la fila seleccionada y presione Siguiente.

Figura 18: Seleccionar el SELECT que devuelve la opción de fila (haga clic para ver la imagen en tamaño completo)

En la siguiente pantalla se muestra la consulta principal de TableAdapter, que simplemente muestra el nombre del procedimiento almacenado (dbo.Products_Select). Reemplace el nombre del procedimiento almacenado por la instrucción siguiente SELECT , que devuelve todos los campos de producto de un producto especificado:

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

Figura 19: Reemplazar el nombre del procedimiento almacenado por una SELECT consulta (haga clic para ver la imagen de tamaño completo)

La pantalla posterior le pide que asigne un nombre al procedimiento almacenado que se creará. Escriba el nombre Products_SelectByProductID y haga clic en Siguiente.

Figura 20: Asignar un nombre al nuevo procedimiento Products_SelectByProductID almacenado (haga clic para ver la imagen de tamaño completo)

El último paso del asistente nos permite cambiar los nombres de método generados, así como indicar si se debe usar el patrón de Rellenar un DataTable, Devolver un DataTable o ambos. Para este método, deje activadas ambas opciones, pero cambie el nombre de los métodos a FillByProductID y GetProductByProductID. Haga clic en Siguiente para ver un resumen de los pasos que realizará el asistente y, a continuación, haga clic en Finalizar para completar el asistente.

Figura 21: Cambiar el nombre de los métodos de TableAdapter a FillByProductID y GetProductByProductID (Haga clic para ver la imagen de tamaño completo)

Después de completar el asistente, TableAdapter tiene disponible un nuevo método, GetProductByProductID(productID) que, cuando se invoca, ejecutará el Products_SelectByProductID procedimiento almacenado que se acaba de crear. Dedique un momento a ver este nuevo procedimiento almacenado desde el Explorador de servidores explorando en profundidad la carpeta Procedimientos almacenados y abriendo Products_SelectByProductID (si no la ve, haga clic con el botón derecho en la carpeta Procedimientos almacenados y elija Actualizar).

Tenga en cuenta que el SelectByProductID procedimiento almacenado toma @ProductID como parámetro de entrada y ejecuta la SELECT instrucción que especificamos en el asistente.

ALTER PROCEDURE dbo.Products_SelectByProductID
(
    @ProductID int
)
AS
    SET NOCOUNT ON;
SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

Paso 6: Crear una clase de capa lógica de negocios

A lo largo de la serie de tutoriales, nos hemos esforzado por mantener una arquitectura en capas en la que la capa de presentación realizó todas sus llamadas a la capa de lógica de negocios (BLL). Para seguir esta decisión de diseño, primero es necesario crear una clase BLL para el nuevo DataSet tipado para poder acceder a los datos del producto desde la capa de presentación.

Cree un nuevo archivo de clase denominado ProductsBLLWithSprocs.cs en la ~/App_Code/BLL carpeta y agréguelo al código siguiente:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindWithSprocsTableAdapters;
[System.ComponentModel.DataObject]
public class ProductsBLLWithSprocs
{
    private ProductsTableAdapter _productsAdapter = null;
    protected ProductsTableAdapter Adapter
    {
        get
        {
            if (_productsAdapter == null)
                _productsAdapter = new ProductsTableAdapter();
            return _productsAdapter;
        }
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Select, true)]
    public NorthwindWithSprocs.ProductsDataTable GetProducts()
    {
        return Adapter.GetProducts();
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Select, false)]
    public NorthwindWithSprocs.ProductsDataTable GetProductByProductID(int productID)
    {
        return Adapter.GetProductByProductID(productID);
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Insert, true)]
    public bool AddProduct
        (string productName, int? supplierID, int? categoryID, 
         string quantityPerUnit, decimal? unitPrice, short? unitsInStock, 
         short? unitsOnOrder, short? reorderLevel, bool discontinued)
    {
        // Create a new ProductRow instance
        NorthwindWithSprocs.ProductsDataTable products = 
            new NorthwindWithSprocs.ProductsDataTable();
        NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
        product.ProductName = productName;
        if (supplierID == null) 
            product.SetSupplierIDNull(); 
        else 
            product.SupplierID = supplierID.Value;
        if (categoryID == null) 
            product.SetCategoryIDNull(); 
        else 
            product.CategoryID = categoryID.Value;
        if (quantityPerUnit == null) 
            product.SetQuantityPerUnitNull(); 
        else 
            product.QuantityPerUnit = quantityPerUnit;
        if (unitPrice == null) 
            product.SetUnitPriceNull(); 
        else 
            product.UnitPrice = unitPrice.Value;
        if (unitsInStock == null) 
            product.SetUnitsInStockNull(); 
        else 
            product.UnitsInStock = unitsInStock.Value;
        if (unitsOnOrder == null) 
            product.SetUnitsOnOrderNull(); 
        else 
            product.UnitsOnOrder = unitsOnOrder.Value;
        if (reorderLevel == null)
            product.SetReorderLevelNull(); 
        else 
            product.ReorderLevel = reorderLevel.Value;
        product.Discontinued = discontinued;
        // Add the new product
        products.AddProductsRow(product);
        int rowsAffected = Adapter.Update(products);
        // Return true if precisely one row was inserted, otherwise false
        return rowsAffected == 1;
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Update, true)]
    public bool UpdateProduct
        (string productName, int? supplierID, int? categoryID, string quantityPerUnit,
        decimal? unitPrice, short? unitsInStock, short? unitsOnOrder, 
        short? reorderLevel, bool discontinued, int productID)
    {
        NorthwindWithSprocs.ProductsDataTable products = 
            Adapter.GetProductByProductID(productID);
        if (products.Count == 0)
            // no matching record found, return false
            return false;
        NorthwindWithSprocs.ProductsRow product = products[0];
        product.ProductName = productName;
        if (supplierID == null) 
            product.SetSupplierIDNull(); 
        else 
            product.SupplierID = supplierID.Value;
        if (categoryID == null) 
            product.SetCategoryIDNull(); 
        else 
            product.CategoryID = categoryID.Value;
        if (quantityPerUnit == null) 
            product.SetQuantityPerUnitNull(); 
        else 
            product.QuantityPerUnit = quantityPerUnit;
        if (unitPrice == null) 
            product.SetUnitPriceNull(); 
        else 
            product.UnitPrice = unitPrice.Value;
        if (unitsInStock == null) 
            product.SetUnitsInStockNull(); 
        else 
            product.UnitsInStock = unitsInStock.Value;
        if (unitsOnOrder == null) 
            product.SetUnitsOnOrderNull(); 
        else 
            product.UnitsOnOrder = unitsOnOrder.Value;
        if (reorderLevel == null) 
            product.SetReorderLevelNull(); 
        else 
            product.ReorderLevel = reorderLevel.Value;
        product.Discontinued = discontinued;
        // Update the product record
        int rowsAffected = Adapter.Update(product);
        // Return true if precisely one row was updated, otherwise false
        return rowsAffected == 1;
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Delete, true)]
    public bool DeleteProduct(int productID)
    {
        int rowsAffected = Adapter.Delete(productID);
        // Return true if precisely one row was deleted, otherwise false
        return rowsAffected == 1;
    }
}

Esta clase imita la semántica de la clase ProductsBLL de los tutoriales anteriores, pero usa los objetos ProductsTableAdapter y ProductsDataTable del conjunto de datos NorthwindWithSprocs. Por ejemplo, en lugar de tener una using NorthwindTableAdapters instrucción al principio del archivo de clase ProductsBLL, la clase ProductsBLLWithSprocs usa using NorthwindWithSprocsTableAdapters. Del mismo modo, los objetos ProductsDataTable y ProductsRow usados en esta clase usan el prefijo del espacio de nombres NorthwindWithSprocs. La ProductsBLLWithSprocs clase proporciona dos métodos de acceso a datos, GetProducts y GetProductByProductID, y para agregar, actualizar y eliminar una única instancia de producto.

Paso 7: Trabajar con elNorthwindWithSprocsconjunto de datos de la capa de presentación

En este momento hemos creado un DAL que usa procedimientos almacenados para acceder a los datos de base de datos subyacentes y modificarlos. También hemos creado un BLL rudimentario con métodos para recuperar todos los productos o un producto determinado junto con métodos para agregar, actualizar y eliminar productos. Para completar este tutorial, vamos a crear una página de ASP.NET que use la clase de BLL para ProductsBLLWithSprocs mostrar, actualizar y eliminar registros.

Abra la página en la carpeta NewSprocs.aspx y arrastre un control GridView desde el cuadro de herramientas al Diseñador, nombrándolo AdvancedDAL. En la etiqueta inteligente GridView, elija enlazarla a un nuevo ObjectDataSource denominado ProductsDataSource. Configure ObjectDataSource para usar la clase ProductsBLLWithSprocs, como se muestra en la Figura 22.

Figura 22: Configurar ObjectDataSource para usar la clase (ProductsBLLWithSprocs la imagen de tamaño completo)

La lista desplegable de la pestaña SELECT tiene dos opciones, GetProducts y GetProductByProductID. Puesto que queremos mostrar todos los productos en GridView, elija el GetProducts método . Las listas desplegables de las pestañas UPDATE, INSERT y DELETE solo tienen un método. Asegúrese de que cada una de estas listas desplegables tiene seleccionado su método adecuado y, a continuación, haga clic en Finalizar.

Una vez completado el asistente ObjectDataSource, Visual Studio agregará BoundFields y CheckBoxField a GridView para los campos de datos del producto. Active las características integradas de edición y eliminación de GridView comprobando las opciones Habilitar edición y Habilitación de eliminación presentes en la etiqueta inteligente.

Figura 23: La página contiene un control GridView con soporte de edición y eliminación habilitado (haga clic para ver la imagen en tamaño completo)

Como hemos discutido en los tutoriales anteriores, al finalizar el asistente de ObjectDataSource, Visual Studio establece la propiedad OldValuesParameterFormatString a original_{0}. Esto debe revertirse a su valor predeterminado de {0} para que las funciones de modificación de datos funcionen correctamente, dado los parámetros esperados por los métodos de nuestra BLL. Por lo tanto, asegúrese de establecer la propiedad en OldValuesParameterFormatString o quitarla por completo de la sintaxis declarativa.

Después de completar el Asistente para configurar orígenes de datos, activar la edición y eliminar la compatibilidad en GridView y devolver la propiedad ObjectDataSource a OldValuesParameterFormatString su valor predeterminado, el marcado declarativo de la página debe tener un aspecto similar al siguiente:

<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" 
            InsertVisible="False" ReadOnly="True" 
            SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" 
            SortExpression="SupplierID" />
        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" 
            SortExpression="CategoryID" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" 
            SortExpression="QuantityPerUnit" />
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" 
            SortExpression="UnitPrice" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" 
            SortExpression="UnitsInStock" />
        <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" 
            SortExpression="UnitsOnOrder" />
        <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" 
            SortExpression="ReorderLevel" />
        <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server" 
    DeleteMethod="DeleteProduct" InsertMethod="AddProduct" 
    SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs" 
    UpdateMethod="UpdateProduct">
    <DeleteParameters>
        <asp:Parameter Name="productID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="productName" Type="String" />
        <asp:Parameter Name="supplierID" Type="Int32" />
        <asp:Parameter Name="categoryID" Type="Int32" />
        <asp:Parameter Name="quantityPerUnit" Type="String" />
        <asp:Parameter Name="unitPrice" Type="Decimal" />
        <asp:Parameter Name="unitsInStock" Type="Int16" />
        <asp:Parameter Name="unitsOnOrder" Type="Int16" />
        <asp:Parameter Name="reorderLevel" Type="Int16" />
        <asp:Parameter Name="discontinued" Type="Boolean" />
        <asp:Parameter Name="productID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="productName" Type="String" />
        <asp:Parameter Name="supplierID" Type="Int32" />
        <asp:Parameter Name="categoryID" Type="Int32" />
        <asp:Parameter Name="quantityPerUnit" Type="String" />
        <asp:Parameter Name="unitPrice" Type="Decimal" />
        <asp:Parameter Name="unitsInStock" Type="Int16" />
        <asp:Parameter Name="unitsOnOrder" Type="Int16" />
        <asp:Parameter Name="reorderLevel" Type="Int16" />
        <asp:Parameter Name="discontinued" Type="Boolean" />
    </InsertParameters>
</asp:ObjectDataSource>

En este momento, podríamos ordenar gridView personalizando la interfaz de edición para incluir la validación, tener las CategoryID columnas y SupplierID representadas como DropDownLists, etc. También podríamos agregar una confirmación del lado cliente al botón Eliminar y le recomendamos que dedique tiempo a implementar estas mejoras. Dado que estos temas se han tratado en tutoriales anteriores, no volveremos a tratarlos aquí.

Independientemente de si mejora GridView o no, pruebe las características principales de la página en un explorador. Como se muestra en la figura 24, la página muestra los productos de una clase GridView que proporciona funcionalidades de edición y eliminación por fila.

Figura 24: Los productos se pueden ver, editar y eliminar de GridView (haga clic para ver la imagen de tamaño completo)

Resumen

Los TableAdapters de un DataSet tipado pueden acceder a los datos de la base de datos mediante instrucciones SQL ad hoc o mediante procedimientos almacenados. Al trabajar con procedimientos almacenados, se pueden usar procedimientos almacenados existentes o se puede indicar al asistente TableAdapter que cree nuevos procedimientos almacenados basados en una SELECT consulta. En este tutorial hemos explorado cómo crear automáticamente los procedimientos almacenados.

Aunque tener los procedimientos almacenados generados automáticamente ayuda a ahorrar tiempo, hay algunos casos en los que el procedimiento almacenado creado por el asistente no se alinea con lo que habríamos creado por nuestra cuenta. Un ejemplo es el procedimiento almacenado Products_Update, que esperaba tanto los parámetros de entrada @Original_ProductID y @ProductID aunque el parámetro @Original_ProductID era superfluo.

En muchos escenarios, es posible que los procedimientos almacenados ya se hayan creado o que deseemos compilarlos manualmente para tener un mayor grado de control sobre los comandos del procedimiento almacenado. En cualquier caso, queremos indicar a TableAdapter que use los procedimientos almacenados existentes para sus métodos. Veremos cómo hacerlo en el siguiente tutorial.

¡Feliz programación!

Lecturas adicionales

Para obtener más información sobre los temas tratados en este tutorial, consulte los siguientes recursos:

• Crear y mantener procedimientos almacenados
• Procedimientos almacenados: Información general
• Crear un procedimiento almacenado

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 en 24 horas. Se puede contactar con él en mitchell@4GuysFromRolla.com.

Agradecimientos especiales a

Esta serie de tutoriales contó con la revisión de muchos revisores que fueron de gran ayuda. El revisor principal de este tutorial fue Hilton Geisenow. ¿Le interesa revisar mis próximos artículos de MSDN? Si es así, mándame un mensaje a mitchell@4GuysFromRolla.com.

Próximo