Entity Framework: Usando Stored Procedures

Stored Procedures nada mais são do que instruções SQL executadas em etapas no banco de dados mantendo a lógica de negócio da aplicação dentro do próprio banco. O seu uso apresenta vantagens e desvantagens: dentre as vantagens estão o fácil acesso as mesmas por qualquer linguagem de programação sem que o programador necessite ter conhecimento de sua lógica; em contrapartida elas podem criar uma grande sobrecarga do SGBD.

Montando a Estrutura

Agora começaremos a montar nosso ambiente de desenvolvimento: primeiro precisaremos do nosso banco de dados e para isso basta executarmos o script que criará e populará o banco de dados Vendas, que terá duas tabelas. Veja o código da Listagem 1.

Listagem 1. Script SQL para criação da estrutura do banco de dados

USE [master] GO /****** Object: Database [Vendas] Script Date: 12/03/2013 20:18:11 ******/ CREATE DATABASE [Vendas] ON PRIMARY ( NAME = N'Vendas', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS \MSSQL\DATA\Vendas.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Vendas_log', FILENAME = N'C:\Program Files \Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA \Vendas_log.LDF' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO USE [Vendas] GO /****** Object: Table [dbo].[Clientes] Script Date: 12/03/2013 20:11:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Clientes]( [Id] [int] IDENTITY(1,1) NOT NULL, [ClienteName] [nvarchar](max) NULL, CONSTRAINT [PK_Clientes] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Compras] Script Date: 12/03/2013 20:21:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Compras]( [Id] [int] IDENTITY(1,1) NOT NULL, [DataCompra] [datetime] NOT NULL, [Valor] [decimal](18, 2) NOT NULL, [ClienteId] [int] NOT NULL, CONSTRAINT [PK_Compras] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Compras] WITH CHECK ADD CONSTRAINT [FK_Compras_Clientes_ClienteId] FOREIGN KEY([ClienteId]) REFERENCES [dbo].[Clientes] ([Id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Compras] CHECK CONSTRAINT [FK_Compras_Clientes_ClienteId] GO INSERT INTO [Vendas].[dbo].[Clientes] ([ClienteName]) VALUES ('Josh Bailey') GO INSERT INTO [Vendas].[dbo].[Compras] ([DataCompra] ,[Valor] ,[ClienteId]) VALUES ('2013-11-29 12:57:01.357' ,5.99 ,1) GO

Executaremos esse script na instância do SQL Server instalado em nossa máquina. Em seguida, o nosso banco de dados será gerado como mostra a Figura 1.

Figura 1. Estrutura do banco de dados Vendas

Criando Stored Procedure pelo Server Explorer

Para usarmos Stored Procedures no Entity Framework, é necessário que as mesmas já estejam criadas na base de dados, só então atualizaremos o Data Model com elas. Para isso, abriremos o Visual Studio e em seguida o server Explorer, escolhendo o menu View e Server Explorer, ou ainda pressionando Ctrl + Alt + S, como mostrado na Figura 2.

Figura 2. Janela do server explorer do Visual Studio

Com o server explorer aberto, visualize a conexão com o banco de dados Vendas criado anteriormente. Caso a mesma não apareça, será necessário criá-la manualmente através do assistente, clicando com o botão direito em Data Connection e escolhendo Add Connection.

Depois da conexão criada ou visualizada, clique na seta apontando para a direita ao lado dele. Você verá uma lista de pastas associadas ao banco de dados, incluindo a pasta Stored Procedures, conforme a Figura 3.

Figura 3. Conexão ativa com o banco de dados Vendas pelo Server Explorer

Clicando com o botão direito do mouse na pasta Stored Procedures e escolhendo Add New Stored Procedure no menu de contexto, uma nova janela para a criação de Stored Procedures se apresentará. Nela coloque o código que contenha o script SQL com a lógica que a procedure conterá, como mostrado na Figura 4.

Figura 4. Script SQL para criação da Procedure pelo Visual Studio

Clicando no botão Save na barra de ferramentas, a procedure será criada na base de dados. Em seguida, para testarmos o funcionamento da mesma, basta clicar com o botão direito sobre ela e em seguida execute para que seja mostrado os resultados da Figura 5.

Figura 5. Resultado da Procedure Mostra Clientes

Criando a aplicação de exemplo

Criaremos agora o Windows Forms Application utilizando a linguagem de programação C# com o nome de TestProcedureModel, como mostrado na Figura 6.

Figura 6. Janela de criação do projeto no Visual Studio

Em seguida, criaremos o DataModel onde iremos gerar nosso modelo de dados de acordo com a estrutura do banco de dados Vendas. Para isso, basta clicarmos com o botão direito no projeto, Add, New Item ou CRTL + SHIFT + A e escolheremos ADO.NET Entity data Model. Veja o exemplo da Figura 7.

Figura 7. Janela de criação do DataModel

Preencheremos o campo name com VendasModel e em seguida clicaremos em add. Em seguida, no assistente para criação do DataModel escolheremos o Generate From Database, pois iremos gerar nosso modelo automaticamente do banco de dados criado no início deste artigo, fazendo uso da abordagem de desenvolvimento Data-Base-First. Na janela Entity Data Model Wizard preencheremos os campos de acordo com a Figura 8 para que seja criada uma conexão com o banco de dados Vendas. Na janela seguinte (Figura 9) para escolha dos Data Objects, escolheremos as tabelas Clientes e Compras e a Procedure Mostra Clientes.

Figura 8. Janela do Entity Data Model Wizard

Figura 9. Janela DataBase Objects

Com o nosso Data Model criado, abriremos novamente o Server Explorer e clicando com botão direito na procedure MostraClientes clicaremos em Open para realizarmos uma alteração no seu script para deixá-la um pouco mais robusta, como mostrado na Listagem 2.

Listagem 2. Script SQL para alteração da Procedure no Visual Studio

ALTER PROCEDURE MostraClientes @OrderBy NVarChar(20) AS SELECT * FROM Clientes AS C INNER JOIN Compras AS P ON C.Id = P.ClienteId ORDER BY CASE @OrderBy WHEN 'Data da Compra' THEN P.DataCompra WHEN 'Valor' THEN P.Valor WHEN 'ClienteId' THEN P.ClienteId ELSE P.DataCompra END

Nota: Como boa prática não se deve criar procedures que contenham apenas comandos Select, mas de preferência instruções que contenham transações.

Agora nossa Procedure está pronta para uso, agora iremos integrá-la na aplicação para ver o seu funcionamento. Abra o arquivo Form1.cs e adicione esses controles a ele:

No combobox cn cbOrderBy colocaremos seus valores para seleção alterando a propriedade Itens e inserindo os valores Valor, Codigo Cliente e

Data da Compra. Em seguida, precisaremos atualizar com o arquivo aberto clicaremos com o botão direito em qualquer área vazia do modelo e escolhermos Update Model From Database para que seja aberta novamente a janela de escolha dos DataObjects, conforme a Figura 10.

Figura 10. Janela DataBase Objects

Ao marcarmos Stored Procedures, clicaremos em Finish para, em seguida, salvarmos nosso modelo. Com isso, nossa procedure estará visível a partir do Entity Framework e para finalizarmos nossa aplicação de exemplo incluiremos nossa lógica para uso da procedure no evento OnClick, como mostra a Listagem 3.

Listagem 3. Lógica do evento onClick.

private void btnQuery_Click(object sender, EventArgs e) { // Criando o contexto. VendasEntities context = new VendasEntities(); var clientesList = from clientes in context.MostraClientes(cbOrderBy.SelectedItem.ToString()) select clientes ; StringBuilder Output = new StringBuilder(); // Buscando o Resultado. foreach (var PurchaseEntry in clientesList) Output.Append( //PurchaseEntry.ClienteId + " ID " + PurchaseEntry.Clientes.ClienteName + " Cliente "+ "Valor : "+PurchaseEntry.Valor + " em " + PurchaseEntry.DataCompra); MessageBox.Show(Output.ToString()); }

Depois da lógica implementada testaremos o funcionamento da nossa aplicação clicando no botão F5 ou clicando no botão Run onde a Query executada pela procedure deverá trazer o resultado mostrado na Figura 11.

Figura 11. Resultado da execução da procedure

Neste artigo aprendemos as técnicas para implementar, usar e testar procedures com o Entity Framework pela abordagem de desenvolvimento Database-First. Não deixem de comentar e deixar sugestões para os próximos artigos. Forte abraço!