多个可选的WHERE参数

Question

我想为一组SQL查询创建一个过滤器，以便用户最多可以通过三个值过滤SQL
SELECT。用户界面具有三个文本框，每个文本框都将绑定到SQL表中的列名。用户可以通过这些文本框提供一个，两个或三个条件。

到目前为止，这就是我所拥有的。我知道这些if(textbox...声明将不起作用，但是我找不到解决该问题的方法。（使用"SELECT TOP 10 primaryFile FROM dbo.basket WHERE (basket.itemGuid = @itemguid) AND (basket.batchid = @batchid) AND (basket.account = @account"不会返回任何结果。

private List<string> GetSnippets()
{
    List<string> snippets = new List<string>();

    string connectionString = @"SNIP";
    //string sql = @"SELECT TOP 10 primaryFile FROM dbo.basket WHERE";
    string sql = @"SELECT TOP 10 primaryFile FROM dbo.basket WHERE (basket.itemGuid = @itemguid) AND (basket.batchid = @batchid) AND (basket.account = @account)";
    //if (textBoxGUID.Text.Length > 0) sql += " basket.itemGuid = @itemguid";
    //if (textBoxBatchID.Text.Length > 0) sql += " basket.batchid = @batchid";
    //if (textBoxAccount.Text.Length > 0) sql += " basket.account = @account";

    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@itemguid", textBoxGUID.Text);
        command.Parameters.AddWithValue("@batchid", textBoxBatchID.Text);
        command.Parameters.AddWithValue("@account", textBoxAccount.Text);

        try
        {
            connection.Open();
            if (connection.State == ConnectionState.Open)
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        snippets.Add((string)reader["primaryFile"]);
                        Console.WriteLine(reader["primaryFile"]);
                    }
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
    }
    return snippets;
}

Answer 1

如果我正确遵循，您想根据所有填充变量过滤结果，以处理未填充变量，则需要OR在变量为空（NULL）时添加for ：

WHERE (basket.itemGuid = @itemguid OR @itemguid IS NULL)
  AND (basket.batchid = @batchid OR @batchid IS NULL) 
  AND (basket.account = @account OR @account IS NULL)