Introdução

Com o recém-lançado Visual Studio 2010, o C# passou por diversas melhorias. E para quem trabalha com automação do Excel utilizando VSTO (Visual Studio Tools for Office), a situação melhorou muito para o desenvolvedor C#. Até a versão anterior simplesmente não compensava o esforço de se fazer add-ins para Office utilizando C#. Por ser uma linguagem fortemente tipada e com tipagem estática, ao acessar os objetos COM do modelo de objetos do Office, era necessário realizar múltiplos casts para objetos em .NET existentes nos PIAs (Primary Interop Assemblies). Os PIAs, contêm o modelo de objetos de cada aplicação Office (Excel, Word, etc…) representados por “cópias” em .net de objetos COM. Para trabalhar com o modelo de programação do COM o VB.NET sempre foi melhor, permitindo parâmetros opcionais, variants e etc…, coisas até então inexistentes em C#.

O C# 4.0 agora suporta o sistema de tipagem conhecido por “tipagem dinâmica”. A tipagem dinâmica permite que o você chame métodos e propriedades que serão apenas resolvidos em tempo de execução, similar ao que acontece em Javascript. Essa resolução em tempo de execução é mais arriscada, mas, quando você bem o modelo de objetos, permite um código muito limpo, com quase nenhum cast desnecessário.

Na prática, com o C# 4.0, temos o mesmo poder de criar customizações para o Office utilizando a plataforma .NET que o VB.NET já oferecia. Com grande simplicidade, podemos criar aplicações muito legais e mais baratas, afinal, é possível usar todas as vantagens do Excel para manipular meus dados, sem a chatice de criar páginas em Web só para isso…

Nosso Exemplo

Este exemplo tentará ser um pouco mais completo. Vamos utilizar várias tecnologias para conseguir atingir nossos objetivos. Como ferramentas, utilizamos o VS2010 final, o Excel 2010 e o SQL Server 2008. Vamos realizar os seguintes passos:

1) Instalar os bancos de dados de exemplo do SQL Server 2008

2) Utilizar o Entity Framework para mapear os dados para a minha aplicação

3) Criar o serviço WCF que retorna a lista de produtos

4)Realizar testes simples do modelo e serviço

5) Adicionar a referência do serviço WCF ao Excel

6) Trazer os dados para o Excel

7) Trabalhar com os dados utilizando o C# e VSTO

Vamos detalhar cada passo com imagens, resolução de dúvidas mais recorrentes e vamos explicar tudo passo a passo. Vamos pra cima então!

Instalando o AdventureWorks para o SQL Server 2008

Atualmente os bancos de exemplo estão no CodePlex. O link para baixar os bancos de exemplo é: http://msftdbprodsamples.codeplex.com/releases/view/37109. Vale notar que para o exemplo temos instalado o SQL Server 2008 e não o R2, que saiu recentemente. Os bancos de exemplo são criados para cada versão do SQL Server e é importante instalar a versão adequada. Para esse teste, funciona qualquer versão do SQL Server.

Ao instalar, a dica é liberar antes o FILESTREAM para a instância do SQL Server 2008. Se não fizer isso antes, o instalador não coloca todas as versões do AdventureWorks. O instalador é bem inteligente e já faz a instalação de todas as versões do AW. Entre elas, tem uma versão Light, que é a que utilizamos no exemplo deste post.

Criando o modelo de dados com o Entity Framework

O EF nesta versão do .NET está com mais funcionalidades, entre elas, a possibilidade de criar modelo de dados em .NET e depois criar o banco de dados. Mas como já temos o banco de dados pronto, vamos criar um modelo de dados da forma mais simples possível, utilizando as opções padrão.

Antes de começar, criamos um projeto do tipo Class Library contendo uma pasta chamada BusinessEntities. Adicionamos a esta pasta um modelo de dados do EF:

Criamos uma conexão com o banco de dados AdventureWorksLT2008.

Utilizamos estas tabelas do modelo:

O modelo de dados ficou desta maneira:

Até aqui nada demais. Estamos usando o EF para automatizar o acesso a dados, sem grandes mistérios.

Criando o Serviço WCF que retorna a lista de produtos

Para este caso vamos hospedar o serviço no próprio IIS, utilizando as opções padrão do WCF. No caso vamos apenas renomear o serviço e as classes/interfaces para facilitar o uso deste serviço pelos clientes.

Após renomear os serviços, a solução ficou próxima disso:

O nome do serviço é ProductService e o método que traz todos os produtos se chama “GetAllProducts”. Veja o detalhe do código abaixo:

O método retorna uma lista de produtos que é trazida da biblioteca onde está guardado o modelo de dados do EF. Neste caso, abrimos o contexto que contém as entidades, obtemos os dados com o select e com o ForEach das listas genéricas, presente desde o .NET 2.0, utilizamos uma lambda expression que solicita que cada item da lista seja “desatachado” do contexto de dados do EF. Com isso, eu consigo passar a lista de produtos para os clientes remotos. Se eu não fizer o Detach, acontecerão vários erros ao trabalhar com os itens da lista de produtos, como por exemplo, tentativas de se conectar ao store de dados diretamente do cliente.

Vale lembrar que a string de conexão ao model deve ser declarada no arquivo de configuração do serviço. O construtor do AdventureWorkdLT20008Entities, objeto de contexto do EF responsável por controlar as operações com as entidades, sempre olha o arquivo de configuração para realizar a abertura da conexão. A imagem abaixo mostra o web.config com a informação de string de conexão ao modelo de dados do EF.

E finalmente, o serviço funcionando de forma padrão fica assim:

Na prática este é o jeito mais simples possível de se criar um serviço WCF. Não temos muitos segredos até o momento…

Testando o modelo de dados e o serviço WCF

Embora opcional, este passo consideramos muito importante para o desenvolvimento de qualquer coisa, desde um simples exemplo a uma complexa aplicação. Vamos realizar testes unitários muito simples capazes de indicar que o sistema está pronto para ser chamado do Excel. Não será preciso apelar para TDD neste caso pois nossos métodos são extremamente simples de serem testados. E não vamos contar com falhas como eventuais quedas do banco e do serviço, pois afinal, estamos querendo trabalhar mesmo é o com o Excel neste artigo. Vamos usar a estratégia de testes conceituais para este exemplo.

