多个可选 WHERE 参数

我想为一组 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;
}

我会将 where 子句分开并根据需要附加到它后面。

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

然后您必须执行相同的操作将参数添加到命令中：

if (textBoxGUID.Text.Length > 0) command.Parameters.AddWithValue("@itemguid", textBoxGUID.Text);
if (textBoxBatchID.Text.Length > 0) command.Parameters.AddWithValue("@batchid", textBoxBatchID.Text);
if (textBoxAccount.Text.Length > 0) command.Parameters.AddWithValue("@account", textBoxAccount.Text);

这是更多的代码，但它允许您准确搜索提供的参数。