Agregar columnas adicionales de DataTable (VB)

por Scott Mitchell

Descargar PDF

Cuando se usa el asistente de TableAdapter para crear un DataSet con tipo, la DataTable correspondiente contiene las columnas devueltas por la consulta de base de datos principal. No obstante, hay ocasiones en las que DataTable necesita incluir columnas adicionales. En este tutorial, se explica por qué se recomiendan los procedimientos almacenados cuando se necesitan columnas de DataTable adicionales.

Introducción

Cuando se añade un TableAdapter a un DataSet con tipo, el esquema de DataTable correspondiente viene determinado por la consulta principal del TableAdapter. Por ejemplo, si la consulta principal devuelve los campos de datos A, B y C, DataTable tendrá tres columnas correspondientes denominadas A, B y C. Además de la consulta principal, un TableAdapter puede incluir consultas adicionales que devuelvan, por ejemplo, un subconjunto de los datos basados en algún parámetro. Por ejemplo, además de la consulta principal de ProductsTableAdapter, que devuelve información sobre todos los productos, también contiene métodos como GetProductsByCategoryID(categoryID) y GetProductByProductID(productID), que devuelven información específica del producto basada en un parámetro proporcionado.

El modelo de que el esquema de DataTable refleje la consulta principal de TableAdapter funciona bien si todos los métodos de TableAdapter devuelven los mismos o menos campos de datos que los especificados en la consulta principal. Si un método de TableAdapter necesita devolver campos de datos adicionales, deberíamos expandir el esquema de DataTable en consecuencia. En el tutorial Maestro y detalles cuando se utiliza una lista con viñetas de registros maestros con una DataList de detalles, hemos añadido un método a CategoriesTableAdapter que devuelve los campos de datos CategoryID, CategoryName y Description definidos en la consulta principal más NumberOfProducts, un campo de datos adicional que notifica el número de productos asociados a cada categoría. Hemos añadido manualmente una nueva columna a CategoriesDataTable para capturar el valor del campo de datos NumberOfProducts de este nuevo método.

Como se describe en el tutorial Carga de archivos, se debe tener especial cuidado con los TableAdapters que usan instrucciones SQL ad hoc y tienen métodos cuyos campos de datos no coinciden con la consulta principal. Si se vuelve a ejecutar el asistente de configuración de TableAdapter, actualizará todos los métodos de TableAdapter para que su lista de campos de datos coincida con la consulta principal. Por lo tanto, los métodos con listas de columnas personalizadas se revertirán a la lista de columnas de la consulta principal y no devolverán los datos esperados. Este problema no se produce cuando se utilizan procedimientos almacenados.

En este tutorial, veremos cómo ampliar un esquema de DataTable para incluir columnas adicionales. Debido a la fragilidad de TableAdapter cuando se utilizan instrucciones SQL ad hoc, en este tutorial usaremos procedimientos almacenados. Consulte los tutoriales Creación de nuevos procedimientos almacenados para los TableAdapters del DataSet con tipo y Uso de procedimientos almacenados existentes para los TableAdapters de DataSet con tipo para obtener más información sobre cómo configurar un TableAdapter para usar procedimientos almacenados.

Paso 1: Añadir unaPriceQuartilecolumna alProductsDataTable

En el tutorial Creación de nuevos procedimientos almacenados para los TableAdapters del DataSet con tipo, hemos creado un DataSet con tipo denominado NorthwindWithSprocs. Este DataSet contiene actualmente dos DataTables: ProductsDataTable y EmployeesDataTable. ProductsTableAdapter tiene los tres métodos siguientes:

• GetProducts: la consulta principal, que devuelve todos los registros de la tabla Products.
• GetProductsByCategoryID(categoryID): devuelve todos los productos con el categoryID especificado.
• GetProductByProductID(productID): devuelve el producto concreto con el productID especificado.

La consulta principal y los dos métodos adicionales devuelven el mismo conjunto de campos de datos, es decir, todas las columnas de la tabla Products. No hay subconsultas correlacionadas ni JOIN extrayendo datos relacionados de las tablas Categories o Suppliers. Por lo tanto, ProductsDataTable tiene una columna correspondiente para cada campo de la tabla Products.

