Create Lucene Index in C# for given SQL Stored Procedure

   Tweet

Lucene is a free/open source full-featured text search engine written in java and ported to C# and other popular languages.
Lucene can index any text-based information and then can be queried to retrive the information on various search criteria.
It provides a basic framework from where we can build a layer to implement full-featured search.

Document - The Document class represents a document in Lucene which equalizes a row in sql server.
Field - The Field class represents a section of a Document which equalizes a column and its value in sql server.
Analyzer - The Analyzer class is an abstract class that used to provide an interface that will take a Document and turn it into tokens that can be indexed. There are several useful implementations of this class but the most commonly used is the StandardAnalyzer class.
IndexWriter - The IndexWriter class is used to create indexes.

Creating Lucene Index in C# is simple.
The below function create Lucene Index for the given sql server query on specific path specified.

Namespace Used:

	using System;
	using Lucene.Net.Analysis.Standard;
	using Lucene.Net.Index;
	using Lucene.Net.Documents;
	using System.Data;
	using System.Data.SqlClient;

CreateIndex method creates lucene index on given path for given sql procedure with connection parameters.

	public bool CreateIndex(string ProcedureName,string ConnectionString,string Indexpath)
        {
            bool ret=false;
            try
            {
                if (System.IO.Directory.Exists(Indexpath) == false)
                {
                    System.IO.Directory.CreateDirectory(Indexpath);
                }

                IndexWriter writer = new IndexWriter(Indexpath, new StandardAnalyzer(), true);
                IndexDocs(writer, ProcedureName, ConnectionString);
                writer.Optimize();
                writer.Close();
            }
            catch (Exception ex)
            {
                string errorstr = ex.Message;
            }

            return ret;
        }

IndexDocs function executes the given stored procedure with given connection parameters on sql server. writer.AddDocument method is used to add lucene document for each datarow on the resultant dataset.

        private void IndexDocs(IndexWriter writer, string StoredProcedureName,string ConnectionString)
        {
            DataSet ds = new DataSet();
            using (SqlConnection sqlCon = new SqlConnection(ConnectionString))
            {
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.Connection = sqlCon;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = StoredProcedureName;

                SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
                sqlAdap.Fill(ds);
            }
            if (ds.Tables[0] != null)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        writer.AddDocument(IndexDocument(dr, dt.Columns));
                    }
                }
            }
        }

IndexDocument function creates a new Lucene document for the given datarow.

        private static Document IndexDocument(DataRow dr, DataColumnCollection dcc)
        {
            Document doc = new Document();
            foreach (DataColumn dc in dcc)
            {
                doc.Add(new Field(dc.ColumnName, dr[dc.ColumnName].ToString() , Field.Store.YES, Field.Index.TOKENIZED));
            }
            return doc;
        }

Using these function to create lucene index

	string dsn="Initial Catalog=dbname;Data Source=srvrname;User ID=usrid;password=pwd;";
	CreateIndex("procedurename", dsn,"c:\\temp\\index\\");

   Tweet