Momento POG: Facilitando a construção de Filtros SQL (C# e Stored Procedures)
E aí, galera, tudo bem?
Hoje vou dar uma dica rapidinha, sobre como montar de forma simplificada filtros dinâmicos para consultas SQL em C# com o mesmo conceito aplicável em Stored Procedures.
Construir um filtro dinâmico para uma consulta SQL é basicamente concatenar strings montando-se a cláusula Where desejada. Por exemplo, em uma tabela com a seguinte estrutura:
1: CREATE TABLE EXEMPLO (
2: ID integer not null primary key,
3: NOME varchar(200),
4: RG varchar(9),
5: CPF varchar(11),
6: SEXO char(1)
7: );
Suponhamos que será feita uma pesquisa em que a nossa tabela EXEMPLO será filtrada pelos campos NOME, CPF e SEXO com as seguintes condições: O campo somente será incluído na filtragem se o usuário digitar algum valor (ou selecionar, caso ele não seja um campo livre, como em combo-boxes e afins), caso contrário, ele não será incluído na query.
A princípio, montaríamos a nossa instrução de pesquisa em C# assim:
1: public DataView MinhaPesquisa(string Nome, string CPF, string Sexo)
2: {
3: string isql = "select * from EXEMPLO ";
4: string strWhere = "";
5: ArrayList filtros = new ArrayList();
6:
7: //Aqui verificamos se o usuáro digitou algo nos campos de pesquisa para montarmos o Where
8: if(!Nome.Trim().Equals(""))
9: filtros.Add(String.Format(" (NOME like '{0}%') ",Nome.ToUpper().Trim()));
10: if(!Sexo.Trim().Equals(""))
11: filtros.Add(String.Format(" (SEXO = '{0}') ",Sexo.ToUpper().Trim()));
12: if(!CPF.Trim().Equals(""))
13: filtros.Add(String.Format(" (CPF like '{0}') ",CPF.ToUpper().Trim()));
14: foreach(string s in filtros)
15: where += s + " and ";
16: where = where.Equals("")?"":(" where " + where.Substring(0,where.Length - 4));
17: isql = isql + where;
18:
19: ... //Aqui você executa a instrução SQL...
20: }
Não é muito complicado depois que pega o jeito, o modelo de montagem para qualquer filtro é o mesmo. Declaramos a string que será a nossa instrução SQL (variável isql), nossa cláusula where montada (variável where) e um ArrayList que irá receber os nossos filtros parciais: se o usuário digitar o valor correspondente ao filtro, ele é incluído no ArrayList. Em seguida, essa matriz é varrida e os valores parciais são concatenados em where seguido de um operador SQL and.
Quando terminar o foreach, note que ficará sobrando um "and" no final da string. Então, com o código abaixo do foreach, removemos esse and que ficou sobrando, caso tenha alguma coisa em where (se for vazio, e não fizermos a checagem de string vazia, a instrução Substring irá dar pau).
Mas, para evitar tudo isso, tem uma aplicação prática da POG. Isso mesmo, a famosa Programação Orientada a Gambiarras! Veja como vai ficar a nossa instrução de pesquisa:
1: public DataView MinhaPesquisa(string Nome, string CPF, string Sexo)
2: {
3: string isql = "select * from EXEMPLO where (1 = 1) ";
4: isql += Nome.Trim().Equals("")?"":(String.Format(" and NOME like '{0}%' ",Nome.Trim().ToUpper()));
5: isql += CPF.Trim().Equals("")?"":(String.Format(" and CPF = '{0}' ",CPF.Trim().ToUpper()));
6: isql += Sexo.Trim().Equals("")?"":(String.Format(" and SEXO = '{0}' ",Sexo.Trim().ToUpper()));
7:
8: ...//Execução do SQL
9: }
O que fizemos: Acrescentamos diretamente em isql uma cláuslua Where que retornará todas as tuplas da tabela se não for especificado nenhum filtro. Caso o usuário digite algo nos campos de pesquisa, a concatenação das cláusulas where serão feitas diretamente em isql e note também que o operador AND foi movido para o início de cada filtro.
Com isso, eliminamos os "if", ArrayList, foreach...
Só não escapamos dos "if" se a nossa query for parametrizada.
Ah, também podemos montar nesse mesmo esquema uma Stored Procedure que retornará um resultset, montando a instrução SQL e os filtros dentro da SP. Vou mostrar o exemplo de uma em FireBird:
1: CREATE PROCEDURE SP_MINHA_PESQUISA (
2: nome varchar(200),
3: cpf varchar(11),
4: sexo char(1))
5: as
6: declare variable "ISQL" varchar(1000);
7: begin
8: ISQL = 'select * from EXEMPLO where (1=1) ';
9: if(not (NOME is null) ) then
10: ISQL = ISQL || ' and NOME like ''' || nome || '''%)';
11: if(not (CPF is null) ) then
12: ISQL = ISQL || ' and CPF = ''' || CPF || ''')';
13: if(not (SEXO is null) ) then
14: ISQL = ISQL || ' and SEXO = ''' || SEXO || ''')';
15: for execute statement ISQL into ... do
16: begin
17: --faça o que tiver que fazer :-)
18: end
19: end
Viu como o processo é simples? A mesma "técnica" que usamos no C# fizemos em PSQL também :-)
Espero que eu tenha ajudado!
0 comentários:
Postar um comentário