Network Programming in .NET

If you don’t have Free Text Search for your SQL server database, then you can use Lucene.NET to run free text searches on DataSets.

First you have to build up a Full text index (on disk), based on an SQL statement:

        var strIndexRoot = Server.MapPath("FT");
        if (System.IO.File.Exists(strIndexRoot + @"\write.lock"))
        {
            System.IO.File.Delete(strIndexRoot + @"\write.lock");
        }
        Directory directory = FSDirectory.GetDirectory(strIndexRoot);
        Analyzer analyzer = new StandardAnalyzer();
        IndexWriter writer = new IndexWriter(directory, analyzer);        
        var strSQL = "select id,text from yourTable";       
        System.Data.DataSet ds = ExecuteDataSet(strSQL);
        foreach (DataRow dr in ds.Tables["sql"].Rows)
        {
            Document doc = new Document();
            doc.Add(new Field("id", dr["id"].ToString() , Field.Store.YES, Field.Index.NO));
            doc.Add(new Field("postBody", dr["text"].ToString(), Field.Store.YES, Field.Index.ANALYZED));
            writer.AddDocument(doc);            
        }
        writer.Optimize();
        writer.Flush();
        writer.Close();

You need to have a folder called “FT” (Free text) as a subfolder of your ASP.NET root folder.
I’ve omitted the code for “ExecuteDataSet”, it’s just a call to the database with an SQL command.

The FT folder will have the files:
segments.gen
segments_2
_0.cfs

Once the Free Text Index is generated.

Then to Query the free Text Index, I used the code:

       var strIndexRoot = Server.MapPath("FT");        
        if (IO.File.Exists(strIndexRoot + @"\write.lock"))
        {
            IO.File.Delete(strIndexRoot + @"\write.lock");
        }
        Directory directory = FSDirectory.GetDirectory(strIndexRoot);
        Analyzer analyzer = new StandardAnalyzer();
        IndexWriter writer = new IndexWriter(directory, analyzer);
        QueryParser parser = new QueryParser("postBody", analyzer);
        Query query = parser.Parse("User Query goes here");
        //Setup searcher
        IndexSearcher searcher = new IndexSearcher(directory);
        //Do the search
        Hits hits = searcher.Search(query);
        int hitNumber = hits.Length();        
        for (int i = 0; i < hitNumber; i++)
        {
            Document doc = hits.Doc(i);
            var id = doc.Get("id");
            var text = doc.Get("postBody");
        }
        searcher.Close();
        directory.Close();