代码之家 › 专栏 › 技术社区 › Anyname Donotcare

如何根据参数的动态数目筛选查询

parameterized-query ado.net .net sql c#

Anyname Donotcare · 技术社区 · 6 年前

如何根据用户选择处理查询中的参数组合。

例如:

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

组织包含一组营地。
营地里有一套设施。
教职员包含一组部门。

在这段代码中,我想控制 (int camp,int fac,int dep) . 如果用户选择了所有营地,那么 camp = 0 =>整个组织,因此我想忽略

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

在这种情况下,我只有两个参数 fromYear,toYear

如果用户选择特定的 camp <> 0 然后想选择所有的学院 fac_code = 0 =>所有功能,因此我想忽略:

" AND fac_code = ? AND dep_code =? "

在这种情况下,我只有三个参数 fromYear,toYear,camp_code

如果用户选择特定的 营地<GT;0 然后选择一个特定的教员,然后选择该教员下的所有部门。 dep_code = 0 =>所有部门,因此我想忽略:

" AND dep_code =? "

在这种情况下,我只有四个参数 fromYear,toYear,camp_code,fac_code

如果用户选择了所有三个参数<gt;0,那么我们将包括具有5个参数的所有内容。

我想让它具有动态性和灵活性,以满足这些标准。我解决了它,但却被几十个 (IF-ELSE) 声明。

3 回复 | 直到 6 年前

Hambone 6 年前

我会做一些和科维·勒鲁克斯建议的类似的事情,只是我相信这是参数这是零,而不是数据库中的值。因此,如果使用yoda符号和“in”子句,我认为如果指定的字段匹配或提供了零值,则可以使where条件为真:

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)

您需要声明并分配所有五个参数。

另外,为了它的价值,我不知道ifx是什么——informix?如果是,请查看它是否支持命名参数。如果是这样的话,这样做有助于提高代码的可维护性。

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);

从那里看,任务是一样的。

kowie le roux 6 年前

您希望您的SQL看起来像这样:

    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) ");

如果camp_code为0,dep_code为0,fac_code为0,则返回。如果camp_code<gt;0,dep_code和fac_code为0,则返回特定的camp等。

InBetween 6 年前

像往常一样,把它分成小块会让生活更容易。不要写太多的方法,把它分成小块。

另外,如果您使用扩展方法,甚至可以实现流畅的语法,但这不是真正必要的:

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.

现在在每一个 FilterBy... 方法对命令进行相应修改(或不修改),然后将其传递。没有意大利面代码,每个方法只处理一个过滤器。

您可以轻松构建查询:

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