Crie um projeto usando o template de testes do VS2010. Adicione referência ao projeto contendo o DataModel. Adicione também uma referência ao serviço criado no passo anterior. As imagens mostram como referenciar o serviço ao projeto de testes. Basicamente o VS2010 identifica nos projetos da solução onde há um serviço WCF existente e puxa suas informações públicas, mostrando-as para nós.

Na guia “Advanced”, costumamos utilizar o List ao invés do Array para trabalhar com conjuntos de entidades por ser muito mais prático. Mas isso é opcional. Nós renomeamos a referência para ProductServiceReference para ficar mais legível no código final, pois este namespace que guardará o proxy do serviço WCF.

Com isso, vamos para o teste do modelo de dados:

Neste primeiro testes, conseguimos provar que o modelo funciona. E para comprovar, inicialmente coloquei um trace para que o teste escreva na janela de output o código e o nome de cada produto existente no banco. E o teste também contém um asserção para provar que há dados no banco para os testes. O teste executou ok. Fizemos o mesmo para o método que testa se o serviço realmente retorna dados.

Este teste realmente demorou mais para passar. Tínhamos nos esquecido de desatachar os objetos do contexto e por isso os dados nunca vinham. Outro erro que acontecia era o tamanho do conjunto de dados. O arquivo de configuração vem por padrão com um tamanho reduzido de bytes de tamanho da resposta. Tivemos que aumentar o tamanho do pacote no app.config do projeto de testes.

Vale lembrar que é recomendável sempre fechar o proxy do serviço WCF. Com o fechamento, o WCF libera os recursos como threads e alocações de memória que ele prepara para cada conexão ativa no serviço. E quando estamos trabalhando com o conceito de sessões no WCF, o fechamento indica o término de uma sessão. Abaixo mostramos o resultado final da tela de testes que o Visual Studio mostra quando os testes passam:

Como sempre, podemos debugar o teste, e no debug, podemos passar do cliente para o servidor na mesma execução. Com o F10, posso andar em cada método que é executado, e, se o cliente chama o servidor, o debug vai para o servidor e continua a execução, permitindo que tenhamos pleno controle da situação. Colocando o breakpoint tanto no servidor como no cliente, você sempre terá a informação de quem chamou quem e o que aconteceu durante as chamadas.

Criando uma customização de uma planilha do Excel 2010 com o VS2010

É possível customizar tanto documentos como a própria aplicação. Neste exemplo não é necessário criar uma customização do Excel, apenas é preciso adicionar novas funcionalidades a apenas uma planilha. Com isso, vamos optar pelo template de projeto Excel 2010 Workbook:

Ao prosseguir, teremos um projeto e um documento criado. Teremos algumas classes em .NET representando cada planilha do documento:

Como podemos ver, na prática um projeto com VSTO é muito similar a um projeto comum em .NET. Podemos adicionar novos itens, outras classes, referenciar outros projetos e muito mais. Neste exemplo, a referência ao serviço já foi adicionada ao projeto. Também por padrão é adicionado um certificado temporário para que o projeto possa ser aceito pelo Office.

Antes de prosseguir é preciso esclarecer alguns pontos – vamos focar esta seção mais no Excel, mas as perguntas e respostas são válidas para outros programas.

1. O que é o VSTO? O VSTO nada mais é do que o conjunto de ferramentas necessário para se trabalhar com o Excel utilizando o .NET. É composto de bibliotecas de classes, ferramentas gráficas e customizações do Excel necessárias para que o Add-in seja reconhecido e corretamente visualizado no Excel. Também é responsável por se comunicar com os objetos COM do modelo do objetos do Excel e realizar adequadamente as ações desejadas. E por fim, o VSTO possui um conjunto interessante de eventos trazidos do Excel para o .NET, de forma que é possível saber no .NET quando por exemplo, alguém altera uma determinada célula da planilha.
2. Como eu faço para conhecer o modelo de objetos do Excel? Para trabalhar com a automação é necessário ter noção de como se cria macros no VBA. Uma macro do Excel pode ser transcrita para o C# com certa simplicidade.  O modelo de objetos está muito bem documento na ajuda do VBA. Para mostrar a tela do VBA do Excel, use ALT + F11. Finalmente, use o Gravador de Macros do Excel para ir aprendendo os comandos e sintaxe mais comuns do Excel.
3. Vejo alguns namespaces que estão me confundindo. Para se trabalhar com o Excel, temos as bibliotecas principais representadas por Microsoft.Office.Interop.Excel e Microsoft.Office.Core. Essas bibliotecas contêm basicamente uma representação em .NET do que o Excel contém. Elas são os famosos PIAs. Se na tela do VBA você achar um objeto denominado Chart, no objeto equivalente em .NET você provavelmente também encontrará o Chart. Vale notar que as traduções em .NET são feitas com interfaces e não classes concretas em .NET. Mas na prática, você conseguirá controlar o objeto da mesma forma, chamando os métodos com os mesmos nomes e parâmetros vistos no VBA. Porém, existe um outro namespace que mora em outra biblioteca do VSTO denominada Microsoft.Office.Tools.Excel. Este último namespace guarda objetos em .NET que representam os principais recursos do Excel em .NET, melhorados de forma a se tornarem particularmente úteis. Eles contém uma representação bacana do Workbook, Worksheet, ListObject, NamedRange e alguns outros elementos. Tais elementos podem ser criados diretamente do .NET usando o designer do Visual Studio, muito similar à criação de um controle utilizando Windows Forms. Com isso, o desenvolvedor tem como referenciar de forma simplificada os objetos importantes de sua planilha.
4. Qual o melhor jeito de começar com os testes simples? O ideal é trabalhar com o Globals. Esta classe contém uma referência para todos os objetos do Tools.Excel de forma simples. Para acessar a planilha1, basta pedir Globals.Sheet1 e por aí vai… vamos mostrar na prática como funciona a referência a tais objetos. Brinque com o Range, com o Offset, modifique os conteúdos da célula… Para rodar basta utilizar o F5 comum para fazer os testes. O add-in é carregado e o Visual Studio consegue se atachar ao código .NET que está executando na planilha. Logo, é possível colocar breakpoints no VS2010 e olhar o que está em cada variável. Também é possível utilizar janelas como Immediate Window para realizar testes preliminares sobre a validade de suas expressões. O desenvolvimento para Office exige uma carga adicional de tentativa e erro para realizar uma determinada tarefa. E com o Visual Studio você tem total suporte a isso. Recomendamos que você tenha calma pois muitas vezes poderá acontecer um erro de COM impossível de descobrir pelo fato de não se ter um modelo legal de controle de erros como o .NET. Não se assuste. Vá por partes. Tenha certeza que cada pedaço da expressão funcionou. Lembre-se de não usar índice zero com o Range e laços For. Use e abuse do Immediate Window. Use Listas e Named Ranges – evite lógica com o número/nome da range. Por fim, domine o Excel.
5. E o Ribbon? Como trabalhar com ele? O Ribbon, que é o menu inovador lançado no Office 2007, também continua presente no Office 2010. Na verdade, ele foi ligeiramente modificado no Office 2010 – foi criado um “Backstage Ribbon” no 2010, que contém opções que geralmente ficariam no menu Arquivo. Esse backstage ribbon se chama File na versão americana do Office. No .NET, é um controle que pode ser adicionado ao projeto, com um code-behind contendo eventos convenientes. No designer do VS2010 é possível arrastar e soltar itens para o Ribbon e amarrar eventos a cada botão de forma normal, como se fosse em Windows Forms. Para o desenvolvedor, o Ribbon é uma aba que fica no dentro do menu Add-ins do Excel. E para o desenvolvedor, o Ribbon nada mais é que um form cheio de controles comuns. Simples e prático de usar. O Ribbon pode ser criado mesmo em customizações de documentos. Não necessariamente vale só para customização de aplicações.

