C#和SQL动态where子句

Question

我有一个winform，其中我有许多文本框和组合框。用户将数据填充到我将其作为where子句添加到sql查询中所需的每个文本框中。

在检查用户是否添加或选择了一个项目后，我尝试创建一个列表。我需要知道如何将此列表添加到我的查询中。

不带列表的查询：

 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);

此查询工作正常，但我必须填充所有控件。我需要知道如何使条件，如果用户只填写框。

Answer 1

我不确定这是否是最好的答案，但它会奏效。我解决这个问题的方法是使用C#动态创建搜索字符串。如果搜索条件不是null或"“，例如：

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.

我也不会直接插入值，因为您可能会受到SQL注入攻击。

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

您将需要一些代码来删除搜索字符串末尾的任何尾随" and“或" or”，如果没有使用搜索字符串，您还需要删除开头的"WHERE“。

删除最后一个'AND‘或' or’的代码如下：

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);

不确定这是否是最好的解决方案，但它对我有效。

我已经根据评论进行了修改，我还更改了AddWithValue，即使res_from为空也可以工作，否则您将得到一个错误。