list search in C#

I have a list in my C# web-application. this list is fill with keywords fetch from database. Now i want to search in the list , as i have mulitliple keyword search.

if a person type in blue so all the record matching blue are fetched into the list. now when he type in 2nd keyword suppose yellow so the list should be filtered and it must contain only those records which having blue and yellow and same is for 3rd and fourth key word.

here is the code now what i have to do next ?

public override List<ThumbNail> GetSearchResults(string search1, string path)
        {
  List<ThumbNail> mylist = new List<ThumbNail>();
       if (search1.Length - 1 == search1.LastIndexOf(","))
       search1 = search.Remove(search1.Length - 1);     
  List<string> search = new List<string>(search1.Split(','));
    for(int i=0; i <search1.Count;i++)

{
      OleDbCommand sqlcmdCommand1 = 
      new OleDbCommand("select Name,File,keyword from table1 where keyword like @searchone", mycon);

 sqlcmdCommand1.Parameters.AddWithValue("searchone", search[i]);
 sqlcmdCommand1.CommandType = CommandType.Text;
 OleDbDataReader sdaResult = sqlcmdCommand1.ExecuteReader();
 whil开发者_JAVA技巧e (sdaResult.Read())
 {
  mytable thmb = new Thumbl();
  thmb.Name = sdaResult.GetString(0);
  thmbN.File = sdaResult.GetString(1);
  thmb.keyword = sdaResult.IsDBNull(3) ? "" : sdaResult.GetString(6);
  mylist.Add(thmb);
 }

} sdaResult.Close();

return mylist;
}

public class Thumbnail
{

        public int Id { get; set; }
        public string Name { get; set; }
        public string File { get; set; }
        public string keyword { get; set; }
        public string path { get; set; }
   }

this action is performed when user type in first keyword. and all the records matching first keyword are now in mylist, now when user type in second keyword so a search must be performed on list which match the first and 2nd keyword and the result is returned.

The DB table look like this:

id   name   keyword  File  Fkey
1     a      yellow  c:/   20
2     a      blue    c:/   20
3     a      Pinky   c:/   20
4     b      orange  c:/   21
5     b      Redish  c:/   21

EDIT: After you updated your question, my answer no longer applies, since your table structure is different than what I expected. My answer would work if your table looked like this:

id   name   keywords            File 
1     a     yellow blue pinky   c:/  
2     b     orange reddish      c:/  

OLD ANSWER: First, you need to select the keywords as well, so that you can do further filtering.

thmb.Keywords = sdaResult.GetString(someIndex);

Then, you can use LINQ to create a filtered version of your list...

var filtered = mylist.Where(thmb => thmb.Keywords.Contains(search2));

...which you can filter further, as soon as new search keywords are entered.

var filtered = filtered.Where(thmb => thmb.Keywords.Contains(search3));

filtered is an IEnumerable. If you need a list, apply .ToList() to it.

NEW ANSWER: Basically, what you need to do with your table structure is to:

1. Make a second fetch from the database, making a list of names that contain the second keyword and

2. remove all list entries of your first list that are not contained in the second list. For example, if your list of names containing the second keyword is a List<string> called narrowingSearch, then you could do

   var filteredList = mylist.Where(thmb => narrowingSearch.Contains(thmb.Name));
   

EDIT: Now that you have a common FK, you should be able to do this by using a more advanced SQL, something along the lines of:

SELECT name, file
  FROM table1
 WHERE keyword like @search0
   AND fk IN (SELECT fk FROM table1 WHERE keyword LIKE @search1)
   AND fk IN (SELECT fk FROM table1 WHERE keyword LIKE @search2)
   ...

You can construct this SQL in a loop like this:

string[] search = search1.Split(',');

OleDbCommand cmd = new OleDbCommand("SELECT name, file FROM table1 WHERE keyword LIKE @search0", mycon);
cmd.Parameters.AddWithValue("@search0", search[0]);

for (int i = 1; i < search.Length; i++) {
    cmd.CommandText += " AND fk IN (SELECT fk FROM table1 WHERE keyword LIKE @search" + i.ToString() + ")";
    cmd.Parameters.AddWithValue("@search" + i.ToString(), search[i]);
}