How to filter a query based on dynamic number of parameters

Question

How to handle a combination of parameters in a query based on user selections.

For Example:

public DataTable GetStudents(int fromYear,int toYear,int camp_code,int fac_code,int dep_code)
        {
            StringBuilder cmdTxt = new StringBuilder();
            cmdTxt.Append(" SELECT register_no,name ");
            cmdTxt.Append(" FROM student WHERE year BETWEEN ? AND ? ");
            cmdTxt.Append(" AND camp_code = ?  AND fac_code = ? AND dep_code =? ");//Is a variable

            using (var myIfxCmd = new IfxCommand(cmdTxt.ToString(), con))
            {
                myIfxCmd.CommandType = CommandType.Text;
                myIfxCmd.Parameters.Add("year1", IfxType.Integer);
                myIfxCmd.Parameters.Add("year2", IfxType.Integer);
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                myIfxCmd.Parameters[0].Value = fromYear;
                myIfxCmd.Parameters[1].Value = toYear;
                //..............
            }

---

• Organization contains set of camps.
• Camp contains set of faculties.
• Faculty contains set of departments.

In this code I want to control (int camp,int fac,int dep). If the user selects all camps then camp = 0 => The whole organization, So I want to ignore

" AND camp_code = ?  AND fac_code = ? AND dep_code =? "

And in this case I have just two parameters for fromYear,toYear

---

If the user selects a specific camp <> 0 and wants to select all faculties then fac_code = 0 => all faculties, So I want to ignore:

" AND fac_code = ? AND dep_code =? "

And in this case I have just three parameters for fromYear,toYear,camp_code

---

If the user selects a specific camp <> 0 and selects a specific faculty then wants to select all the departments under this fac then dep_code = 0 => all departments, So I want to ignore:

" AND dep_code =? "

And in this case I have just four parameters for fromYear,toYear,camp_code,fac_code

---

If the user selects all three arguments <> 0 then we will include every thing having 5 parameters.

---

I want to make it dynamic and flexible to meet these criteria. I solve it but overwhelmed by dozens of (IF-ELSE) statements.

Answer 1

I would do something similar to what kowie le roux suggested, only I believe it's the parameter that is zero, not the value in the database. As such, if you use yoda notation and an "in" clause, I think you can cause the where condition to be true if either the specified field matches or a zero-value was furnished:

select register_no, name
from student
where
  year between ? and ? and
  ? in (camp_code, 0) and
  ? in (fac_code, 0) and
  ? in (dep_code, 0)

You would need to declare and assign all five parameters.

Also, for what it's worth, I don't know what Ifx is -- Informix? If so, see if it supports named parameters. If it does, something like this helps make the code a lot more maintainable.

string sql = @"
    select register_no, name
    from student
    where
        year between @FROM and @THRU and
        @CAMP in (camp_code, 0) and
        @FAC in (fac_code, 0) and
        @DEP in (dep_code, 0)
";

using (var myIfxCmd = new IfxCommand(sql, con))
{
    myIfxCmd.CommandType = System.Data.CommandType.Text;
    myIfxCmd.Parameters.Add("@FROM", IfxType.Integer);
    myIfxCmd.Parameters.Add("@THRU", IfxType.Integer);
    myIfxCmd.Parameters.Add("@CAMP", IfxType.Integer);
    myIfxCmd.Parameters.Add("@FAC", IfxType.Integer);
    myIfxCmd.Parameters.Add("@DEP", IfxType.Integer);

The assignments would look the same from there.