Antes de prosseguir com a customização, fizemos um teste básico para ver se realmente era possível chamar o WCF de dentro do Excel. Embora isso pareça ser óbvio, não necessariamente tudo acontece conforme o esperado. No evento que é chamado quando o arquivo abre, colocamos o seguinte teste:

Entretanto, ocorreu um erro. Ao dar F5, o Excel tenta abrir:

Mas em seguida surgiu uma mensagem de erro:

Conforme esperado, o conjunto de dados era maior do que o padrão (conforme visto nos testes). Deixamos este erro acontecer para demonstrar que quando o erro acontece você pode parar, debugar e continuar com a execução. Não recomendamos que você mate o processo dando stop na depuração. Sempre continue com F5 e só então termine a execução. As falhas podem acontecer e quando acontecem, o Excel lança o erro neste formato mostrado acima e continua normalmente a rodar, sem cair.

Finalmente acertamos o config da aplicação e testamos novamente com sucesso.

Trazendo dados do WCF para o Excel

Tendo a certeza de que o WCF estava trazendo os dados e que o Excel é capaz de mostrar os dados, vamos para o próximo passo: mostrar os produtos na planilha do Excel.

Para isso, crie uma nova tabela na planilha principal com 2 colunas. Assuma que as colunas terão um header. Como as tabelas devem ter um nome, nós alteramos para tblProdutos:

Esta funcionalidade se chama “Tabela” no Office 2007 em diante. No Office 2003 esta funcionalidade se chama “Lista”. Por isso que no .NET ela também se chama ListObject. Com o uso de tabelas, os dados ficam muito mais organizados do que em planilhas comuns, sendo uma forma muito inteligente de trabalhar com dados no Excel, mesmo quando não estamos fazendo coisas tão avançadas como Macros e customizações com .NET.

O próximo passo é criar o Ribbon, que será mostrado ao usuário final e terá alguns itens para podermos realizar nossos exemplos. Ao escolher a opção Add New Item do projeto Excel, no VS2010 aparecerá vários itens. Vá no subgrupo Office e escolha a opção Ribbon (Visual Designer). É possível criar um Ribbon editando um arquivo XML específico diretamente – modo avançado de se trabalhar com o Ribbon.

O editor visual mostra o Ribbon vazio. Devem ser adicionados os controles ao Ribbon usando o designer visual por ser mais prático.

Com o Ribbon pronto, vamos fazer o código busca a lista no WCF e faz o databind com o ListObject do VSTO/Table do Excel. O código que executa é um evento do click do botão de dentro do Ribbon. Logo, ele não está em nenhuma planilha. Portanto é preciso referenciar os objetos de interesse utilizando a classe Globals. Vejamos como fazer isso:

A classe Globals é gerada automática pelo template do VS2010 e representa como propriedades estáticas os objetos importantes do documento Excel. O Sheet1 é uma representação melhorada da planilha para uso em .NET e não o Sheet do Interop. O tblProdutos é um campo do objeto .NET Sheet1.

O método SetDataBinding permite fazer o databind com qualquer conjunto de dados comum do .NET. No caso, este método possui a capacidade de escolher quais colunas podem ser mostradas ou não. O segundo parâmetro está vazio, mas por exemplo, poderia ser o nome da tabela que esteja dentro do Dataset no caso de múltiplas tabelas.

Com o databinding, eu consigo colocar os dados na lista sem ter que me preocupar com o número de linhas na lista, redimensionamento e sem ter que usar um laço For para processar cada registro. Muitos incômodos a menos. Com isso a lista preenchida fica conforme mostra a figura abaixo. O Ribbon tem mais 2 botões que serão úteis para os testes seguintes em cima da lista preenchida. Note que o Ribbon que montamos ficou dentro da aba Add-Ins. Nesta aba ficam também os outros add-ins de fabricantes diversos.

Trabalhando com os dados obtidos no Excel utilizando C# 4.0

A nossa Ribbon agora deverá ter mais dois botões conforme mostrado na tela acima. No designer ela fica assim:

O botão “Trabalhar com Lista” irá mostrar os 10 primeiros registros da lista. Desta forma poderemos ver algumas novidades que o C# 4.0 tem para nos ajudar nesta tarefa.

Antes de mostrar o método completo temos que comentar sobre os objetos dinâmicos do C#. Veja a imagem abaixo extraída do Intellisense:

Se você observar bem, o objeto correspondente a uma Range específica é do tipo dynamic. Isto é, não precisamos fazer um cast para o objeto exato do Interop para poder trabalhar com o objeto desejado. Note também que em C# antes era obrigatório passar o Type.Missing para os parâmetros opcionais e agora não é mais – ele já vem com o valor pré-fixado para nossa conveniência.

