Artigos .NET 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.

Estrutura do banco de dados Vendas

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.

Janela do server explorer do visual studio 2010

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.

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

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.

Script SQL para criação da Procedure pelo visual studio

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.

Resultado da Procedure Mostra Clientes

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.

Janela de criação do projeto no Visual Studio

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.

Janela de criação do DataModel

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.

Janela do Entity Data Model Wizard

Figura 8. Janela do Entity Data Model Wizard

Janela DataBase Objects

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:


Button (Name) btnQuery, (Text) Executar
Label (Name) lblOrderBy, (Text) Ordenar por
ComboBox (Name) cbOrderBy, (Text) Data da Compra

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.

Janela DataBase Objects

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.

Resultado da execução da procedure

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!