CODEWITHSUNDEEP
c#sql
MRDRAG
MRDRAG

Reputation: 65

C# and SQL dynamic where clause

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.

Upvotes: 1

Views: 1217

Answers (1)

Christopher Vickers
Christopher Vickers

Reputation: 1953

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.

Upvotes: 2

Related Questions