Para ser mais completo na explicação, o Range possui um indexer com o parâmetro coluna e linha opcionais. Isso significa que posso ter vários tipos de seleção de conjunto de células e na prática, vários tipos de retornos do mesmo indexer. Para isso é conveniente a assinatura com parâmetros opcionais e retorno dinâmico, pois mais tarde o desenvolvedor, que já espera qual será o retorno adequado, pode trabalhar com o resultado esperado sem se preocupar em tempo de compilação. Abaixo segue a assinatura do indexer da classe Range do Interop.Excel – o Range normal não foi evoluído no Tools.Excel. Só o NamedRange.

Voltando então ao exemplo, decidimos deixar o método mais prolixo para explicitar o uso do dynamic do C# 4.0:

Neste caso, embora não fosse necessário para este exemplo, fizemos com que a cada iteração do For, o Range equivalente à linha da lista de produtos ficasse guardado numa variável dinâmica. A partir deste momento, tudo o que for feito com a variável x também se torna dinâmico, permitindo que eu chame métodos e realize atribuições sem saber exatamente o que tem em x com a ajuda do Intellisense. Se eu quisesse chamar uma propriedade x.Teste, só iria dar erro em tempo de execução. O compilador, ao lidar com variáveis dinâmicas, não tem como descobrir quais são os métodos e propriedades de x. Mas como conhecemos o modelo de objetos, as variáveis valorColuna1 e valorColuna2 ficarão com os valores adequados. Finalmente, o método Append do StringBuilder saberá qual overload usar para inserir o número ou o nome do produto.

Finalmente, com um último exemplo mais prático, vamos mostrar todos os código de produto recebidos pelo serviço num MessageBox. Com isso vamos mostrar que também é possível iterar com foreach de forma mais prática em alguns casos. Podemos usar dynamic diversas situações como mostra a figura a seguir.

Executando os dois exemplos temos os seguintes resultados:

a) Primeiros 10 itens da lista:

b) Todos os códigos de produto recebidos do serviço:

Com isso conseguimos concluir como mostrar e trabalhar com dados no Excel, concluindo assim o exemplo.

Conclusão

Com esta pequena demonstração conseguimos mostrar como trabalhar com fontes de dados modernas como web services, por exemplo. O uso de VBA, embora seja bastante válido e muito poderoso, não permite que se use arquiteturas mais modernas nem ferramentas de desenvolvimento mais atualizadas para se criar aplicações atuais para a plataforma Microsoft Office.

O uso da plataforma Office está crescendo muito e os desenvolvedores hoje precisam entender que nem todo sistema precisa ser Web! Muitas vezes, uma solução muito mais complicada em Web pode ser facilmente resolvida utilizando o Office com inteligência.

O uso de .NET e Visual Studio 2010 também favorece muito o desenvolvimento de aplicações baseadas em Sharepoint, o que torna o uso do Excel, do Word e do Outlook particularmente importante como plataforma de desenvolvimento de aplicações corporativas. Com vários templates para Sharepoint, é possível criar solução altamente integradas com custos muito menores de desenvolvimento, utilizando ferramentas que os information workers conhecem há mais de 10 anos – a plataforma Microsoft Office.

Sobre Nós

A Accendis é uma consultoria que presta serviços utilizando a plataforma de desenvolvimento Microsoft. Conheça mais sobre o nosso trabalho www.accendis.com.br. Se você precisar de treinamento para você, sua empresa, alocação de profissionais em formato body shop e consultoria para resolver problemas mais complexos, estamos aqui para te ajudar.

Grande Abraço

Equipe Accendis

Introdução

Para prosseguir com nossos estudos sobre o Entity Framework, vamos falar agora das operações básicas de select com as entidades do modelo de dados. Para seguir por esse exemplo, é necessário que você tenha lido o artigo anterior explicando como foram feitas etapas do projeto. Clique aqui para acessar o artigo inicial desta série.

Os testes unitários nesta fase do projeto não serão tão rigorosos, com inúmeras asserções e casos de falha – nosso objetivo é a praticidade e a velocidade no aprendizado.  Vale lembrar que é necessário um conhecimento básico de LINQ para conseguir progredir nestes exemplos. Neste artigo vamos aprender como fazer consultas básicas com WHERE e ORDER BY.

Executando comandos SELECT simples.

Nesta série de exemplos iremos criar testes que apenas buscam dados do banco de dados. Com isso teremos uma notável sensação de economia de tempo ao ver que é possível modificar à vontade o método de acesso a dados sem ter que ficar escrevendo tediosos comandos SQL.

Vamos relembrar do artigo passado como fizemos o nosso primeiro SELECT no banco. Se você seguiu o que foi sugerido no artigo passado, o banco deverá ter 3 registros na tabela Unidade:

using System;
using System.Collections.Generic;
using System.Linq;
using Accendis.Artigos.GerFac.DataModel.BusinessEntities;
using Accendis.Artigos.GerFac.DataModel.Config;
using NUnit.Framework;
 
namespace Accendis.Artigos.GerFac.UnitTests
{
    [TestFixture]
    public class UnidadeTest
    {
        [Test]
        public void ListarUnidades()
        {
            //Abrir uma conexão ao data model e implicitamente, abrir uma conexão com banco de dados
            //utilizando o Entity Framework. Utilizar o bloco using para que os recursos sejam fechados
            //após o termino da execução do bloco using.
            using (GerFacEntities entities = new GerFacEntities(AppConfiguration.ConnectionString))
            {
                //obter as entidades do banco de dados, materializar numa lista e realizar
                //as operações desejadas com a lista.
 
                List unidades = (from u in entities.Unidade select u).ToList();
                foreach (Unidade unidade in unidades)
                {
                    Console.WriteLine(string.Format("{0} - {1}", unidade.Nome, unidade.Descricao));
                }
                Assert.IsTrue(unidades.Count() > 0);
            }
        }
    }
}

Vamos explicar em detalhes este método. Primeiramente, é criada uma classe do contendo o atributo [TestFixture]. Este atributo indica que esta classe poderá ter vários testes unitários. O atributo [Test]  indica que o método ListarUnidades() é um teste unitário. Como regra geral, as classes de teste não podem ter construtores customizados (com parâmetros) e os métodos de teste devem ser do estilo “void-no-args”, isto é, não podem retornar nada e não devem possuir parâmetros.

O bloco using mostra criação de uma instância de uma conexão ao contexto de dados do entity framework. Seria equivalente a preparar um SqlCommand. Ele usa como parâmetro uma connection string especial (que você vê em detalhes no seu app.config do projeto de testes). Neste caso, usamos uma classe e método estático que acessa o arquivo de configuração e nos retorna os dados desejados sem grande esforço.

