Como usar ADO.NET no ASP.NET MVC

Recentemente ministrei um treinamento para uma empresa com um pedido não muito usual, que foi a inspiração deste artigo. O pedido do Alexandre de Almeida foi que eu não poderia usar o Entity Framework, apenas o ADO.NET, visto que a empresa já trabalha desta forma e há um legado exatamente assim. O outro motivo é que existem muitas aplicações que usam o ADO.NET e não podemos nos esquecer deste legado.

Os pré-requisitos para este artigo são o Visual Studio .NET 2013, ADO.NET e conhecimento da linguagem C#.

Estrutura gráfica da solução

Veja na Figura 1 a representação gráfica da solução, sendo que existe uma classe chamada Produto, uma interface chamada IGerenciaCRUD, que é implementada na classe DALProduto que controla todo o CRUD. O Controller ProdutoController usa a DALProduto para controlar toda a estrutura de acesso aos dados, que será chamada pelas Actions deste Controller.

Projeto ASP.NET MVC 5

Neste artigo, criei um projeto no Visual Studio .NET 2013 com o template de ASP.NET MVC 5. Como o template já cria toda a estrutura de MVC, a primeira coisa a fazer é criar a classe Produto na pasta Models, conforme mostrado a seguir. É uma classe com quatro propriedades simples, sendo a chave o ProdutoID.

namespace Mvc_AdoNet.Models
{
    public class Produto
    {
        public int ProdutoID { get; set; }
        public string Nome { get; set; }
        public decimal Preco { get; set; }
        public int Estoque { get; set; }
    }
}

Dica: Por que a chave chama-se ProdutoID e não ID ou qualquer outro nome? Porque se um dia você resolver utilizar o Entity Framework 6 ou superior, não é preciso usar o Data Annotations ou Fluent API para informar qual propriedade é a chave primária – o EF já reconhece a chave se você usar o ID ou o nome da classe seguido do ID (ProdutoID, ClienteID, PedidoID etc).

Agora pense comigo: a classe já existe, como posso criar um Controller usando o template do MVC embutido no VS 2013 de forma que o mesmo use o ADO.NET? Para resolver esta questão, usei uma estratégia que funciona 100% e é super rápida: usei o Code First juntamente com o Migrations; e eles, por sua vez, usam o Entity Framework. Desta forma, foram criados o DbContext, o Controller ProdutoController e o Configuration.cs na pasta Migrations. Ou seja, o projeto está pronto e você já pode executá-lo e o banco de dados será criado em tempo de execução. Não vou mostrar o passo a passo porque já escrevi artigos mostrando isto, o foco aqui é produtividade, afinal, se o projeto todo já está criado com o Entity Framework, basta alterar as Actions dos Controllers para usar a classe de acesso a dados com o ADO.NET (que irei detalhar neste artigo). Enfim, espero que tenha acompanhado o raciocínio. Agora, se preferir escrever na mão todas as linhas, fique à vontade.

A título de curiosidade, quando você invocar o Migrations com o comando Enable-Migrations [nome do contexto], veja que no Configuration.cs é gerado o método Seed e eu já criei uma lista com alguns nomes de produtos (veja a listagem a seguir) a serem inseridos apenas na primeira vez que o Migration for executado com o Add-Migration [labelInicial] e o Update-Database (este, sim, cria o banco fisicamente).

protected override void Seed(Mvc_AdoNet.Models.BancoContexto context)
{
    //  This method will be called after migrating to the latest version.

    //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
    //  to avoid creating duplicate seed data. E.g.
    //
    //    context.People.AddOrUpdate(
    //      p => p.FullName,
    //      new Person { FullName = "Andrew Peters" },
    //      new Person { FullName = "Brice Lambson" },
    //      new Person { FullName = "Rowan Miller" }
    //    );
    //

    new List<Produto> { 
        new Produto { Nome="Produto A", Estoque=10, Preco=10 },
        new Produto { Nome="Produto B", Estoque=1, Preco=140 },
        new Produto { Nome="Produto C", Estoque=6, Preco=110 },
        new Produto { Nome="Produto D", Estoque=150, Preco=110 },
        new Produto { Nome="Produto E", Estoque=120, Preco=210 },
        new Produto { Nome="Produto F", Estoque=40, Preco=450 },
        new Produto { Nome="Produto G", Estoque=10, Preco=50 }
    }.ForEach(p => context.Produtos.Add(p));

}

