http://ayende.com/Blog/archive/2006/10/01/UsingSQLFunctionsInNHibernate.aspx
On Fri, Sep 19, 2008 at 2:57 PM, burkhard_m <[EMAIL PROTECTED]>wrote: > > Hi everyone! > > I'm currently working on a small merchandise management tool and have > to implement a full-text search for the article descriptions.Our > project is based on NHibernate & ActiveRecord as well as MSSQL Server > 2005 and we would prefer to use the indexing capability of the SQL > Server rather than implementing NHibernate.Search with Lucene.NET. > > I have read about several suggestions at Google Groups including > Ayende Rahien's: > "You need to create an derived dialect and register the contains > function, if > you want to use HQL. Or, you can create an ICritertion implementation > that will deal with this." > (see > http://groups.google.com/group/nhusers/browse_thread/thread/e9745dafd370db88 > ) > > I tried hard for several days but I'm stuck. I have no idea how to > implement this correctly. > All I get is a QueryException telling me that I have a "Incorrect > query syntax". > > Yes, the Full text indexing is activated for the property 'Text'. > The SQL-Query works: > SELECT * FROM [dbo].[T_Descriptions] > WHERE CONTAINS(Text, ' "T*" ') > > The HQL-Query "FROM Description d WHERE d.Text LIKE 'T%'" also works > fine. > > Would anyone help me, please? > Maybe you could tell me what I have done wrong from the following > code? > > Thanks a lot! > > Best regards, > Martin > > // The config section > > <activerecord isWeb="false"> > <config> > <add key="hibernate.connection.driver_class" > value="NHibernate.Driver.SqlClientDriver" /> > <add key="hibernate.dialect" value="SQLServerDialectWithFTS, > <AssemblyName>" /> > <add key="hibernate.connection.provider" > value="NHibernate.Connection.DriverConnectionProvider" /> > <add key="hibernate.max_fetch_depth" value="3"></add> > <add key="hibernate.connection.connection_string" > value="ConnectionString = ${ourConnectionString}" /> > </config> > </activerecord> > > // The model > > [ActiveRecord("[T_Descriptions]")] > [Serializable] > public class Description : ActiveRecordBase<Description> > { > protected Description() {} > > protected Description(string text) : this() > { > Text = text; > } > > [PrimaryKey] > public long Id { get; set; } > > [Property] > public string Text { get; set; } > } > > // The customized dialect > > public class SQLServerDialectWithFTS : MsSql2005Dialect > { > public SQLServerDialectWithFTS() > { > RegisterFunction("CONTAINS", new > StandardSQLFunction("CONTAINS", NHibernateUtil.String)); > } > } > > > // The search > > IConfigurationSource source = ActiveRecordSectionHandler.Instance; > > > Castle.ActiveRecord.ActiveRecordStarter.Initialize(typeof(Description).Assembly, > source); > > using(SessionScope scope = new SessionScope()) > { > var sq = new SimpleQuery<Description>(typeof (Description), > "FROM Description d WHERE > CONTAINS(d.Text, ' \"T*\" ')"); > var res = sq.Execute(); > } > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Castle Project Users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/castle-project-users?hl=en -~----------~----~----~----~------~----~------~--~---