Dentro do bloco using, podemos montar nossa consulta utilizando LINQ à vontade. Neste caso, fizemos um comando equivalente a um SELECT * na tabela Unidade do banco de dados. Nós poderíamos ter feito uso da palavra chave “var” e rescrito a linha de comando assim:

//Não utilizar este padrão!
var unidades = from u in entities.Unidade select u;

Este comando também funcionaria e então poderíamos realizar o mesmo foreach abaixo. Esta alternativa é válida em casos simples mas no caso genérico, onde é necessário retornar um objeto para as camadas superiores da aplicação, o ideal é fazer a materialização da lista chamando o método ToList(). Com uma lista formalizada, é possível realizar quais e quantas operações eu quiser.

O Entity Framework retorna as consultas do banco de dados de forma que elas sejam apenas enumeráveis uma única vez. Lembrem-se disso. Só é possível ler o resultado de uma mesma consulta uma única vez! Com a lista não – é possível ler quantas vezes quisermos. Nos códigos seguintes sempre usaremos este padrão, que é usar lista fortemente tipada e evitando ao máximo o uso do “var”. Aliás, o uso do “var” fora de consultas com LINQ é uma péssima prática de programação quando se fala em C#.

O foreach final percorre os registros recebidos e mostra na tela de resultados do plugin de testes unitários que o ReSharper oferece. Vale lembrar que esta ferramenta é opcional. E no caso geral, não é necessário imprimir os resultados de testes unitários, pois a idéia é que os mesmos sejam automáticos no futuro.

Um fato imporante é o pedaço do código: entities.Unidade – esta propriedade representa um objeto do tipo ObjectQuery. Esta classe na prática implementa IQueryable e IQueryable, que no fim das contas são as interfaces que quando implementadas fazem o trabalho de transformar queries do LINQ em comandos SQL possíveis de serem executados. Cada entidade possui uma propriedade criada de forma automática.

A última linha indica uma asserção. Todos os frameworks de testes unitários oferecem um conjunto de asserções para que o teste unitário faça a checagem se uma determinada condição é verdadeira ou falsa. Caso seja falsa, o teste falha e indica que houve uma falha de asserção.
Agora que já sabemos como é um teste unitário básico, vamos progredir com os exemplos de select simples.

        [Test]
        public void ListarUnidadesWhere()
        {
 
            using (GerFacEntities entities = new GerFacEntities(AppConfiguration.ConnectionString))
            {
 
                List unidades = (from u in entities.Unidade where u.Descricao == "Unidade 2" select u).ToList();
                foreach (Unidade unidade in unidades)
                {
                    Console.WriteLine(string.Format("{0} - {1}", unidade.Nome, unidade.Descricao));
                }
                Assert.IsTrue(unidades.Count() == 0);
 
                unidades = entities.Unidade.Where(u => u.Nome == "Unidade 2").ToList();
                foreach (Unidade unidade in unidades)
                {
                    Console.WriteLine(string.Format("{0} - {1}", unidade.Nome, unidade.Descricao));
                }
                Assert.IsTrue(unidades.Count() == 1);
            }
        }

O exemplo acima contém duas formas de se fazer uma consulta com where. A primeira usa a sintaxe chamada pela comunidade de “query syntax”, que se assemelha muito a consultas SQL comuns. A sua leitura é bem simples. A segunda consulta mostra como realizar a consulta utilizando Lambda Expressions entrada do método Where e sua sintaxe é conhecida como “fluent syntax / method syntax / full query syntax”. As consultas geradas pelo EF são as mesmas independentemente da sintaxe utilizada. Também é possível misturar sintaxes, pois no fim, o compilador analisa e monta as expressões lógicas necessárias.

Finalmente, vamos falar de ordenação e o que pode acontecer ao buscar uma única entidade do banco. Veja o teste abaixo:

        [Test]
        public void ListarUnidadesOrderbyNull()
        {
            using (GerFacEntities entities = new GerFacEntities(AppConfiguration.ConnectionString))
            {
                List unidades = (from u in entities.Unidade
                                          where u.Nome == "Unidade 2"
                                          orderby u.ID descending
                                          select u).ToList();
 
                Assert.IsTrue(unidades.Count() > 0);
 
                Unidade unidadeInexistente = (from u in entities.Unidade
                                              where u.Nome == "Unidade 4"
                                              select u).First();
                Assert.IsNull(unidadeInexistente);
 
            }
        }

A primeira consulta contém um comando de ordenação simples e passará pela primeira asserção. Já a segunda consulta irá causar a exceção já que não temos a Unidade 4 no banco. Com isso acontece a seguinte falha:

Este erro é causado pelo fato de se buscar a primeira entidade de uma lista que não contém elementos. Este erro é evitável utilizando usando o método “FirstOrDefault” no lugar de “First”. Com este método, é possível retornar valores nulos da consulta sem problemas. O método corrigido fica assim:

        [Test]
        public void ListarUnidadesOrderbyNull()
        {
            using (GerFacEntities entities = new GerFacEntities(AppConfiguration.ConnectionString))
            {
                List unidades = (from u in entities.Unidade
                                          where u.Nome == "Unidade 2"
                                          orderby u.ID descending
                                          select u).ToList();
 
                Assert.IsTrue(unidades.Count() > 0);
 
                Unidade unidadeInexistente = (from u in entities.Unidade
                                              where u.Nome == "Unidade 4"
                                              select u).FirstOrDefault();
 
                Assert.IsNull(unidadeInexistente);
 
            }
        }

Os comandos que buscam dados do banco podem ser mais complexos do que os exemplos acima. Porém, com o que já temos é possível realizar grande parte das buscas simples do dia-a-dia e provavelmente você estará fazendo este tipo de consulta 70% do tempo.

Encorajamos você a pesquisas os métodos que passaram a ser disponibilizados a partir da versão 3.5 do .NET. Navegue pelos métodos disponibilizados pelas entidades do modelo, tais como entidade.Unidde, entidade.Aluno e etc. Com esse background sua a experiência será cada vez maior.

No próximo artigo vamos falar sobre as operações de modificação de dados. Até já!

Objetivos

Esta série de artigos visa mostrar com detalhes um exemplo relativamente complexo de aplicação do Entity Framework  em projetos do nosso dia-a-dia. Com isso, queremos demonstrar de forma imparcial os ganhos que obtivemos com a aplicação desta nova forma de trabalhar com dados que se revela muito promissora.

