ASP.NET - Implementing Search Functionality and SQL

Question

I have created a form which the user can use in order to search for transactions. This is a picture of the form:

enter image description here

Now, the total price drop down list has the members:

Any Price
Exact Amount
Below Amount
Above Amount

The currency drop down list has the members:

Any Currency
EUR
USD
GBP

The transaction date drop down list has the members:

Any Date
Exact Date
Below Date
Above Date

The transaction status drop down list has the members:

Any Status
Active
Expired
Paid

All the details are being fetched from a table called Payments.

Can someone please help me how I can go about to search in the Payments table? Can I satisfy all the different possibilities using one SQL statement? Or do I have to use multiple SQL statements? Can someone please give me a template of an SQL statement that can be used to satisfy the different possibilities? Please help since SQL is not my strongest point. Thank you :)

Adriano Carneiro · Accepted Answer · 2013-06-21 17:22:15Z

3

Best solution would be dynamically assembling the SQL query string in the C# code, according to the fields entered in the search form.

answered Jun 21, 2013 at 17:22

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matthew Over a year ago

I understand. It didn't cross my mind. That would be a good alternative. Thanks :)

Andrew Morton Over a year ago

And don't forget to use SQL parameters to pass the values. That avoids problems like SQL injection attacks and apostrophes in the data.

JDB Over a year ago

Why dynamic query over parameterized stored proc? Stored procs are faster and signficantly more resistant to injection attacks.

Matthew Over a year ago

@Cyborgx37 Yes but how am I going to build the statement to satisfy all possibilities? The user can leave out any details he wants, he does not have to fill every field. That's where the challenge lies.

Adriano Carneiro Over a year ago

@Cyborgx37 If you have to create code on the SQL side AND create conditionals in the C# code, I fail to see where your solution is simpler and more readable. Just because you can do something, does not mean you should. Additionally, you take functionality out of the source code and into the database. You have to remember there's code in the SQL side, which might not be under to version control (while C# code should). Also, the filters might change with timewhich enforces my last point. As for the injection, you can create dynamic SQL code that takes parameters.

|

JDB · Accepted Answer · 2013-06-21 19:00:11Z

1

UPDATE: Modified code below to allow ranges (including unbounded ranges)

A stored proc can easily handle queries such as this, if I understand correctly. You can make the parameters optional simply by checking for NULL. If a parameter is NULL, don't query based on it.

CREATE PROCEDURE schema.FindPayments
(
    @MinPrice double = NULL,
    @MaxPrice double = NULL,
    @Currency char(3) = NULL,
    @MinTranDate datetime = NULL,
    @MaxTranDate datetime = NULL,
    @TranStatus int = NULL
)
AS BEGIN

    SELECT      *
    FROM        Payments
    WHERE       (
                        @MinPrice IS NULL
                    OR  TotalPrice >= @MinPrice
                )
            OR  (
                        @MaxPrice IS NULL
                    OR  TotalPrice <= @MaxPrice
                )
            OR  (
                        @Currency IS NULL
                    OR  Currency = @Currency
                )
            OR  (
                        @MinTranDate IS NULL
                    OR  TranDate >= @MinTranDate
                )
            OR  (
                        @MaxTranDate IS NULL
                    OR  TranDate <= @MaxTranDate
                )
            OR  (
                        @TranStatus IS NULL
                    OR  TranStatus = @TranStatus
                )
END

You can now call this stored proc from code passing in either DBNull.Value for unspecified parameters or, because I've assigned NULL as the default for all paramters, you can just pass the selected parameters.

SqlCommand l_findPayments = new SqlCommand("FindPayments", new SqlConnection("..."));
l_findPayments.CommandType = CommandType.StoredProcedure;

if ( l_totalPriceComparison == "Exact Amount" )
{
    findPayments.Parameters.Add(new SqlParameter("@MinPrice", l_price));
    findPayments.Parameters.Add(new SqlParameter("@MaxPrice", l_price));
}
else if ( l_totalPriceComparison == "Below Amount" )
    findPayments.Parameters.Add(new SqlParameter("@MaxPrice", l_price));
else if ( l_totalPriceComparison == "Above Amount" )
    findPayments.Parameters.Add(new SqlParameter("@MinPrice", l_price));
// "Any Price" will just leave the parameter
// blank, so it will not filter on price

// ... repeat for all params

SqlDataReader l_result = l_findPayments.ExecuteReader();

edited Jun 21, 2013 at 19:00

answered Jun 21, 2013 at 17:38

JDB

26.1k5 gold badges81 silver badges132 bronze badges

2 Comments

HardCode Over a year ago

This only works when the user selects "Exact Amount", "Exact Date" and any status other than "Any Status". If the user selects, for example, "Below Amount", this will fail to return accurate results.

JDB Over a year ago

@HardCode - You are correct. A simple modification will allow you to specify ranges. An exact value N would simply be in the range N to N. For something like "Any Status", you'd just leave the parameter blank (thus returning any status).

Mike Varosky · Accepted Answer · 2013-06-21 17:59:32Z

A good way to do it is to use your DB column names as the text box/dropdown id's. that way, you can utilize their ID property from the code behind and you can use a loop to build a query based on your needs. Assuming that you have these build in an HTML table or some other structure that can be similarly looped...

string sql = "SELECT * FROM payments ";
string field = "";
string value = "";
int parameter_count = 0;
foreach(HtmlTableRow row in table.Rows)
{ 
    foreach(Control c in row.Cells[1].Controls)
    {
        if (c is Textbox)
        {
             TextBox txt = c as TextBox;
             if (txt.Text.Length > 0)
             {
                 field = txt.ID;
                 value = txt.Text.Trim();
                 if (parameter_count == 0)
                 {
                     sql += string.Format(" WHERE {0}='{1}' ", field, value);
                     parameter_count++;
                 }
                 else
                 {
                     sql += string.Format(" AND {0}='{1}' ", field, value);
                     parameter_count++;
                 }
             }
        }
        else if (c is DropDownList)
        {
            DropDownList ddl = c as DropDownList;
             if (ddl.SelectedValue.Length > 0)
             {
                 field = ddl.ID;
                 value = ddl.SelectedValue.Trim();
                 if (parameter_count == 0)
                 {
                     sql += string.Format(" WHERE {0}='{1}' ", field, value);
                     parameter_count++;
                 }
                 else
                 {
                     sql += string.Format(" AND {0}='{1}' ", field, value);
                     parameter_count++;
                  }
              }
          }
      }
  }

Now, there is an obvious drawback to simply using the string.Format to build your query, mainly that you are open to SQL injections. However, I would suggest throwing out the string.Format section as they were purely for the sake of the example so that you could see the moment where you can capture the fields/values you will need to build a properly parameterized query. The more important piece of this is all of the logic that wraps around those statements. It gives you the ability to exclude the blank fields, and make them not affect the query result unless they have a value. Hope this helps!

So, how would you build an injection-attack-proof solution with dynamic column names?

Collectives™ on Stack Overflow

ASP.NET - Implementing Search Functionality and SQL

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related