Programatically searching sharepoint

Scenario:
You want to write custom code to return the results of a keyword query

public ResultTableCollection Search(string keyword) {

KeywordQuery query = new KeywordQuery(ServerContext.Current);
query.QueryText = keyword;
return query.Execute();
}

This code creates an instance of the KeywordQuery class. This class allows you to search SharePoint for existing keywords. You simply set the QueryText property to a keyword query and call the Execute method.

If you want to use a SQL query, you must use the FullTextSqlQuery class.

using (SPSite site = new SPSite("http://localhost")){

          using( SPWeb web = site.OpenWeb()){

           FullTextSqlQuery query = new FullTextSqlQuery(site);

           query.QueryText =

           "Select Title, Rank, Path from Scope() where freetext('Search Keyword') AND site='http://moss2007' ORDER BY Rank desc";

             query.RowLimit = 100;
             query.ResultTypes = ResultType.RelevantResults;

             ResultTableCollection results = query.Execute();
             ResultTable result = results[ResultType.RelevantResults];

            while (result.Read()){
                 Console.WriteLine(result[0].ToString() + ", " + result[1].ToString() + ", " + result[2].ToString());
             }
       }
}