Outra curiosidade a ser destacada é que quando criei o Controller ProdutoController, informei o nome do contexto a ser criado (novo), chamado BancoContexto, o qual herda de DbContext, faz a referência à chave BancoContexto criada no Web.Config e que aponta para o banco de dados. E também já referencia o DbSet para a entidade de Produto, conforme listagem a seguir.

public class BancoContexto : DbContext
{
    public BancoContexto() : base("name=BancoContexto")
    {
    }

    public DbSet<Produto> Produtos { get; set; }
}

Interface da classe

Quando pensamos em acesso a dados com o ADO.NET, logo vem à cabeça o uso de SqlConnection, DataReader, DataTable, ExecuteNonQuery, bons comandos do ADO.NET. Mas que tal criarmos uma implementação baseada em uma interface, com métodos bem definidos, a fim de tornar um padrão para toda e qualquer implementação das entidades no banco?

Sem dúvida, isto tem muita relevância, padrões e regras bem definidos é a melhor forma de desenvolver, principalmente na manutenção de códigos, equipes diferenciadas e descentralizadas. Sendo assim, crie uma pasta chamada DAL (Data Access Layer) e dentro desta, adicione uma interface chamada IGerenciaCRUD.

Veja a estrutura da IGerenciaCRUD, que já estou pensando em generalizar a interface, permitindo usá-la para qualquer funcionalidade de CRUD. Note que declaro o parâmetro chamado <TEntity> e digo que este é uma classe (where TEntity : class). O conteúdo da interface são os métodos de CRUD, que recebem ou a instrução SQL, o ID ou o objeto (TEntity) em si. Salve a classe e compile o projeto.

using System.Collections.Generic;

namespace Mvc_AdoNet.DAL
{
    interface IGerenciaCRUD<TEntity> where TEntity : class
    {
        List<TEntity> Get(string sql);
        TEntity GetById(int id);
        void Cadastra(TEntity obj);
        void Atualiza(TEntity obj);
        void Exclui(int id);
    }
}

Pergunta: Renatão, o certo não é criar um projeto de DAL e referenciá-lo neste projeto? Sim, você pode e deve criar um projeto de DAL para isolar o acesso a dados, mas como estou ilustrando com apenas uma entidade, farei no mesmo.

Classe de acesso a dados com ADO.NET

Uma vez que a interface esteja implementada, que será o padrão de código com os devidos métodos, chegou a hora de criarmos a classe de acesso a dados. Sendo assim, dentro da pasta DAL adicione uma classe chamada DALProduto. Adicione os usings necessários para referenciar a classe Produto em Models e o System.Collections.Generic. Assim que a classe é criada, o primeiro passo a fazer é implementar a interface IGerenciaCRUD, o que necessita referenciar a classe Produto. Mas o fato de escrever: IGerenciaCRUD<Produto> não adianta nada, então, o próprio Visual Studio já mostra o aviso de que é preciso implementar. Deixe o cursor no texto IGerenciaCRUD<Produto> e dê um CTRL + . (ponto). Isto irá implementar a estrutura desta interface. Pode babar com este recurso, caso não o conheça, pois evita erros de implementação.

Sem seguida, é preciso fazer uma referência ao banco de dados; na verdade precisamos saber qual é a string de conexão que está declarada no Web.Config, na chave BancoContexto, conforme a seguir.

<connectionStrings>
    <add name="BancoContexto" connectionString="Data Source=(localdb)\v11.0; Initial Catalog=BancoContexto-20140814211547; Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|BancoContexto-20140814211547.mdf"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

No código C# veja como fazer para ler tal chave:

string conexao = WebConfigurationManager.ConnectionStrings["BancoContexto"].ConnectionString;

Em seguida, você verá o código completo da classe já implementada usando o ADO.NET, onde detalho cada método.

O método Get deverá retornar uma lista de produtos, por isto o uso do List<Produto>. Ele recebe uma instrução SQL, abre a conexão com o SqlConnection, executa o comando com o SqlCommand e quando fizer o looping do DataReader com o ExecuteReader, adiciona cada produto na lista chamada dados. Esta lista de dados é que retorna para quem a chamou.

