Pattern or best practice to handle multi parameter queries in C#

Question

The approach i always had in programming a winforms or wpf application to perform queries on a database is the following:

• Design an interface with several controls to pass parameters to my query classes

• Build a "DataAccess" class with fields, properties and methods for the queries, based on Linq or Entity Framework as data source.

• Managing the events generated by the controls, to pass parameters and select wich method to use to retrieve data.

Example from the DataAccess class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MyApplication
{
    public class DataAccess
    {
        public LinqDataContext db = new LinqDataContext();

#region Private Fields
#region Properties(Constructors)
#region Methods

// Sample method
public List<DataAccess> Mymethod(string valoredata, int esenzione,
            string valorebatch)
{
    if (esenzione == 0)
    {
        return (from elementi in db.IndexTables
            from elementi2 in db.DocumentTables
            where elementi.ID == elementi2.ID 
                && elementi.DataScansione == Convert.ToDateTime(valoredata)
                && elementi.Batch == valorebatch  
                && elementi.NonEsente == true                    
            select associazionePropieta(elementi, elementi2)).ToList();
    }
    else if (esenzione == 1) 
    {
        return (from elementi in db.IndexTables
            from elementi2 in db.DocumentTables
            where elementi.ID == elementi2.ID 
                && elementi.DataScansione == Convert.ToDateTime(valoredata)
                && elementi.Batch == valorebatch 
                && elementi.BiffaturaReddito == false 
                && elementi.FirmaAutocertificazione == false 
                && elementi.NonEsente == false
            select associazionePropieta(elementi, elementi2)).ToList();
    }
    else
    {
        return (from elementi in db.IndexTables
            from elementi2 in db.DocumentTables
            where elementi.ID == elementi2.ID 
                && elementi.DataScansione == Convert.ToDateTime(valoredata)
                && elementi.Batch == valorebatch 
                && (elementi.BiffaturaReddito == true 
                    || elementi.FirmaAutocertificazione == true)
            select associazionePropieta(elementi, elementi2)).ToList();
    }

}

#endregion

// From Refactoring...
private static DataAccess associazionePropieta(IndexTable elementi, 
                             DocumentTable elementi2)
{
    return new DataAccess
    {
        codiceImpegnativa = elementi.CodiceImpegnativa,
        nominativo = elementi.Nominativo,
        codiceFiscale = elementi.CodiceFiscale,
        dataImpegnativa = elementi.DataImpegnativa,
        nonEsente = Convert.ToBoolean(elementi.NonEsente),
        biffaturaReddito = Convert.ToBoolean(elementi.BiffaturaReddito),
        autocertificazione = Convert.ToBoolean(elementi.FirmaAutocertificazione),
        codiceEsenzione = elementi.CodiceEsenzione,
        raoU = Convert.ToBoolean(elementi.RaoU),
        raoB = Convert.ToBoolean(elementi.RaoB),
        raoD = Convert.ToBoolean(elementi.RaoD),
        raoP = Convert.ToBoolean(elementi.RaoP),
        dataScansione = Convert.ToDateTime(elementi.DataScansione),
        batch = elementi.Batch,
        documentID = elementi.DcumentID,
        path = elementi2.Path,
        ID = elementi2.ID.ToString()
    };
}

Here is a little piece from the "MainWindow" code, i have 7 controls (1 datepicker, 3 comboboxes and 3 textboxes) to use as parameters for the query:

if (datePickerData.SelectedDate != null 
        && comboBatch.SelectedValue == null
        && comboEsenzione.SelectedValue != null 
        && nome == true 
        && impegnativa == true 
        && fiscale == true)
{
    this.dataGridRisultati.ItemsSource =
        dati.Mymethod(datePickerData.SelectedDate.ToString(),
            comboEsenzione.SelectedIndex);
}

}

Has you can imagine, handling all the combinations of parameters that can be passed and the decisional structures (if or switch case) is becoming a huge work... i diveded the decisional methods in regions (1 parameter, 2 parameters, 3.... 7 parameters) and that is giving me some rest, but today i just had to stop at the 4 parameters region, i don't know if i was just tired or what, but i could not figure out a pattern to match all the combinations.

So finally my question is:

I'm sure there is a much simpler way to handle the probability that the user will use the combobox instead of the textbox, or all 7 the controls at the same time to query my database, but i just can't figure it out.

Is there a best practice or a pattern that could help?

Thanks in advance

Answer 1

Here a technique I use:

1. Make your controls Name properties the same as the name of the data access objects property that they correspond to.
2. add them to a panel (ParametersPanel)
3. When the user clicks the 'go' button, loop over the controls in the ParametersPanel, set the data access object properties with the corresponding name using reflection.
4. The data access object (or better, your data access framework/api) can then build the query dynamically based on which parameters have been set.

Hard coding giant switches and queries like that is usually asking for trouble.

Answer 2

I'm not sure I completely understand your question 100% but I believe you're asking what is an efficient way to structure your data access calls when there are many parameters, but they are optional.

You may not be aware that LINQ queries can be built in stages. For example, think of this:

var query = from record in datasource
    select record;

if (Parameter1HasValue) query = query.Where(record => record.Field1 == Parameter1);
if (Parameter2HasValue) query = query.Where(record => record.Field2 == Parameter2);
if (Parameter3HasValue) query = query.Where(record => record.Field3 == Parameter3);
return query.ToList();

You might have this in a method which takes all the filters as nullable parameters, or maybe the filter values are other properties on your data access class -- this is up to you.