Consulta SQL padronizada e parametrizada ao mesmo tempo flexível

Consulta SQL padronizada e parametrizada ao mesmo tempo flexível.

No inicio da minha carreira como desenvolvedor, tive muitos problemas para estruturar minhas consultas SQL, queria que o usuário tivesse a possibilidade de escolher quais os parâmetros utilizar para fazer uma consulta, por exemplo, filtrar um cliente pelo nome, email, CPF, etc., mas não engessar o processo exigindo assim todos os parâmetros, deixando bem flexível a ponto de utilizar quais parâmetros ele queira combinando quantos forem necessários.

Após empenhar noites e noites sem dormir cheguei a uma estrutura que depois de montada deixa o sistema flexível e muito produtivo.

Pois bem, sem mais demoras vamos ao que interessa. Irei montar para exemplo a consulta para duas tabelas.

Tabela Cliente

Tabela Venda

O primeiro passo e definir quais campos o usuário poder usar para filtrar, para a tabela cliente será possível filtrar através de todos os campos, para a tabela venda será CodVenda, CodCliente, DTVenda e Pago.

Agora que já tempos os campos para o filtro definidos iremos criar em uma Unit uma classe onde deveremos ter um tipo para cada tabela, conforme abaixo:

cwCliente = (wcliCodCliente, wcliNome, wcliEmail, wcliCPF);

cwVenda = (wvenCodVenda, wvenCodCliente, wvenDTVenda);

twCliente = set of cwCliente;

twVenda = set of cwVenda;

E aconselhável que se adote um padrão para criação dos tipos, acrescentar o “cw” antes de cada tipo e um exemplo, para cada item do tipo e acrescido “w” de Where mais uma referencia para tabela, por exemplo, campo CodCliente da tabela Cliente assumo “wcliCodCliente” e assim por diante.

Utilizar o Tipo de cada objeto como set of e uma forma de se adicionar vários valores em uma única variável, caso necessário pode-se criar um vetor dinâmico, mas para evitar ter q redimensionar o vetor a cada inserção foi adotado está forma.

Agora o próximo passo e criar um objeto que receba os valores de acordo com o planejado, no caso  um Record e a forma mais pratica.

pckCliente = Record

Tipo : twCliente;

CodCliente : Integer;

Nome : string;

Email : string;

CPF : string;

End;

pckVenda = Record

Tipo : twVenda;

CodVenda : Integer;

CodCliente : Integer;

DTVendaIni, DTVendaFim : TDateTime;

End;

O próximo passo e criar uma procedure que aguarde os objetos (pckCliente, pckVenda), segue exemplo.

procedure TSQL.Cliente(pCliente: pckCliente);

  Var Commando : TStringList;

begin

  Commando := TStringList.Create;

  Commando.Add(Select * from Cliente Where CodCliente is Not Null);

  if wcliCodCliente in pCliente.Tipo then Commando.Add( and CodCliente = + IntToStr(pCliente.CodCliente));

  if wcliNome in pCliente.Tipo then Commando.Add( and Nome Like + QuotedStr( % + pCliente.Nome + %));

  if wcliEmailin pCliente.Tipo then Commando.Add( and Email = + QuotedStr(pCliente.Email));

  if wcliCPFin pCliente.Tipo then Commando.Add( and Email = + QuotedStr(pCliente.CPF));

  DModule.dsCliente.Active := False;

  DModule.dsCliente.CommandText := Commando.Text;

  Commando.Free;

end;

procedure TSQL.Venda(pVenda: pckVenda);

  Var Commando : TStringList;

begin

  Commando := TStringList.Create;

  Commando.Add(Select * from Venda Where CodVenda is Not Null);

  if wvenCodVenda in pVenda.Tipo then Commando.Add( and CodVenda = + IntToStr(pVenda.CodVenda));

  if wvenCodCliente in pVenda.Tipo then Commando.Add( and CodCliente = + IntToStr(pVenda.CodCliente));

  if wvenDTVenda in pVenda.Tipo then Commando.Add( and DTVenda BetWeen + QuotedStr(DateToStr(pVenda.DTVendaIni)) + and + QuotedStr(DateToStr(pVenda.DTVendaFim)));

  DModule.dsVenda.Active := False;

  DModule.dsVenda.CommandText := Commando.Text;

  Commando.Free;

end;

No exemplo acima, não é nada de complexo. Usando uma variável do tipo TStringList montamos nossa instrução dinamicamente e retornamos a mesma ao nosso DataSet.

Vale ressaltar alguns detalhes que se observados com atenção não teremos problemas:

1 - Sempre acrescente um parâmetro na instrução base, mesmo que o mesmo seja “Where <Campo> is Not Null”, para que assim todas as demais instruções possam iniciar com o operador “AND”.

2 - Sempre inicie instruções adicionais com espaço em branco, lembrando que o resultado final será uma instrução SQL. Para que se possa testar como esta nossa instrução podemos utilizar o método SaveToFile da  classe TStringList (Ex.: Command.SaveToFile(‘C:\Cliente.SLQ’);)

3 - Sempre antes de passar a instrução ao DataSet tenha certeza que o mesmo se encontra com a propriedade active = false;

4 - Ao trabalhar com string para montar a nossa consulta sem a utilização de parâmetros estamos sujeitos a um “SQL Inject” se não tratarmos a entrada. Por isso para cada entrada (que o usuário digite) temos QuotedStr que faz com que o valor da entrada seja colocado entre aspas. Não entrarei muito neste assunto para não sair do foco, mas recomendo a pesquisa.

5 - Escreva métodos a parte que trate dados de entrada, como por exemplo formatação de datas, concatenação, etc. para que assim não torne seu código muito extenso.

6 - Sempre confira a saída da sua instrução antes de colocar o código em produção.

7 - Por uma questão de otimização libere a variável Command do tipo TStringList da memória.

Agora para que possamos utilizar o nosso método de consulta padronizado utilizamos o exemplo abaixo.

procedure TForm1.btnBuscaClick(Sender: TObject);

  Var pCliente : pckCliente;

pVenda : pckVenda;

SQL : TSQL;

begin

  SQL:= TSQL.Create;

  pCliente.Tipo:= [wcliEmail];

  pCliente.Email := marvio.bezerra@gmail.com;

  pVenda.Tipo := [wvenDTVenda, wvenCodCliente];

  pVenda.CodCliente := 1;

  pVenda.DTVendaIni := Date - 7;

  pVenda.DTVendaFim := Date;

  SQL.Cliente(pCliente);

  SQL.Venda(pVenda);

  SQL.Free;

DataSetQualquer.Active = true;

end;

Vale ressaltar que quando trabalhamos com Set Of de um tipo para atribuirmos um valor  o mesmo deve estar entre [], contamos com os métodos  “Include” e “Exclude” e ainda podemos utilizar o operador + e – (Tipo := Tipo + [w] ou Tipo := Tipo - [w]).

Trabalho com ADO e DBExpress e ambos funcionam perfeitamente com este método. No exemplo foram utilizadas consultas SQL simples, podem ser montadas instruções com grau de complexidade necessário desde que se esteja atento a como será a saída desta instrução.

Caso haja alguma duvida me enviem, um e-mail, podem mandar que eu respondo mesmo.

Espero que gostem e que utilizem.

Um abraço.

Márvio André B. Silverio

marvio.bezerra@gmail.com