Adicionando colunas adicionais ao DataTable (C#)

por Scott Mitchell

Baixar PDF

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

Introdução

Ao adicionar um TableAdapter a um Conjunto de Dados Tipado, o esquema de DataTable correspondente é determinado pela consulta principal do TableAdapter. Por exemplo, se a consulta principal retornar os 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, 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 do DataTable refletir a consulta principal do TableAdapter funciona bem se todos os métodos do TableAdapter retornarem o mesmo número ou menos campos de dados do que aqueles especificados na consulta principal. Se um método TableAdapter precisar retornar campos de dados adicionais, devemos expandir o esquema do DataTable adequadamente. No tutorial Mestre/Detalhe Usando uma Lista com Marcadores de Registros Mestres com um DataList de Detalhes, adicionamos um método ao CategoriesTableAdapter que retornou os campos de dados CategoryID, CategoryName e Description definidos na consulta principal, além de NumberOfProducts, um campo de dados adicional que relatava o número de produtos associados a cada categoria. Adicionamos manualmente uma nova coluna no CategoriesDataTable para capturar o valor do campo de dados NumberOfProducts por meio desse novo método.

Conforme discutido no tutorial Carregar Arquivos , é necessário ter muito cuidado com Os 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 do TableAdapter for executado novamente, ele atualizará todos os métodos do TableAdapter para que a lista de campos de dados corresponda à consulta principal. Consequentemente, todos os métodos com listas de colunas personalizadas serão revertidos 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, examinaremos como estender um esquema do DataTable para incluir colunas adicionais. Devido à fragilidade do TableAdapter ao usar instruções SQL ad hoc, neste tutorial usaremos procedimentos armazenados. Consulte o tutorial Criando novos procedimentos armazenados para os TableAdapters do Conjunto de Dados Tipado para obter mais informações sobre como configurar um TableAdapter para usar procedimentos armazenados.

Etapa 1: adicionando umaPriceQuartilecoluna aoProductsDataTable

No tutorial "Criando Novos Procedimentos Armazenados para o TableAdapters do Conjunto de Dados Tipado" criamos um Conjunto de Dados Tipado chamado NorthwindWithSprocs. Atualmente, esse DataSet contém duas DataTables: ProductsDataTable e EmployeesDataTable. O ProductsTableAdapter possui os três métodos a seguir:

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

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, a ProductsDataTable coluna tem uma coluna correspondente para cada campo na Products tabela.

Para este tutorial, vamos adicionar um método ao ProductsTableAdapter nomeado GetProductsWithPriceQuartile que retorna todos os produtos. Além dos campos de dados do produto padrão, GetProductsWithPriceQuartile também incluirá um PriceQuartile campo de dados que indica em qual quartil o preço do produto cai. Por exemplo, aqueles 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 que armazene os resultados de PriceQuartile quando o método GetProductsWithPriceQuartile for 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 exibir a imagem em tamanho real)

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

