Adicionando colunas DataTable adicionais (VB)

por Scott Mitchell

Descarregar PDF

Ao usar o Assistente de TableAdapter para criar um Conjunto de Dados Tipado, o DataTable correspondente contém as colunas retornadas pela consulta principal ao banco de dados. Mas há ocasiões em que o DataTable precisa incluir colunas adicionais. Neste tutorial, aprendemos por que os procedimentos armazenados são recomendados quando precisamos de colunas DataTable adicionais.

Introdução

Ao adicionar um TableAdapter a um DataSet tipado, o esquema do DataTable correspondente é determinado pela consulta principal do TableAdapter. Por exemplo, se a consulta principal retornar campos de dados A, B e C, a DataTable terá três colunas correspondentes chamadas A, B e C. Além de sua consulta principal, um TableAdapter pode incluir consultas adicionais que retornam, talvez, um subconjunto dos dados com base em algum parâmetro. Por exemplo, além da ProductsTableAdapter consulta principal s, que retorna informações sobre todos os produtos, ela também contém métodos como GetProductsByCategoryID(categoryID) e GetProductByProductID(productID), que retornam informações específicas do produto com base em um parâmetro fornecido.

O modelo de ter o esquema de DataTable s refletir a consulta principal de TableAdapter funciona bem se todos os métodos de TableAdapter retornarem os mesmos ou menos campos de dados do que os especificados na consulta principal. Se um método TableAdapter precisar retornar campos de dados adicionais, expandiremos o esquema de DataTable de acordo. No tutorial Master/Detail Using a Bulleted List of Master Records with a Details DataList, adicionámos um método ao CategoriesTableAdapter que devolvia os campos de dados CategoryID, CategoryName, e Description definidos na consulta principal, além de NumberOfProducts, um campo de dados adicional que indicava o número de produtos associados a cada categoria. Adicionamos manualmente uma nova coluna ao CategoriesDataTable para capturar o NumberOfProducts valor do campo de dados desse novo método.

Conforme discutido no tutorial Carregamento de Ficheiros, muito cuidado deve ser tomado com TableAdapters que usam instruções SQL ad-hoc e têm métodos cujos campos de dados não correspondem precisamente à consulta principal. Se o assistente de configuração de TableAdapter for executado novamente, ele atualizará todos os métodos de TableAdapter para que sua lista de campos de dados corresponda à consulta principal. Consequentemente, quaisquer métodos com listas de colunas personalizadas reverterão para a lista de colunas da consulta principal e não retornarão os dados esperados. Esse problema não surge ao usar procedimentos armazenados.

Neste tutorial, veremos como estender um esquema de DataTable para incluir colunas adicionais. Devido à fragilidade do TableAdapter ao usar instruções SQL ad-hoc, neste tutorial usaremos procedimentos armazenados. Consulte os tutoriais Criar novos procedimentos armazenados para os TableAdapters do DataSet Typed e Usar procedimentos armazenados existentes para os TableAdapters do DataSet Typed para obter mais informações sobre como configurar um TableAdapter para utilizar procedimentos armazenados.

Etapa 1: Adicionando umaPriceQuartilecoluna aoProductsDataTable

No tutorial Criando novos procedimentos armazenados para os TableAdapters do Conjunto de Dados Tipado, criamos um Conjunto de Dados Tipado chamado NorthwindWithSprocs. Este DataSet atualmente contém duas DataTables: ProductsDataTable e EmployeesDataTable. O ProductsTableAdapter tem os seguintes três métodos:

• GetProducts - a consulta principal, que retorna todos os registros da Products tabela
• GetProductsByCategoryID(categoryID) - devolve todos os produtos com o categoryID especificado.
• GetProductByProductID(productID) - devolve o produto específico com o productID especificado.

A consulta principal e os dois métodos adicionais retornam o mesmo conjunto de campos de dados, ou seja, todas as colunas da Products tabela. Não há subconsultas correlacionadas ou JOIN s extraindo dados relacionados das tabelas Categories ou Suppliers. Portanto, o ProductsDataTable tem uma coluna correspondente para cada campo na Products tabela.

