LINQ to Entities, Trabalhando com Stored Procedures

Trabalhar com Stored Procedures é sem dúvida uma boa ideia quando se trabalha com banco de dados.  Encapsular funções de forma simples, diminuir o trafego na rede e diminuir a repetição de código são apenas algumas das vantagens da utilização desta tecnologia. A Microsoft disponibilizou no Entity Framework 4 uma forma de utilizalar-mos nossas Stored Procedures como se fossem métodos do objeto de mapeamento e a capacidade de retornar os mais variados tipos podendo ser entidades já mapeadas, valores simples ou até entidades complexas, veremos a seguir como trabalhar com estes mais variados tipos de Stored Procedures:

Os exemplos a seguir serão baseados nas entidades mostradas na Figura 1:

Figura 1. Diagrama de Entidades Mapeadas

1- Stored Procedure simples

Vamos iniciar nosso exemplo com uma SP simples que retorna todos os dados de uma entidade, por exemplo de produtos, um velho e não recomendando select * from servirá como exemplo conforme o código SQL na listagem 1:

USE example
GO
CREATE PROC RetornaProdutos
AS
SELECT * FROM Produto

Listagem 1. SQL para criação da Procedure Simples

Após a criação da procedure, vamos mapear nossa SP para o EDMX, neste primeiro momento realizar este procedimento em um arquivo de mapemaneto já existente, posteriormente mostrarei como se fazer em um arquivo novo.  O processo de mapeamento é bem simples, basta abrir-mos nosso EDMX e clicar-mos com o botão direito em qualquer área em branco, então selecionar Update Model from Database… conforme a Figura 2.

Figura 2. Atualizando o Modelo de dados

O Update Wizard irá abrir e checar as modificações do banco de dados. Após a verificação deve-se expandir á árvore de objetos na aba Add, no nó Stored Procedures e selecionar a SP que criamos conforme a Figura 3 e então aperta-se o Finish.

Figura 3. Selecionando a SP

Até o momento mapeamos a SP para nosso EDMX, mas ainda não podemos usa-la, pois precisamos agora importar a SP como função, para isto, ainda no EDMX,  deve-mos abrir a aba Model Browser que geralmente fica perto da velha conhecida Solution Explorer. Caso ela não esteja visível, pode-se utilizar o atalho do teclado apertando simultaneamente o CTRL + 1.

Na aba que se abriu, veja que existe uma pasta dentro do banco de dados mapeado chamada Stored Procedure, dentro dela constam todas as SP mapeadas. Vamos clicar em cima da RetornaProdutos com o botão direito e selecionar Add Function Import conforme a Figura 4.

Figura 4. Importando a SP como função

A tela que se abriu é aonde se define detalhes importantes como o nome da função, a SP que está sendo importada e principalmente o tipo de retorno da SP. Se nada for selecionado a função será executada porem nada será retornado, o que pode funcionar para SP que executam alguma modificação no banco (por exemplo uma inserção). Para nosso exemplo que vamos retornar uma entidade inteira, para isto vamos selecionar produto no campo Entities conforme a Figura 5 e depois apertar o OK e o Salvar no mapeamento.

Figura 5. Selecionando o Retorno

Pronto, agora já podemos utilizar nossa SP como uma função, segue na Listagem 2 um exemplo de chamada do método retornaProdutos.

exampleEntities ex = new exampleEntities();

var retorno = ex.RetornaProdutos();
foreach (Produto v in retorno)
{

}

Listagem 2. Código para chamar a função

2- Stored Procedure com parâmetros

Sendo agora mais resumido, vamos alterar a SP anterior adicionando a possibilidade de filtrar os produtos por nome, segue na Listagem 3 o código SQL da alteração

USE example
GO
ALTER PROC RetornaProdutos
@Nome nvarchar(255)
AS
SELECT * FROM Produto
WHERE Nome like '%'+@Nome+'%'

Listagem 3. Alteração da SP,adicionando parâmetros

Para que a modificação seja mapeada pelo EDMX, é necessário que sejam refeitos todos os passos mostrados no exemplo anterior, a diferença será no momento de selecionar a SP na tela Update Wizard, pois a SP não estará mais visível na aba Add, mas na aba Refresh. Ao apertar o botão Finish, o modelo será recarregado e já podemos utilizar nossa SP com as modificações realizadas conforme a Listagem 4.

var retorno = ex.RetornaProdutos("Celular");
foreach (Produto v in retorno)
{

}

Listagem 4. Utilizando a SP com o parâmetro

3- Retornando Tipos Complexos

Diferente de entidades, que conceitualmente representam tabelas do banco de dados, os tipos complexos são objetos que se assemelham as classes com suas propriedades definidas mas que não contem funções, ou seja, não tem métodos. A criação de um Tipo Complexo tem várias finalidades dentro do LINQ to Entities, para o nosso exemplo, ela será responsável por obter o retorno de uma Stored Procedure que faz um join entre várias tabelas.  Segue na Listagem5 o código SQL para a criação da SP.

USE example
GO
CREATE PROC RETORNAVENDASPORCOMPRADOR
AS
SELECT pessoa.Nome,produto.Nome,produto.Valor,venda.DtVendas FROM Pessoa pessoa
inner join Vendas venda on pessoa.Id = venda.Pessoa_Id
inner join Produto produto on produto.Id = venda.Produto_Id

Listagem 5. Stored Procedure com joins

Agora que já criamos nossa SP, vamos seguir os passos citados no Exemplo 1 para o mapeamento da SP no EDMX como função. Somente no Add Function Import os passos serão diferentes, pois conforme visto acima, estamos retornado em uma única consulta dados de várias tabelas e isto não está mapeado como um retorno no LINQ. Para isto, vamos criar um tipo complexo com o retorno esperado. Este procedimento é bastante simples bastando apenas que clique-mos nos botões, Get Column Information para que o Visual Studio analise a SP e traga os dados referentes as colunas retornadas e depois no botão Create New Complext Type conforme a Figura 6.  Apertar o OK e o Salvar.

Figura 6. Criando o Tipo Complexo

E para finalizar, segue na Listagem 6 o código chamando a nova procedure e já retornado o Complex Type que cria-mos para este propósito.

var retornoTC = ex.RETORNAVENDASPORCOMPRADOR();
string NomeCliente, NomeProduto;
DateTime dtVendas;
double ValorVenda;
foreach (RETORNAVENDASPORCOMPRADOR_Result v in retornoTC)
{
NomeCliente = v.Cliente;
NomeProduto = v.Produto;
dtVendas = v.DtVendas;
ValorVenda = v.Valor;
}

Listagem 6. Chamando a SP com Tipo Complexo

Espero ter demonstrado um pouco da facilidade que é trabalhar com Stored Procedures no LINQ to Entities, o poder que isto pode agregar as aplicações e as funcionalidades básicas que podem ser utilizadas com está tecnologia. Até a proxima.

 

USE example

GO

CREATE PROC RetornaProdutos

AS

SELECT * FROM Produto