C# Entity Framework: SQL Server Stored Procedures

Dependendo da arquitetura escolhida pelo arquiteto de software, pode haver alguns casos em que a regra de negócio esteja no banco de dados, por exemplo, as gerações de relatório, que podem estar contidas nas stored procedures. Outro caso muito comum de se ter stored procedures com regra de negócio é em sistemas legados antigos.

Neste artigo vamos tratar de como integrar uma stored procedure que tenha como retorno um relatório. Para isso, vamos utilizar o Entity FrameWork.

Vamos começar pela implementação da Stored Procedure. Para isso, vamos utilizar o SQLServer, abrindo o Management Studio e criando uma nova base de dados (você pode também acessar alguma de sua preferência). Clique com o botão direito do mouse e escolha new query, conforme a Figura 1.

Figura 1 – Acessando uma nova Query

Como o intuito desse tutorial não é a criação de tabelas, então apenas copiem o código da Listagem 1 e rodem na nova query aberta no Management Studio.

Listagem 1. Criação de tabelas

SET ANSI_PADDING ON GO --Tabela Função CREATE TABLE [dbo].[FUNCAO]( [FUNCAO_ID] [int] IDENTITY(1,1) NOT NULL, [FUNCAO_DESCRICAO] [varchar](50) NOT NULL, CONSTRAINT [PK_FUNCAO] PRIMARY KEY CLUSTERED ( [FUNCAO_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] --Tabela Turno CREATE TABLE [dbo].[TURNO]( [TURNO_ID] [int] IDENTITY(1,1) NOT NULL, [TURNO_DESCRICAO] [varchar](50) NOT NULL, CONSTRAINT [PK_TURNO] PRIMARY KEY CLUSTERED ( [TURNO_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] --Tabela Funcionário CREATE TABLE [dbo].[FUNCIONARIOS]( [FUNCIONARIO_ID] [int] IDENTITY(1,1) NOT NULL, [FUNCIONARIO_NOME] [varchar](50) NOT NULL, [FUNCIONARIO_SALARIO] [numeric](18, 2) NOT NULL, [FUNCIONARIO_DT_ADM] [date] NULL, [TURNO_ID] [int] NULL, [FUNCAO_ID] [int] NULL, CONSTRAINT [PK_FUNCIONARIOS] PRIMARY KEY CLUSTERED ( [FUNCIONARIO_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 SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[FUNCIONARIOS] WITH CHECK ADD CONSTRAINT [FK_FUNCIONARIOS_FUNCAO] FOREIGN KEY([FUNCAO_ID]) REFERENCES [dbo].[FUNCAO] ([FUNCAO_ID]) GO ALTER TABLE [dbo].[FUNCIONARIOS] CHECK CONSTRAINT [FK_FUNCIONARIOS_FUNCAO] GO ALTER TABLE [dbo].[FUNCIONARIOS] WITH CHECK ADD CONSTRAINT [FK_FUNCIONARIOS_TURNO] FOREIGN KEY([TURNO_ID]) REFERENCES [dbo].[TURNO] ([TURNO_ID]) GO ALTER TABLE [dbo].[FUNCIONARIOS] CHECK CONSTRAINT [FK_FUNCIONARIOS_TURNO]

Agora que já temos nosso banco de dados pronto, vamos para implementação da nossa Stored Procedure. O que vamos fazer é uma stored procedure que retorne todos os funcionários com seus respectivos salário, turno e função, podendo filtrar a data de admissão, o turno e a função.

Acesse a base de dados e clique sobre o sinal de + para expandi-la. Vá até a pasta Programmability e novamente expanda-a. Sobre a pasta Stored Procedures, clique com o botão direito do mouse e escolha a opção New Stored Procedure, conforme mostra a Figura 2.

Figura 2 – Criando uma Nova Stored Procedure.

Com isso, o Management Studio irá gerar a estrutura correta para você criar sua Stored Procedure. Basta apenas substituir os códigos que estão entre os sinais de maior e menor (< >), assim, sua Stored procedure deverá ficar conforme a Listagem 2.

Listagem 2. Criação da Stored Procedure.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SP_REL_FUNCIONARIO] ( @DATA_ADM as DATE = null, @TURNO_DESCRICAO as Varchar(50) = null, @FUNCAO_DESCRICAO as varchar(50)= null ) AS BEGIN Declare @Filtro as varchar(4000), @SQL as varchar(4000), @SQLTEMP as varchar(4000) Set @Filtro=' '; if @DATA_ADM is not null begin Set @Filtro=' and Convert (varchar,FUNC.FUNCIONARIO_DT_ADM,103)= '''+Convert(varchar,@DATA_ADM,103)+''''; end; if @TURNO_DESCRICAO is not null begin Set @Filtro=' and TUR.TURNO_DESCRICAO='''+@TURNO_DESCRICAO+''''; end; if @FUNCAO_DESCRICAO is not null begin Set @Filtro=' and FUN.FUNCAO_DESCRICAO= '''+@FUNCAO_DESCRICAO+''''; end; Set @SQL = 'Select FUNC.FUNCIONARIO_ID ,FUNC.FUNCIONARIO_NOME ,FUNC.FUNCIONARIO_SALARIO ,FUNC.FUNCIONARIO_DT_ADM ,TUR.TURNO_DESCRICAO ,FUN.FUNCAO_DESCRICAO From FUNCIONARIOS FUNC inner join TURNO TUR on (FUNC.TURNO_ID=TUR.TURNO_ID) inner join FUNCAO FUN on (FUNC.FUNCAO_ID=FUN.FUNCAO_ID) where 1=1 ' +@Filtro execute(@SQL) END GO

Agora vamos para implementação do código C# para podermos integrar a stored procedure com o Entity Framework. Abram o Visual Studio e criem um novo projeto clicando em File -> New -> Project. Veja a Figura 3.

Figura 3. Criando um novo Projeto.

Na janela seguinte digite “empty” na caixa de pesquisa, e selecione Blank Solution, altere a propriedade Name para FirstSPEntity ou para nome que preferir, conforme mostra a Figura 4.

Figura 4. Criando uma Blank Solution

Agora acesse o Solution explorer e clique com o botão direito do mouse sobre a Solution > Add... e em seguida New Project. Crie um projeto do Tipo Console Application e altere a propriedade Name para FirstSPEntity, conforme demonstrado nas Figuras 5 e 6.

Figura 5. Adicionando um projeto na Solução.

Figura 6. Projeto Console Application.

Agora vá ate o Solution Explorer e selecione o projeto FirstSPEntity. Clique com o botão direito do mouse e depois em Add -> New Item ou simplesmente Ctrl+Shift+A e escolha a opção ADO.NET Entity Data Model. Na propriedade Name coloque SPEntity, conforme as Figuras 7 e 8.

Figura 7. Adicionando um Novo Item.

Figura 8. Adicionando um ADO.NET Entity Data model.

Agora você terá uma janela com duas opções: Generate From Database e Empty Model. Escolha a opção Generate From Database e clique em Next.

Figura 9. Entity Data Model wizard.

Clique no botão New Connection e configure as informações conforme seu banco de dados; clique em OK. No textBox coloque SPEntities, marque a opção “Yes, Include de Sensitive data in the connection string.” e clique em Next. Selecione a stored procedure que criamos e clique em Finish. Veja os passos nas Figuras 10, 11 e 12.

Figura 10. Configurações do Entity.

Figura 11. Informações do Banco de Dados.

Figura 12. Selecionando a Stored Procedure

Agora selecione a Guia Model Browser (a direita do monitor) e vá até o item Complex Types. Clique com o botão direito e escolha Create Complex Type conforme ilustrado na Figura 13.

Figura 13. Adicionando um ComplexType.

Após a criação do novo Complex Type, clique sobre ele com o botão direito do mouse e escolha a opção Add-> Scalar Property -> e selecione o Tipo da Propriedade (String, Int16, Decimal, etc.). Apenas como informação Scalar Property são as propriedades que são dos tipos padrões, como string, int, decimal. Porém, os ComplexType também permitem que você adicione propriedades complexa, ou seja, adicionem um ComplexType a outro. Observe a Figura 14.

Figura 14. Adicionando Propriedade Scalar.

Deverá ser feito isso para cada Propriedade que a Stored Procedure irá retornar. Após terminar, o resultado ficará como na Figura 15. Lembrando que o Tipo da Scalar Property deve ser o mesmo que o tipo do banco ou um tipo similar, mas que suporte o tipo que vem do banco de dados.

Figura 15. Ilustração do ComplexType.

Ainda no Model Browser, clique na pasta Function Importe vá até a Stored Procedure SP_REL_FUNCIONARIOS. Dê um duplo clique e configure a tela que irá abrir: na propriedade Returns a Collections Of marque a opção ComplexType e no combobox a frente dessa opção selecione a ComplexType que acabamos de criar. A Figura 16 ilustra essa alteração.

Figura 16. Configurando a Function Import.

Agora vamos testar para ver se está funcionando. No Solution Explorer, dê um clique duplo sobre o arquivo program.cs. Nele vamos implementar a chamada da stored procedure. O seu código deve ficar como o da Listagem 3.

Listagem 3. Código de chamada da Stored Procedure.

static void Main(string[] args) { List<SP_REL_FUNCIONARIO_RESULT> _lsp_rel_funcionario_result = new List<SP_REL_FUNCIONARIO_RESULT>(); SPEntities _spentity = new SPEntities(); _lsp_rel_funcionario_result = _spentity.SP_REL_FUNCIONARIO(null, null, null).ToList<SP_REL_FUNCIONARIO_RESULT>(); foreach (var func in _lsp_rel_funcionario_result) { Console.WriteLine("ID: , NOME: , SALARIO: , DATA ADM: , TURNO: , FUNCAO: " , func.FUNCIONARIO_ID, func.FUNCIONARIO_NOME, func.FUNCIONARIO_SALARIO, func.FUNCIONARIO_DT_ADM , func.TURNO_DESCRICAO, func.FUNCAO_DESCRICAO); } Console.ReadKey(); }

Agora acesse o menu Build -> Build Solution ou Ctrl+Shift+B e depois menu Debug-> Starting Debug ou simplesmente F5. Conforme a Figura 17 é o resultado que esperamos.

Figura 17. Resultado Final.

Concluindo, utilizar Stored Procedures para geração de Relatórios ou mesmo implementação de outras regras de negócio não são aconselháveis, pois isso te deixa preso ao banco e impede você de criar uma aplicação multi-banco, mas, como existem inúmeros sistemas legados e algumas arquiteturas desenhadas para que a regra de negócio fique no banco, é bom estar preparado.

Espero que tenham gostado e ate à próxima.