Para este tutorial, vamos adicionar um método ao ProductsTableAdapter nome GetProductsWithPriceQuartile que retorna todos os produtos. Além dos campos de dados padrão, GetProductsWithPriceQuartile também incluirá um campo de PriceQuartile que indica em qual quartil se enquadra o preço do produto. Por exemplo, os produtos cujos preços estão nos 25% mais caros terão um PriceQuartile valor de 1, enquanto aqueles cujos preços caem nos 25% inferiores terão um valor de 4. Antes de nos preocuparmos em criar o procedimento armazenado para retornar essas informações, no entanto, primeiro precisamos atualizar o ProductsDataTable para incluir uma coluna para armazenar os PriceQuartile resultados quando o GetProductsWithPriceQuartile método é usado.

Abra o NorthwindWithSprocs DataSet e clique com o botão direito do ProductsDataTable. Escolha Adicionar no menu de contexto e, em seguida, escolha Coluna.

Figura 1: Adicionar uma nova coluna à ProductsDataTable (Clique para visualizar a imagem em tamanho real)

Isso adicionará uma nova coluna à DataTable chamada Column1 do tipo System.String. Precisamos atualizar o nome desta coluna para PriceQuartile e seu tipo para System.Int32 uma vez que ele será usado para manter um número entre 1 e 4. Selecione a coluna recém-adicionada no ProductsDataTable e, na janela de Propriedades, defina a propriedade Name como PriceQuartile e a propriedade DataType como System.Int32.

Figura 2: Definir as novas Name colunas e DataType propriedades (Clique para visualizar a imagem em tamanho real)

Como mostra a Figura 2, há propriedades adicionais que podem ser definidas, como se os valores na coluna devem ser exclusivos, se a coluna é uma coluna de incremento automático, se os valores de banco de dados NULL são permitidos ou não, e assim por diante. Deixe esses valores nos seus valores padrão.

Etapa 2: Criando oGetProductsWithPriceQuartilemétodo

Agora que o ProductsDataTable foi atualizado para incluir a PriceQuartile coluna, estamos prontos para criar o GetProductsWithPriceQuartile método. Comece clicando com o botão direito do mouse no TableAdapter e escolhendo Adicionar consulta no menu de contexto. Isso abre o assistente de Configuração de Consulta TableAdapter, que primeiro nos pergunta se queremos usar instruções SQL ad-hoc ou um procedimento armazenado novo ou existente. Como ainda não temos um procedimento armazenado que retorne os dados do quartil de preço, permitamos que o TableAdapter crie esse procedimento armazenado para nós. Selecione a opção Criar novo procedimento armazenado e clique em Avançar.

Figura 3: Instrua o Assistente TableAdapter a criar o procedimento armazenado para nós (Clique para visualizar a imagem em tamanho real)

Na tela subsequente, mostrada na Figura 4, o assistente nos pergunta que tipo de consulta adicionar. Como o GetProductsWithPriceQuartile método retornará todas as colunas e registros da tabela, selecione a Products opção SELECT que retorna linhas e clique em Next.

Figura 4: Nossa consulta será uma SELECT instrução que retorna várias linhas (Clique para visualizar a imagem em tamanho real)

Em seguida, somos solicitados a introduzir a consulta SELECT. Insira a seguinte consulta no assistente:

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

A consulta acima usa a nova NTILE função do SQL Server 2005 para dividir os resultados em quatro grupos, onde os UnitPrice grupos são determinados pelos valores classificados em ordem decrescente.

Infelizmente, o Construtor de Consultas não sabe como analisar a OVER palavra-chave e exibirá um erro ao analisar a consulta acima. Portanto, insira a consulta acima diretamente na caixa de texto do assistente sem usar o Construtor de Consultas.

Observação

Para obter mais informações sobre NTILE e outras funções de classificação do SQL Server 2005, consulte ROW_NUMBER (Transact-SQL) e a seção Funções de classificação dos Manuais Online do SQL Server 2005.