using Mvc_AdoNet.Models;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace Mvc_AdoNet.DAL
{
    public class DALProduto : IGerenciaCRUD<Produto>
    {
        // string de conexao
        string conexao = WebConfigurationManager.ConnectionStrings["BancoContexto"].ConnectionString;
        
        public List<Produto> Get(string sql)
        {
            string sql = "Select * FROM Produtoes ORDER BY Nome";
            using (var conn = new SqlConnection(conexao))
            {
                var cmd = new SqlCommand(sql, conn);
                List<Produto> dados = new List<Produto>();
                Produto p = null;
                try
                {
                    conn.Open();
                    using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            p = new Produto();
                            p.ProdutoID = (int)reader["ProdutoID"];
                            p.Nome = reader["Nome"].ToString();
                            p.Preco = (decimal)reader["Preco"];
                            p.Estoque = (int)reader["Estoque"];
                            dados.Add(p);
                        }
                    }
                }
                finally
                {
                    conn.Close();
                }
                return dados;
            }
        }

Neste método GetById será preciso aplicar um filtro que é o próprio ID declarado como parâmetro do método. Note que o retorno do método é um objeto do tipo Produto. É usado o SqlConnection e é declarada a instrução T-SQL com o cuidado de declarar o parâmetro com o cmd.Parameters no SqlCommand para evitar injeção de SQL por hackers. Em seguida, é usado o ExecuteReader para verificar se há algum registro correspondente; e caso haja, é montado o objeto Produto com as devidas propriedades para retornar no método.

public Produto GetById(int id = 0)
        {
            using (var conn = new SqlConnection(conexao))
            {
                string sql = "Select * FROM Produtoes WHERE ProdutoID=@cod";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@cod", id);
                Produto p = null;
                try
                {
                    conn.Open();
                    using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.HasRows)
                        {
                            if (reader.Read())
                            {
                                p = new Produto();
                                p.ProdutoID = (int)reader["ProdutoID"];
                                p.Nome = reader["Nome"].ToString();
                                p.Preco = (decimal)reader["Preco"];
                                p.Estoque = (int)reader["Estoque"];
                            }
                        }
                    }
                }
                finally
                {
                    conn.Close();
                }
                return p;
            }
        }

Veja o método Cadastra que recebe o objeto Produto montado, usa o SqlConnection e o SqlCommand com todos os parâmetros declarados, e na execução do ExecuteNonQuery é inserido o produto no banco de dados. Cabe ressaltar a escrita da instrução Insert do T-SQL com os parâmetros. Muitos desenvolvedores usam a concatenação de strings para montar o T-SQL, sem ter o cuidado de declarar os parâmetros. Isto é uma porta aberta para injeção de códigos maliciosos, portanto, atente a isto.

public void Cadastra(Produto obj)
        {
            using (var conn = new SqlConnection(conexao))
            {
                string sql = "INSERT INTO Produtoes (Nome, Preco, Estoque) VALUES (@nome, @preco, @estoque)";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@nome", obj.Nome);
                cmd.Parameters.AddWithValue("@preco", obj.Preco);
                cmd.Parameters.AddWithValue("@estoque", obj.Estoque);
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    conn.Close();
                }
            }
        }

Já o método Atualiza recebe o objeto Produto montado, usa o SqlConnection, monta a instrução Update do T-SQL e no SqlCommand são declarados todos os parâmetros. Ao final, o ExecuteNonQuery efetiva a operação.

public void Atualiza(Produto obj)
        {
            using (var conn = new SqlConnection(conexao))
            {
                string sql = "UPDATE Produtoes SET Nome=@nome, Preco=@preco, Estoque=@estoque Where ProdutoID=@cod";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@cod", obj.ProdutoID);
                cmd.Parameters.AddWithValue("@nome", obj.Nome);
                cmd.Parameters.AddWithValue("@preco", obj.Preco);
                cmd.Parameters.AddWithValue("@estoque", obj.Estoque);
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    conn.Close();
                }
            }
        }

E para finalizar, temos o método Exclui, que recebe apenas o ID do produto, monta a instrução Delete do T-SQL e finaliza com a execução do ExecuteNonQuery.

public void Exclui(int id = 0)
        {
            using (var conn = new SqlConnection(conexao))
            {
                string sql = "DELETE Produtoes Where ProdutoID=@cod";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@cod", id);
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }
}

A dica para a passagem de parâmetros é que, às vezes, é preciso passar uma coleção de parâmetros, pois muitas vezes montam-se os critérios da clausula Where do T-SQL dinamicamente. Neste caso, indico que seja um List<> de dicionário, tipo uma HashTable chave e valor. Assim, na hora de montar o T-SQL você faz um looping nesta coleção List.

Controller com o ADO.NET ao invés do Entity Framework

