advanced search ASP.NET using c#

See more:

C#

ASP.NET

search

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.