Depois de inserir a SELECT consulta e clicar em Avançar, o assistente nos pede para fornecer um nome para o procedimento armazenado que ele criará. Nomeie o novo procedimento Products_SelectWithPriceQuartile armazenado e clique em Avançar.

Figura 5: Nomeie o procedimento Products_SelectWithPriceQuartile armazenado (Clique para visualizar a imagem em tamanho real)

Por fim, somos solicitados a nomear os métodos TableAdapter. Deixe as caixas de seleção Fill a DataTable e Return a DataTable marcadas e nomeie os métodos FillWithPriceQuartile e GetProductsWithPriceQuartile.

Figura 6: Nomeie os métodos do TableAdapter e clique em Concluir (Clique para visualizar a imagem em tamanho normal)

Com a consulta SELECT especificada e nomeados o procedimento armazenado e os métodos TableAdapter, clique em Concluir para finalizar o assistente. Neste ponto, você pode receber um ou dois avisos do assistente dizendo que a OVER construção ou instrução SQL não é suportada. Esses avisos podem ser ignorados.

Depois de concluir o assistente, o TableAdapter deve incluir os métodos FillWithPriceQuartile e GetProductsWithPriceQuartile, e o banco de dados deve incluir um procedimento armazenado chamado Products_SelectWithPriceQuartile. Reserve um momento para verificar se o TableAdapter realmente contém esse novo método e se o procedimento armazenado foi adicionado corretamente ao banco de dados. Ao verificar o banco de dados, se você não vir o procedimento armazenado, tente clicar com o botão direito do mouse na pasta Stored Procedures e escolher Atualizar.

Figura 7: Verificar se um novo método foi adicionado ao TableAdapter

Figura 8: Verifique se o banco de dados contém o procedimento armazenado (Products_SelectWithPriceQuartile imagem em tamanho real)

Observação

Um dos benefícios de usar procedimentos armazenados em vez de instruções SQL ad-hoc é que a nova execução do assistente de Configuração de TableAdapter não modificará as listas de colunas de procedimentos armazenados. Verifique isso clicando com o botão direito do mouse no TableAdapter, escolhendo a opção Configurar no menu de contexto para iniciar o assistente e, em seguida, clicando em Concluir para concluí-lo. Em seguida, vá para o banco de dados e visualize o Products_SelectWithPriceQuartile procedimento armazenado. Observe que sua lista de colunas não foi modificada. Se estivéssemos usando instruções SQL ad-hoc, executar novamente o assistente de Configuração de TableAdapter teria revertido essa lista de colunas de consulta para corresponder à lista de colunas de consulta principal, removendo assim a instrução NTILE da consulta usada pelo GetProductsWithPriceQuartile método.

Quando o método Data Access Layer s GetProductsWithPriceQuartile é invocado, o TableAdapter executa o Products_SelectWithPriceQuartile procedimento armazenado e adiciona uma linha ao ProductsDataTable para cada registro retornado. Os campos de dados retornados pelo procedimento armazenado são mapeados para as colunas ProductsDataTable. Como há um PriceQuartile campo de dados retornado do procedimento armazenado, seu valor é atribuído à ProductsDataTable coluna s PriceQuartile .

Para os métodos TableAdapter cujas consultas não retornam um PriceQuartile campo de dados, o PriceQuartile valor da coluna é o valor especificado por sua DefaultValue propriedade. Como mostra a Figura 2, esse valor é definido como DBNull, o padrão. Se preferir um valor padrão diferente, basta definir a DefaultValue propriedade de acordo. Apenas certifique-se de que o DefaultValue valor é válido dada a coluna s DataType (ou seja, System.Int32 para a PriceQuartile coluna).

Neste ponto, executamos as etapas necessárias para adicionar uma coluna adicional a uma DataTable. Para verificar se essa coluna adicional funciona conforme o esperado, vamos criar uma página de ASP.NET que exiba o nome, o preço e o quartil de preço de cada produto. Antes, porém, de fazermos isso, precisamos primeiro atualizar a Camada de Lógica de Negócios para incluir um método que chame o método GetProductsWithPriceQuartile do DAL. Atualizaremos a BLL em seguida, na Etapa 3, e depois criaremos a página ASP.NET na Etapa 4.