Esta série de artigos aplica-se aos desenvolvedores que usam .NET Framework 2.0 e gostariam de aprender ou convencer seus pares a adotar o .NET Framework 3.5. Estes exemplos são criados utilizando o Visual Studio 2008, banco de dados SQL Server 2005 e também, ferramentas auxiliares com o NUnit (sem custo) e ReSharper (possui custo – mas é opcional). Além disso, é necessário obter o Service Pack 1 do .NET Framework 3.5.

Introdução: Acesso a Dados

O acesso a dados é uma das tarefas mais corriqueiras de um desenvolvedor de aplicações empresariais comuns, como cadastros web por exemplo. Se você estiver utilizando a plataforma .NET, provavelmente cansou de escrever camadas de acesso a dados repetitivas para depois sofrer com mudanças no esquema, mudanças nas colunas, novos joins, erros de sintaxe e erros e compilação de comandos SQL.

Provavelmente você também já deve ter ouvido falar de várias ferramentas que tendem a ajudar na tarefa de mapear tabelas de bancos de dados relacionais em objetos autênticos no .NET. E provavelmente você acabou experimentando e usando com sucesso o NHibernate em sua mais recente versão, conseguindo acelerar radicalmente o tempo de criação da sua camada de acesso a dados. Entretanto, também sofreu com a criação dos trabalhosos arquivos de mapeamento do NHibernate. Com o tempo relativamente curto, nós, desenvolvedores, geralmente evitamos lidar com tecnologias completamente novas sem antes testar várias situações problemáticas. Sem o uso de NHibernate e sem o uso de ADO.NET clássico, evita-se a busca dos inúmeros outros fornecedores de soluções similares haja vista que cada tipo de ferramenta, paga ou não, necessita de um tempo de adaptação, power-users, treinamento de equipe e incorre em custos para o cliente ou projeto final, que custeia a curva de aprendizado do time.

Com o surgimento do framework 3.5, o mundo .NET sofreu uma revolução, não apenas uma evolução: foi introduzido o LINQ, que basicamente uniformiza o acesso a qualquer tipo de dados da atualidade. Neste release foi lançado também o Linq to SQL, que passou a ser um produto para simplificar radicalmente a acesso a dados para o SQL Server 2005. Muitas pessoas utilizaram e ainda utilizam o Linq to SQL. Entretanto, o LINQ to SQL não é considerado pela comunidade como uma solução corporativa pois é muito mais focado em acelerar o desenvolvimento de cadastros simples e também, só funciona com o SQL Server. Para exemplos de LINQ sugiro visitar estes links: Introdução ao LINQ,  Página do LINQ do MSDN e Primeiros Exemplos com LINQ .

O lançamento do Service Pack 1 do .NET introduziu o Entity Framework, que passou a ser a principal ferramenta de mapeamento objeto-relacional disponível para a comunidade .NET. É considerada uma solução corporativa e está no mainstream do desenvolvimento da Microsoft. Com os exemplos buscaremos mostrar como foi a adoção desta ferramenta, suas vantagens e desvantagens.  Com esta ferramenta, já é possível ignorar a necessidade o NHibernate, mesmo que este framework seja muito mais maduro que o seu concorrente. Isso se faz verdade pois com isso conseguimos diminuir o número de dependências externas. Outra vantagem é que não se cria a dependência de ferramentas não-Microsoft, argumento que é válido em muitas organizações que visam diminuir os supostos riscos de uma ferramenta open-source.

Preparação do Exemplo: Gerenciamento de Faculdade

Para começar a explicar como funciona o Entity Framework, vamos apresentar nosso exemplo. Este exemplo poderá ser a base de muitos outras postagens e portanto é bom que se entenda com calma quais são as especificações do sistema.

O sistema em questão é um sistema relativamente simples de controle de alunos, turmas e professores de uma faculdade. Os requerimentos sistema estão listados nos itens abaixo, de forma muito simplificada inicialmente:

1. O sistema suportará várias unidades (filiais) da faculdade.

2. A faculdade possui um grupo de disciplinas comum a todas as unidades.

3. Cada unidade possui uma turma distinta de uma dada disciplina. Isso permite que a mesma disciplina seja oferecida em diferentes unidades.

4. Cada unidade contém seus respectivos alunos. Os alunos não poderão utilizar mais do que uma unidade.

5. Cada aluno deverá conter um cadastro básico obrigatório e um cadastro não-básico opcional.

1. Cadastro básico: Nome, Formas de Contato
2. Cadastro não básico: Campos livres, decididos de acordo com cada unidade. Por exemplo, deverá ser possível montar um questionário sócio-econômico.

6. Cada professor pode ter uma ou mais turmas.

7. O sistema deverá suportará o cadastro de turmas. Cada turma é única em todo o sistema, independente de qual unidade ela pertença

1. O sistema deverá suportar o envio de mensagens para os alunos da turma
2. Deverá ser possível guardar informações do aluno em cada turma que estiver matriculado

8. Cada professor pode ministrar aulas em mais de uma unidade. Para os administradores de cada unidade, é necessário saber quem são os seus professores pois assim conseguem determinar os custos de estacionamento e honorários. A relação de professor x unidade existe independentemente de haver turmas para o professor.

Agora vamos citar alguns outros requisitos de negócio necessários para prosseguir com o exemplo:

1. O sistema deverá rodar em plataforma Web

2. O sistema poderá expor seus dados utilizando o padrão REST para Web Services.

Preparação do Exemplo: Modelagem dos dados

Considerando os requerimentos listados e levando em conta a necessidade de simplicidade do exemplo, vamos demonstrar a criação do modelo de dados através do uso de uma ferramenta que gera diagramas ORM. O ORM é uma ferramenta de engenharia de software conhecida no mercado que gera diagramas demonstrando a relação entre as entidades e complementa a notação UML ao especificar sistemas. Clique aqui para saber sobre ORM.

A ferramenta utilizada para gerar se chama Natural ORM Architect. Ela é Open Source e é exposta como um plugin do Visual Studio 2008. Trata-se de uma ferramenta completa e bem elaborada – quem dera se houvesse uma ferramenta similar totalmente open source para usar com UML! Clique aqui para obter esta ferramenta.

