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

por 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 para su ejecución. 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 usar el asistente para TableAdapter para generar nuevos procedimientos almacenados.

Introducción

La capa de acceso a datos (DAL, por sus siglas en inglés) de estos tutoriales usa conjuntos de datos con tipo. Como se describe en el tutorial Creación de una capa de acceso a datos, los conjuntos de datos con tipo constan de DataTables y TableAdapters fuertemente tipados. Los elementos DataTables representan las entidades lógicas del sistema, mientras que los elementos TableAdapters interactúan con la base de datos subyacente para realizar trabajos de acceso a 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 los procedimientos almacenados a las instrucciones SQL ad hoc por motivos de seguridad, mantenimiento y actualización. Otros prefieren indudablemente las instrucciones SQL ad hoc por su flexibilidad. En mi propio trabajo, prefiero los procedimientos almacenados a las instrucciones SQL ad hoc, pero he decidido usar instrucciones SQL ad hoc para simplificar los tutoriales anteriores.

Al definir un TableAdapter o agregar nuevos métodos, el asistente para TableAdapter hace que crear nuevos procedimientos almacenados o usar procedimientos almacenados existentes sea tan fácil como usar instrucciones SQL ad hoc. En este tutorial examinaremos cómo hacer que el asistente para 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 ¿Todavía no usa procedimientos almacenados? y la entrada de blog de Frans BoumaLos procedimientos almacenados son malos, ¿vale? para leer un animado debate sobre las ventajas y desventajas de los procedimientos almacenados y SQL ad hoc.

Conceptos básicos de los 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. Las funciones pueden aceptar parámetros de entrada y, opcionalmente, devolver un valor. Los procedimientos almacenados son construcciones de base de datos que comparten muchas similitudes con las funciones de los 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 entre cero y muchos parámetros de entrada, y puede devolver valores escalares, parámetros de salida o, normalmente, conjuntos de resultados de consultas SELECT.

Nota:

Los procedimientos almacenados suelen denominarse sprocs o SP (por sus siglas en inglés).

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

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 mediante el IDE de Visual Studio. Sin embargo, en este tutorial vamos a permitir que el asistente para TableAdapter genere automáticamente los procedimientos almacenados.

Además de simplemente devolver datos, los procedimientos almacenados a menudo se usan para ejecutar 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 parámetro @CategoryID y ejecutar dos instrucciones DELETE: una para eliminar los productos relacionados y otra para eliminar la categoría especificada. Cuando hay varias instrucciones dentro de un procedimiento almacenado, no se encapsulan automáticamente en una transacción. Es necesario ejecutar comandos T-SQL adicionales para asegurarse de que los múltiples comandos del procedimiento almacenado se tratan como una operación atómica. Veremos cómo encapsular los comandos de un procedimiento almacenado dentro del ámbito de una transacción en un tutorial posterior.

Cuando se usan procedimientos almacenados dentro de una arquitectura, los métodos de la 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 definirla en 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 de dónde y cómo se usa la base de datos.

Para obtener más información sobre los aspectos básicos de los procedimientos almacenados, consulte los recursos de la sección Información adicional al final de este tutorial.

Paso 1: Creación de páginas web para escenarios avanzados de la capa de acceso a datos

Antes de comenzar nuestra discusión sobre la creación de una DAL mediante procedimientos almacenados, primero dediquemos un momento a crear las páginas ASP.NET en nuestro proyecto de sitio web que necesitaremos para este y para los siguientes tutoriales. Empiece agregando 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

Add the ASP.NET Pages for the Advanced Data Access Layer Scenarios Tutorials

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

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.

Add the SectionLevelTutorialListing.ascx User Control to Default.aspx

Figura 2: Agregue el control de usuario SectionLevelTutorialListing.ascx a Default.aspx (haga clic aquí para ver la imagen a tamaño completo)

Por último, agregue las siguientes páginas como entradas al archivo Web.sitemap. 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 tutoriales a través de un explorador. El menú de la izquierda ahora incluye elementos para los tutoriales de escenarios avanzados de DAL.

The Site Map Now Includes Entries for the Advanced DAL Scenarios Tutorials

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

Paso 2: Configuración de un TableAdapter para crear nuevos procedimientos almacenados

Para mostrar 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 conjunto de datos con tipo en la carpeta ~/App_Code/DAL denominado NorthwindWithSprocs.xsd. Dado que hemos explicado este proceso en detalle en los tutoriales anteriores, aquí avanzaremos por los pasos rápidamente. Si no sabe cómo seguir o necesita instrucciones paso a paso adicionales para crear y configurar un conjunto de datos con tipo, consulte 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 carpeta DAL. Después, seleccione Agregar nuevo elemento y elija la plantilla Conjunto de datos como se muestra en la figura 4.

