Advance search filter using ASP.NET C#

Question

1.00/5 (1 vote)

See more:

Hi friends,

I am trying to create the advanced search query , that allow the users to enter as many filters as they want. this filter contain different Asp controls like dropdownlist and checkbox.
This is the query that i use , the problem is that it require the enter all filter to work ,i need it to accept any collection of filters ,

(please please post your answers with modifying my code for better understanding).

Check is my query right ?
Check also is my paramitrized code right ?

THANKS TO ALL.

What I have tried:

C#

public partial class Searchpage : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    string price1;
    string price2;
    string osx;
    string checktwog;
    string checkthreeg;
    string checkfourg;
    string phonetype;
    string cam;
    string ram;
    string q;
    protected void Page_Load(object sender, EventArgs e)
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        con.Open();
        try
        {
           price1 = Convert.ToString(Session["price1"]);
           price2 = Convert.ToString(Session["price2"]);
           osx = Convert.ToString(Session["osx"]);
           checktwog = Convert.ToString(Session["checktwog"]);
           checkthreeg = Convert.ToString(Session["checkthreeg"]);
           checkfourg = Convert.ToString(Session["checkfourg"]);
           phonetype = Convert.ToString(Session["phonetype"]);
           cam = Convert.ToString(Session["cam"]);
           ram = Convert.ToString(Session["ram"]);
           
           q = "SELECT * FROM legacy WHERE [price] >= @prc1 AND [price] <= @prc2 AND [os] = @systm AND [gprsedge] = @gtwog AND [threeg] = @gthreeg AND [fourg] = @gfourg AND [touchscreen] = @touchtype AND [camera] = @ccamera AND [ram] = @rram";
           SqlDataSource1.SelectCommand = q;
           SqlDataSource1.DataBind();
           lbl_item_page.Text = Convert.ToString(checktwog);
           SqlCommand comm = new SqlCommand(q, con);
           comm.Parameters.AddWithValue("@prc1", price1);
           comm.Parameters.AddWithValue("@prc2", price2);
           comm.Parameters.AddWithValue("@systm", osx);
           comm.Parameters.AddWithValue("@gtwog", checktwog);
           comm.Parameters.AddWithValue("@gthreeg", checkthreeg);
           comm.Parameters.AddWithValue("@gfourg", checkfourg);
           comm.Parameters.AddWithValue("@touchtype", phonetype);
           comm.Parameters.AddWithValue("@ccamera", cam);
           comm.Parameters.AddWithValue("@rram", ram);
           
        }
        catch (Exception ex)
        {
            
            Response.Write(ex.ToString());
        }

    }

Posted 23-Nov-16 3:24am

navi G

Updated 19-Sep-18 7:29am

v2

Add a Solution

Comments

F-ES Sitecore 23-Nov-16 9:52am

Check if the variables have any valid data before adding them to the where clause

q = "select * from legacy where "
if (!string.IsNullOrWhitespace(price1))
{
q = q + "price >= @price and "
}

and so on, then strip any "and " text at the end of the string and you'll have a query that only contains the things you are interested in. You'll need to cater for when there are no valid params etc too.
}

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-11-23T03:35:00

Quote:
I will paramitrized query later.

No. No, you won't. Because it will be "working" and you will move on to other things. And as a result, your code will always be vulnerable to SQL Injection. Fix it first, then move on to doing what you are trying to do.
And ... if you fix your filter problem without parameterizing the query, it's even less likely that you will get round to changing it!

In fact, you can fix both problems at the same time.
What you need to do is: create a StringBuilder to hold the query as you assemble it, and a List<SqlParameter> to hold the parameter values. Add an integer, and preset it to 1.
Write a method, that returns an integer, and accepts an int, a StringBuilder, your list, and two strings: one called Condition, and one called Parameter
In the method, check Parameter - if it's empty or whitespace, return the integer unchanged.
Otherwise, add the condition string to the StringBuilder, plus a '@' and the integer value. Increment the integer. Create an SQLParameter, give it the same name as you just added to the StringBuilder, and set it's value to the Parameter. Add it to the list, and return the new integer value.
Now you just call that for each possible search term, and when you are done, set the SQL command and its parameters.

It's a lot quicker to code than to write out in English!