使用C#的高级搜索ASP.NET [英] advanced search ASP.NET using c#

问题描述

大家好，

我正在尝试编写高级搜索查询，该查询允许用户输入他/她希望的尽可能多的过滤器?
这是我使用的查询，问题是它要求用户输入所有过滤器才能工作，我需要它接受任何过滤器集合，


我感谢您的帮助！

Hi All ,

I am trying to write the advanced search query , that allow the user to enter as many filters as he/she wish ?
This is the query that i use , the problem is that it require the user to enter all filter to work ,i need it to accept any collection of filters ,


I appreciate your help!

String q="SELECT [user].fname, [user].mname, [user].lname, ";
                   q+="[user].email, [user].mobile_num, [user].user_name, doctor_job_history.date, ";
                   q+="doctor_job_history.operation_id, entity.name AS entity_name, country.name AS con_name, ";
                   q+="degree.degree, working_hours.day, working_hours.time_from, working_hours.time_until, educaation.description, ";
                   q+="doctor_job_history.entity_id ";
                   q += "FROM [user] ,doctor_info,educaation,";
                   q += "doctor_job_history ,working_hours,degree,country,entity ";

                   q += "WHERE ([user].id = doctor_info.user_id) ";
                   q +="AND(educaation.reg_num = doctor_info.reg_num) ";
                   q +="AND(doctor_info.reg_num = working_hours.reg_num) ";
                   q +="AND(doctor_info.reg_num = doctor_job_history.reg_num) ";
                   q +="AND(degree.id=educaation.degree_id) ";
                   q += "AND(working_hours.entity_id=entity.id) ";
                   q += "AND(degree.id=educaation.degree_id) ";
                  q += "AND(country.country_id = entity.country_id) ";
                   //q += "AND(entity.id=working_hours.entity_id) ";
                  q += "AND([user].fname LIKE @fname or @fname = null)AND ([user].lname LIKE @lname or @lname = null) AND (educaation.degree_id = @degree_id or @degree_id = null) AND (educaation.univ_id = @univ_id or @univ_id = null) AND (educaation.description LIKE @description or @description = null) AND (working_hours.day = @day or @day = null) AND (working_hours.time_from = @time_from or @time_from = null) AND (working_hours.time_until = @time_until or @time_until = null) AND (working_hours.entity_id = @entity_id or @entity_id i= null)";
                
            SqlCommand comm = new SqlCommand(q, conn);
            comm.Parameters.Add("@fname", System.Data.SqlDbType.NVarChar);
            comm.Parameters.Add("@lname", System.Data.SqlDbType.NVarChar);
            comm.Parameters.Add("@degree_id", System.Data.SqlDbType.Int);
            comm.Parameters.Add("@description", System.Data.SqlDbType.NVarChar);
            comm.Parameters.Add("@day", System.Data.SqlDbType.NVarChar);
            comm.Parameters.Add("@time_from", System.Data.SqlDbType.Time);
            comm.Parameters.Add("@time_until", System.Data.SqlDbType.Time);
           comm.Parameters.Add("@entity_id", System.Data.SqlDbType.Int);
            comm.Parameters.Add("@univ_id", System.Data.SqlDbType.Int);
            comm.Parameters["@fname"].Value =Txtname .Text ;
            comm.Parameters["@lname"].Value = Txtlname .Text ;
            comm.Parameters["@degree_id"].Value =ddldegree .SelectedValue;
            comm.Parameters["@description"].Value =Txtdescription .Text ;
            comm.Parameters["@day"].Value = ddlday.SelectedValue;
            comm.Parameters["@time_from"].Value = ddltimef.SelectedValue;
            comm.Parameters["@time_until"].Value = ddltimentil.SelectedValue;
           comm.Parameters["@entity_id"].Value = ddlentity.SelectedValue;
            comm.Parameters["@univ_id"].Value = ddlniv.SelectedValue;

请帮助我.

在此先谢谢您.

Please help me in this.

Thanks in Advance.

推荐答案

您可以像这样动态创建查询

You can dynamically create the query like this

...
                   if(doctor_info.reg_num != ""){
                      q +="AND(educaation.reg_num = doctor_info.reg_num) ";
                   }
...

或

您可以使用LIKE代替=
设置where

OR

You can set up the where with LIKE instead of =

...
                   q += "WHERE ([user].id LIKE ''%'' + doctor_info.user_id + ''%'') ";
...

like返回包含％符号之间的字符串的所有结果

这样，如果他们将一个字段留空，它将搜索%%或其他内容

The like returns any results that contain the string in between the % signs

That way if they leave one field blank it searches for %% or anything

A similar question[^] was asked earlier - maybe answers from that can help you.

if(doctor_info.reg_num != ""){
q +="AND(educaation.reg_num = doctor_info.reg_num) ";
                 }

这篇关于使用C#的高级搜索ASP.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！