En este tutorial, vamos a añadir un método al ProductsTableAdapter denominado GetProductsWithPriceQuartile que devuelve todos los productos. Además de los campos de datos de producto estándar, GetProductsWithPriceQuartile también incluirá un campo de datos PriceQuartileque indica a qué cuartil corresponde el precio del producto. Por ejemplo, aquellos productos cuyos precios se sitúen en el 25% más caro tendrán un valor de PriceQuartile igual a 1, mientras que aquellos cuyos precios se sitúen en el 25% más bajo tendrán un valor igual a 4. Antes de preocuparnos por crear el procedimiento almacenado para devolver esta información, primero es necesario actualizar ProductsDataTable para que incluya una columna que contenga los resultados de PriceQuartile cuando se usa el método GetProductsWithPriceQuartile.

Abra el DataSet NorthwindWithSprocs y haga clic con el botón derecho en ProductsDataTable. Elija Añadir en el menú contextual y, a continuación, elija Columna.

Figura 1: Añadir una nueva columna a ProductsDataTable (haga clic aquí para ver la imagen a tamaño completo)

Esto añadirá una nueva columna a la DataTable denominada Column1 de tipo System.String. Es necesario actualizar el nombre de esta columna a PriceQuartile y su tipo a System.Int32, ya que se usará para contener un número entre 1 y 4. Seleccione la columna que acaba de añadir en ProductsDataTable y, en la ventana Propiedades, establezca la propiedad Name en PriceQuartile y la propiedad DataType en System.Int32.

Figura 2: Establecer las propiedades Name y DataType de la nueva columna (haga clic aquí para ver la imagen a tamaño completo)

Como se muestra en la figura 2, se pueden establecer propiedades adicionales como, por ejemplo, si los valores de la columna deben ser únicos, si la columna es una columna de incremento automático, si se permiten o no valoresNULL de base de datos, etc. Deje estos valores establecidos en sus valores predeterminados.

Paso 2: Crear elGetProductsWithPriceQuartilemétodo

Ahora que ProductsDataTable se ha actualizado para incluir la columna PriceQuartile, estamos listos para crear el método GetProductsWithPriceQuartile. Para empezar, haga clic con el botón derecho en el TableAdapter y elija Añadir consulta en el menú contextual. Se abrirá el asistente de configuración de consultas de TableAdapter, que primero nos pregunta si queremos usar instrucciones SQL ad hoc o un procedimiento almacenado nuevo o existente. Puesto que aún no tenemos un procedimiento almacenado que devuelva los datos cuartiles de precio, vamos a permitir que TableAdapter cree este procedimiento almacenado para nosotros. Seleccione la opción Crear nuevo procedimiento almacenado y haga clic en Siguiente.

Figura 3: Indicar al asistente de TableAdapter que cree el procedimiento almacenado (haga clic aquí para ver la imagen a tamaño completo)

En la siguiente pantalla, que se muestra en la figura 4, el asistente nos pregunta qué tipo de consulta queremos añadir. Como el método GetProductsWithPriceQuartile devolverá todas las columnas y registros de la tabla Products, seleccione la opción SELECT que devuelve filas y haga clic en Siguiente.

Figura 4: Nuestra consulta será una instrucción SELECT que devuelve varias filas (haga clic aquí para ver la imagen a tamaño completo)

A continuación, se solicita la consulta SELECT. Introduzca la siguiente consulta en el asistente:

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued,
       NTILE(4) OVER (ORDER BY UnitPrice DESC) as PriceQuartile
FROM Products

La consulta anterior usa la nueva función de NTILE de SQL Server 2005 para dividir los resultados en cuatro grupos, que están determinados por los valores UnitPrice en orden descendente.

Desafortunadamente, el Generador de consultas no sabe cómo analizar la palabra clave OVER y mostrará un error al analizar la consulta anterior. Por lo tanto, especifique la consulta anterior directamente en el cuadro de texto del asistente sin usar el Generador de consultas.

Nota:

Para obtener más información sobre las otras funciones de clasificación de NTILE y SQL Server 2005, consulte ROW_NUMBER (Transact-SQL) y la sección Funciones de clasificación de los Manuales de SQL Server 2005 online.

