I recently had to provide full text searching ability to the Atlanta Symphony website I co-created with Tribal Chicken. I came across this article on setting up full text searching on a database. Since I already use NHibernate with the Atlanta Symphony website I didn't want to have to introduce DAO so I played around with the CreateSqlQuery function of the NHibernate Session object.
As a simple example my table is the events table, it describes an event with a title, description and other fields not pertinent to this disucssion.
So from the database we have the EVENTS table, from my hibernate mapping files I have CalendarEvent which represents 1 row in the EVENTS table. For my query I want to pass in a search string such as "conductor" or "conductor and beethoven" and have list of CalendarEvents returned.
My code ended up being :
session.CreateSQLQuery("Select DISTINCT {calendarEvent.*} from EVENTS {calendarEvent} where Contains(calendarEvent.ProgramDescription, :searchString) or Contains(calendarEvent.title, :searchString)", "calendarEvent", typeof(CalendarEvent)).SetString("searchString", searchString).List();
The beauty if this is a I get to use a sql server feature but still keep NHibernate.
The table is EVENTS which has been aliased to calendarEvent. The columns I'm searching on are ProgramDescription and title, these are the COLUMN names of the table, not properties of the CalendarEvent object I've declared for NHibernate to use.
to seee a quick and dirty example in action see :
http://72.3.196.129/test/testdbsearch.aspx
try
spano and conductor
spano or conductor
to see it work.
