Agregar columnas adicionales de DataTable (VB)

de Scott Mitchell

Descargar PDF

Cuando se usa el Asistente para TableAdapter para crear un Typed DataSet, el DataTable correspondiente contiene las columnas devueltas por la consulta principal de la base de datos. Pero 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

Al agregar un TableAdapter a un DataSet tipado, el esquema del DataTable correspondiente queda determinado por la consulta principal del TableAdapter. Por ejemplo, si la consulta principal devuelve campos de datos A, B y C, DataTable tendrá tres columnas correspondientes denominadas A, B y C. Además de su consulta principal, un TableAdapter puede incluir consultas adicionales que devuelven, quizás, un subconjunto de los datos basados en algún parámetro. Por ejemplo, además de la ProductsTableAdapter consulta principal, 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 tener el esquema de DataTable refleja que 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 TableAdapter necesita devolver campos de datos adicionales, deberíamos expandir el esquema de DataTable en consecuencia. En el tutorial Master/Detail Using a Bulleted List of Master Records with a Details DataList se ha agregado un método al CategoriesTableAdapter que devolvió los CategoryIDcampos de datos , CategoryNamey Description definidos en la consulta principal más NumberOfProducts, un campo de datos adicional que notificó el número de productos asociados a cada categoría. Hemos agregado manualmente una nueva columna al CategoriesDataTable para capturar el valor del NumberOfProducts campo de datos de este nuevo método.

Como se describe en el tutorial Carga de archivos , se debe tener gran cuidado con TableAdapters que usan instrucciones SQL ad hoc y tienen métodos cuyos campos de datos no coinciden con la consulta principal. Si el Asistente para configuración de TableAdapter se vuelve a ejecutar, 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 surge al usar procedimientos almacenados.

En este tutorial veremos cómo ampliar un esquema de DataTable para incluir columnas adicionales. Debido a la frágilidad de TableAdapter al usar instrucciones SQL ad hoc, en este tutorial usaremos procedimientos almacenados. Consulte los tutoriales Creación de nuevos procedimientos almacenados para los TableAdapters de conjuntos de datos tipados y Uso de procedimientos almacenados existentes para los TableAdapters de conjuntos de datos tipados para obtener más información sobre cómo configurar los TableAdapters para usar procedimientos almacenados.

Paso 1: Agregar unaPriceQuartilecolumna alProductsDataTable

En el tutorial Creating New Stored Procedures for the Typed DataSet s TableAdapters (Crear nuevos procedimientos almacenados para tableAdapters de DataSet con tipo ), creamos un conjunto de datos con tipo denominado NorthwindWithSprocs. Este conjunto de datos 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 Products tabla.
• GetProductsByCategoryID(categoryID) : devuelve todos los productos con el valor 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 Products tabla. No hay subconsultas correlacionadas ni elementos de JOIN que extraigan datos relacionados de las tablas Categories o Suppliers. Por lo tanto, el ProductsDataTable tiene una columna correspondiente para cada campo en la tabla Products.

En este tutorial, vamos a agregar 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 PriceQuartile campo de datos que indica en qué cuartil cae el precio del producto. Por ejemplo, aquellos productos cuyos precios están en el más caro 25% tendrán un PriceQuartile valor de 1, mientras que aquellos cuyos precios caen en la parte inferior de 25% tendrán un valor de 4. Antes de preocuparnos por crear el procedimiento almacenado para devolver esta información, primero es necesario actualizar para incluir ProductsDataTable una columna para contener los resultados de PriceQuartile cuando el método GetProductsWithPriceQuartile se usa.

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

Figura 1: Agregar una nueva columna a ProductsDataTable (haga clic para ver la imagen de tamaño completo)

Esto agregará una nueva columna a la tabla de datos denominada Column1 de tipo System.String. Es necesario actualizar este nombre de 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 recién agregada en ProductsDataTable y, en la ventana Propiedades, establezca la Name propiedad en PriceQuartile y la DataType propiedad en System.Int32.

Figura 2: Establecer las nuevas columnas Name y DataType propiedades (haga clic para ver la imagen de tamaño completo)

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

Paso 2: Crear elGetProductsWithPriceQuartilemétodo