Etapa 3: Aumentando a camada de lógica de negócios

Antes de usarmos o novo GetProductsWithPriceQuartile método da Camada de Apresentação, devemos primeiro adicionar um método correspondente à BLL. Abra o ProductsBLLWithSprocs arquivo de classe e adicione o seguinte código:

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

Como os outros métodos de recuperação de dados no ProductsBLLWithSprocs, o GetProductsWithPriceQuartile método simplesmente chama o método correspondente GetProductsWithPriceQuartile da DAL e retorna seus resultados.

Etapa 4: Exibindo as informações do quartil de preço em uma página da Web ASP.NET

Com a adição da BLL concluída, estamos prontos para criar uma página de ASP.NET que mostra o quartil de preço para cada produto. Abra a página AddingColumns.aspx na pasta AdvancedDAL e arraste um GridView da Caixa de Ferramentas para o Designer, definindo a sua propriedade ID como Products. Na tag inteligente do GridView, vincule-o a um novo ObjectDataSource chamado ProductsDataSource. Configure o ObjectDataSource para usar o método s da ProductsBLLWithSprocsGetProductsWithPriceQuartile classe. Como se trata de uma grelha apenas de leitura, configure as listas pendentes nas abas UPDATE, INSERT e DELETE como (Sem).

Figura 9: Configurar o ObjectDataSource para usar a classe (ProductsBLLWithSprocs imagem em tamanho real)

Figura 10: Recuperar informações do produto a partir do GetProductsWithPriceQuartile método (Clique para visualizar a imagem em tamanho real)

Depois de concluir o assistente Configurar Fonte de Dados, o Visual Studio adicionará automaticamente um BoundField ou CheckBoxField ao GridView para cada um dos campos de dados retornados pelo método. Um desses campos de dados é PriceQuartile, que é a coluna que adicionamos à ProductsDataTable na Etapa 1.

Edite os campos do GridView, removendo todos, exceto os ProductName, UnitPrice e PriceQuartile BoundFields. Configure o campo associado UnitPrice para formatar o seu valor como moeda e ter os campos associados UnitPrice e PriceQuartile alinhados ao centro e à direita, respectivamente. Por fim, atualize as propriedades restantes de BoundFields HeaderText para "Produto," "Preço," e "Quartil de Preço," respectivamente. Além disso, assinale a caixa de seleção Ativar classificação a partir da marca inteligente do GridView.

Após essas modificações, a marcação declarativa de GridView e ObjectDataSource deve ter a seguinte aparência:

<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>

A Figura 11 mostra esta página quando visitada através de um navegador. Note-se que, inicialmente, os produtos são ordenados pelo seu preço em ordem decrescente com cada produto atribuído um valor apropriado PriceQuartile . É claro que esses dados podem ser classificados por outros critérios, com o valor da coluna Quartil de Preço ainda refletindo a classificação do produto em relação ao preço (ver Figura 12).

Figura 11: Os produtos são ordenados por seus preços (Clique para visualizar a imagem em tamanho real)

Figura 12: Os produtos são ordenados por seus nomes (Clique para visualizar a imagem em tamanho real)

Observação

Com algumas linhas de código, poderíamos aumentar o GridView para que ele colorisse as linhas do produto com base em seu PriceQuartile valor. Podemos colorir esses produtos no primeiro quartil de verde claro, os do segundo quartil de amarelo claro e assim por diante. Eu encorajo você a reservar um momento para adicionar esta funcionalidade. Se você precisar de uma atualização sobre a formatação de um GridView, consulte o tutorial Formatação personalizada com base em dados .

Uma abordagem alternativa - Criando outro TableAdapter

Como vimos neste tutorial, ao adicionar um método a um TableAdapter que retorna campos de dados diferentes daqueles explicitados pela consulta principal, podemos adicionar colunas correspondentes à DataTable. Tal abordagem, no entanto, funciona bem apenas se houver um pequeno número de métodos no TableAdapter que retornam campos de dados diferentes e se esses campos de dados alternativos não variarem muito da consulta principal.

