O objetivo deste artigo é mostrar o uso de Stored Procedures no Entity Framework 6 através do recurso de Code First. Esta funcionalidade foi implementada no EF 6 e está disponível apenas para operações de Insert, Delete e Update. Outra característica é que só funciona para quem usa o recurso de Code First, ou seja, primeiro você cria a estrutura de classes, implementa na aplicação e, ao executar, o banco de dados é criado na hora.
Outro recurso importante que você aprenderá neste artigo é o uso do Migrations, com a finalidade de manipular as operações no banco de dados diretamente em C#, pelo Visual Studio 2013. Tais operações podem ser desde a criação do banco, das entidades, das propriedades das entidades e campos, assim como as Stored Procedures. Todo e qualquer implementação ocorre através de um recurso chamado Fluent API, o qual você terá que dominar e entender o uso. E, para finalizar a introdução, saber o que é e como utilizar expressões Lambda é o mínimo. Portanto, se você nunca usou Lambda, trate de aprender o mais rápido possível; garanto que a sua vida de desenvolvedor não será a mesma com o conhecimento dessas expressões.
Os pré-requisitos para este artigo é o Visual Studio .NET 2013.
Projeto ASP.NET MVC 5
O recurso deste artigo pode ser usado em qualquer tipo de projeto. Mas, escolhi um projeto do tipo ASP.NET MVC 5, assim já teremos tudo pronto pra facilitar. Sendo assim, abra o Visual Studio 2013, selecione a opção File / New / Project ou CTRL + SHIFT + N ou na janela inicial, clique em New Project. Conforme a figura 1, selecione a linguagem Visual C#, template de Web. No nome do projeto digite SProcedure_EF6 e no location você pode gravar onde desejar.
Clique no botão OK. O Visual Studio 2013 abrirá uma janela para você selecionar o template, portanto, selecione MVC, conforme a figura 2. Para o modo de autenticação, clique no botão Change Authentication, selecione No Authentication.
Clique no botão OK e deixe que o Visual Studio crie o projeto. Veja na figura 3 que o Solution Explorer contém a estrutura básica de um projeto ASP.NET MVC.
Definição da classe
O próximo passo é criar uma classe com as devidas propriedades dentro do folder Models. De acordo com o padrão MVC, na pasta Models são inseridas todas as classes referentes as entidades. Para isto, clique com o botão direito e selecione Add / Class. No nome da classe digite Medico, conforme figura 4.
Clique no botão Add e digite as seguintes propriedades:
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace SProcedure_EF6.Models
{
public class Medico
{
public int medicoID { get; set; }
public string nome { get; set; }
public string celular { get; set; }
public string especialidade { get; set; }
public int rating { get; set; }
public decimal preco { get; set; }
}
}
Veja que a chave da entidade não está especificada com o atributo Key do Data Annotations. Na versão 6 do Entity Framework isto não é preciso desde que a propriedade chave seja composta pelo nome da classe + ID, ou seja, se a classe se chama Medico, a propriedade medicoID é automaticamente identificada como chave primária. Já as demais propriedades são básicas como o nome, o celular, a especialidade, o rating e o preço da consulta. Caso queira adicionar outras propriedades, fique à vontade.
Instalação do Entity Framework
Agora precisamos instalar o EF neste projeto. Para isto, selecione o menu Tools / Library Package Manager / Package Manager Console. Será aberta uma janela do Nuget, então digite:
Install-Package EntityFramework
Pressione ENTER e aguarde que o EF seja instalado neste projeto. Para que isto ocorra, é fundamental estar conectado à internet. Esta linha de comando irá instalar o EF, e caso já tenha o EF instalado no projeto e queira atualizar, use:
Update-Package EntityFramework
Veja o resultado da instalação na figura 5. Note que foi instalado a versão 6.0.2, ou seja, via Nuget você não tem que ficar pesquisando qual a última versão, o Visual Studio faz isto pra você e instala corretamente. E se houver pré-requisitos? Não se preocupe que o Nuget também instala todos os pré-requisitos que uma determinada DLL necessita.
Compilar o projeto e criar um Controller
O próximo passo é compilar o projeto, então selecione o menu Build / Build Solution. Isto é necessário porque iremos adicionar um Controller, e sem compilar a classe criada, não é possível. Agora vamos adicionar um Controller para a classe Medico. Para isto, clique com o botão direito na pasta Controller, selecione Add / Controller. Na janela de Scaffold (templates) aberta, selecione MVC 5 Controller with views, using Entity Framework, conforme a figura 6.
Clique no botão Add. Será aberta uma janela, conforme a figura 7, onde você deverá digitar o nome do Controller (Controller name), neste caso será MedicoController. Em Model class, selecione a classe Medico, por isto é preciso compilar o projeto com sucesso, senão a classe não é listada aqui. Em Data context class é preciso informar o nome da classe de contexto, e como não temos, clique no botão New data context e preencha com o texto SProcedure_EF6. O Visual Studio sugeri este nome e nada o impede de alterar para outro, é preciso apenas o nome da classe de contexto que será gerada.
Clique no botão Add e aguarde o Visual Studio criar o respectivo MedicoController e todas as Views (UI) para fazer o CRUD de dados.
O que é um contexto?
Para o Entity Framework, um contexto é o local na memória onde estarão vinculadas todas as classes listadas no DbSet. Toda a parte de criação, exclusão, abertura de conexão do banco de dados está embutida na API chamada DbContext. Note que na pasta Models foi criada uma classe chamada SProcedure_EF6Context.cs que herda o DbContext. Veja ainda que o DbSet referencia a classe Medico, que é responsável por todas as operações no banco de dados desta classe. Tudo isto é criado dinamicamente, ou seja, você não escreve nunca mais nenhuma linha de instrução T-SQL. Sugiro você se aprofundar no DbContext para entender melhor.
C#
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
namespace SProcedure_EF6.Models
{
public class SProcedure_EF6Context : DbContext
{
public SProcedure_EF6Context() : base("name=SProcedure_EF6Context")
{
}
public DbSet<Medico> Medicos { get; set; }
}
}
Controller
Se você abrir a pasta Controller verá que foi gerado o arquivo MedicoController.cs, que herda de Controller. Ou seja, tudo o que for referente a API de Controller já está criado. Você não precisa implementar nada, somente as funcionalidades específicas da classe Medico. Antes do construtor desta classe já há referência do contexto SProcedure_EF6Context, ou seja, acessa o banco de dados, todos os métodos (Actions) de Index, Details, Edit e Delete para gerenciar a classe Medico diretamente no banco de dados.
C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using SProcedure_EF6.Models;
namespace SProcedure_EF6.Controllers
{
public class MedicoController : Controller
{
private SProcedure_EF6Context db = new SProcedure_EF6Context();
// GET: /Medico/
public ActionResult Index()
{
return View(db.Medicos.ToList());
}
// GET: /Medico/Details/5
public ActionResult Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Medico medico = db.Medicos.Find(id);
if (medico == null)
{
return HttpNotFound();
}
return View(medico);
}
// GET: /Medico/Create
public ActionResult Create()
{
return View();
}
// POST: /Medico/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include="medicoID,nome,celular,especialidade,rating,preco")] Medico medico)
{
if (ModelState.IsValid)
{
db.Medicos.Add(medico);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(medico);
}
// GET: /Medico/Edit/5
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Medico medico = db.Medicos.Find(id);
if (medico == null)
{
return HttpNotFound();
}
return View(medico);
}
// POST: /Medico/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include="medicoID,nome,celular,especialidade,rating,preco")] Medico medico)
{
if (ModelState.IsValid)
{
db.Entry(medico).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(medico);
}
// GET: /Medico/Delete/5
public ActionResult Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Medico medico = db.Medicos.Find(id);
if (medico == null)
{
return HttpNotFound();
}
return View(medico);
}
// POST: /Medico/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
Medico medico = db.Medicos.Find(id);
db.Medicos.Remove(medico);
db.SaveChanges();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
Novamente compile o projeto até que esteja 100% com sucesso. Como não mudamos nenhum código, isto deve ocorrer na primeira vez.
Views (UI interface de usuário)
Quando o Controller é criado usando o template citado anteriormente, automaticamente são criadas todas as Views (telas do usuário). Note na figura 8 que dentro da pasta Views/Medico existem 5 arquivos criados de acordo com o template padrão do MVC 5, permitindo ao usuário realizar todas as operações de CRUD no banco de dados.
Pronto, o projeto está criado e podemos executá-lo. No entanto, vamos aproveitar e fazer um pequeno ajuste. Na pasta Views/Shared, abra o arquivo _Layout.cshtml; localize o código a seguir e adicione a linha que monta o menu de Médicos, apontando para a Action Index do Controller Medico.
C#
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("Medicos", "Index", "Medico")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
</div>
Excelente! Agora sim, você pode executar o projeto. Pressione F5 para abrir a aplicação no navegador. Clique no menu Medicos, cadastre uns uns médicos para termos alguns dados no banco, conforme a figura 9.
Até aqui você não sabe o que o Entity Framework está gerando de instrução T-SQL para executar no banco. Será que ele usa uma Stored Procedure ou um T-SQL? Neste caso, garanto que é um T-SQL, pois você não informou ao projeto que será uma Stored Procedure.
LOG no Entity Framework
Para que possamos rastrear e visualizar o que o EF está fazendo nos bastidores, vamos aprender uma nova funcionalidade do EF 6, o uso de Log. Abra o MedicoController.cs, adicione o respectivo using System.Diagnostics para que possamos visualizar o resultado na janela de Output do Visual Studio durante a execução. E, para implementar o Log, adicione o construtor da classe e o código db.Database.Log, que contém a expressão Lambda x => Debug.Write(x). Isto fará com que seja impressa na janela de Output do VS a instrução T-SQL usada no momento.
C#
using System.Diagnostics;
namespace SProcedure_EF6.Controllers
{
public class MedicoController : Controller
{
private SProcedure_EF6Context db = new SProcedure_EF6Context();
public MedicoController()
{
db.Database.Log = x => Debug.Write(x);
}
// GET: /Medico/
public ActionResult Index()
{
return View(db.Medicos.ToList());
}
Execute (F5) o projeto novamente. Clique no menu Medicos para listar os médicos cadastrados. Em seguida, não feche o navegador, e sim, alterne (ALT + TAB) para o VS. Abra a janela de Output (menu Debug / Window / Output) e veja a instrução Select usada, assim como a data e hora da execução e quantos milisegundos levaram. Este código usa o método (Action no Controller) Index, o qual seleciona todos os campos da entidade Medicos e retorna uma lista para a View (UI Index do Controller Medico).
SELECT
SELECT
[Extent1].[medicoID] AS [medicoID],
[Extent1].[nome] AS [nome],
[Extent1].[celular] AS [celular],
[Extent1].[especialidade] AS [especialidade],
[Extent1].[rating] AS [rating],
[Extent1].[preco] AS [preco]
FROM [dbo].[Medicos] AS [Extent1]
-- Executing at 17/02/2014 16:40:14 -03:00
-- Completed in 24 ms with result: SqlDataReader
Isto significa que está sendo usada uma instrução T-SQL. Em seguida, cadastre um novo médico com alguns dados. Ao clicar no botão Create da janela de cadastro, será gerada uma instrução T-SQL do tipo INSERT no Log. Então, alterne para o VS, confira a instrução INSERT com todos os parâmetros contendo os dados. Aqui o método (Action do Controller) chamado é o Create que recebe como parâmetro da View o objeto Medico preenchido com todos os dados.
SQL
INSERT [dbo].[Medicos]([nome], [celular], [especialidade], [rating], [preco]) VALUES (@0, @1, @2, @3, @4) SELECT [medicoID] FROM [dbo].[Medicos] WHERE @@ROWCOUNT > 0 AND [medicoID] = scope_identity() -- @0: 'Alvaro' (Type = String, Size = -1) -- @1: '8098098' (Type = String, Size = -1) -- @2: 'Urologista' (Type = String, Size = -1) -- @3: '3' (Type = Int32) -- @4: '190' (Type = Decimal, Precision = 18, Scale = 2)
Como criar e usar Stored Procedures?
A criação de Stored Procedures é feita diretamente no banco de dados. Caso o seu time tenha um DBA, com certeza ele é que fará a análise do código T-SQL e criará a SP. No entanto, se você não tem DBA, cabe a você criar a SP. Mas, a pergunta é: por que criar a SP na mão se o Visual Studio com o EF pode fazer este trabalho pra nós? E, o melhor, sem errar na sintaxe e na declaração de parâmetros.
Mas, para isto é preciso aprender o Migrations, que é uma forma de usar a técnica de manipular o banco de dados via código C# sem precisar abrir o banco em si. Confesso que faz anos que não abro o banco; faço tudo via Migrations, pois é mais seguro, fácil, produtivo e ainda consigo guardar diversas versões do mesmo banco de dados. Hoje vou abordar apenas as funcionalidades do Migrations necessárias para este artigo, então, cabe a você estudar um pouco.
Todos os códigos do Migrations são executados via Nuget, portanto, abra a janela do Package Manager Console. O primeiro código é ativar o Migrations, então, digite (você pode usar a tecla TAB para o auto preenchimento):
PM> Enable-Migrations
Caso tenha mais de um contexto é preciso fornecer o nome completo do contexto a ser aplicado o Migrations. Veja a seguir o resultado da execução. Note que foi detectado o banco de dados e foi criada uma pasta chamada Migrations contendo versões das classes C# para fazer o upgrade ou downgrade.
Note:
PM> Enable-Migrations
Checking if the context targets an existing database…
Detected database created with a database initializer. Scaffolded migration ‘201402171929506_InitialCreate’ corresponding to existing database. To use an automatic migration instead, delete the Migrations folder and re-run Enable-Migrations specifying the -EnableAutomaticMigrations parameter.
Code First Migrations enabled for project SProcedure_EF6.
Pronto, o projeto já contém o Migrations. Agora é preciso informar ao contexto que iremos usar Stored Procedures. Abra o arquivo SProcedure_EF6Context.cs e digite o bloco do override a seguir. É preciso usar o Fluent API para informar ao modelBuilder (é o parâmetro do tipo DbModelBuilder) que usará a Entidade (Entity) declarada Medico (nome da classe), mapeando-a para Stored Procedure.
C#
public class SProcedure_EF6Context : DbContext
{
public SProcedure_EF6Context() : base("name=SProcedure_EF6Context")
{
}
public DbSet<Medico> Medicos { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Criar as Stored Procedures
modelBuilder.Entity<Medico>().MapToStoredProcedures();
}
}
Compile o projeto. Em seguida, no Nuget, digite a primeira linha para adicionar um ponto de migração (Add-Migration) chamado criaSPs (você pode dar qualquer nome).
Note:
PM> Add-Migration criaSPs
Scaffolding migration ‘criaSPs’.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running ‘Add-Migration criaSPs’ again.
Tecle ENTER e prepare-se para a grande emoção. Veja que o VS cria um arquivo chamado 201402172004463_criaSPs.cs dentro da pasta Migrations, contendo os códigos em C# para criar três tipos de Stored Procedures, sendo: Insert, Update e Delete. Observe que o nome é o nome da classe seguido do caracter underscore (_), seguido da palavra chave Insert, Update ou Delete. Veja como que são declarados os parâmetros e as instruções T-SQL. Isto é fantástico! Sem palavras para o time do Entity Framework, parabéns!
C#
namespace SProcedure_EF6.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class criaSPs : DbMigration
{
public override void Up()
{
CreateStoredProcedure(
"dbo.Medico_Insert",
p => new
{
nome = p.String(),
celular = p.String(),
especialidade = p.String(),
rating = p.Int(),
preco = p.Decimal(precision: 18, scale: 2),
},
body:
@"INSERT [dbo].[Medicos]([nome], [celular], [especialidade], [rating], [preco])
VALUES (@nome, @celular, @especialidade, @rating, @preco)
DECLARE @medicoID int
SELECT @medicoID = [medicoID]
FROM [dbo].[Medicos]
WHERE @@ROWCOUNT > 0 AND [medicoID] = scope_identity()
SELECT t0.[medicoID]
FROM [dbo].[Medicos] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[medicoID] = @medicoID"
);
CreateStoredProcedure(
"dbo.Medico_Update",
p => new
{
medicoID = p.Int(),
nome = p.String(),
celular = p.String(),
especialidade = p.String(),
rating = p.Int(),
preco = p.Decimal(precision: 18, scale: 2),
},
body:
@"UPDATE [dbo].[Medicos]
SET [nome] = @nome, [celular] = @celular, [especialidade] = @especialidade, [rating] = @rating, [preco] = @preco
WHERE ([medicoID] = @medicoID)"
);
CreateStoredProcedure(
"dbo.Medico_Delete",
p => new
{
medicoID = p.Int(),
},
body:
@"DELETE [dbo].[Medicos]
WHERE ([medicoID] = @medicoID)"
);
}
public override void Down()
{
DropStoredProcedure("dbo.Medico_Delete");
DropStoredProcedure("dbo.Medico_Update");
DropStoredProcedure("dbo.Medico_Insert");
}
}
}
E para que servem os métodos Up e Down? Servem para fazer o upgrade e o downgrade. E, como temos o arquivo .cs criado, é preciso informar ao banco de dados para criar as SPs, correto?
Para isto, abra o Nuget e digite Update-Database:
SQL
PM> Update-Database Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Applying explicit migrations: [201402172004463_criaSPs]. Applying explicit migration: 201402172004463_criaSPs. Running Seed method.
Onde está o banco de dados com as Stored Procedures?
Neste projeto, o banco de dados está embutido na pasta App_Data. Isto porque no Web.Config eu não configurei para nenhum outro provedor ou máquina. Caso queira alterar, basta informar a string de conexão completa no Web.Config. No Solution Explorer, clique no ícone para mostrar todos os arquivos do projeto, conforme a figura 10. Na pasta App_Data é mostrado o banco de dados com um nome enorme, mas isto é porque eu não alterei no Web.Config para um outro nome.
Com o banco mostrado no App_Data, dê um duplo clique para abri-lo. Expanda a pasta Stored Procedures e observe que foram criadas três, conforme a figura 11. Pronto, nunca mais você irá escrever T-SQL para SPs. Faça tudo com códigos C# com Fluent API e expressões Lambda. E, use o Migrations para atualizar o banco de dados.
E as Stored Procedures de Select? Isto ainda não é possível, pois são dinâmicas e depende de cada situação.
No início do artigo eu disse que este recurso de Stored Procedures só está disponível para quem usa o Code First. No entanto, se você já tiver o banco de dados pronto, você pode usar o recurso de engenharia reversa com o Entity Framework, e em seguida, aplicar este recurso de Stored Procedures através do Migrations.
Usando as Stored Procedures
Agora vem a melhor parte deste artigo. Você se lembra quantos códigos tivemos que alterar no Controller para que ele use Stored Procedures? Nenhum. Isto mesmo: nenhum código. E o compilador identifica que há SPs e usa. Caso contrário, se algum dia você decidir não usar SPs, sem problemas, basta exclui-las no banco de dados e o seu projeto irá criar T-SQL de acordo com a necessidade.
Sendo assim, execute (F5) o projeto no navegador. Clique no menu Medicos e cadastre um novo médico. Abra a janela de Output e veja que foi usada a SP Medico_Insert, contendo todos os parâmetros digitados pelo usuário.
dbo
[dbo].[Medico_Insert] -- nome: 'Patricia' (Type = String, Size = 1073741823) -- celular: '984093284' (Type = String, Size = 1073741823) -- especialidade: 'Cardiologista' (Type = String, Size = 1073741823) -- rating: '5' (Type = Int32, IsNullable = false) -- preco: '390' (Type = Decimal, IsNullable = false, Precision = 18, Scale = 2) -- Executing at 17/02/2014 17:28:57 -03:00 -- Completed in 29 ms with result: SqlDataReader
Experimente alterar os dados de um médico. Note no Output que foi usada a SP Medico_Update.
dbo
[dbo].[Medico_Update] -- medicoID: '1' (Type = Int32, IsNullable = false) -- nome: 'Renato' (Type = String, Size = 1073741823) -- celular: '4234823948' (Type = String, Size = 1073741823) -- especialidade: 'Neurologista' (Type = String, Size = 1073741823) -- rating: '3' (Type = Int32, IsNullable = false) -- preco: '480' (Type = Decimal, IsNullable = false, Precision = 18, Scale = 2) -- Executing at 17/02/2014 17:30:35 -03:00 -- Completed in 70 ms with result: 1
É possível alterar o nome da Stored Procedure?
Muitos desenvolvedores questionam os nomes gerados das SPs. É claro que o DBA não aceita o padrão do EF, ou melhor, exige que seja usado o padrão da empresa. Então, basta usar o Migrations para renomear. Para isto, abra o arquivo SProcedure_EF6Context.cs e logo após o código já existente para mapear as SPs. Customize o nome da SP Medico_Insert para AdicionaMedico. Veja que no código basta referenciar a entidade Medico e usar a expressão Lambda para mapear a SP de Insert (x.Insert) e informar o novo nome.
C#
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Criar as Stored Procedures
modelBuilder.Entity<Medico>().MapToStoredProcedures();
// alterar o nome da SP de Insert
modelBuilder.Entity<Medico>()
.MapToStoredProcedures(
x => x.Insert(i => i.HasName("AdicionaMedico")));
}
Compile o projeto novamente. Abra o Nuget e digite:
PM> Add-Migration renomearSPInsert
Em seguida, execute a alteração:
PM> Update-Database
Abra o banco de dados e certifique-se que a SP foi renomeada, conforme a figura 12.
Conclusão
Aprender novos recursos é fundamental para o progresso e a evolução do desenvolvedor. O Entity Framework 6 implementou vários recursos, e este de Stored Procedures é um dos pontos chaves de um time de desenvolvimento. Todos sabemos que há discussões sobre os prós e contras de Stored Procedures, mas o foco aqui não é este, e sim, mostrar como aplicar tal recurso do fantástico Migrations para nos auxiliar na aplicação do recurso em si.
Agradeço a oportunidade de poder compartilhar o conhecimento deste novo recurso do Entity Framework. 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?