C# and SQL dynamic where clause

Question

I have a winform where I have many textboxes and comboboxes. The user fills the data into every text box that I need to add it to my sql query as a where clause.

I have tried to create a list after checking if the user added or selected an item. I need to know how to add this list to my query.

Query without the list:

 int val;
 int val2;
 Int32.TryParse(category.SelectedValue.ToString(), out val);
 Int32.TryParse(type.SelectedValue.ToString(), out val2);

 SqlConnection conn = new SqlConnection();
 conn.ConnectionString = "Data Source=ANTHONY-LAP\\SQLEXPRESS;Initial Catalog=Tenant Management;Integrated Security=True";
 conn.Open();
 string search_channel = @"select c.[Name],cate.[Channel Category],tp.type,st.Status,c.Surface,
                         g.GOVERNATOR + ' ' + d.District + ' ' + cit.City + ' ' as [Address],
                         c.[Short term Price per night] as [Short term monthly amount],
                         c.[Long term price per month] as [Long term monthly amount],c.[Selling Price]
                         from[dbo].[Channel] c
                         inner join[dbo].[Governator] g on c.[Governator ID] = g.ID
                         inner join[dbo].[District] d on c.[District ID] = d.ID
                         inner join[dbo].[City] cit on c.[City ID] = cit.id
                         inner join[dbo].[Channel_Category] cate on c.[Channel Category ID] = cate.ID
                         inner join[dbo].[Channel_Type] tp on c.[Channel Type] = tp.id
                         inner join[dbo].[Channel_Status] st on c.[Channel Status] = st.ID
                         inner join[dbo].[Reservations] r on c.[ID] = r.[Channel ID]
                         where r.[Actual Date in] < " + res_from.Value.ToString("yyyy/MM/dd") + " and r.[Actual Date out] > " + res_to.Value.ToString("yyyy/MM/dd") +
                         "and c.[Channel Status]!= '2' and c.[Channel Category ID] =" + val + "and c.[Channel Type] ="
                          + val2 + " and c.Surface =" + Convert.ToInt32(surf.Text) +
                          " and c.[Short term Price per night]= " + Decimal.Parse(shrtrntprice.Text) +
                          "and c.[Long term price per month]=" + Decimal.Parse(lngrent.Text) + "and c.[Selling Price] =" + Decimal.Parse(sellprc.Text);

This query is working correctly, but I must fill all the controls. I need to know how to make the condition if the user fill the boxes only.

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Aug 13 '18 at 10:38
Your WHERE clause can use `ISNULL(@param, [column name])` constructs to filter on the parameter if a value is given or the column value (effectively no filter) if the parameter is null. — Crowcoder, Aug 13 '18 at 10:39
Start the `WHERE` clause where `1 = 1`. Then, check each parameter - if it is set appropriately and you need to add a condition then `search_channel += " AND your_condition_here"`. See my duplicate also though, since you don't want to be passing parameters the way you are - you must use **parameters** instead. _And definitely pass dates as **dates** in your parameters, don't format them as strings._ — mjwills, Aug 13 '18 at 10:40
You should use SqlParameters with this, currently as your dates are unquoted your probably doing a numeric comparison base on division between date parts. — Alex K., Aug 13 '18 at 10:40

Christopher Vickers · Accepted Answer · 2018-08-13T11:02:21.253

I'm not sure if this is the best answer but it will work. The way I got round this is to dynamically create the search string using C#. If the search criteria is anything but null or "" so for example:

if(res_from != null && res_from != "")
   search_channel += "r.[Actual Date in] < @VALUE AND"; //You will need to add an AND/OR at the end and remove the trailing ones.

I would also not insert values directly as you open yourself to SQL injection attacks.

cmd.Parameters.AddWithValue("@VALUE", (res_from == null) ? res_from.Value.ToString("yyyy/MM/dd) : "");

You will need some code to remove any trailing "AND" or "OR" at the end of your search string and if no search string are used you will also need to remove the "WHERE" at the start.

The code to remove the last 'AND' or 'OR' would be something like:

if(search_channel.EndsWith("WHERE"))
    search_channel = search_channel.Substring(0, myString.Length-5);
if(search_channel.EndsWith("AND"))
    search_channel = search_channel.Substring(0, myString.Length-3);
if(search_channel.EndsWith("OR"))
    search_channel = search_channel.Substring(0, myString.Length-2);

Not sure if this is the best solution but its worked for me.

I have amended based on the comment and I have also changed the AddWithValue to work even if the res_from is null, otherwise you will get an error.

Changed based on your comment. My solution still isn't the full answer but the OP should be able to put together the rest based on what I have added. — Christopher Vickers, Aug 13 '18 at 10:56

C# and SQL dynamic where clause

1 Answers1