Para modelar um sistema geralmente a documentação é lida com os seguintes focos: buscar as entidades e buscar as relações/ações entre tais entidades. Tomando essas ações e aproveitando que temos um exemplo corriqueiro à disposição, consegue-se chegar no seguinte diagrama ORM:

Diagrama ORM representando as entidades deste exemplo - clique pra aumentar todas as imagens deste artigo

Ao observar este diagrama devemos que lembrar:

1. Os retângulos são entidades formais – nossos “objetos” do domínio – de quem falamos. No caso, Unidade, Professor, Aluno e Turma.

2. Os fatos são as ligações entre as unidades. São os “verbos” de estado ou ação, que determinam como uma entidade se relaciona com outra. Estas relações podem ser de alguns tipos bem conhecidos: 1-1, 1-N, N-1. No nosso exemplo, a Unidade tem relação 1-N com Aluno, Aluno possui uma relação N-N com Turma, e assim vai.

3. O fato também pode ser “objetificado”. Isto é, a relação de alunos com turmas, apenas de ser N-N, é qualificada, isto é, a relação possui propriedades úteis. Isso ficará mais claro durante a criação do banco de dados. Quando um fato é objetificado, ele recebe um círculo no diagrama.

4. O “risco” roxo acima de cada fato significa a multiplicidade. O lado da relação que tiver o risco possui multiplicidade N.

5. A “bolinha” ao lado de cada relação indica que a presença da entidade na relação é obrigatória. Um exemplo de leitura neste caso seria: “Um Professor ministra em pelo menos uma ou mais Unidades. No caso inverso, Turma pode conter 0 ou mais alunos. Também, podemos ver que a Turma pode existir sem alunos.

6. Os termos colocados acima de cada relação demonstram como é efetivamente a relação de cada entidade. Isto é feito para facilitar a leitura do diagrama.

7. É possível estender o modelo acrescentando propriedades às entidades, como por exemplo, uma suposta classe “Endereço” à entidade Aluno.

É possível complicar muito mais este tipo de diagrama, pois é possível definir “constraints” no modelo, igualdades, “and”, “or” e vários outros tipos de condições entre as entidades. Embora Turma permita alunos de unidades diferentes, essa restrição é mais fácil de ser feita no software do que no banco de dados – não pode haver mistura de alunos de unidades diferentes.

Preparação do Exemplo: Criação do banco de dados

Muitas correntes de pensamento do mercado assumem que é interessante partir do modelo de dados final para depois criar o banco da dados – o objetivo disso é que não se crie uma aplicação já dependendo de um esquema pré-desenhado e muitas vezes, não tão bem pensando como poderia ser.

No nosso caso, temos um modelo misto. Vamos usar a modelagem acima para criar o banco e dados para depois, referenciar este esquema recém-criado no Entity Framework, pois este costuma ser o jeito mais prático de montar o modelo da aplicação.

Como a criação de tabelas é uma atividade corriqueira, vamos acelerar este processo e vamos montar as relações de forma que por enquanto, não haja nenhum tipo de ação ao alterar/remover linhas de um relacionamento entre tabelas, isto é, sem UPDATE ou DELETE action.

Esquema do banco de dados

O modelo acima está um pouco simplificado em termos de número de campos por tabela. Com particularidades no design temos as seguintes observações:

1. Nas entidades Unidade, Professor, Aluno e AlunoNasTurmas foi criada a coluna [Detalhes]. Esta coluna é do tipo XML. Este é um tema que será detalhado mais para frente, mas já se pode adiantar que o uso deste tipo de coluna proporciona o uso de documentos XML neste tipo de coluna, com total suporte busca e manipulação de informação. No .NET, este tipo de dados é representando como String simples.

2. Foi acrescentado ao modelo a entidade DisciplinaOferecimento, que contém os tipos de semestre que são oferecidas as disciplinas. Ela indicará que uma disciplina é semestral, anual ou de férias, por exemplo.

3. A entidade Disciplina usa como chave o seu próprio código, que contém letras e números.

4. A relação entre Turma e Aluno é uma relação N-N qualificada. Ela contém várias propriedades interessantes e também possui um campo do tipo xml para armazenar outras informações não previstas neste momento, como por exemplo, a nota de cada prova.

O script completo da criação do banco e a solução do Visual Studio se encontram nos anexos listados no final deste artigo.

Preparação do Exemplo: Criação do modelo de dados com Entity Framework

Com o banco de dados construído, já temos condições de criar um modelo de dados consistente para nosso exemplo para uso no .NET. Para prosseguir com esse exemplo é necessário possuir o .NET Framework 3.5 SP1 instalado.

A princípio nossa solução será criada com um projeto contendo o modelo de dados e outro projeto contendo os testes unitários para podemos testar o modelo. Para facilitar a compreensão do processo siga os próximos passos:

1. Crie uma solução com um projeto Class Library. Crie uma pasta chamada BusinessEntities ou similar. Esta pasta irá conter o modelo de dados e todas as classes complementares ao modelo. Optamos pelo nome óbvio de GerFac para que o nome do projeto e de suas referências fique sucinto. Note que também eu já tenho no projeto o modelo ORM deste sistema, pois como foi dito antes, a ferramenta Natural ORM permite que se use o Visual Studio 2008 para desenhar os diagramas – os arquivos .orm são considerados como “Solution Items”.

2. Clique com o botão direito na pasta BusinessEntities e adicione um novo item do tipo “ADO.NET Entity Data Model”. Na tela subseqüente escolha “Generate from database”.

Criando uma nova solução.

3. Conecte-se à base de dados contendo as tabelas do exemplo. Ao realizar a conexão bem sucedida, salve a conexão com o nome sugerido no App.Config.

Armazenando os dados da connection string no arquivo de configuração

4. Escolhas as tabelas criadas conforme o exemplo abaixo. Aceite o namespace padrão do modelo. Clique em Finish.

Escolhendo as tabelas do banco da dados

5. Se tudo correu bem, você conseguirá visualizar seu modelo de dados como mostra a figura abaixo. Também será possível ver as barras de ferramentas do Entity Framework. Analise o modelo. Note que algumas tabelas parecem ter sumido. Quando há uma relação N-N, a tabela de junção não é mostrada no modelo. Na prática, na relação de Unidade e Professor, a classe Unidade possui uma lista de [Professor] e a classe Professor possui uma lista de [Unidade].

Modelo de dados representado como classes em .NET

