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.
De 0 a 10, o quanto você recomendaria este artigo para um amigo?