Linq to SQL - Trabalhando com Stored Procedures complexas (pt-BR)
Para este artigo vamos trabalhar com uma procedure bem mais complexa que a do artigo anterior, onde a mesma utilizará parâmetros de entrada, parâmetros de saída, tipos de retorno dinâmico e código de retorno.
Criando a Stored Procedure
Vamos criar a procedure ObtemGrupos, a mesma recebe um código de grupo de produtos, e retorna através do parâmetro de saída “@quantidadeProdutos” a quantidade de produtos que pertencem ao código do grupo solicitado. A mesma retorna ainda todas ou apenas algumas colunas da tabela de Grupos dependendo do parâmetro “@todasColunas” e caso não exista um grupo com o código passado a mesma retorna 1, caso contrário retorna 0.
Vejamos como fica:
CREATE PROCEDURE ObtemGrupos@codGrupo INT,@todasColunas BIT,@quantidadeProdutos INT OUTPUTASSET NOCOUNT ON IF NOT EXISTS (SELECT 1 FROM tbProdutosGrupos WHERE codProdutoGrupo = @codGrupo) RETURN 1 IF @todasColunas = 1 SELECT * FROM tbProdutosGrupos WHERE codProdutoGrupo = @codGrupo ELSE SELECT nome, sigla FROM tbProdutosGrupos WHERE codProdutoGrupo = @codGrupo SELECT @quantidadeProdutos = COUNT(*) FROM tbProdutos WHERE codProdutoGrupo = @codGrupo RETURN 0
Mapeando a Procedure com Linq
Agora que criamos a procedure vamos mapear a mesma com nosso arquivo SerieLinq.dbml, para tanto basta arrastar/soltar dentro do design do arquivo DBML para que a mesma apareça na guia de "Methods Pane". Conforme a imagem abaixo:
http://rafaelzaccanini.files.wordpress.com/2011/12/img11.jpg
O seguinte código é gerado automaticamente pelo Linq mapeando nossa procedure:
[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.ObtemGrupos")]public ISingleResult<ObtemGruposResult> ObtemGrupos([global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> codGrupo,[global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Bit")] System.Nullable<bool> todasColunas,[global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] ref System.Nullable<int> quantidadeProdutos){ IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), codGrupo, todasColunas, quantidadeProdutos); quantidadeProdutos = ((System.Nullable<int>)(result.GetParameterValue(2))); return ((ISingleResult<ObtemGruposResult>)(result.ReturnValue));}
Observe que a propriedade quantidadeProdutos é passado como parâmetro, porém não é retornada após a execução do método. A mesma será recuperada apenas chamando result.GetParameterValue.
A classe ObtemGruposResult também foi criada, esta classe é o que irá representar o retorno da procedure contendo um único conjunto de resultados.
Porém, não é isto que queremos, pois desta forma é retornada somente um conjunto de resultados.
Customizando a classe de representação do DataContext
Primeiramente vamos criar um novo arquivo chamado SerieLinqDataContext.cs, este arquivo irá conter as definições para customização da classe de representação da nossa procedure.
Criaremos então uma classe para representação do retorno com apenas algumas colunas, ou seja, o retorno quando o parametro da procedure "todasColunas" for igual a 0.
Vejamos como fica nossa classe:
public class GrupoParcial{ public string nome; public string sigla;}
Agora vamos criar uma segunda classe, porém esta será uma classe parcial da nossa classe SerieLinqDataContext, abaixo segue definição da classe:
public partial class SerieLinqDataContext{ [Function(Name = "dbo.ObtemGrupos")] [ResultType(typeof(GrupoParcial))] [ResultType(typeof(tbProdutosGrupo))] public IMultipleResults ObtemGruposCorrigido( [global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "Int")] System.Nullable<int> codGrupo, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "Bit")] System.Nullable<bool> todasColunas, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "Int")] ref System.Nullable<int> quantidadeProdutos) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), codGrupo, todasColunas, quantidadeProdutos); quantidadeProdutos = ((System.Nullable<int>)(result.GetParameterValue(2))); return ((IMultipleResults)(result.ReturnValue)); }}
Como podemos ver, nós definimos um método ObtemGruposCorrigido para mapear os resultados para diferentes tipos, observe que definimos dois tipos de retorno.
NOTA: É possivel modificar diretamente no SerieLinq.designer.cs, porém sempre que ocorrer uma alteração suas correções serão perdidas.
Testando nossas implementações
Para testar nossas modificações vamos criar o seguinte método:
public static void ProcedureComplexa(int cod, bool todasColunas, SerieLinqDataContext _db){ int? quantidadeProdutos = 0; IMultipleResults result = _db.ObtemGruposCorrigido(cod, todasColunas, ref quantidadeProdutos); int codRetorno = (int)result.ReturnValue; if (codRetorno == 0) { if (todasColunas == true) { tbProdutosGrupo pg = result.GetResult<tbProdutosGrupo>().FirstOrDefault(); Console.WriteLine("Código: {0}", pg.codProdutoGrupo); Console.WriteLine("Nome: {0}", pg.nome); Console.WriteLine("Sigla: {0}", pg.sigla); Console.WriteLine("Data Cadastro: {0}", pg.dataCadastro.Value.ToShortDateString()); } else { GrupoParcial gp = result.GetResult<GrupoParcial>().FirstOrDefault(); Console.WriteLine("Nome: {0}", gp.nome); Console.WriteLine("Login: {0}", gp.sigla); } Console.WriteLine("Quantidade de Produtos no Grupo: {0}", quantidadeProdutos); } else { Console.WriteLine("Não existe um grupo com o código({0}) informado - CÓD RETORNO {1}", cod, codRetorno); } Console.WriteLine("n");}
E iremos realizar as seguintes chamadas:
//Todas as colunas do GrupoExemplos.ProcedureComplexa(6, true, _db); //Apenas algumas as colunas do GrupoExemplos.ProcedureComplexa(7, false, _db); //Grupo inválidoExemplos.ProcedureComplexa(20, true, _db);
Como podemos ver abaixo, o retorno apropriado para cada tipo de solicitação a nossa procedure.
A primeira chamada retorna todas as colunas do grupo e exibe a quantidade de produtos, a segunda chamada retorna apenas algumas colunas (nome e silga) do grupo e a quantidade de produtos, já a terceira chamada exibe a mensagem, pois não existe um grupo com o código 20:
http://rafaelzaccanini.files.wordpress.com/2011/12/img22.jpg
Rafael Zaccanini
MTAC – Microsoft Technical Audience Contributor
Blog: http://www.rafaelzaccanini.net
**Twitter: **@rafaelzaccanini
Facebook: http://www.facebook.com/RafaelZaccaniniNet