Multiple nested conditions
I have five conditions that I have to check (for example, the user wants to search this field or not). There are four combo boxes and one text box. The user can search using any field or multiple fields as they wish. To check which field the user is selected in, I built several if and else if statements. But when I only do it for two conditions, I realized how tedious task for five conditions is the best way to do it?
if (cmbAgent.Text=="")
{
if (cmbDegree.Text=="")
{
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM UniversityData", connection);
}
else
{
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM UniversityData WHERE Expertise LIKE '%" + cmbDegree.Text + "%' ", connection);
}
}
else if(cmbDegree.Text=="")
{
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM UniversityData WHERE SourceOfContact LIKE '%"+ cmbAgent.Text + "%' ", connection);
}
else
{
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM UniversityData WHERE SourceOfContact LIKE '%" + cmbAgent.Text + "%' and Expertise LIKE '%" + cmbDegree .Text + "%' ", connection);
}
source to share
What if the user wants / wants to enter multiple values?
You can easily build your query dynamically.
By the way, you have to use query parameters to prevent SQL injection.
// the "where 1=1" allows to always concatenate "and xxx"
// instead of testing if there were fulfilled conditions before
var query = "SELECT * FROM UniversityData WHERE 1 = 1";
var parameters = new Dictionary<string, string>();
if (txtDegree.Text != "")
{
query += " AND Expertise like '%' + ? + '%' ";
parameters.Add("degree", txtDegree.Text);
}
if(txtAgent.Text != "")
{
query += " AND SourceOfContact like '%' + ? + '%' ";
parameters.Add("agent", txtAgent.Text);
}
OleDbDataAdapter da = new OleDbDataAdapter(query, connection);
// add the parameters
foreach (var p in parameters) {
da.SelectCommande.Parameters.Add(p.Key, OleDbType.VarChar, p.Value);
}
Note that OleDb does not support named parameters. If possible, I would consider switching to Sql commands and adapters.
By the way, if you ever can / want to use Linq to build your queries (via Entity Framework, for example, or any other ORM), you can also do that, since Linq and Entity Framework are tied together with late constraints (which means that the query is not executed until the results are read).// build the query
var results = from ud in context.UniversityData
select ud;
if (txtDegree.Text != string.Empty) {
results = from ud in results
where ud.Expertise.Contains(txtDegree.Text)
select ud;
}
if (txtAgent.Text != string.Empty) {
results = from ud in results
where ud.SourceOfContact.Contains(txtAgent.Text)
select ud;
}
// use the results
myControl.DataSource = results.ToList(); // the ToList() call actually calls the query
source to share
This is why most build the query / query string separately. Example:
var sb = new StringBuilder();
sb.Append("Select * from UniversityData where 1 = 1");
if(!string.IsNullOrEmpty(cmbDegree.Text.Trim())){
sb.Append(" and Expertise like '%" + cmbDegree.Text + "%'")
}
//...
var querystring = sb.ToString();
OleDbDataAdapter da = new OleDbDataAdapter(querystring);
source to share
I would do something like this:
var query = "Select * from UniveristyData";
var wheres = new List<string>();
if (!cmbDegree.Text.IsNullOrEmpty())
wheres.Add("Expertise like '%" + cmbDegree.Text + "%'");
if (!cmbAgent.Text.IsNullOrEmpty())
wheres.Add("SourceOfContact like '%"+cmbAgent.Text+"%'");
if (wheres.Any())
query += " where " + string.Join(" and ", wheres);
var da = new OleDbDataAdapter(query, connection);
source to share
First, you can create a list of key values:
List<Tuple<string, string>> keyValueList = new List<Tuple<string, string>>();
keyValueList.Add(new Tuple<string, string>("Expertise", cmbDegree.Text));
keyValueList.Add(new Tuple<string, string>("SourceOfContact", cmbAgent.Text));
etc and then create your Where-Clause from keyValueList:
var conditionsArray = keyValueList.Where(p => !string.IsNullOrWhiteSpace(p.Item2)).Select(q => q.Item1 + " LIKE '%" + q.Item2 + "%'").ToArray();
Finally:
var sqlQuery = "SELECT * FROM UniversityData WHERE " + string.Join(" AND ", conditionsArray);
You need to add checks if the array is empty before adding a WHERE clause, etc., but I doubt you need help with that :-)
To add additional conditions, you just need to add more Tuples to the keyValueList (1 line) and don't need to change other code later.
source to share