Después de especificar la consulta SELECT y hacer clic en Siguiente, el asistente solicita que proporcionemos un nombre para el procedimiento almacenado que creará. Llame al procedimiento almacenado Products_SelectWithPriceQuartile y haga clic en Siguiente.

Figura 5: Llamar al procedimiento almacenado Products_SelectWithPriceQuartile (haga clic aquí para ver la imagen a tamaño completo)

Por último, se nos solicitará que asignemos un nombre a los métodos de TableAdapter. Deje marcadas las casillas Rellenar una DataTable y Devolver una DataTable, y llame a los métodos FillWithPriceQuartile y GetProductsWithPriceQuartile.

Figura 6: Asignar un nombre a los métodos de TableAdapter y hacer clic en Finalizar (haga clic aquí para ver la imagen a tamaño completo)

Con la consulta SELECT especificada y los métodos de procedimiento almacenado y TableAdapter con nombre, haga clic en Finalizar para completar el asistente. En este punto, puede recibir una advertencia o dos del asistente indicándole que no se admite la instrucción o la construcción SQL OVER. Estas advertencias se pueden omitir.

Después de completar el asistente, TableAdapter debe incluir los métodos FillWithPriceQuartile y GetProductsWithPriceQuartile, y la base de datos debe incluir un procedimiento almacenado denominado Products_SelectWithPriceQuartile. Dedique un momento a comprobar que TableAdapter contiene realmente este nuevo método y que el procedimiento almacenado se ha añadido correctamente a la base de datos. Cuando compruebe la base de datos, si no ve el procedimiento almacenado, haga clic con el botón derecho en la carpeta Procedimientos almacenados y elija Actualizar.

Figura 7: Comprobar que se ha añadido un nuevo método a TableAdapter

Figura 8: Asegurarse de que la base de datos contiene el procedimiento almacenado Products_SelectWithPriceQuartile (haga clic aquí para ver la imagen a tamaño completo)

Nota:

Una de las ventajas de usar procedimientos almacenados en lugar de instrucciones SQL ad hoc es que, cuando se vuelve a ejecutar el asistente de configuración de TableAdapter, no se modifican las listas de columnas de procedimientos almacenados. Para comprobarlo, haga clic con el botón derecho en el TableAdapter, elija la opción Configurar en el menú contextual para iniciar el asistente y, a continuación, haga clic en Finalizar para completarlo. A continuación, vaya a la base de datos y vea el procedimiento almacenado Products_SelectWithPriceQuartile. Observe que su lista de columnas no se ha modificado. Si hubiéramos utilizado instrucciones SQL ad hoc, al volver a ejecutar el asistente de configuración de TableAdapter, se habría revertido esta lista de columnas de consulta para coincidir con la lista de columnas de consulta principal, eliminando así la instrucción NTILE de la consulta usada por el método GetProductsWithPriceQuartile.

Cuando se invoca el método de GetProductsWithPriceQuartile capa de acceso a datos, TableAdapter ejecuta el procedimiento almacenado Products_SelectWithPriceQuartile y añade una fila a ProductsDataTable para cada registro devuelto. Los campos de datos devueltos por el procedimiento almacenado se asignan a las columnas de ProductsDataTable. Como se devuelve un campo de datos PriceQuartile desde el procedimiento almacenado, su valor se asigna a la columna PriceQuartile de ProductsDataTable.

Para los métodos de TableAdapter cuyas consultas no devuelven un campo de datos PriceQuartile, el valor de la columna PriceQuartile es el valor especificado por su propiedad DefaultValue. Como se muestra en la figura 2, este valor se establece en DBNull, el valor predeterminado. Si prefiere un valor predeterminado diferente, solo tiene que establecer la propiedad DefaultValue en consecuencia. Asegúrese antes de que el valor DefaultValue sea válido según el valor DataType de las columnas (es decir, System.Int32 para la columna PriceQuartile).

En este punto, hemos realizado los pasos necesarios para añadir una columna adicional a una DataTable. Para comprobar que esta columna adicional funciona según lo esperado, vamos a crear una página de ASP.NET que muestre el nombre, el precio y el cuartil de cada producto. Sin embargo, antes de hacerlo, primero es necesario actualizar la capa de lógica empresarial para incluir un método que llame al método GetProductsWithPriceQuartile de DAL. A continuación, actualizaremos el BLL, en el paso 3, y crearemos la página de ASP.NET, en el paso 4.