Em vez de adicionar colunas ao DataTable, você pode adicionar outro TableAdapter ao DataSet que contém os métodos do primeiro TableAdapter que retornam campos de dados diferentes. Para este tutorial, em vez de adicionar a coluna PriceQuartile ao ProductsDataTable (onde ela é usada apenas pelo método GetProductsWithPriceQuartile), poderíamos ter adicionado um TableAdapter adicional ao DataSet chamado ProductsWithPriceQuartileTableAdapter que utiliza o procedimento armazenado Products_SelectWithPriceQuartile como sua consulta principal. Páginas do ASP.NET que precisassem obter informações sobre o produto com o quartil de preço usariam o ProductsWithPriceQuartileTableAdapter, enquanto aquelas que não precisassem, continuariam a usar o ProductsTableAdapter.

Ao adicionar um novo TableAdapter, as DataTables permanecem intactas e suas colunas refletem com precisão os campos de dados retornados pelos métodos do seu TableAdapter. No entanto, TableAdapters adicionais podem introduzir tarefas e funcionalidades repetitivas. Por exemplo, se essas páginas ASP.NET que exibiam a coluna PriceQuartile também precisassem fornecer suporte a inserção, atualização e exclusão, o ProductsWithPriceQuartileTableAdapter precisaria ter suas propriedades InsertCommand, UpdateCommand e DeleteCommand configuradas corretamente. Embora essas propriedades espelhem o ProductsTableAdapter s, essa configuração introduz uma etapa extra. Além disso, agora há duas maneiras de atualizar, excluir ou adicionar um produto ao banco de dados - através das ProductsTableAdapter classes e ProductsWithPriceQuartileTableAdapter .

O download para este tutorial inclui uma ProductsWithPriceQuartileTableAdapter classe no NorthwindWithSprocs DataSet que ilustra essa abordagem alternativa.

Resumo

Na maioria dos cenários, todos os métodos em um TableAdapter retornarão o mesmo conjunto de campos de dados, mas há momentos em que um ou dois métodos específicos podem precisar retornar um campo adicional. Por exemplo, no tutorial Master/Detail Using a Bulleted List of Master Records with a Details DataList , adicionamos um método ao CategoriesTableAdapter que, além dos campos de dados da consulta principal, retornou um NumberOfProducts campo que relatava o número de produtos associados a cada categoria. Neste tutorial, analisámos a adição de um método no ProductsTableAdapter que retornou um campo PriceQuartile além dos campos de dados da consulta principal. Para capturar campos de dados adicionais retornados pelos métodos de TableAdapter, precisamos adicionar colunas correspondentes ao DataTable.

Se você planeja adicionar colunas manualmente ao DataTable, é recomendável que o TableAdapter use procedimentos armazenados. Se o TableAdapter usar instruções SQL ad-hoc, sempre que o assistente de Configuração do TableAdapter for executado, todas as listas de campos de dados de métodos serão revertidas para os campos de dados retornados pela consulta principal. Esse problema não se estende aos procedimentos armazenados, e é por isso que eles são recomendados e foram usados neste tutorial.

Feliz Programação!

Sobre o Autor

Scott Mitchell, autor de sete livros sobre ASP/ASP.NET e fundador da 4GuysFromRolla.com, trabalha com tecnologias Web da Microsoft desde 1998. Scott trabalha como consultor, formador e escritor independente. Seu último livro é Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Ele pode ser contatado em mitchell@4GuysFromRolla.com.

Um agradecimento especial a

Esta série de tutoriais foi revisada por muitos revisores úteis. Os principais revisores deste tutorial foram Randy Schmidt, Jacky Goor, Bernadette Leigh e Hilton Giesenow. Interessado em rever meus próximos artigos do MSDN? Se for o caso, envie-me uma mensagem para mitchell@4GuysFromRolla.com.

Anterior Seguinte