Dynamically extend SQL statement - page search

Question

In my model I have a string txtSearche with the value coming from a textbox like :

"hello all friends"

How can I write my statement dynamic adding WHERE text LIKE '%Text%' for each word additional? something like 3 times:

WHERE Text LIKE '%@Text%'";

This is my code:

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

I suppose I need to do it with help of For loop but i don't know how. please help me

Answer 1

Something like:

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;

If you wanted to stick to parameters, you have to create a scheme to generating a unique param, maybe something like:

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.

Answer 2

See if this works... totally untested :)

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 3

SQL won't interpolate parameters in strings, and you can use linq to clean up some messy looping code.

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

For what its worth, don't use like to implement a forum search, use full text search instead.