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? — mjwills
– mjwills, Commented Aug 13, 2018 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
– Crowcoder, Commented Aug 13, 2018 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
– mjwills, Commented Aug 13, 2018 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.
– Alex K., Commented Aug 13, 2018 at 10:40

Christopher Vickers · Accepted Answer · 2018-08-13 11:02:21Z

2

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.

edited Aug 13, 2018 at 11:02

answered Aug 13, 2018 at 10:42

Christopher Vickers

1,9931 gold badge16 silver badges20 bronze badges

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

1 Comment

Christopher Vickers Over a year ago

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.

Collectives™ on Stack Overflow

C# and SQL dynamic where clause

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related