Figura 2: Definir as novas colunas Name e DataType propriedades (clique para exibir 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 for 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 definidos como padrões.

Etapa 2: Criando oGetProductsWithPriceQuartilemétodo

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

Figura 3: Instrua o Assistente tableAdapter a criar o procedimento armazenado para nós (clique para exibir 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 Products tabela, selecione a opção SELECT que retorna linhas e clique em Avançar.

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

Em seguida, somos solicitados pela 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 em que os grupos são determinados pelos UnitPrice 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 no assistente sem usar o Construtor de Consultas.

Observação

Para obter mais informações sobre as outras funções de classificação do NTILE e 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 solicita que forneçamos 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 exibir a imagem em tamanho real)

Por fim, somos solicitados a nomear os métodos TableAdapter. Mantenha marcadas as caixas de seleção Preencher uma DataTable e Retornar uma DataTable e nomeie os métodos FillWithPriceQuartile e GetProductsWithPriceQuartile.

Figura 6: Nomeie os métodos tableAdapter e clique em Concluir (clique para exibir a imagem em tamanho real)

Com a SELECT consulta especificada, e com o procedimento armazenado e os métodos TableAdapter já nomeados, clique em Finalizar para concluir o assistente. Neste ponto, você pode receber um aviso ou dois do assistente dizendo que OVER não há suporte para o constructo ou instrução SQL. 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 Procedimentos Armazenados 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 Products_SelectWithPriceQuartile procedimento armazenado (clique para exibir a imagem em tamanho real)

Observação

Um dos benefícios de usar procedimentos armazenados em vez de instruções SQL ad hoc é que executar novamente o assistente de Configuração 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 exiba o Products_SelectWithPriceQuartile procedimento armazenado. Observe que sua lista de colunas não foi modificada. Se tivéssemos usado instruções SQL ad hoc, executar o assistente de Configuração do TableAdapter novamente teria revertido essa lista de colunas da consulta para corresponder à lista de colunas da consulta principal, removendo assim a instrução NTILE da consulta usada pelo método GetProductsWithPriceQuartile.

Quando o método da Camada de GetProductsWithPriceQuartile Acesso a Dados é 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 representadas por 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 em que as consultas não retornam um campo de dados PriceQuartile, o valor da coluna PriceQuartile é o valor especificado pela sua propriedade DefaultValue. Como mostra a Figura 2, esse valor é definido como DBNull, o padrão. Se você preferir um valor padrão diferente, basta definir a DefaultValue propriedade adequadamente. Apenas verifique se o DefaultValue valor é válido considerando as colunas 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 ASP.NET que exibe o nome, o preço e o quartil de preço de cada produto. Antes de fazermos isso, porém, primeiro precisamos atualizar a Camada lógica de negócios para incluir um método que exige o método da GetProductsWithPriceQuartile DAL. Atualizaremos a BLL em seguida, na Etapa 3, e criaremos a página ASP.NET na Etapa 4.

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

Antes de usarmos o novo GetProductsWithPriceQuartile método da Camada de Apresentação, primeiro devemos 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 NorthwindWithSprocs.ProductsDataTable GetProductsWithPriceQuartile()
{
    return Adapter.GetProductsWithPriceQuartile();
}

Assim como os outros métodos de recuperação de dados, ProductsBLLWithSprocso GetProductsWithPriceQuartile método simplesmente chama o método correspondente GetProductsWithPriceQuartile do 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 de 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 sua propriedade ID como Products. No tag inteligente do GridView, vincule-a a um novo ObjectDataSource chamado ProductsDataSource. Configure o ObjectDataSource para usar o ProductsBLLWithSprocs método da GetProductsWithPriceQuartile classe. Como esta será uma grade de leitura somente, configure as listas suspensas nas guias UPDATE, INSERT e DELETE como (Nenhuma).

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

Figura 10: Recuperar informações do produto do GetProductsWithPriceQuartile método (clique para exibir 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 etapa 1.

Edite os campos de GridView, removendo todos, exceto o ProductName, UnitPrice e PriceQuartile BoundFields. Configure o UnitPrice BoundField para formatar seu valor como moeda e ter o UnitPrice BoundFields PriceQuartile alinhado à direita e ao centro, respectivamente. Por fim, atualize as propriedades BoundFields restantes HeaderText para Produto, Preço e Quartil de Preço, respectivamente. Além disso, marque a caixa de seleção Habilitar Classificação na marca inteligente do GridView.

Após essas modificações, a marcação declarativa de GridView e ObjectDataSource deve ser semelhante à seguinte:

<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 por meio de um navegador. Observe que, inicialmente, os produtos são ordenados por 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 (Consulte Figura 12).

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

Figura 12: Os produtos são ordenados por seus nomes (clique para exibir 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 um verde claro, aqueles no segundo quartil um amarelo claro, e assim por diante. Eu o encorajo a ter um momento para adicionar essa funcionalidade. Se você precisar de uma revisão da formatação de um GridView, consulte o tutorial Formatação Personalizada Baseada 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. Essa abordagem, no entanto, funciona bem somente 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 à 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 PriceQuartile coluna ao ProductsDataTable (em que ela é usada apenas pelo GetProductsWithPriceQuartile método), poderíamos ter adicionado um TableAdapter adicional ao DataSet nomeado ProductsWithPriceQuartileTableAdapter que usava o Products_SelectWithPriceQuartile procedimento armazenado como sua consulta principal. ASP.NET páginas que precisavam obter informações do produto com o quartil de preço usariam o ProductsWithPriceQuartileTableAdapter, enquanto as que não precisavam poderiam continuar a usar o ProductsTableAdapter.

Ao adicionar um novo TableAdapter, os DataTables permanecem intactos, e suas colunas espelham precisamente os campos de dados retornados por seus métodos TableAdapter. No entanto, TableAdapters adicionais podem introduzir tarefas repetitivas e funcionalidades. Por exemplo, se essas páginas ASP.NET que exibiam a PriceQuartile coluna também precisarem fornecer suporte de inserção, atualização e exclusão, será necessário configurar corretamente as propriedades ProductsWithPriceQuartileTableAdapter, InsertCommand e UpdateCommand. Embora essas propriedades espelhassem os 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 – por meio das classes ProductsTableAdapter e ProductsWithPriceQuartileTableAdapter.

O download deste 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 Master/Detail usando uma lista com marcadores de registros mestre com um DataList de detalhes, adicionamos um método ao CategoriesTableAdapter, o qual, além dos campos de dados da consulta principal, retornava um campo NumberOfProducts que indicava o número de produtos associados a cada categoria. Neste tutorial, analisamos como adicionar um método dentro de ProductsTableAdapter que retornava um campo PriceQuartile além dos campos de dados da consulta principal. Para capturar campos de dados adicionais retornados pelos métodos tableAdapter, precisamos adicionar colunas correspondentes à DataTable.

Se você planeja adicionar manualmente colunas 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 dos métodos serão revertidas para os campos de dados retornados pela consulta principal. Esse problema não se estende aos procedimentos armazenados, razão pela qual eles são recomendados e usados neste tutorial.

Divirta-se programando!

Sobre o autor

Scott Mitchell, autor de sete livros asp/ASP.NET e fundador da 4GuysFromRolla.com, trabalha com tecnologias da Microsoft Web desde 1998. Scott trabalha como consultor independente, treinador e escritor. Seu último livro é Sams Teach Yourself ASP.NET 2.0 em 24 Horas. Ele pode ser alcançado em mitchell@4GuysFromRolla.com.

Agradecimentos Especiais 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 revisar meus próximos artigos do MSDN? Se assim for, deixe-me uma linha em mitchell@4GuysFromRolla.com.

AnteriorPróximo