Entity Framewor - Usando Stored Procedures

Entity Framework - Usando Stored Procedures

Você já deve estar ciente que é melhor usar Stored Procedures (SP) a consultas SQL dinâmicas; como as SP são parametrizadas elas não são suscetíveis aos ataques de injeção SQL, e, além disso você pode controlar os riscos que uma conta de usuário pode ter atribuindo permissões de execução em SP específicas.

Usando SP você só precisa atribuir permissão para a SP enquanto que para as consultas SQL dinâmicas você deve atribuir permissões nas tabelas relacionadas.

Então sempre que possível é aconselhável usar Stored Procedures a Consultas SQL dinâmicas.

Neste artigo veremos como usar Stored Procedures com o Entity Framework (EF) lembrando que o SQL gerado pelo EF (por trás dos panos) é parametrizado e por isso se enquadra nos quesitos de segurança das SP.

Como exemplo eu vou usar o Visual Web Developer 2010 Express Edition em uma aplicação ASP .NET Web Forms mas poderia ter usado tanto o Visual Studio 2010 como as versões Express do Visual Basic e C#.

Criando o Entity Data Model (EDM)

Abra o Visual Web Developer 2010 (WVD) e crie um novo projeto do tipo ASP .NET Web Application usando a linguagem VB .NET e atribuindo o nome EF_Usando_SP;

Optei por usar este template pois vou aproveitar os recursos e a estrutura já criada neste modelo.

Vamos incluir um EDM no projeto via menu Project-> Add New Item escolhendo o template ADO .NET Entity Data Model e informando o nome Northwind.edmx visto que vou usar o banco de dados Northwind.mdf neste exemplo:

Vamos gerar o modelo a partir de um banco de dados existente :

Escolha a conexão existente com o Northwind.mdf (se ela não existir clique em New Connection para criar a conexão);

Selecione a tabela Products e a tabela Categories e clique em Finish;

Observando o modelo de entidades gerado e analisando o mapeamento na janela Model Browser vemos que não temos nenhuma stored procedure mapeada;

Vamos então abrir a janela DataBase Explorer e expandir os objetos para o nó Stored Procedures no banco de dados Northwind;

Vemos que já existem algumas stored procedures criadas mas para esse exemplo irei criar duas novas stored procedures;

Criando as Stored Procedures

Clique com o botão direito do mouse sobre Stored Procedures e selecione Add New Stored Procedure;

Vamos criar a stored procedure chamada sproc_Produtos digitando o seguinte comando na janela do editor:

CREATE PROCEDURE sproc_Produtos
AS
Select ProductID, ProductName, UnitsInStock, UnitPrice
From Products
RETURN

Observe que a stored procedure criada aparece na relação de objetos stored procedures do banco de dados;

Vamos criar outra Store Procedure usando um parâmetro de forma a obter os produtos por categoria; para isso digite o código abaixo na janela do Editor:

CREATE PROCEDURE [dbo].[sproc_ProdutosPorCategoria]
@categoria int
AS
SELECT * FROM Products
WHERE CategoryID = @categoria

Após Salvar a stored procedures teremos duas novas SP criadas no banco de dados conforme mostra a figura abaixo:

Tudo bem, já temos as stored procedures. Como podemos mapeá-las para o nosso modelo de entidades ???

Primeiro temos que incluir as SP criadas em nosso modelo.

Clique com o botão direito do mouse sobre o descritor EDM e clique em Update Model from Database;

A seguir marque as duas stored procedures criadas na aba Add da janela Choose Your Database Objects e clique em Finish;

Ao final veremos na janela Model Browser o modelo atualizado exibindo as stored procedures:

Mas ainda não terminamos !!!!

Observe que embora o modelo de armazenamento contenha as stored procedures ainda temos que mapeá-las para o modelo de entidades;

Vamos fazer isso...

Mapeando as Stored Procedures no Modelo

Clique com o botão direito do mouse sobre o modelo de entidades no descritor e no menu suspenso selecione Add-> Function Import...

Vamos começar mapeando a a stored procedure sproc_Produtos :

1- Informe um nome para a identificar a função relativa a essa SP: RelacaoDeProdutos
2- Seleciona a stored procedure: sproc_Produtos
3- Vamos mapear esta SP com um tipo complexo (Complex Type);
4- Clique no botão - Get Column Information para obter a relação as colunas do banco de dados e seus tipos de dados;
5- Clique no botão - Create New Complex Type;

6- Será gerada um tipo complexo e o seu nome exibido na caixa Complex;
7- Clique no botão OK;

A final na janela Model Browser veremos a função RelacaoDeProdutos mapeado como um tipo complexo para a stored procedure sproc_Produtos;

Agora vamos mapear a stored procedure sproc_ProdutosPorCategoria, definindo os seguintes passos:

1- Informe um nome para a identificar a função relativa a essa SP: ProdutosPorCategoria
2- Seleciona a stored procedure: sproc_ProdutosPorCategoria
3- Vamos mapear esta SP com retornando uma Entidade do tipo Product;
4- Clique no botão OK;

Na janela Model Browser podemos ver a função gerada pelo mapeamento.

Usando as funções mapeadas para as Stored Procedures

Vamos agora mostrar como podemos usar as stored procedures mapeadas em nosso modelo de entidades.

Abra o arquivo Default.aspx e, a partir da ToolBox, inclua os seguintes controles no formulário web:

Label - Text = Informe a Categoria
DropDownList - ID=ddlCategoria
Button - ID=btnFiltrar - Text =Filtrar Por Categoria
Button - ID=btnProdutos - Text=Exibir Todos os Produtos

Conforme o leiaute a abaixo:

Vamos vincular ao controle DropDownList a entidade Category da seguinte forma:

Selecione o controle DropDownList e clique em Choose Data Source;
Selecione o item Entity e aceite o nome padrão EntityDataSource1;
Selecione a conexão NorthwindEntities;
Em EntitySetName escolha a entidade Categories e defina os campos CategoryID e CategoryName;
Ao final defina o campo CategoryName na exibição das categorias e o campo CategoryID como valor da seleção;

Agora vamos definir o código de cada evento Click para os botões de comando:

1- Botão - Filtrar por Categoria

Protected Sub btnFiltrar_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnFiltrar.Click

        Dim contexto As New NorthwindEntities

        gdvProdutos.DataSource = contexto.ProdutosPorCategoria(ddlCategoria.SelectedValue)
        gdvProdutos.DataBind()

    End Sub

2- Botão - Exibir todos os Produtos

Protected Sub btnProdutos_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnProdutos.Click

        Dim contexto As New NorthwindEntities

        gdvProdutos.DataSource = contexto.RelacaoDeProdutos
        gdvProdutos.DataBind()

    End Sub

Note que estamos usando as funções que mapeamos para as stored procedures que criamos no banco de dados.

RelacaoDeProdutos - retorna todos os produtos
ProdutosPorCategoria(ddlCategoria.SelectedValue) - retorna os produtos pela categoria selecionada

Executando o projeto e testando cada uma das opções teremos:

Função: ProdutosPorCategoria(ddlCategoria.SelectedValue) - Filtra por categoria

Função : RelacaoDeProdutos - retorna todos os produtos

Viu como é simples criar, mapear e usar procedimentos armazenados com o Entity Framework !!!

Pegue o projeto completo aqui : EF_Usando_SP.zip

1Pedro 2:15 Porque assim é a vontade de Deus, que, fazendo o bem, façais emudecer a ignorância dos homens insensatos,
1Pedro 2:16 como livres, e não tendo a liberdade como capa da malícia, mas como servos de Deus.

Referências:

José Carlos Macoratti