6.  Salve e compile seu trabalho. Corrija eventuais erros. Analise o arquivo .designer do modelo. Esse arquivo .designer possui a representação de cada classe do modelo, assim como suas propriedades e métodos. Note também que esse arquivo sempre é gerado automaticamente – nunca mexa com ele diretamente.

7. O objetivo é tornar fácil o acesso à string de conexão do modelo de dados. A classe ConfigurationManager do .NET 2.0 facilita este processo. Vamos criar uma classe estática contendo um método estático que realize esta tarefa para nós. Para isso, crie uma pasta chamada Config e dentro desta pasta, crie uma classe chamada AppConfiguration. Adicione ao projeto uma referência ao assembly System.Configuration. Adicione o código abaixo à classe AppConfiguration:

using System.Configuration;
 
namespace Accendis.Artigos.GerFac.DataModel.Config
{
    public static class AppConfiguration
    {
        #region Public Properties
 
        /// Returns the connectionstring for the application.
        public static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["GerFacEntities"].ConnectionString;
            }
        }
 
        #endregion
    }
}

8. Compile seu projeto e corrija eventuais erros. Ao final da tarefa sua solução deverá ficar como a figura abaixo:

Detalhe do projeto contendo o modelo de dados (referências e organização das pastas)

Preparação do Exemplo: Primeiros Testes

Vamos começar a brincar com o nosso modelo de dados para verificar que o mesmo funciona. Para isso, adicione mais um projeto do tipo Class Library à solução. Denomine o projeto como UnitTests.

Referências do projeto de teste - observe a referência ao NUnit!

Para poder executar os teste unitários, é necessário instalar o NUnit em sua versão mais recente.  O NUnit é um framework para execução e automação de testes unitários open source e livre. É de longe a ferramenta de testes mais utilizada no mercado, embora exista ferramentas mais modernas e completas como por exemplo, o pacote de testes do Visual Studio Team System. O NUnit pode ser obtido a partir deste link.

Adicione uma referência ao projeto contendo o modelo de dados conforme a figura acima. Crie também uma classe chamada UnidadeTest, que será o lugar da primeira experiência prática com o Entity Framework. Copie o arquivo App.config do projeto DataModel para o projeto de testes. Isso é necessário para que o ConfigurationManager (criado por você anteriormente) que está no projeto DataModel consiga enxergar a connection string. Na prática, o projeto que chama o assembly DataModel injeta a string de conexão. Seja ele projeto de testes, Console Application, Windows ou Web. E essa injeção é possível com o uso do ConfigurationManager.

Uma ferramenta paga muito interessante para se trabalhar é o ReSharper. Ele é ótimo para verificar erros de codificação antes de compilar os dados mas uma das minhas funcionalidades favoritas é a execução de testes unitários. Esta ferramenta acelera muito a execução dos testes e permite ainda, que o teste escreva numa espécie de console. O ReSharper pode ser obtido a partir deste link. Vale a pena conferir!

Para terminar nosso teste básico, vamos listar e modificar entidades do tipo Unidade. Para isso faça o seguinte:

1. Crie um backup do banco completamente vazio.

2. Edite a tabela Unidade diretamente no SQL Server. Crie 3 unidades. Com isso teremos dados para retornar. Não se preocupe com o campo [Detalhes] neste momento.

Criando unidades para o teste

3. Adicione referência ao assembly System.Data.Entity ao projeto. Se estiver o utilizando o ReSharper, ele tentará fazer isso por você utilizando um aviso de erro.

4. Vamos criar um primeiro teste que indica que somos capazes de buscar as unidades do banco de dados e mostrar na tela. Para alcançar esse objetivo adicione o seguinte código:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Accendis.Artigos.GerFac.DataModel.BusinessEntities;
using Accendis.Artigos.GerFac.DataModel.Config;
using NUnit.Framework;
 
namespace Accendis.Artigos.GerFac.UnitTests
{
    [TestFixture]
    public class UnidadeTest
    {
        [Test]
        public void ListarUnidades()
        {
            //Abrir uma conexão ao data model e implicitamente, abrir uma conexão com banco de dados
            //utilizando o Entity Framework. Utilizar o bloco using para que os recursos sejam
            //liberados ao sair do bloco using.
            using (GerFacEntities entities = new GerFacEntities(AppConfiguration.ConnectionString))
            {
                //obter as entidades do banco de dados, materializar numa lista e realizar
                //as operações desejadas com a lista.
 
                List unidades = (from u in entities.Unidade select u).ToList();
                foreach (Unidade unidade in unidades)
                {
                    Console.WriteLine(string.Format("{0} - {1}",
                         unidade.Nome, unidade.Descricao));
                }
                Assert.IsTrue(unidades.Count() > 0);
            }
        }
    }
}

5. Execute este teste. Se estiver com o ReSharper, haverá uma bolinha para clicar no lado de cada teste, como é mostrado na figura abaixo.  Escolha a opção “Run”. Veja o resultado do teste na janela “Unit Test Sessions”.

Menu do Resharper para chamar a execução do teste unitário.

Resharper: Unit Test Explorer indicando o resultado da execução do teste unitário

É isso aí! Se você conseguiu chegar até aqui parabéns! Com isso você já está utilizando o Entity Framework e tem acesso total ao modelo de dados. Nós te encorajamos a brincar com as entidades e suas relações. Note que por exemplo, a entidade Unidade possui várias propriedades do tipo EntityCollection, que na prática representa a sua lista de professores ou então, a sua lista de seus alunos.

Cabe ainda alguns comentários sobre este primeiro teste:

1. A grande vantagem de usar o Entity Framework é o uso do LINQ para realizar consultas. Com isso, eu consigo fazer consultas tanto a objetos como dados que estão no banco de dados.

2. Só é possível enumerar 1 vez os dados quando eles são trazidos do banco de dados. Para isso, é muito útil colocar o que é recebido numa lista genérica. Por isso é utilizando o método ToList().

3. O LINQ acrescenta vários métodos úteis a objetos comun do .NET como coleções genéricas e enumerações (tudo o que suporta o foreach). Com isso, é possível transformar enumerações em listas, dicionários, encontrar máximos, somas e muito mais. Vamos falar sobre isso nos próximos posts.

Aguarde os próximos posts desta série!

Atenciosamente,

Equipe Accendis – www.accendis.com.br

Clique aqui para baixar os anexos deste artigo.