CODEWITHSUNDEEP
c#sql.netado.netparameterized-query
Anyname Donotcare
Anyname Donotcare

Reputation: 11393

How to filter a query based on dynamic number of parameters

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;
                //..............
            }

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.

Upvotes: 1

Views: 197

Answers (3)

Hambone
Hambone

Reputation: 16377

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.

Upvotes: 1

InBetween
InBetween

Reputation: 32750

As always, breaking this into smaller bits makes life easier. Don’t write methods that do too much, break it into smaller bits.

As a bonus, if you use extension methods you can even achieve fluent syntax but that isn’t really necessary:

static IfxCommand GetStudents() { //create unfiltered query here ... }
static IfxCommand FilterByYears(
    this IfxCommand command,
    DateTime start,
    DateTime end) { ... }
 static IfxCommand FilterByCampus(
     this IfxCommand command,
     int campusId) { ... }
  //etc.

Now in each FilterBy... method you modify the command accordingly (or not) and you pass it along. No spaghetti code, each method only takes care of one filter.

And you build your query easily:

var command =
   GetStudents().FilterByYears(start, end)
                .FiltersByCampus(campus)
                .FilerByFaculty(fac);

Upvotes: 1

kowie le roux
kowie le roux

Reputation: 84

You would want your SQL to look something like this:

    cmdTxt.Append(" SELECT register_no,name ");
    cmdTxt.Append(" FROM student WHERE year BETWEEN ? AND ? ");
    cmdTxt.Append(" AND (camp_code = ? OR camp_code = 0) AND (fac_code = ? or fac_code = 0) AND (dep_code = ? or dep_code = 0) ");

The above would mean that if camp_code is 0, dep_code is 0 and fac_code is 0, all would return. If camp_code <> 0, and dep_code and fac_code is 0, it will return the specific camp etc.

Upvotes: 1

Related Questions