Add a New Typed DataSet to the Project Named NorthwindWithSprocs.xsd

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

Esto creará el nuevo conjunto de datos con tipo, abrirá su diseñador, creará un nuevo TableAdapter e iniciará el asistente para la configuración de TableAdapter. El primer paso del asistente para la 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 debería aparecer en la lista desplegable. Selecciónela esto y haga clic en Siguiente.

En la siguiente pantalla, podemos elegir cómo debe acceder el elemento TableAdapter 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.

Instruct the TableAdapter to Create New Stored Procedures

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

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

Use la siguiente consulta SELECT para este elemento TableAdapter:

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

Enter the SELECT Query

Figura 6: Introducción de la consulta SELECT (haga clic aquí para ver la imagen a tamaño completo)

Nota:

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

Dedique un momento a hacer clic en el botón Opciones avanzadas. Aquí podemos especificar si el asistente también debe generar instrucciones Insert, Update y Delete para el TableAdapter, si se debe usar la simultaneidad optimista y si la tabla de datos se debe actualizar después de las inserciones y actualizaciones. La opción Generar instrucciones Insert, Update y Delete está activada de forma predeterminada. Déjela activada. Para este tutorial, desactive la opción Usar simultaneidad optimista.

Cuando el asistente para 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 recién actualizado, como veremos en el paso 3.

Leave the Generate Insert, Update and Delete statements Option Checked

Figura 7: Deje activada 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 cláusula WHERE 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 consulta SELECT y confirmar que la opción Generar instrucciones Insert, Update y Delete está activada, haga clic en Siguiente. En la siguiente pantalla (figura 8) se solicitan los nombres de los procedimientos almacenados que creará el asistente para seleccionar, insertar, actualizar y eliminar datos. Cambie los nombres de estos procedimientos almacenados a Products_Select, Products_Insert, Products_Update y Products_Delete.

Rename the Stored Procedures

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

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

Preview the SQL Script Used to Generate the Stored Procedures

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. Igual que con las instrucciones SQL ad hoc, podemos crear métodos que rellenen un objeto DataTable existente o devuelvan uno nuevo. 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 Devolver un DataTable a GetProducts (tal y como se muestra en la figura 10).

Name the Methods Fill and GetProducts

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

Haga clic en Siguiente para ver un resumen de los pasos que realizará el asistente. Para finalizar el asistente, haga clic en el botón Finalizar. Una vez finalizado el asistente, se le devolverá al Diseñador de DataSet, que ahora debería incluir ProductsDataTable.

The DataSet s Designer Shows the Newly Added ProductsDataTable

Figura 11: El Diseñador de DataSet muestra el elemento ProductsDataTable recién agregado (haga clic aquí para ver la imagen a tamaño completo)

Paso 3: Inspección de los procedimientos almacenados recién creados

El asistente para TableAdapter usado en el paso 2 ha creado automáticamente los procedimientos almacenados para seleccionar, insertar, actualizar y eliminar datos. Estos procedimientos almacenados se pueden ver o modificar por medio de Visual Studio; para ello, vaya al Explorador de servidores y vaya hasta 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_Select y Products_Update.

The Four Stored Procedures Created in Step 2 Can Be Found in the Database s Stored Procedures Folder

Figura 12: Los cuatro procedimientos almacenados creados en el paso 2 se pueden encontrar en la carpeta 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 los productos que ha agregado 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 procedimiento almacenado Products_Delete una vez abierto.

Stored Procedures Can Be Opened and Modified From Within Visual Studio

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

El contenido de los procedimientos almacenados Products_Delete y Products_Select es bastante sencillo. Por otro lado, los procedimientos almacenados Products_Insert y Products_Update garantizan una inspección más detallada, ya que ambos ejecutan una instrucción SELECT después de las instrucciones INSERT y UPDATE. Por ejemplo, el siguiente código SQL constituye el procedimiento almacenado Products_Insert:

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 columnas Products devueltas por la consulta SELECT especificada en el asistente para TableAdapter, y estos valores se usan en una instrucción INSERT. Después de la instrucción INSERT, se usa una consulta SELECT para devolver los valores de las columnas Products (incluidos los valores ProductID) del registro recién agregado. Esta funcionalidad de actualización es útil al agregar un nuevo registro mediante el patrón de actualización por lotes, ya que actualiza automáticamente las instancias de ProductRow recién agregadas para las propiedades ProductID con los valores de incremento automático asignados por la base de datos.