Paso 3: Aumentar la capa de lógica empresarial

Para poder usar el nuevo método GetProductsWithPriceQuartile de la capa de presentación, primero debemos añadir un método correspondiente al BLL. Abra el archivo de clase ProductsBLLWithSprocs y añada el siguiente código:

<System.ComponentModel.DataObjectMethodAttribute_
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceQuartile() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceQuartile()
End Function

Al igual que los otros métodos de recuperación de datos en ProductsBLLWithSprocs, el método GetProductsWithPriceQuartile simplemente llama al método GetProductsWithPriceQuartile correspondiente de DAL y devuelve sus resultados.

Paso 4: Mostrar la información de cuartil de precios en una página web de ASP.NET

Con la adición de BLL completa, estamos listos para crear una página de ASP.NET que muestre el cuartil de precios de cada producto. Abra la página AddingColumns.aspx en la carpeta AdvancedDAL, arrastre un control GridView desde el cuadro de herramientas al Diseñador y establezca su propiedad ID en Products. En la etiqueta inteligente de GridView, enlácela a un nuevo ObjectDataSource denominado ProductsDataSource. Configure ObjectDataSource para que utilice el método GetProductsWithPriceQuartile de la clase ProductsBLLWithSprocs. Como se trata de una cuadrícula de solo lectura, establezca las listas desplegables de las pestañas UPDATE, INSERT y DELETE en (Ninguno).

Figura 9: Configurar ObjectDataSource para usar la clase ProductsBLLWithSprocs (haga clic aquí para ver la imagen a tamaño completo)

Figura 10: Recuperar información del producto del método GetProductsWithPriceQuartile (haga clic aquí para ver la imagen a tamaño completo)

Después de completar el asistente para configurar orígenes de datos, Visual Studio añadirá automáticamente un BoundField o CheckBoxField a GridView para cada uno de los campos de datos devueltos por el método. Uno de estos campos de datos es PriceQuartile, que es la columna que hemos añadido a ProductsDataTable en el paso 1.

Edite los campos de GridView, eliminando todos los campos, excepto los BoundFields ProductName, UnitPrice y PriceQuartile. Configure el BoundField UnitPrice para dar formato a su valor como una moneda y que los BoundFields UnitPrice y PriceQuartile estén alineados a la derecha y al centro, respectivamente. Por último, actualice las propiedades HeaderText de los BoundFields restantes como Product, Price y Price Quartile, respectivamente. Asimismo, active la casilla Habilitar ordenación en la etiqueta inteligente de GridView.

Después de estas modificaciones, el marcado declarativo de GridView y ObjectDataSource debe ser similar al siguiente:

<asp:GridView ID="Products" runat="server" AllowSorting="True"
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="ProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="Product" 
            SortExpression="ProductName" />
        <asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}" 
            HeaderText="Price" HtmlEncode="False" 
            SortExpression="UnitPrice">
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="PriceQuartile" HeaderText="Price Quartile" 
            SortExpression="PriceQuartile">
            <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetProductsWithPriceQuartile" 
    TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

En la figura 11, se muestra esta página cuando se visita en un explorador. Tenga en cuenta que, inicialmente, los productos se clasifican por su precio en orden descendente, y se asigna a cada producto un valor PriceQuartile correspondiente. Por supuesto, estos datos se pueden ordenar por otros criterios, mientras que el valor de la columna Price Quartile seguirá reflejando la clasificación del producto según el precio (véase la figura 12).

Figura 11: Los productos están ordenados por su precio (haga clic aquí para ver la imagen a tamaño completo)

Figura 12: Los productos están ordenados por su nombre (haga clic aquí para ver la imagen a tamaño completo)

Nota:

Con algunas líneas de código, podríamos aumentar GridView para que coloree las filas del producto en función de su valor de PriceQuartile. Podríamos colorear esos productos en el primer cuartil con un verde claro, los del segundo cuartil con un amarillo claro, etc. Le animamos a que dedique unos instantes a añadir esta funcionalidad. Si necesita recordar cómo dar formato a una GridView, consulte el tutorial Formato personalizado basado en datos.

Un enfoque alternativo: crear otro TableAdapter

