Native SQL Queries

» Home » ActiveRecord » Documentation » ActiveRecord trunk Documentation » User's Guide » Native SQL Queries

There is a couple of scenarios in which HQL doesn't provide for all features you need. For example if you want to intersec three or more tables adding them to the "from" clause. Sometimes HQL complies with your requirements (most of them) but you want to get a fine grained sql statement control.

Teaching Native SQL Queries is out of the scope of this article. You should consult the NHibernate documentation on Native SQL Queries.

CreateSQLQuery

CreateSQLQuery is used in cases where the query would be a native SQL query.

Here are an example that involves two models, one to store words and another to store synonyms.


using Castle.ActiveRecord.Queries;
using Castle.ActiveRecord;
using Castle.ActiveRecord.Framework;
using System.Collections;
using Iesi.Collections;
using NHibernate.Expression;
using NHibernate;

[ActiveRecord]
public class Word : ActiveRecordBase<Word>
{
    private int _id;
    private string _key;

    public Word() {}

    [PrimaryKey]
    private int Id
    {
        get { return _id; }
        set { _id = value; }
    }

    [Property]
    public string Key
    {
        get { return _key; }
        set { _key = value; }
    }

    public IList<Word> FindSynonyms()
    {
            string query = @"
select synonym.key
from word, synonym
where 
    synonym.word = word.id and
    word.key = :key";
            
            return (IList<Word>) ActiveRecordMediator<Word>.Execute(
                delegate(ISession session, object instance)
                {
                    return session.CreateSQLQuery(query, "synonym", typeof(Word))
                        .SetParameter("key", this.Key)
                        .SetMaxResult(10)
                        .List<Word>();
                }, this);
    }
}

Here you have a more complex query. There is a Menu model to store menu items, there is a MenuItemTranslation model to store items translations and the last model is the Language one, to store languages.


private const string translationQuery = @"
select menuitemtranslation.translation
from  menu, language, menuitemtranslation
where 
    menuitemtranslation.menu = :menuid and
    menuitemtranslation.lang = language.id and
    language.englishname = :lang
";

public string FindTranslation(string lang)
{
    if ((lang == null) || (lang.Length == 0)) return Description;
    
    IList<string> translations = (IList<string>) ActiveRecordMediator<MenuItemTranslation>.Execute(
        delegate(Isession session, object instance)
        {
            return session.CreateSQLQuery(translationQuery)
                .SetParameter("menuid", this.Id)
                .SetParameter("lang", lang)
                .SetMaxResults(1)
                .IList<string>();
        }, null);
    if ((translations != null) && (translations.Count > 0))
    {
        return translations[0];
    }
    else
    {
        return Description; 
    }
}

Home

Castle Stronghold

Native SQL Queries

CreateSQLQuery