Ahora que ProductsDataTable se ha actualizado para incluir la PriceQuartile columna, estamos listos para crear el GetProductsWithPriceQuartile método . Para empezar, haga clic con el botón derecho en TableAdapter y elija Agregar consulta en el menú contextual. Esto abre el Asistente para 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 devuelve 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 para TableAdapter que cree el procedimiento almacenado para nosotros (haga clic para ver la imagen de tamaño completo)

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

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

A continuación, se le solicitará la SELECT consulta. Escriba la consulta siguiente 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 NTILE función de SQL Server 2005 para dividir los resultados en cuatro grupos en los que los grupos están determinados por los UnitPrice valores ordenados en orden descendente.

Desafortunadamente, el Generador de consultas no sabe cómo analizar la OVER palabra clave y mostrará un error al analizar la consulta anterior. Por lo tanto, escriba 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 NTILE y otras funciones de clasificación de SQL Server 2005, vea ROW_NUMBER (Transact-SQL) y la sección Funciones de clasificación de la Documentación en Línea de SQL Server 2005.

Después de escribir la SELECT consulta y hacer clic en Siguiente, el asistente nos pide que proporcione un nombre para el procedimiento almacenado que creará. Asigne un nombre al nuevo procedimiento Products_SelectWithPriceQuartile almacenado y haga clic en Siguiente.

Figura 5: Asignar un nombre al procedimiento Products_SelectWithPriceQuartile almacenado (hacer clic para ver la imagen de tamaño completo)

Por último, se le pedirá que asigne un nombre a los métodos TableAdapter. Deje activadas las casillas Fill a DataTable y Return a DataTable (Rellenar una tabla de datos) y asigne un nombre a los métodos FillWithPriceQuartile y GetProductsWithPriceQuartile.

Figura 6: Asigne un nombre a los métodos tableAdapter y haga clic en Finalizar (haga clic para ver la imagen de tamaño completo)

Con la consulta especificada y el SELECT procedimiento almacenado, y los métodos de TableAdapter denominados, haga clic en Finalizar para completar el asistente. En este momento puede recibir una o dos advertencias del asistente indicando que la construcción o instrucción SQL OVER no está admitida. Estas advertencias se pueden omitir.

Después de completar el asistente, el 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 agregado correctamente a la base de datos. Al comprobar 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 agregado un nuevo método a TableAdapter

Figura 8: Asegurarse de que la base de datos contiene el procedimiento almacenado (Products_SelectWithPriceQuartile imagen de tamaño completo)

Nota:

Una de las ventajas de usar procedimientos almacenados en lugar de instrucciones SQL ad hoc es que volver a ejecutar el Asistente para configuración de TableAdapter no modificará las listas de columnas de procedimientos almacenados. Para comprobarlo, haga clic con el botón derecho en 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 Products_SelectWithPriceQuartile procedimiento almacenado. Tenga en cuenta que su lista de columnas no se ha modificado. Si hubiéramos estado utilizando instrucciones SQL ad hoc, al volver a ejecutar el Asistente para la Configuración de TableAdapter habría revertido esta lista de columnas de la consulta para que coincidiera con la lista de columnas de la consulta principal, eliminando así la instrucción NTILE de la consulta utilizada por el método GetProductsWithPriceQuartile.

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

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

En este momento hemos realizado los pasos necesarios para agregar una columna adicional a una DataTable. Para comprobar que esta columna adicional funciona según lo previsto, 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 de negocios para incluir un método que llame al método dal GetProductsWithPriceQuartile . Actualizaremos el BLL siguiente, en el paso 3 y, a continuación, crearemos la página ASP.NET en el paso 4.

Paso 3: Aumentar la capa de lógica de negocios

Antes de usar el nuevo GetProductsWithPriceQuartile método de la capa de presentación, primero deberíamos agregar un método correspondiente al BLL. Abra el ProductsBLLWithSprocs archivo de clase y agregue el código siguiente:

<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 GetProductsWithPriceQuartile método simplemente llama al método correspondiente GetProductsWithPriceQuartile 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 muestra el cuartil de precio para cada producto. Abra la página AddingColumns.aspx en la carpeta AdvancedDAL y arrastre un control GridView desde el cuadro de herramientas al Diseñador, estableciendo su propiedad ID en Products. En la etiqueta inteligente del GridView, vincule a un nuevo ObjectDataSource denominado ProductsDataSource. Configura ObjectDataSource para utilizar la clase ProductsBLLWithSprocs y el método GetProductsWithPriceQuartile. Puesto que se trata de una cuadrícula de solo lectura, establezca las listas desplegables en las pestañas UPDATE, INSERT y DELETE en (Ninguno).

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

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

