动态扩展SQL语句页搜索

Question

在我的模型中，我有一个字符串txtSearche，它的值来自文本框，如下所示：

“大家好，朋友们”

如何动态地为每个单词添加WHERE text LIKE '%Text%'来编写我的语句？大概有3次：

WHERE Text LIKE '%@Text%'";

这是我的代码：

string[] wordsFromTxtSearche = txtSearche.Split(' ');

SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = @"SELECT * "
                  + " FROM ForumThread "
                  + " WHERE Text LIKE '%@Text%'";
cmd.Parameters.Add(new SqlParameter("@Text", txtSearche));

我想我需要借助For循环来做这件事，但我不知道怎么做。请帮帮我

Answer 1

看看这个能不能用..。完全未经测试:)

SqlCommand cmd = new SqlCommand(); 
cmd.Connection = connection; 
cmd.CommandType = System.Data.CommandType.Text; 
string sql = "SELECT * FROM ForumThread WHERE ";
// assuming you have at least 1 item always in wordsFromTxtSearche
int count = 1;
foreach (string word in wordsFromTxtSearche)
{
    if (count > 1) sql += " AND ";
    sql += "Text LIKE @Text" + count.ToString();
    cmd.Parameters.Add(new SqlParameter("@Text" + count.ToString(),
        string.Format("%{0}%", word)));
    count++;
}
cmd.CommandText = sql;

Answer 2

SQL不会插入字符串中的参数，您可以使用linq清理一些杂乱的循环代码。

string[] words = txtSearche.Split(' ', StringSplitOption.RemoveEmptyEntries);
string[] paramNames = Enumerable.Range(1, words.Length)
    .Select(i => "p" + i)
    .ToArray();
string likeClause = string.Join("AND ",
     paramNames.Select(name => "col like '%' + " + name + " + '%'");
SqlParmeter[] sqlParams = Enumerable.Range(1, words.Length)
    .Select(i => new SqlParameter(paramNames[i], words[i]))
    .ToArray();

SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = @"SELECT * FROM ForumThread WHERE " + likeClause;
cmd.Parameters.AddRange(sqlParams);

无论如何，请不要使用like来实现论坛搜索，而要使用full text search。

Answer 3

类似于：

string command = @"SELECT * FROM ForumThread where ";
bool first = false;

foreach (string word in words)
{
   if (first)
       command += " and ";
   else
       first = true;

   command += " Text like '%" + word + "%' ";
}

cmd.CommandText = command;

如果你想坚持使用参数，你必须创建一个方案来生成一个唯一的参数，可能是这样的：

string command = @"SELECT * FROM ForumThread where ";
bool first = false;

for(int i = 0, len = words.Length; i < len; i++)
{
   string word = words[i];
   if (first)
       command += " and ";
   else
       first = true;

   command += " Text like @param" + i.ToString() + " ";
   cmd.Parameters.Add("@param" + i.ToString(), "%" + words[i] + "%");
}

cmd.CommandText = command;

HTH。