No início do artigo, eu disse que criei o Controller baseado no Entity Framework, assim todas as Actions são criadas automaticamente. Agora que já temos a classe DALProduto criada, é preciso alterar algumas coisas no Controller ProdutoController, a fim de adaptar o código para o uso do ADO.NET.

Veja a seguir a listagem completa do Controller ProdutoController.cs com as referências dos usings necessários, os códigos comentados do Entity Framework e os novos códigos que fazem referência à classe DALProduto. Cada Action está bem descrita e documentada, a fim de melhorar o entendimento e as chamadas dos métodos da classe de acesso a dados.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Mvc_AdoNet.Models;

using System.Data.SqlClient;
using Mvc_AdoNet.DAL;

namespace Mvc_AdoNet.Controllers
{
    public class ProdutoController : Controller
    {
        //private BancoContexto db = new BancoContexto();
        DALProduto DAL = new DALProduto();
        
        //
        // GET: /Produto/

        public ActionResult Index()
        {
            // usando EF
            //return View(db.Produtos.ToList());

            // usando ADO.NET com DAL
            return View(DAL.Get().ToList())
        }

        //
        // GET: /Produto/Details/5

        public ActionResult Details(int id = 0)
        {
            // usando EF
            //Produto produto = db.Produtos.Find(id);
            
            //usando ADO.NET com DAL
            Produto produto = DAL.GetById(id);

            if (produto == null)
            {
                return HttpNotFound();
            }
            return View(produto);
        }

        //
        // GET: /Produto/Create

        public ActionResult Create()
        {
            return View();
        }

        //
        // POST: /Produto/Create

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create(Produto produto)
        {
            if (ModelState.IsValid)
            {
                // usando EF
                //db.Produtos.Add(produto);
                //db.SaveChanges();

                // usando ADO.NET com DAL
                DAL.Cadastra(produto);
                return RedirectToAction("Index");
            }

            return View(produto);
        }

        //
        // GET: /Produto/Edit/5

        public ActionResult Edit(int id = 0)
        {
            // usando EF
            //Produto produto = db.Produtos.Find(id);

            //usando ADO.NET com DAL
            Produto produto = DAL.GetById(id); 
            
            if (produto == null)
            {
                return HttpNotFound();
            }
            return View(produto);
        }

        //
        // POST: /Produto/Edit/5

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit(Produto produto)
        {
            if (ModelState.IsValid)
            {
                // usando EF
                //db.Entry(produto).State = EntityState.Modified;
                //db.SaveChanges();

                //usando ADO.NET com DAL
                DAL.Atualiza(produto);
                
                return RedirectToAction("Index");
            }
            return View(produto);
        }

        //
        // GET: /Produto/Delete/5

        public ActionResult Delete(int id = 0)
        {
            // usando EF
            //Produto produto = db.Produtos.Find(id);

            //usando ADO.NET com DAL
            Produto produto = DAL.GetById(id); 

            if (produto == null)
            {
                return HttpNotFound();
            }
            return View(produto);
        }

        //
        // POST: /Produto/Delete/5

        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int id)
        {
            // usando EF
            //Produto produto = db.Produtos.Find(id);
            //db.Produtos.Remove(produto);
            //db.SaveChanges();

            //usando ADO.NET com DAL
            DAL.Exclui(id); 

            return RedirectToAction("Index");
        }

        //protected override void Dispose(bool disposing)
        //{
        //    db.Dispose();
        //    base.Dispose(disposing);
        //}
    }
}

Execução do projeto

Compile todo o projeto e execute-o. Veja que os produtos aparecem no cadastro, afinal quando usei o Migrations para criar o banco, o método Seed já cadastrou uma lista de produtos, conforme a figura 2. Agora basta testar toda a manutenção CRUD dos produtos.

Sugiro que você coloque breakpoints em cada Action do Controller e execute passo a passo F11 para acompanhar que o DALProduto é chamado e executado conforme a ação.

Conclusão

Nem sempre o que há de mais novo se aplica à todos os clientes e aplicações. É preciso pensar no legado, na integração e produtividade. Por isto que neste artigo abordei uma criação inicial com o que há de mais recente e produtivo no MVC com Entity Framework, e depois implementamos a interface e classe de acesso a dados em ADO.NET.

Agradeço a oportunidade de poder compartilhar o conhecimento deste artigo. Qualquer dúvida e preparação de times de desenvolvimento, por favor, me contate.