Escolha uma Página

Última Atualização

(Last Updated On: 14/10/2019)

Usando SQL Storage Procedures no CORE MVC – parte 2

por | out 14, 2019 | Desenvolvimento de Aplicações | 0 Comentários

Um dos problemas complicados no Core MVC é que não dá para usar as Views do SQL. Quando você tem de mostrar dados que tem diversas tabelas agrupadas, há algumas formas de contornar isso, usando procedures do SQL.

Se você vai operar com procedures, antes de tudo instale pelo Gerenciador do NuGet – se ainda não o tem instalado na sua aplicação, o Microsoft.AspNetCore.Identity.EntityFrameworkCore.

Casos do método FromSql

Para executar procedures, use o método FromSql executa consultas RAW SQL. Mas existem algumas limitações para executar consultas RAW SQL ou procedures.

  • As consultas SQL só podem ser usadas para retornar tipos de entidades que fazem já parte do seu modelo. Portanto, não podem conter dados relacionados ou “montados” através de procedures. Existe uma forma de contornar isso, mas ninguém explica direito. Você deve montar um modelo “fake”, falso mesmo. Que não existe fisicamente no banco de dados. Depois de montar o modelo tem que fazer duas coisas:
    • incluir esse modelo “fake” no seu arquivo SuaAplicacaoContext.cs, dentro do diretório de modelos (Models);
    • o modelo tem que ter uma chave única (uniq key). No arquivo do modelo “fake” a chave da tabela combinada da procedure tem que ser precedida pelo comando [Key] (e para isso é preciso acrescentar a diretiva, no topo do arquivo de modelo, using System.ComponentModel.DataAnnotations). Aí, mesmo sem ser um arquivo físico ,a procedure ser comportará como se assim o fosse;
  • A consulta SQL sempre deve retornar dados para todas as propriedades da entidade. Então, basicamente, sua consulta SQL sempre vai ser algo como “Select * from {NomeDaTabela}” (sempre de uma única tabela dentre as que já estão mapeadas ou o da tabela “fake”).
    É até possível filtrar a tabela, usando parâmetros que funcionarão como uma cláusula “where”. Porém a procedure sempre irá retornar todos os campos da tabela (simples ou “fake”), retornando listas (claro que a procedure poderá trabalhar com outras tabelas, fazendo outras operações, mas o método FromSql sempre retornará uma lista do tipo “select * from Tabela” ou,em alguns casos, um valor único como “Select Count(*) from Tabela“. Você é quem decide: se o resultado é uma lista, injeta num .ToList(). Se o resultado é único, injeta num .SingleOrDefault();
  • Os nomes das colunas no conjunto de resultados sempre devem corresponder exatamente aos nomes das colunas às quais as propriedades são mapeadas (no modelo, ou Model, do contrário vai dar erro e s colunas que não batem virão com valores nulos).

Não há sentido em se usar o FromSql se não houver necessidade de filtrar a tabela com parâmetros ou necessidade de fazer operações acessórias dentro da procedure (atualizando outras tabelas). A procedure abaixo é praticamente inútil, no sentido de que poderia ser facilmente obtida sem nenhuma procedure:

CREATE PROCEDURE lista_todas_consultorias
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * from consultorias
END
GO

Ela teria que usar, no controller, algo do tipo:

List<Consultorias> ListaDeConsultorias = _context.Consultorias.FromSql("lista_todas_consultorias").ToList;

Mas isso poderia ser obtido de várias outras formas, pois a tabela Consultorias já é mapeada, sendo possível obter a lista como no como por exemplo:

var ListagemDeConsultorias = await _context.Consultorias.ToListAsync();

Procedures simples com parâmetros usando FromSql

Já quando você que utilizar uma tabela já mapeada usando filtros (passando parâmetros), começa a fazer sentido usar o FromSql. Por exemplo, selecionar pessoas que estão ligadas a uma determinada Consultoria (considerando que as pessoas estão numa tabela já mapeada):

CREATE PROCEDURE seleciona_pessoas_duma_consultoria 
	@idConsultoria int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT *  FROM dbo.ac_redepessoas
WHERE        (idConsultoria = @idConsultoria)
END
GO

Mesmo este uso, considerando que a tabela de pessoas já é mapeada e que contém o campo IdConsultoria, é bobinho. A instrução FromSql seria:

int idConsultoria = 1;
List<AcRedepessoas> ListagemDePessoasDaConsultoria = _context.AcRedepessoas.FromSql("seleciona_pessoas_duma_consultoria @p0", idConsultoria).ToList;

Mas isso também seria facilmente obtido diretamente no Core:

ListagemDePessoasDaConsultoria =  _context.AcRedepessoas.Where(x => x.IdConsultoria == idConsultoria).ToList();

Então, o uso de FromSql deve ocorrer em situações onde o Core MVC não pode, automaticamente, atualizar alguma tabela secundária ou fazer operações intermediárias. Também não se presta para operações do tipo Insert, Update e Delete.

Passando mais de um parâmetro

Algumas vezes você precisa passar mais de um parâmetro. O FromSql() tem um “overload”que aceita os parâmetros do objeto []. A primeira coisa a notar é o nome do parâmetro é “@p0”. Se houver mais parâmetros, incremente o contador como @p1, @p2 etc… O código seguinte mostra como passar vários parâmetros:

var ListagemDeConsultorias = await _context.Consultorias.ToListAsync();
int idConsultoria = 1;
var NomesDePessoas = "Roberto";
var ListagemDePessoasDaConsultoria = _context.AcRedepessoas
    .FromSql("ListaPessoasPorConsultoriaENome @p0, @p1",
    parameters: new[] { idConsultoria.ToString(), NomesDePessoas })
    .ToList();

Um cuidado: ao passar números inteiros, converta para string antes (se não vai dar erro no SQL). Existe também outra maneira de passar os parâmetros. Substitua @p0 por {0} (e se tiver mais parâmetros, @p1 por {1} e assim por diante.

int idConsultoria = 1;
var parametroConsultoria = new SqlParameter("@idDaConsultoria", idConsultoria);
List<acRedepessoas> ListaDePessoas = _context.acRedePessoas
            .FromSql("proc_lista_pessoas @idDaConsultoria", parametroConsultoria)
            .ToList();

Nesse formato, não é preciso antes converter o inteiro em “string“. Para passar 4 parâmetros, por exemplo:

var userType = _context.acRedepessoas.FromSql("dbo.AlgumaProcedure @Id = {0}, @Nome = {1}", 45, "Ada");

 

 

 

 

Para casos mais complexos, é preciso usar ExecuteSqlComand

Quando se trata de alterar um arquivo com procedure, este é comando que executa a procedure: ExecuteSqlComand, adequado para operações do tipo Insert, Update e Delete.

O que quase lugar nenhum explica é o seguinte: o comando ExecuteSqlComand não retorna listas. Retorna um inteiro. 0, se a operação foi bem sucedida. É só isso. Novamente: não há sentido em se usar esse comando a menos que você esteja atualizando outras tabelas intermediárias ou fazendo cálculos complexos antes de alterar ou inserir em registro.

A procedure a seguir insere um registro na tabela de Consultorias Categorias.

CREATE PROCEDURE usp_InsereConsultoria
 @NomeDaConsultoria nvarchar(300)
AS
BEGIN
    SET NOCOUNT ON;
    Insert into Consultorias Values (@NomeDaConsultoria)
END
GO

ExecuteSqlCommand deve ser usado em context.Database (na minha aplicação de exemplo _context = contexto;). Você pode usá-lo da seguinte maneira.

var NomeConsultoria= "José Consultores e Associados";
_context.Database
           .ExecuteSqlCommand("usp_InsereConsultoria @p0", NomeConsultoria);

É possível rodar o ExecuteSqlCommand no modo assíncrono também, como por exemplo:

await _context.Database.ExecuteSqlCommandAsync("usp_CriaConsultoria @p0, @p1", 
        parameters: new[] { "Nome Completo da Consultoria", "Nome de Guerra" });

 

A procedures mostradas anteriormente são muito simples. O problema é quando ser quer manusear resultados de diversas tabelas, amarrando umas às outras (joining).

O exemplo seguinte é baseado em uma aplicação que é essencialmente um questionário de avaliação de uma pessoa (qe se avalia e é avaliada por terceiros).

Ocorre que um questionário é sempre cadastrado por uma consultoria (e uma consultoria pode ter diversos questionários cadastrados). Quando ela quer aplicar o questionário para uma pessoa que vai ser avaliada, o sistema gera formulários de resposta para o avaliado e para todos avaliadores do avaliado. Quando qualquer uma das pessoas entra no sistema, ela acessa o questionário específico que deve responder. Nesta aplicação as respostas tem uma régua com todas alternativas (amarrada também ao modelo do questionário).

Ao acessar o formulário de respostas, o respondente tem de ler cada pergunta de cada questionário e salvar sua resposta. Há portanto uma tabela de respostas que guarda a NOTA dada (poderia ser uma resposta em texto). Essa tabela, no entanto, está amarrada:

  • a uma tabela de Avaliações, que tem a lista de todas avaliações do sistema, identificando o avaliador e o avaliado;
  • que por sua vez está amarrada a uma tabela de pessoas, que tem todos os participantes do sistema, de onde é pego o nome do Avaliado;
  • que por sua vez está amarrada a uma tabela de Consultorias, pois cada participante pertence a apenas uma Consultoria (e aqui pego o nome da Consultoria.

A tabela de Respostas também está amarrada:

  • A uma tabela que contém o texto das perguntas;
  • que por sua vez está amarrada a uma tabela que contém as competências a que cada pergunta pertence (com seu título e descrição);
  • que está amarrada a uma tabela de questionários modelos,
  • que está amarrada a uma tabela de tipos de questionários,
  • que finalmente está amarrada a um tipo de régua específico para cada questionário.

O modelo visual em SQL é o seguinte:

Construindo um modelo (model)

A primeira coisa a fazer é constrir um modelo que contenha todos os dados que serão usados, extraídos de todas fabelas acima. No caso. o fromulárioViewModel.cs:

namespace Coaching.Models.FormularioViewModels
{
    public class FormularioViewModel
    {
        public int IdRespostaAvaliacao { get; set; }
        public int? IdAvaliacao { get; set; }
        public int? IdPerguntaBanco { get; set; }
        public double? Nota { get; set; }
        public int IdAvaliado { get; internal set; }
        public int IdAvaliador { get; internal set; }
        public string TipoDeAvaliador { get; internal set; }
        public string NomeDoAvaliado { get; internal set; }
        public string NomeConsultoriaMenu { get; internal set; }
        public int? IdTipoQuestionario;
        public string TituloCompetencia;
        public string DescCompetencia;
        public string TituloCompetenciaLimpo;
        public string DescCompetenciaLimpo;
        public string OrientadoPara;
        public string NomeQuestionario;
        public int? IdConsultoria;
        public string NomeRegua;
        public int IdRegua;
        public int IdCompetenciaBanco;
        public int? OrdemPergunta;
        public string AssertivaAuto;
        public string AssertivaGeral;

       
    }
}

Esse modelo tem tudo que é necessário para selecionar e para mostrar uma única pergunta e sua resposta.

Preparando o Controller

Usando Linq, somente para pegar a lista de possíveis respostas de uma única avaliação, olha o tamanho do Join necessário:

var listarespostas = await (from a in _context.QuestionarioTipos
                                        join b in _context.QuestionariosModelos on a.IdTipoQuestionario equals b.IdTipoQuestionario
                                        join c in _context.PerguntasBancoGeral on b.IdPerguntaBanco equals c.IdPerguntaBanco
                                        join d in _context.CompetenciasBancoGeral on c.IdCompetenciaBanco equals d.IdCompetenciaBanco
                                        join e in _context.Reguas on a.IdRegua equals e.IdRegua
                                        join f in _context.AvaliacoesRespostas on c.IdPerguntaBanco equals f.IdPerguntaBanco
                                        join g in _context.Avaliacoes on f.IdAvaliacao equals g.IdAvaliacao
                                        join h in _context.AcRedepessoas on g.IdAvaliado equals h.IdUsuario
                                        join i in _context.Consultorias on h.IdConsultoria equals i.IdConsultoria
                                        where g.IdAvaliacao == id
                                        select new Models.FormularioViewModels.FormularioViewModel
                                        {
                                            NomeConsultoriaMenu = i.NomeConsultoriaMenu,
                                            NomeDoAvaliado = h.Nome,
                                            TipoDeAvaliador = g.TipoDeAvaliador,
                                            IdAvaliador = g.IdAvaliador,
                                            IdAvaliado = g.IdAvaliado,
                                            AssertivaAuto = c.Assertivaauto,
                                            AssertivaGeral = c.Assertivageral,
                                            OrdemPergunta = b.OrdemPergunta,
                                            IdCompetenciaBanco = d.IdCompetenciaBanco,
                                            IdRegua = e.IdRegua,
                                            NomeRegua = e.NomeRegua,
                                            IdConsultoria = a.IdConsultoria,
                                            NomeQuestionario = a.NomeQuestionário,
                                            OrientadoPara = a.OrientadoPara,
                                            DescCompetenciaLimpo = d.DescCompetenciaLimpo,
                                            TituloCompetenciaLimpo = d.TituloCompetenciaLimpo,
                                            DescCompetencia = d.DescCompetencia,
                                            TituloCompetencia = d.TituloCompetencia,
                                            IdTipoQuestionario = b.IdTipoQuestionario,
                                            IdAvaliacao = f.IdAvaliacao,
                                            IdPerguntaBanco = f.IdPerguntaBanco,
                                            IdRespostaAvaliacao = f.IdRespostaAvaliacao,
                                            Nota = f.Nota
                                        })
                                        .OrderBy(b => b.OrdemPergunta)
                                        .OrderBy(c => c.IdPerguntaBanco)
                                        .ToListAsync();

Para usar uma procedure equivalente ao modelo, é preciso gerá-la no servidor (pode ser via o próprio Visual Studio, mas é melhor usar o seu Microsoft Managment SQL Studio. O VS, mesmo 2017, é lento demais):

CREATE PROCEDURE visualiza_lista_respostas 
	-- Add the parameters for the stored procedure here
	@idQuestionario int 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT dbo.questionario_tipos.idTipoQuestionario AS IdTipoQuestionario, dbo.competencias_banco_geral.tituloCompetencia AS TituloCompetencia, dbo.competencias_banco_geral.descCompetencia AS DescCompetencia, 
             dbo.competencias_banco_geral.tituloCompetenciaLimpo AS TituloCompetenciaLimpo, dbo.competencias_banco_geral.descCompetenciaLimpo AS DescCompetenciaLimpo, dbo.questionario_tipos.orientadoPara AS OrientadoPara, 
             dbo.questionario_tipos.NomeQuestionário AS NomeQuestionario, dbo.questionario_tipos.idConsultoria AS IdConsultoria, dbo.reguas.NomeRegua, dbo.questionario_tipos.idRegua AS IdRegua, dbo.competencias_banco_geral.idCompetenciaBanco AS IdCompetenciaBanco, 
             dbo.questionarios_modelos.OrdemPergunta, dbo.avaliacoes_respostas.idRespostaAvaliacao AS IdRespostaAvaliacao, dbo.avaliacoes_respostas.idAvaliacao AS IdAvaliacao, dbo.avaliacoes_respostas.idPerguntaBanco AS IdPerguntaBanco, 
             dbo.avaliacoes_respostas.nota AS Nota, dbo.perguntas_banco_geral.assertivageral AS AssertivaGeral, dbo.avaliacoes.idAvaliado AS IdAvaliado, dbo.avaliacoes.idAvaliador AS IdAvaliador, dbo.avaliacoes.tipoDeAvaliador AS TipoDeAvaliador, 
             dbo.avaliacoes.idTipoquestionario AS idTipoQuestionario, dbo.ac_redepessoas.Nome AS NomeDoAvaliado, dbo.consultorias.nomeConsultoria AS NomeConsultoriaMenu, dbo.perguntas_banco_geral.assertivaauto AS AssertivaAuto
FROM   dbo.questionario_tipos INNER JOIN
             dbo.questionarios_modelos ON dbo.questionario_tipos.idTipoQuestionario = dbo.questionarios_modelos.idTipoQuestionario INNER JOIN
             dbo.perguntas_banco_geral ON dbo.questionarios_modelos.idPerguntaBanco = dbo.perguntas_banco_geral.idPerguntaBanco INNER JOIN
             dbo.competencias_banco_geral ON dbo.perguntas_banco_geral.idCompetenciaBanco = dbo.competencias_banco_geral.idCompetenciaBanco INNER JOIN
             dbo.reguas ON dbo.questionario_tipos.idRegua = dbo.reguas.idRegua INNER JOIN
             dbo.avaliacoes_respostas ON dbo.perguntas_banco_geral.idPerguntaBanco = dbo.avaliacoes_respostas.idPerguntaBanco INNER JOIN
             dbo.avaliacoes ON dbo.avaliacoes_respostas.idAvaliacao = dbo.avaliacoes.idAvaliacao INNER JOIN
             dbo.ac_redepessoas ON dbo.avaliacoes.idAvaliado = dbo.ac_redepessoas.idUsuario INNER JOIN
             dbo.consultorias ON dbo.ac_redepessoas.idConsultoria = dbo.consultorias.idConsultoria
WHERE (dbo.avaliacoes_respostas.idAvaliacao = @idQuestionario)
ORDER BY dbo.questionarios_modelos.OrdemPergunta, IdPerguntaBanco
END
GO

Para testar a procedure no servidor, usamos nosso conhecido comando execute (no exemplo abaixo, pegando todas respostas da avaliação número 40):

execute visualiza_lista_respostas 40
-- ou, se só quiser pegar um valor de retorno (0 no caso de sucesso)
DECLARE @return_value intDECLARE @return_value int
EXEC @return_value = [dbo].[visualiza_lista_respostas] @idQuestionario = 40
SELECT 'Return Value' = @return_value
GO

Rodando o execute no Servidor SQL, temos (visão parcial de um formulário selecionado):

Para rodar num controller do MVC, será preciso usar o comando ExecuteSqlCommand . 

No entanto, precisamos ter ao meno uma view (visualizador). Usando o scafolding, vamos gerar uma view simples sobre o modelo já implementado, Daremos a esta primeira ação da view o nome respondente_formulario

 

Obrigado pela sua leitura. Continue visitando este blog e compartilhe artigos em sua rede de relacionamento. Por favor, se quiser, registre sugestões e comentários ao final da página.