Después de completar el Asistente para configurar el origen de datos, Visual Studio agregará automáticamente un BoundField o un CheckBoxField a la 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 añadimos al ProductsDataTable en el paso 1.

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

Después de estas modificaciones, el marcado declarativo de GridView y ObjectDataSource debe ser similar a lo 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 a través de un explorador. Tenga en cuenta que, inicialmente, los productos se ordenan por su precio en orden descendente con cada producto asignado un valor adecuado PriceQuartile . Por supuesto, estos datos se pueden ordenar por otros criterios con el valor de la columna Precio cuartil todavía reflejando la clasificación del producto con respecto al precio (véase la figura 12).

Figura 11: Los productos están ordenados por sus precios (haga clic para ver la imagen de tamaño completo)

Figura 12: Los productos están ordenados por sus nombres (haga clic para ver la imagen de tamaño completo)

Nota:

Con unas pocas líneas de código, podríamos ampliar el GridView para que coloree las filas de productos en función de su valor de PriceQuartile. Podríamos colorear esos productos en el primer cuartil un verde claro, aquellos en el segundo cuartil un amarillo claro, etc. Te animamos a que dediques un momento a agregar esta funcionalidad. Si necesita un repaso sobre cómo formatear el GridView, consulte el tutorial Formato personalizado basado en datos.

Un enfoque alternativo: crear otro tableAdapter

Como vimos en este tutorial, al agregar un método a un TableAdapter que devuelve campos de datos distintos de los escritos por la consulta principal, podemos agregar las columnas correspondientes a DataTable. Sin embargo, este enfoque funciona bien solo 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 agregar columnas a DataTable, en su lugar puede agregar otro TableAdapter al DataSet que contiene los métodos del primer TableAdapter que devuelve campos de datos diferentes. En este tutorial, en lugar de agregar la PriceQuartile columna a ProductsDataTable (donde solo la usa el GetProductsWithPriceQuartile método ), podríamos haber agregado un tableAdapter adicional al conjunto de datos denominado ProductsWithPriceQuartileTableAdapter que usó el Products_SelectWithPriceQuartile procedimiento almacenado como consulta principal. Las páginas de ASP.NET que necesitaban obtener información del producto con el cuartil de precios usarían ProductsWithPriceQuartileTableAdapter, mientras que las que no, podían seguir usando ProductsTableAdapter.

Al agregar un nuevo TableAdapter, las DataTables permanecen inalteradas y sus columnas reflejan con precisión los campos de datos devueltos por sus métodos TableAdapter. Sin embargo, los tableAdapters adicionales pueden 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 tendría que tener sus propiedades InsertCommand, UpdateCommand y DeleteCommand correctamente configuradas. Aunque estas propiedades reflejarían los ProductsTableAdapter s, esta configuración presenta 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 ProductsTableAdapter clases y ProductsWithPriceQuartileTableAdapter .

La descarga de este tutorial incluye una ProductsWithPriceQuartileTableAdapter clase en el NorthwindWithSprocs Conjunto de datos 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 ocasiones en las que un método determinado o dos pueden necesitar devolver un campo adicional. Por ejemplo, en el tutorial Master/Detail Using a Bulleted List of Master Records with a Details DataList se ha agregado un método al CategoriesTableAdapter que, además de los campos de datos de la consulta principal, devolvió un NumberOfProducts campo que notificó el número de productos asociados a cada categoría. En este tutorial, vimos cómo agregar un método en ProductsTableAdapter que devuelve un PriceQuartile campo, 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 agregar las columnas correspondientes a DataTable.

Si planea agregar columnas manualmente a DataTable, se recomienda que TableAdapter use procedimientos almacenados. Si el TableAdapter utiliza instrucciones SQL ad hoc, cada vez que se ejecute el Asistente para la configuración de TableAdapter, todas las listas de campos de datos de los métodos se revertirán a los campos de datos devueltos por la consulta principal. Este problema no se extiende a los procedimientos almacenados, por lo 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 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. Los revisores principales de este tutorial fueron Randy Schmidt, Jacky Goor, Bernadette Leigh y Hilton Giesenow. ¿Le interesa revisar mis próximos artículos de MSDN? Si es así, mándame un mensaje a mitchell@4GuysFromRolla.com.

Siguiente anterior