Click here to Skip to main content
15,041,788 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Currently I'm trying to filter a query to my database, but how would I avoid causing an error if my variable for the where clause was left blank.

Right now it's currently working to support the min and max price values, but I plan allowing all variables to be used.

For instance if I had "where make like @make" and left the textbox for make empty how would I ignore that clause from the query?

What I have tried:

I tried to append the additional statements if textboxes were not empty but I'd run into problems if select boxes were still missing..

}
C#
using (SqlConnection sqlCon = new SqlConnection(con))
            {
                sqlCon.Open();
                DataTable dt = new DataTable();
                if (string.IsNullOrEmpty(make) && (string.IsNullOrEmpty(model)) && (string.IsNullOrEmpty(color)) && (string.IsNullOrEmpty(min)) && (string.IsNullOrEmpty(max)) && (string.IsNullOrEmpty(miles)))
                {
                    SqlCommand updateDataGridViewCmd = new SqlCommand("select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic from car join makemodel as m ON m.mmid = car.mmid join color ON  car.colorid = color.colorid join carlot ON  carlot.carid = car.carid; ", sqlCon);
                    
                    dt.Load(updateDataGridViewCmd.ExecuteReader());
                    
                }
                else
                {
                    StringBuilder sqlCommandText = new StringBuilder();
                    sqlCommandText.Append("select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic from car join makemodel as m ON m.mmid = car.mmid join color ON  car.colorid = color.colorid join carlot ON  carlot.carid = car.carid");
                    string commandtext = sqlCommandText.ToString();
                    SqlCommand updateDataGridViewCmd = new SqlCommand(commandtext, sqlCon);
                    
                    //updateDataGridViewCmd.Parameters.AddWithValue("@model", model);
                    //updateDataGridViewCmd.Parameters.AddWithValue("@min", min);
                    //updateDataGridViewCmd.Parameters.AddWithValue("@max", max);
                    //updateDataGridViewCmd.Parameters.AddWithValue("@mileage", miles);
                    //updateDataGridViewCmd.Parameters.AddWithValue("@color", color);
                    dt.Load(updateDataGridViewCmd.ExecuteReader());

                    if(!string.IsNullOrEmpty(make))
                    {
                        sqlCommandText.Append(" where make like @make");
                        updateDataGridViewCmd.Parameters.AddWithValue("@make", make);
                        commandtext = sqlCommandText.ToString();

                    }

                    if (!string.IsNullOrEmpty(model))
                    {
                        sqlCommandText.Append(" OR @model");
                        updateDataGridViewCmd.Parameters.AddWithValue("@model", model);
                        commandtext = sqlCommandText.ToString();

                    }

                    sqlCommandText.Append(";");
                    commandtext = sqlCommandText.ToString();
                }
                dataGridView1.DataSource = dt;
            }
        }
Posted
Updated 28-Apr-21 8:36am

You didn't specify the problems you ran into but I take it one was the location of keyword WHERE

When you build the conditions dynamically you can for example use a constant condition to ensure that WHERE is always present. For example
C#
sqlCommandText.Append(@"select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic 
from car 
join makemodel as m ON m.mmid = car.mmid 
join color ON  car.colorid = color.colorid 
join carlot ON  carlot.carid = car.carid
where 1 = 1");


Another observation from your code is that the model condition seems malformed since it's missing the column name. Should it be
C#
if (!string.IsNullOrEmpty(model))
{
    sqlCommandText.Append(" OR m.model = @model");


Third thing is that if you're using OR's you most likely need to use parenthesis to ensure that proper group of conditions are handled with OR's. Otherwise you may end up getting rows that would not belong to the result set.

And last one is that while it's okay to put a semicolon to the end of the statement, you actually don't need to do this since your command has only one statement to execute.

Hope these help.
   
Comments
Mike Mattera (Mike Mattera) 28-Apr-21 1:44am
   
Ok now the problem is if make is left blank then model will start with an OR
Mike Mattera (Mike Mattera) 28-Apr-21 1:47am
   
also wouldn't each additional statement need a comma?
Wendelius 28-Apr-21 12:04pm
   
No, in SQL conditions are not separated using a comma
Wendelius 28-Apr-21 12:04pm
   
You need to control when to put OR's or AND's into the statement. An easy way is to use a simple boolean variable indicating if first condition is already applied or not. Based on the variable value you then add OR or AND between the conditions.
If you're building the query string, you can do it in a stored procedure.

then you can use conditionals and CASE statements, as appropriate, to handle your empty string and NULL values. The DBA that uses my Generic Reporting web pages handles user input (builds filters) on a routine basis. Also, you can make good use of ISNULL() and match values in tables to input values to your SP.

Cleverly done, it can even be done with "LIKE '%'" to get unfiltered results for appropriate filters although if you build the string and just skip them performance will be better. If the table's don't get very large then you can cheap-out and use the wild-card LIKE.


   
Comments
Richard Deeming 29-Apr-21 4:28am
   
The down-side of that approach is that SQL Server often can't choose a query plan that works well for all combinations of values.
Optional Parameters and Missing Index Requests - Brent Ozar Unlimited®[^]
W Balboos, GHB 29-Apr-21 8:05am
   
I wasn't even going near indices. Just the simple coding with a bunch of like clauses that may or may not have content along with the %'s vs. building the query piece-by-piece and just not adding the WHERE bits that aren't used.

That second path became necessary for one of our queries, indices and all, but it was heavily but necessarily over-stuffed with optional user filters. Even then, it took a while for the tables to grow large enough for it to show (i.e., a timeout). It's been preemptively applied to a few sister queries.

Mostly, tuning is done with indices and such. My boss (DBA) knows the inner workings and fixes these. Mainly, there's a boiler-plate root design because we most of this is done by the two of us - SQL mainly by him - and productivity takes a back seat to performance at times - since no one else, here, can do this.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900