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:
Button (Name) btnQuery, (Text) Executar
Label (Name) lblOrderBy, (Text) Ordenar por
ComboBox (Name) cbOrderBy, (Text) Data da CompraNo 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!