El código siguiente muestra esta característica. Contiene un objeto ProductsTableAdapter y un objeto ProductsDataTable creados para el conjunto de datos con tipo NorthwindWithSprocs. Se agrega un nuevo producto a la base de datos creando una instancia de ProductsRow, proporcionando sus valores, llamando al método Update del TableAdapter y pasando el objeto ProductsDataTable. Internamente, el método Update de TableAdapter enumera las instancias de ProductsRow en el objeto DataTable pasado (en este ejemplo solo hay una, la que acabamos de agregar) y ejecuta el comando de inserción, actualización o eliminación adecuado. En este caso, se ejecuta el procedimiento almacenado Products_Insert, el cual agrega un nuevo registro a la tabla Products y devuelve los detalles del registro recién agregado. Después, se actualiza el valor ProductID de la instancia de ProductsRow. Una vez completado el método Update, podemos acceder al valor ProductID del registro recién agregado a través de la propiedad ProductID de ProductsRow.

// 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 procedimiento almacenado Products_Update incluye de forma similar una instrucción SELECT después de su instrucción UPDATE.

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 del seguro social del empleado como clave principal. Para cambiar el número del seguro social de un empleado existente, se debe proporcionar el nuevo número del seguro social y el número original. Para la tabla Products, esta funcionalidad no es necesaria porque la columna ProductID es una columna de IDENTITY y nunca se debe cambiar. De hecho, la instrucción UPDATE del procedimiento almacenado Products_Update no incluye la columna ProductID en su lista de columnas. Por lo tanto, mientras @Original_ProductID se usa en la cláusula WHERE de la instrucción UPDATE, es superfluo para la tabla Products y podría reemplazarse por el parámetro @ProductID. Al modificar los parámetros de un procedimiento almacenado, es importante que también se actualicen los métodos del TableAdapter que usan ese procedimiento almacenado.

Paso 4: Modificación de los parámetros de un procedimiento almacenado y actualización del TableAdapter

Dado que el parámetro @Original_ProductID es superfluo, vamos a quitarlo por completo del procedimiento almacenado Products_Update. Abra el procedimiento almacenado Products_Update, elimine el parámetro @Original_ProductID y, en la cláusula WHERE de la instrucción UPDATE, cambie el nombre del parámetro usado de @Original_ProductID a @ProductID. Después de realizar estos cambios, el código T-SQL del procedimiento almacenado debería tener un aspecto 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 punto, el procedimiento almacenado Products_Update no espera un parámetro de entrada @Original_ProductID, pero el objeto TableAdapter está configurado para pasar este parámetro. Para ver los parámetros que el TableAdapter enviará al procedimiento almacenado Products_Update, seleccione el TableAdapter en el Diseñador de DataSet, vaya a la ventana Propiedades y haga clic en los puntos suspensivos de la colección Parameters de UpdateCommand. Esto abre el cuadro de diálogo Editor de la colección de parámetros que se muestra en la figura 14.

The Parameters Collection Editor Lists the Parameters Used Passed to the Products_Update Stored Procedure

Figura 14: El editor de la colección de parámetros enumera los parámetros usados pasados al procedimiento almacenado Products_Update

Puede quitar este parámetro desde aquí simplemente seleccionando el parámetro @Original_ProductID 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 el TableAdapter en el Diseñador y seleccionando Configurar. Se abrirá el asistente para la 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 del procedimiento almacenado Products_Update, que ya 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 el TableAdapter.

You Can Alternatively Use the TableAdapter s Configuration Wizard to Refresh Its Methods Parameter Collections

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

Paso 5: Adición de métodos de TableAdapter adicionales

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

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

Add a New Query to the TableAdapter

Figura 16: Agregue una nueva consulta al TableAdapter

Esto iniciará el asistente para la configuración de consultas de TableAdapter, que primero pregunta cómo debe acceder el TableAdapter a la base de datos. Para crear un nuevo procedimiento almacenado, elija la opción Crear nuevos procedimientos almacenados y haga clic en Siguiente.

Choose the Create a new stored procedure Option

Figura 17: Elija la opción Crear nuevos procedimientos almacenados (haga clic aquí para ver la imagen a tamaño completo)

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

Choose the SELECT which returns row Option

Figura 18: Elija la opción SELECT que devuelve filas (haga clic aquí para ver la imagen a tamaño completo)

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

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

Replace the Stored Procedure Name with a SELECT Query

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

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

Name the New Stored Procedure Products_SelectByProductID

Figura 20: Asigne el nombre Products_SelectByProductID al procedimiento almacenado (haga clic aquí para ver la imagen a tamaño completo)

El último paso del asistente nos permite cambiar los nombres de los métodos generados, así como indicar si se debe usar el patrón Rellenar un DataTable, el patrón 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, después, haga clic en Finalizar para completar el asistente.