Como hemos visto en este tutorial, cuando se añade un método a un TableAdapter que devuelve campos de datos distintos de los especificados por la consulta principal, podemos añadir las columnas correspondientes a la DataTable. No obstante, este enfoque solo funciona bien si hay un pequeño número de métodos en TableAdapter que devuelven campos de datos diferentes y si esos campos de datos alternativos no varían demasiado de la consulta principal.

En lugar de añadir columnas a DataTable, puede agregar otro TableAdapter al DataSet que contenga los métodos del primer TableAdapter que devuelve campos de datos diferentes. En este tutorial, en lugar de añadir la columna PriceQuartile a ProductsDataTable (donde solo la usa el método GetProductsWithPriceQuartile), podríamos haber añadido un TableAdapter adicional al DataSet denominado ProductsWithPriceQuartileTableAdapter que usaba el procedimiento almacenado Products_SelectWithPriceQuartile como consulta principal. Las páginas de ASP.NET que debían obtener información del producto con el cuartil de precios usarían ProductsWithPriceQuartileTableAdapter, mientras que las que no podrían seguir usando ProductsTableAdapter.

Al añadir un nuevo TableAdapter, las DataTables no se ven perjudicadas y sus columnas reflejan con precisión los campos de datos devueltos por sus métodos de tableAdapter. No obstante, la adición de TableAdapters puede introducir tareas y funcionalidades repetitivas. Por ejemplo, si esas páginas de ASP.NET que mostraban la columna PriceQuartile también necesitaban proporcionar compatibilidad para insertar, actualizar y eliminar, ProductsWithPriceQuartileTableAdapter debería tener sus propiedades InsertCommand, UpdateCommandy DeleteCommand debidamente configuradas. Aunque estas propiedades reflejarán los ProductsTableAdapter, esta configuración introduce un paso adicional. Además, ahora hay dos maneras de actualizar, eliminar o agregar un producto a la base de datos: a través de las clases ProductsTableAdapter y ProductsWithPriceQuartileTableAdapter.

La descarga de este tutorial incluye una clase ProductsWithPriceQuartileTableAdapter en el DataSet NorthwindWithSprocs que ilustra este enfoque alternativo.

Resumen

En la mayoría de los escenarios, todos los métodos de un TableAdapter devolverán el mismo conjunto de campos de datos, pero hay casos en los que un método determinado o dos pueden necesitar devolver un campo adicional. Por ejemplo, en el tutorial Maestro y detalles cuando se utiliza una lista con viñetas de registros maestros con una DataList de detalles, hemos añadido un método a CategoriesTableAdapter que, además de los campos de datos de la consulta principal, devuelve un campo NumberOfProducts que notifica el número de productos asociados a cada categoría. En este tutorial, hemos visto cómo añadir un método en ProductsTableAdapter que devuelve un campo PriceQuartile además de los campos de datos de la consulta principal. Para capturar campos de datos adicionales devueltos por los métodos de TableAdapter, es necesario añadir las columnas correspondientes a DataTable.

Si tiene previsto añadir columnas manualmente a DataTable, se recomienda que TableAdapter use los procedimientos almacenados. Si TableAdapter usa instrucciones SQL ad hoc, siempre que se ejecute el asistente de configuración de TableAdapter, todas las listas de campos de datos de métodos se revertirán a los campos de datos devueltos por la consulta principal. Este problema no se produce con los procedimientos almacenados, motivo por el que se recomiendan y se usan en este tutorial.

¡Feliz programación!

Acerca del autor

Scott Mitchell, autor de siete libros de ASP/ASP.NET y fundador de 4GuysFromRolla.com, ha estado trabajando con tecnologías web de Microsoft desde 1998. Scott trabaja como consultor independiente, entrenador y escritor. Su último libro es Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Puede ponerse en contacto con él via mitchell@4GuysFromRolla.com. o a través de su blog, que se puede encontrar en http://ScottOnWriting.NET.

Agradecimientos especiales a

Muchos revisores han evaluado esta serie de tutoriales. Los revisores principales de este tutorial han sido Randy Schmidt, Jacky Goor, Bernadette Leigh y Hilton Giesenow. ¿Le interesa revisar mis próximos artículos de MSDN? Si es así, escríbame a mitchell@4GuysFromRolla.com.

AnteriorSiguiente