Dynamic search in c# and WPF

Question

Good morning

I would like to ask a question about a dynamic research.

Setting:

I have a search screen, where the user selects 3 fields, being them:

1. The first is the search screen field, where you select which field you want to fetch the value.
2. The second will be the condition where you can select the values = Containing, Starting, Ending or Equal
3. The Third would be the value he wishes to research

Screen example:

So what would be the best way to develop this in C#?

• Create dropdown with the values of the fields and conditions. Then create a method where I take the informed values and so generate a switch case with the conditions data? more or less like this:

->

public Cliente pesquisarCliente(string condicao, string campo, string    valor)
           {
            string operador = "";
            string sql;
            switch(condicao)
            {
                case "Condendo":
                    operador = "LIKE %"+ valor +"%";
                    break;
                case "Iniciando":
                    operador = "LIKE %"+ valor;
                    break;
                case "Terminando":
                    operador = "LIKE "+ valor +"%";
                    break;
                case "Igual":
                    operador = " = "+ valor;
                    break;  
            }
            sql = "SELECT * FROM CLIENTES where @campo "+valor;
            Com = new NpgsqlCommand (sql);
           } 

That would be the best way ? Someone would have another way to develop it?

I don’t know if I was clear in my doubt.

Thank you for your attention.

OBS: I am developing in c# layers with WPF and Postgresql database; I own the layers DAL, BLL, GUI and Model

Answer 1

Good morning.

I would make the screen only with the "value" and with the "fields" that should be used for the search. Something like the following:

All "fields" selected by the user would be added to a list, and the method pesquisarCliente would look like this:

public List<Cliente> pesquisarCliente(string valor, List<string> campos)
{
    string sql;

    string where = "";
    for (int i = 0; i < campos.Count(); i++)
    {
        where += string.Format("{0} LIKE '%{1}%'", campos[i], valor);
        if (i < campos.Count() - 1)
        {
            where += " OR ";
        }
    }

    sql = string.Format("SELECT * FROM Clients WHERE ({0});", where);

    // Executar sql aqui e retornar lista com clientes encontrados
}

Answer 2

//Metodo Pesquisar elo artigo
public static List<Cliente> Pesquisar_Artigo(string condicao, string campo, string    valor)
{
    string cn = Properties.Settings.Default.SGV;
    SqlConnection conexao = new SqlConnection(cn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conexao;
    cmd.CommandType = CommandType.Text;

    //Faz o teu case aqui
    cmd.CommandText = pesq;

    //Aqui faz o teu parametro
    ex.: cmd.Parameters.AddWithValue("@Cliente", Nome);
    conexao.Open();
    SqlDataReader dr;
    dr = cmd.ExecuteReader();
    List<Cliente> Pesquisa = new List<Cliente>();
    if (dr.Read())
    {
        //Instacia a classe e depoius adciona todos os atributos ex.: 
        Ciente obj = new Cliente();
        obj.id_cliente = Convert.ToInt32(dr["id_cliente"].ToString());
        obj.Nome = dr["Nome"].ToString();

        Pesquisa.Add(obj);
    }
    return Pesquisa;
}    

Answer 3

Usa Entity Framework + npgsql works 100% and you make every kind of query you need

Postgresql + Entityframework a necessary change (I do not know if it is more after so long I use) is to add to OnModelCreating in its Context class which is the default database squema (I don’t think this has in the article)

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.HasDefaultSchema("public");
}

Example of consultation:

public List<Pessoa> ListarPaginado(string noDoc, string nome, string email, int currentPage, int pageSize, out int totalPaginas)
    {
        var pagina = (currentPage - 1) >= 0 ? (currentPage - 1) : 0;
        if (pageSize <= 0) pageSize = 1;

        using (var db = new MeuContext())
        {
            var total = db.Pessoas.Count(x =>
                (
                    !string.IsNullOrEmpty(noDoc)
                    ? x.Documentos.Any(d => d.NoDoc.Contains(noDoc))
                    : x.Id > 0
                )
                &&
                (
                    !string.IsNullOrEmpty(email)
                    ? x.Email.Contains(email)
                    : x.Id > 0
                )
                &&
                (
                    !string.IsNullOrEmpty(nome)
                    ? x.Nome.Contains(nome)
                    : x.Id > 0
                )
                && x.Id > 1 //Não mostra o Admin
            );
            totalPaginas = total % pageSize > 0 ? (total / pageSize) + 1 : total / pageSize;

            return db.Pessoas.Where(x =>
                (
                    !string.IsNullOrEmpty(noDoc)
                    ? x.Documentos.Any(d=>d.NoDoc.Contains(noDoc))  
                    : x.Id > 0
                )
                &&
                (
                    !string.IsNullOrEmpty(email)
                    ? x.Email.Contains(email)
                    : x.Id > 0
                )
                &&
                (
                    !string.IsNullOrEmpty(nome)
                    ? x.Nome.Contains(nome)
                    : x.Id > 0
                )
                && x.Id > 1 //Não mostra o Admin
            )
            .Include(x=>x.Documentos)
            //.Include(x=>x.Enderecos)
            .OrderBy(x => x.Nome)
            //.ThenBy(x => x.NoDoc)
            .Skip(pagina * pageSize)
            .Take(pageSize)
            .ToList();
        }
    }