Rename the TableAdapter s Methods to FillByProductID and GetProductByProductID

Figura 21: Cambie el nombre de los métodos del TableAdapter a FillByProductID y GetProductByProductID (haga clic aquí para ver la imagen a tamaño completo)

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

Tenga en cuenta que el procedimiento almacenado SelectByProductID toma @ProductID como parámetro de entrada y ejecuta la instrucción SELECT que escribimos 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: Creación de una clase de capa lógica de negocios

A lo largo de esta serie de tutoriales, nos hemos esforzado por mantener una arquitectura en capas en la que la capa de presentación realiza todas sus llamadas a la capa de lógica de negocios (BLL, por sus siglas en inglés). Para cumplir esta decisión de diseño, primero es necesario crear una clase de BLL para el nuevo conjunto de datos con tipo para poder acceder a los datos de productos desde la capa de presentación.

Cree un nuevo archivo de clase denominado ProductsBLLWithSprocs.cs en la carpeta ~/App_Code/BLL y agréguele el 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 instrucción using NorthwindTableAdapters al principio del archivo de clase como con ProductsBLL, la clase ProductsBLLWithSprocs usa using NorthwindWithSprocsTableAdapters. Del mismo modo, los objetos ProductsDataTable y ProductsRow usados en esta clase tienen como prefijo el espacio de nombres NorthwindWithSprocs. La clase ProductsBLLWithSprocs proporciona dos métodos de acceso a datos, GetProducts y GetProductByProductID, y métodos para agregar, actualizar y eliminar una única instancia de producto.

Paso 7: Uso del conjunto de datos NorthwindWithSprocs desde la capa de presentación

Hasta ahora, hemos creado una DAL que usa procedimientos almacenados para acceder a los datos de base de datos subyacentes y modificarlos. También hemos creado una 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 ASP.NET que use la clase ProductsBLLWithSprocs de la BLL para mostrar, actualizar y eliminar registros.

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

Configure the ObjectDataSource to Use the ProductsBLLWithSprocs Class

Figura 22: Configure ObjectDataSource para usar la clase ProductsBLLWithSprocs (haga clic aquí para ver la imagen a 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 el control GridView, elija el método GetProducts. 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, después, haga clic en Finalizar.

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

The Page Contains a GridView with Editing and Deleting Support Enabled

Figura 23: La página contiene un control GridView con las opciones de edición y eliminación habilitadas (haga clic aquí para ver la imagen a tamaño completo)

Como hemos descrito en los tutoriales anteriores, al finalizar el asistente para ObjectDataSource, Visual Studio establece la propiedad OldValuesParameterFormatString en original_{0}. Esto debe revertirse a su valor predeterminado de {0} para que las características de modificación de datos funcionen correctamente según los parámetros esperados por los métodos de nuestra capa de lógica de negocios (BLL). Por lo tanto, asegúrese de establecer la propiedad OldValuesParameterFormatString en {0} o quite la propiedad por completo de la sintaxis declarativa.

Después de completar el asistente para configurar orígenes de datos, de activar la edición y eliminación en el control GridView y devolver la propiedad OldValuesParameterFormatString de ObjectDataSource a su valor predeterminado, el marcado declarativo de la página debería ser 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 punto, podríamos ordenar el control GridView personalizando la interfaz de edición para incluir la validación, haciendo que las columnas CategoryID y SupplierID se representen 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 el 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 enumera los productos de un GridView que proporciona funcionalidades de edición y eliminación por fila.

The Products Can Be Viewed, Edited, and Deleted from the GridView

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

Resumen

El objeto TableAdapters de un conjunto de datos con tipo puede 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 para TableAdapter que cree nuevos procedimientos almacenados a partir de una consulta SELECT. En este tutorial hemos explorado cómo crear automáticamente los procedimientos almacenados.

Aunque la generación automática de los procedimientos almacenados 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 parámetros de entrada @Original_ProductID como @ProductID, aunque el parámetro @Original_ProductID fuera superfluo.

En muchos escenarios, es posible que los procedimientos almacenados ya se hayan creado o que queramos compilarlos manualmente para tener un mayor grado de control sobre los comandos del procedimiento almacenado. En cualquier caso, querríamos indicar al 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:

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, instructor y escritor. Su último libro es Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Contacte con el vía mitchell@4GuysFromRolla.com. o a través de su blog, que se puede encontrar en http://ScottOnWriting.NET.

Agradecimientos especiales a

Esta serie de tutoriales fue revisada por muchos revisores de gran ayuda. El revisor principal de este tutorial fue Hilton Giesenow. ¿Le interesa revisar mis próximos artículos de MSDN? Si fuera así, escríbame a mitchell@4GuysFromRolla.com.